[SOLVED] Page number mysql problem

[11Tami]

Hello, I have a certain amount of data in a database table.

 

Then I have code that divides the total of all the rows in the database by so many rows per page. So the code knows how many pages total there are.

 

Trouble is, how do I list links for all the individual pages so that the search engines will follow them? If I list the whole code here its such a mess, I won't get any help with it.

 

Know that I have a basic pull from a database using the usual queries like this plus how to get into the database above this with passwords and such.

 

$data = mysql_query("SELECT fieldname FROM table") or die(mysql_error()); 

$rows = mysql_num_rows($data); 

//This is the number of results displayed per page 
$page_rows = 9; 

//This tells how many pages there are
$last = ceil($rows/$page_rows); 

while($info = mysql_fetch_array( $data_p )) 
{  
echo "$info['fieldname']";
}

 

If I could get some suggestions on how to add the page links to the page, I can build the rest in this post with some help.

 

Please get back to me on how to number the pages, thank you very much.

 

[hackerkts]

Try read this http://www.phpfreaks.com/tutorials/73/0.php

[11Tami]

Thats pretty amazing, I already saw that link, but it didn't help. I couldn't adjust it for what I have. But thanks for the help. For instance I don't know what I'm supposed to do with this to populate my database with it. I guess I'm supposed to put the name of my table below instead of "pages" but how do I send it to my database?

 

<?php  
for($i = 1; $i <= 102; $i++){ 
    mysql_query("INSERT INTO pages (title) VALUES ('Item $i')"); 
    echo "Item $i inserted into db <br />"; 
} 
?> 

[11Tami]

Do I even need it? The rows already all have an id sequentially.

[11Tami]

Ok I'll show everybody what I've got in hopes that someone can help me. It is using the above link you gave me. This is the error message the page is giving me.

 

"You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near '-10' at line 1"

 

It seems to be talking about $page_rows = 10;  Because whenever I adjust the row, the above message changes to the different number as well. Here's where I am using the link you gave above.

 

<?php 
// Connects to your Database 
mysql_connect('databaseaddress', 'databasename', 'password') or die(mysql_error()); 
mysql_select_db("databasename") or die(mysql_error()); 

//Here we count the number of results 
//Edit $data to be your query 
$data = mysql_query("SELECT fieldname FROM tablename") or die(mysql_error()); 

$rows = mysql_num_rows($data); 

//How many rows to put on each page 
$page_rows = 10; 

// Figure out the limit for the query based 
// on the current page number. 
$max = 'LIMIT ' .(($total_pages * $page_rows) - $page_rows);

// Figure out the total number of results in DB: 
$total_results = mysql_result(mysql_query("SELECT COUNT(*) as ID FROM tablename"),0); 

// Figure out the total number of pages. Always round up using ceil() 
$total_pages = ceil($total_results / $max); 

// Perform MySQL query on only the current page number's results 
$data_p = mysql_query("SELECT fieldname FROM tablename $max") or die(mysql_error()); 

//This is where you display your query results
while($info = mysql_fetch_array( $data_p )) 
{ 
Print $info['Name'];
echo "$info[fieldname";
} 

echo "<p>";

// Build Page Number Hyperlinks 
echo "Select a Page"; 

// Build Previous Link 
if($page > 1){ 
    $prev = ($page - 1); 
    echo "<a href=\"".$_SERVER['PHP_SELF']."?page=$prev\"><<Previous</a> "; 
} 

for($i = 1; $i <= $total_pages; $i++){ 
    if(($page) == $i){ 
        echo "$i "; 
        } else { 
            echo "<a href=\"".$_SERVER['PHP_SELF']."?page=$i\">$i</a> "; 
    } 
} 

// Build Next Link 
if($page < $total_pages){ 
    $next = ($page + 1); 
    echo "<a href=\"".$_SERVER['PHP_SELF']."?page=$next\">Next>></a>"; 
}
?>

[teng84]

SELECT fieldname FROM tablename $max

 

maybe you need to add the limit clause on that query above

[AndyB]

You need to use LIMIT in your query ...

 

$data_p = mysql_query("SELECT fieldname FROM table LIMIT $max, $pagerows");

[11Tami]

