Need Help with pagination in php

[westernbees]

Hey guys, i am new to php ..I really needed your help with pagination in php .The issue is when i look at the out put, i dont see any thing.. just blank screen. Database and other details are correct. I rechecked it

Here is my code :

<?php

$host="xxxxxx"; // Host name 
$username="xxx_username"; // Mysql username 
$password="xxx_password"; // Mysql password 
$db_name="xxx_databasename"; // Database name 
// Connect to server and select database.
mysql_connect("$host", "$username", "$password")or die("cannot connect"); 
mysql_select_db("$db_name")or die("cannot select DB");

$per_page = 6;
$pages_query = mysql_query("SELECT COUNT (*) FROM 'xxx_tablename'");
$pages = ceil(mysql_result($pages_query, 0) / $per_page);

$page = (isset($_GET['page'])) ? (int)$_GET['page'] : 1;
$start = ($page - 1) * $per_page;

$query = mysql_query("SELECT * FROM 'members_list' LIMIT $start, $per_page");
while ($query_row = mysql_fetch_assoc($query)){
echo '<table id="table-3">';
    echo '<thead>';
        echo '<th> Name : </th>';
        echo '<th> Address : </th>';
        echo '<th>Zip Code : </th>';
    echo '</thead>';
    echo '<tbody>';
        echo '<tr>';

            echo '<td>', $query_row['name'], '</td>';
            echo '<td>', $query_row['address'] ,'</td>';
            echo '<td>', $query_row['zipcode'], '</td>';
        echo '</tr>';
    echo '</tbody> ';
echo '</table>';
}
?>

<?php
// close MySQL connection 
mysql_close();
?>    

Didnt complete it because with this much code forget pagination but atleast the data should be visible..

any one ?

[diafol]

You seem to be creating a new table on every iteration - perhaps it would be better to just add a row on every iteration of the while loop. OK, quite a few things. Here's refactored code - it contains html formatting (\n\t) - take them out if you want.

$host="xxxxxx"; // Host name 
$username="xxx_username"; // Mysql username 
$password="xxx_password"; // Mysql password 
$db_name="xxx_databasename"; // Database name 
// Connect to server and select database.
mysql_connect("$host", "$username", "$password")or die("cannot connect"); 
mysql_select_db("$db_name")or die("cannot select DB");

$per_page = 6;

$pages_query = mysql_query("SELECT COUNT (*) FROM 'xxx_tablename'");
$pages = ceil(mysql_num_rows($pages_query) / $per_page);

//pre page - hold putative page number from input (or = 1)
$pre_page = (isset($_GET['page'])) ? (int)$_GET['page'] : 1;

//set proper page number - ensure in range
if($pre_page < 1){
    $page = 1;
}elseif($pre_page > $pages){
    $page = $pages;
}else{
    $page = $pre_page;  
}

$start = ($page - 1) * $per_page;
$query = mysql_query("SELECT * FROM `members_list` LIMIT $start, $per_page");
$output = "<table id='table-3'>\n\t<thead>\n\t\t<tr>\n\t\t\t<th> Name : </th>\n\t\t\t<th> Address : </th>\n\t\t\t<th>Zip Code : </th>\n\t\t</tr>\n\t</thead>\n\t<tbody>"; 

while ($query_row = mysql_fetch_assoc($query)){
    $output .= "\n\t\t<tr>\n\t\t\t<td>{$query_row['name']}</td>\n\t\t\t<td>{$query_row['address']}</td>\n\t\t\t<td>{$query_row['zipcode']}</td>\n\t\t</tr>";
}
$output .= "\n\t</tbody>\n</table>";

echo $output;
// close MySQL connection 
mysql_close();

I think your main problem was that you had single quotes around the table name. For this use `backticks` instead

[westernbees]

can you please give me an example here .

[diafol]

Sorry edited my post while you posted. See above.

[westernbees]

@diafol Thanks for the quick reply.. now i can see the headings.. but cannot see the data which is stored in the database. any thing qrong with the query ?

[diafol]

No this again:

SELECT COUNT (*) FROM 'xxx_tablename'

should be

SELECT COUNT (*) AS cnt FROM `xxx_tablename`

AND

$pages = ceil(mysql_num_rows($pages_query) / $per_page);

COULD BE

$data = mysql_fetch_assoc($pages_query);
$cnt = $data['cnt'];
$pages = ceil($cnt / $per_page);

[westernbees]

Didnt work.. i used this tutorial : http://www.youtube.com/watch?v=0royXp98uks

[diafol]

SO what's not working?

echo out bits of code as you go along, e.g.

echo $cnt;

etc. That should give you an idea of the current values / state of your variables.

[westernbees]

Check your inbox

[diafol]

Please no PMs. Everything to the forum. Anyway, looked at the site/code. You've still got single quotes about your table names. Change them to backticks (`).