Pagination (I give up lol)

[Arwen]

I have an avatars database, but I can't seem to figure out how to add the pagination. I found this tutorial, but don't know where to place it?

Here's my code:

Code:

</td>

Great coding, haha, I can't post my code AGAIN! >.<

Alright.............. can anyone explain how to insert that tutorial's code into a php document (i.e. avatars database)? Thanks!

[Blackstone]

Okay, well I'm not quite sure what your code is, seeing as you only have an end for a table cell in your post, but if you used the same exact code as the one you linked to, then somewhere in your file you will have this:

Code:

/*
While will repeat this query and mysql_fect_array allow me array the content
*/
while ($r = mysql_fetch_array($result)){
echo "$r[title]<br>";//Echoes the content
};

That while loop is the loop that displays all your information. All you need to do is replace the echo with what you want displayed for each row in the database table, in your case, for each avatar. You might replace the echo with something like this:

Code:

echo '<td><img src="'.$r['avatarurl'].'" alt="" /></td>';

If you wanted only the avatar to be displayed, and you had started the table before the while loop and ended it after it (If you want to know how to do this, just ask. I can give you instructions on how to make it so that you can control the number of cells in a row of the table as you are displaying it dynamically.) $r is an array, and each time the while loop executes, a new set of information is stored into that array. Think of each execution of the while loop as one of the rows in your database table. The information stored in the $r array is the information from the next row in the table. $r['avatarurl'] refers to a field in the table. If 'avatarurl' is a field in your table, then inside $r['avatarurl'] is stored any information in that row's field named 'avatarurl'. Make sense?

I hope that is what you were confused about. o.o If it's not, or you have more questions, feel free to ask. ^^

[Yuki-chan]

well, it depends on what database you used! you can found some MySQL tutorials about avatar database and all of them include the pagination

or in other case you use Flat File Database?

[Arwen]

I'm using a MySQL database.

I tried doing what you said, Blackstone, but it didn't work. Anyway, since I can't post my code, I uploaded it to my site here

I also tried DDG's pagination tutorial (which works fine on my other pages), but it didn't work either. Can you tell me where to insert it?

Thanks!

[Blackstone]

Is that code that you posted the only thing on your page? If so, you've left out the pagination part, the part that actually dispalys the numbers and such. That should go in after you end the table. If you have all that on one page, then it should work fine.

Could you tell me exactly what isn't working about it? Does if give you any errors or anything?

[Arwen]

Is that code that you posted the only thing on your page? If so, you've left out the pagination part, the part that actually dispalys the numbers and such.

Exactly, becuase I don't know where to insert it. I tried to insert the first part after the $db = mysql_select_db($database,$connection)
or die ("Couldn't select database"); but it gave me an error.

Could not execute query : LIMIT 0, 4.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 'LIMIT 0, 4' at line 1

Here's the whole code with the pagination.

[Blackstone]

Okay, I think I've found the error. You had your initial queries below the variables, but they needed to be above. I think... >.<