OK that did something, at least this is progressing forward now with some help, I appreciate it very much. Now this is the message I get

"warning, division by 0"

here:

$total_pages = ceil($total_results / $max);

 

Can't figure out how to get it from dividing by 0. I don't know if this is the zero in the code above it is talking about ",0);" at the end of the  $total_results variable.

 

 

Also, the $page variable isn't working at all yet for the page numbering at the bottom of the code because this is how they set it up

here: http://www.phpfreaks.com/tutorials/73/0.php

if(!isset($_GET['page'])){ 
    $page = 1; 
} else { 
    $page = $_GET['page']; 
} 

I didn't add that yet, because I have id's in my table. I didn't know how to incorporate that for the code I am using instead. So $page is still undefined, some suggestions on that would be very helpful.

[11Tami]

I found the problem with this:

 

"warning, division by 0" Its because both of these have each others variables in them. total pages can't ask for $max if $max hasn't been defined yet. Anyone see what variable in these should be changed?

 

// Figure out the total number of pages. Always round up using ceil()

$total_pages = ceil($total_results / $max);

 

// Figure out the limit for the query based

// on the current page number.

$max = 'LIMIT ' .(($total_pages * $page_rows) - $page_rows);

 

[11Tami]

Ok fixed that, getting closer, here's the update. Numbered links are appearing on the bottom of the page now for a total of how many pages there are. And when I click on each different link such as 1 2 etc. the browser address changes to match the number like this: php?page=1 php?page=2 and so on.

 

Problem now is the same database rows are showing up on each page, instead of different rows. I believe thats because as mentioned above $page hasn't been properly defined yet. Anyone have any ideas?

 

<?php 
// Connects to your Database 
mysql_connect('', '', '') or die(mysql_error()); 
mysql_select_db("") or die(mysql_error()); 

//Here we count the number of results 
//Edit $data to be your query 
$data = mysql_query("SELECT fieldname FROM tablename") or die(mysql_error()); 

$rows = mysql_num_rows($data); 

//How many rows to put on each page 
$page_rows = 10; 

// Figure out the total number of results in DB: 
$total_results = mysql_result(mysql_query("SELECT COUNT(*) as ID FROM tablename"),0); 

// Figure out the total number of pages. Always round up using ceil() 
$total_pages = ceil($total_results / $page_rows); 

// Figure out the limit for the query based 
// on the current page number. 
$max = 'LIMIT ' .(($total_pages * $page_rows) - $page_rows);

// Perform MySQL query on only the current page number's results 
$data_p = mysql_query("SELECT fieldname FROM tablename $max") or die(mysql_error()); 

//This is where you display your query results
while($info = mysql_fetch_array( $data_p )) 
{ 

Print $info['Name']; 
echo "$info[fieldname]";
} 

echo "<p>";

// Build Page Number Hyperlinks 
echo "<center>Select a Page<br />"; 

// Build Previous Link 
if($page > 1){ 
    $prev = ($page - 1); 
    echo "<a href=\"".$_SERVER['PHP_SELF']."?page=$prev\"><<Previous</a> "; 
} 

for($i = 1; $i <= $total_pages; $i++){ 
    if(($page) == $i){ 
        echo "$i "; 
        } else { 
            echo "<a href=\"".$_SERVER['PHP_SELF']."?page=$i\">$i</a> "; 
    } 
} 

// Build Next Link 
if($page < $total_pages){ 
    $next = ($page + 1); 
    echo "<a href=\"".$_SERVER['PHP_SELF']."?page=$next\">Next>></a>"; 
}
?>

[11Tami]

I added this to the top of my page and it didn't help. Its supposed to get the number variable out of the browser address so that the code will know what page its on.

 

if(!isset($_GET['page'])){

    $page = 1;

} else {

    $page = $_GET['page'];

}

 

Anyone see what else is wrong with the code?

[AndyB]

Your division by zero is simple.  For some strange reason, you elected to make $max into a string variable (and strings have a numerical value of zero).  Just look over your logic again.  The basics on pagination are LIMIT from and how many

[11Tami]

Your exactly right! Thanks so much for the help! Its working now! Now I can test to see how the search engines handle it, thanks everybody and Andy!!!