problem with retrival sql query in php pager code

Question

0.00/5 (No votes)

See more:

my problem that I want a pager to show 10 record in page and I found this great code at:

Host = "localhost"
user="root"
password=""(Nothing)
 
<?php
    $con = mysql_connect("localhost","root","") or die("Unable to connect");
    $db = mysql_select_db("test",$con) or die("Unable to select DB");
 
    if(isset($_GET['page']))
    {
        $page = $_GET['page'];
    }
    else
    {
        $page = 1;
    }
 
    $sf = ($page-1) * 10;
    $sql = "SELECT * FROM students LIMIT ".$sf.",10";
    $rs = mysql_query($sql,$con);
    //echo $rs;
?>
<html>
    <head>
        <title>Pages</title>
    </head>
    <body>
        <?php
            $sql1 = "SELECT COUNT(name) FROM students";
            $rs1 = mysql_query($sql1,$con);
            $row1 = mysql_fetch_row($rs1);
            $total = $row1[0];
            $tp = ceil($total/10);
 
            for($i = 1; $i <= $tp; $i++)
            {
                echo "<a href='test.php?page=".$i."'>".$i."</a> ";
            }
        ?>
        <table>
            <tr>
                <th>Name</th>
                <th>Phone Number</th>
            </tr>
            <?php
                while($row = mysql_fetch_assoc($rs))
                {
            ?>
            <tr>
                <td><?php echo $row['name']; ?></td>
                <td><?php echo $row['ph_no']; ?></td>
            </tr>
            <?php
                }
            ?>
        </table>
        <?php
            $sql1 = "SELECT COUNT(name) FROM students";
            $rs1 = mysql_query($sql1,$con);
            $row1 = mysql_fetch_row($rs1);
            $total = $row1[0];
            $tp = ceil($total/10);
 
            for($i = 1; $i <= $tp; $i++)
            {
                echo "<a href='test.php?page=".$i."'>".$i."</a> ";
            }
        ?>
    </body>
</html>

when I retrieve table data without condition like in the code("SELECT * FROM students LIMIT ".$sf.",10";) it work but when I add an condition like ("SELECT * FROM students where Instructor-Email=". $_SESSION['ID'] ." OR Instructor-Email IS NULL LIMIT".$sf.",10") it doesn't work I was doubt my query syntax so I download query builder program (flyspeed sql query) and it built the query as:"Select * From students Where (Instructor-Email = '. $_SESSION['ID'] .') Or (Instructor-Email Is Null) LIMIT 0,10" but still didn't work
can you tell me what is the problem? with all my thanks for your efforts

Posted 13-Oct-13 10:20am

Rawan S

Updated 13-Oct-13 10:22am

v3

Add a Solution

Comments

Rawan S 13-Oct-13 16:23pm

see the orignal code at www.phpjabbers.com/php--mysql-select-data-and-split-on-pages-php25.html

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

enhzflep · Answer 1 · 2013-10-13T16:21:00

I'm too tired to compare your code with some that I know works, so I'll have to leave that to you right now.

Disclaimer:
(1) you shouldn't use mysql_*** functions, they've been deprecated - use PDO or mySqli instead. (PDO is more versatile)
(2) plenty of hard-coded stuff here - avoid like the plague in your own production code.
(3) no error checking done

sample-deleteMe.php

PHP

<!doctype html>
<head>
<style>
.heading
{
	color: green;
	font-weight: bold;
}
</style>
</head>
<body>
<?php
	function createDatabase($dbName)
	{
		$sql = "create database ".$dbName;
		mysql_query($sql);
		mysql_select_db($dbName);
		$sql = "CREATE TABLE IF NOT EXISTS `test` (`id` int(11) NOT NULL,`text` varchar(10) NOT NULL) ENGINE=MyISAM DEFAULT CHARSET=latin1;";
		mysql_query($sql);
		
		for ($i=0; $i<20; $i++)
		{
			$sql = "INSERT INTO `$dbName`.`test` (`id`, `text`) VALUES ('$i', 'item $i')";
			mysql_query($sql);
		}
	}
	
	function nukeDatabase($dbName)
	{
		$sql = "DROP DATABASE `$dbName`;";
		mysql_query($sql);
	}

	$host = "localhost";
	$userName="root";
	$password="********";
	$dbName = "cp_qa_test";
 
    $con = mysql_connect($host,$userName,$password) or die("Unable to connect");
	createDatabase($dbName);
	
    $page = 1;
    if(isset($_GET['page']))
        $page = $_GET['page'];
 
	$numPerPage = 4;
    $firstRecordNum = ($page-1) * $numPerPage;
    $sql = "SELECT * FROM test LIMIT $firstRecordNum,$numPerPage";
    $resultSet = mysql_query($sql);

	printf("Showing %d records, starting at record #%d<br>", $numPerPage, $firstRecordNum);
	while ( $curRow = mysql_fetch_row($resultSet) )
	{
		printf("<span class='heading'>ID:</span> %d -- <span class='heading'>Text:</span> %s<br>", $curRow[0], $curRow[1]);
	}
	nukeDatabase($dbName);
?>
</body>
</html>