I rearranged your file a little bit (and straightened it out... hope you don't mind, it was just easier for me to read that way). Hopefully this will work for you.

Code:

<?php 

// CONNECTION
$user="***"; 
$host="localhost"; 
$password="***"; 
$database="***"; 

$connection = mysql_connect&#40;$host,$user,$password&#41; or die &#40;"couldn't connect to server"&#41;;
$db = mysql_select_db&#40;$database,$connection&#41; or die &#40;"Couldn't select database"&#41;; 


// QUERY
if&#40;isset&#40;$category&#41;&#41;&#123; 
$query = "SELECT * FROM `avatars` WHERE category='$category' ORDER BY id DESC"; 
&#125; else &#123; 
$query = "SELECT * FROM `avatars` ORDER BY id DESC"; 
&#125; 
$result= mysql_query&#40;$query, $connection&#41; or die &#40;"Could not execute query &#58; $query." . mysql_error&#40;&#41;&#41;; 


// VARIABLES
// Start  paging variables 
$screen = $_GET['page'&#93;; 
$PHP_SELF = $_SERVER['PHP_SELF'&#93;; 
$rows_per_page=4;         // number of records per page 
$total_records=mysql_num_rows&#40;$result&#41;; 
$pages = ceil&#40;$total_records / $rows_per_page&#41;;        // calculate number of pages required 

if&#40;!isset&#40;$screen&#41;&#41; &#123;
$screen=0; 
&#125;
$start = $screen * $rows_per_page;        // determine start record 
$query .= "LIMIT $start, $rows_per_page"; 
$result= mysql_query&#40;$query&#41; or die &#40;"Could not execute query &#58; $query." . mysql_error&#40;&#41;&#41;;  

$i=0; // set the column indicator 

// START TABLE
echo "<table border=\"0\" cellpadding=\"0\" cellspacing=\"2\" width=\"100%\">"; 

// DISPLAY CONTENT
while &#40;$row=mysql_fetch_array&#40;$result&#41;&#41; &#123; 
$date=$row['date'&#93;; 
$category=$row['category'&#93;; 
$designer=$row['designer'&#93;; 
$designerurl=$row['designerurl'&#93;; 
$avatar=$row['avatar'&#93;; 

if &#40;$i==0&#41; &#123; 
echo "<tr><td class=\"box\"> 
<table border=\"0\" cellpadding=\"2\" cellspacing=\"0\"> 
<tr><td width=\"100\"> 
<img src=\"$avatar\" alt=\"\"> 
</td> 
<td align=\"left\"> 
<b>Added</b>&#58; $date<br> 
<b>Category</b>&#58; $category<br> 
<b>Designer</b>&#58; <a href=\"$designerurl\" target=\"_blank\">$designer</a> 
</td></tr> 
</table> 
</td> "; 
&#125; else &#123; 
echo "<td class=\"box\"> 
<table border=\"0\" cellpadding=\"2\" cellspacing=\"0\"> 
<tr><td width=\"100\"> 
<img src=\"$avatar\" alt=\"\"> 
</td> 
<td align=\"left\"> 
<b>Added</b>&#58; $date<br> 
<b>Category</b>&#58; $category<br> 
<b>Designer</b>&#58; <a href=\"$designerurl\" target=\"_blank\">$designer</a> 
</td></tr> 
</table> 
</td></tr>"; 
&#125;  #end if 

$i++; $i=$i%2;  
&#125; 

// END TABLE
echo "</table>";  

// DISPLAY PAGE LINKS
if &#40;$screen > 0&#41; &#123; 
$j = $screen - 1; 
$url = "$PHP_SELF?page=$j"; 
echo "<a href=\"$url\">&laquo; Prev</a>"; 
&#125; 

// page numbering links now 

for &#40;$i = 0; $i < $pages; $i++&#41; &#123; 
$url = "$PHP_SELF?page=" . $i; 
$j = $i + 1; 
echo " | <a href=\"$url\">$j</a> | "; 
&#125; 

if &#40;$screen < $pages-1&#41; &#123; 
$j = $screen + 1; 
$url = "$PHP_SELF?page=$j"; 
echo "<a href=\"$url\">Next &raquo;</a>"; 
&#125; 


?>

[Arwen]

Thank you, of course I don't mind if you edit the code. But it didn't work, it gave me this error:

Could not execute query : SELECT * FROM `avatars` ORDER BY id DESCLIMIT 0, 4.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 'DESCLIMIT 0, 4' at line 1

It's really weird, cos I'm using the same pagination code for my layouts and other graphics and it works just fine, but it won't work for my avatars... :S This is bugging me..

[Blackstone]

Ooops, find this line:

Code:

$query .= "LIMIT $start, $rows_per_page";

And replace it with this:

Code:

$query .= " LIMIT $start, $rows_per_page";

There needed to be a space before LIMIT. =)

[Arwen]

You're a... STAR!! Thank you sooo much! :D I'll put a link back to you. ;) I didn't know that one single space could affect the whole script.  :shock:

[Blackstone]

Yeah, spaces tend to have that effect sometimes. xD The problem with this one, was that that line I quoted above was being added onto a previous line. Concatenated is the proper term. When they were joined together, without a space, it ended up something like this:

Code:

SELECT * FROM `avatars` ORDER BY id DESCLIMIT $start, $rows_per_page

Notice that DESCLIMIT runs together. But with the space added in, the query was valid.

Code:

SELECT * FROM `avatars` ORDER BY id DESC LIMIT $start, $rows_per_page

Anyways, I'm glad it's working now. ^^ And I appreciate the link, hehe.

[Arwen]

Ah, I have another problem. >.< I guess you're already tired of me. lol

Anyway, I'm using the same code for the msn backgrounds, but the only thing I added is a downloads counter. Now the problem is it won't update the database (it doesn't count the downloads). Here's my php file.

And this is my download.php file.

Do you know what is wrong? Thanks!

Grrr, it's so annoying that I can't post the codes! Everybody else can!  :evil:

[Blackstone]

In your query to update the database, you've forgotten to add the dollar sign to signal a variable.

Code:

$query="update backgrounds set dcounter=dcounter+1 where id='$id'";

should be:

Code:

$query="update backgrounds set dcounter=$dcounter+1 where id='$id'";

And what I would do instead, just to make it a lot simpler, when you store the download counter into the $dcounter variable, you could simply go like this:

Code:

$dcounter=$row["dcounter"] + 1;

So that you can just write this for the query:

Code:

$query="update backgrounds set dcounter='$dcounter' where id='$id'";

That should fix your problems, but if it doesn't, just let me know and I'll take another look. ^^

And what's wrong when you post codes? Have you made sure to click the 'Disable HTML in this post' below the text area? Or do the codes just plain not post? >.<

[Arwen]

Thanks so much, but it didn't work. >.< I have the same download counter for my layouts page and it works with dcounter=dcounter+1 Hmmm...

I think I fixed the codes thing.

Code:

<img src="image.jpg">

:D

[Blackstone]

Okay, does it give you any sort of error? Or does it just not update the table?