next / previous links & multiple pages

[mat]

Hi,

I'm making a product gallery, It's job is to pull out the matching products for that particular collection/product range and display them on the page in 2 rows, 3 to a row max.

this is an bare bones example (http://www.orchid.f2o.org/php_test/output.php?collection=1) in progress

So this dynamic gallery is fine if i have 6 or less products returned but if i have anymore, how can i make it so that instead of just outputting them down the page (i only want 2 rows of 3 per page) it outputs a next link and makes a new page?

the code for m example linked above is below:


<?php

include "dbConnect.php";

$sql = "SELECT p.potID, p.potName, p.image, c.collectionID
FROM pots p, collection c
WHERE p.collection = c.collectionID
AND $collection = c.collectionID ";

$result = mysql_query($sql);

$pictures="";

echo ("

<table border=\"1\" width=\"630\" height=\"600\">
<tr height=\"90\" width=\"630\">
<td height=\"90\" colspan=\"3\">
nav
<td>
</tr>
<tr>
");

$currentDone = $totalDone = 0;

while ($row = mysql_fetch_array($result)){
$potName = $row['potName'];
$pic = $row['image'];
$potID = $row['potID'];

if ($currentDone == 3){
$pictures .="
</tr>
<tr >";
}

$pictures .="

<td height=\"200\" width=\"210\">
<table align=\"center\" border=\"1\" width=\"160\">
<tr>
<td colspan=\"2\"><img src=\"{$pic}_small.jpg\"></td>
</tr>
<tr>
<td><A HREF='detail.php?pot=$potID' target='popup' onClick=\"window.open('detail.php?pot=$potID','popup','width=300,height=500'); return false\">Detail</a></td>
<td>1</td>
</tr>
</table>
</td> ";
$currentDone ++;
$totalDone ++;
}

if ($totalDone == 0) $pictures ="Nothing returned";
echo $pictures;
echo ("
<tr height=\"100%\">
<td colspan=\"3\">&nbsp;</td>
</tr>
</tr>
</table>
");
?>



I have spotted 2 tutorials on this but they seem a bit complicated, I wonder if it can be done simply (so i can understand). something like

if totalDone == 6
then output a next link
and generate a new page to display the rest of the products

on the new page display a previous link etc..


Simple way of doing it?, i'd appreciate some help :)

[bcarl314]

I beleive the function you're looking for is mysql_num_rows(). This will return the total number of results from a query. So were you're using :

$row = mysql_fetch_array($result)

add this

$resultTotal = myswl_num_rows()

Additionally, you could add a LIMIT clause to your SQL statment. Something like,

SELECT p.potID, p.potName, p.image, c.collectionID
FROM pots p, collection c
WHERE p.collection = c.collectionID
AND $collection = c.collectionID LIMIT $startNumber, $LimitNumber

Where $startNumber is the number your would like to start at (ie prod #1, or prod #7, or prod #13 etc) and $LimitNumber is the total result set you're looking for (ie 6).
(I may have that syntax backwards, it may be LIMIT $LimitNumber , #startNumber. Can't remember right now.)

Hope this helps.

[mat]

so,

SELECT p.potID, p.potName, p.image, c.collectionID
FROM pots p, collection c
WHERE p.collection = c.collectionID
AND $collection = c.collectionID LIMIT $startNumber, $limitNumber

$row = mysql_fetch_array($result)

$resultTotal = mysql_num_rows($row)

$startNumber = ? the first result
$limitNumber = ? the sixth result

if the $resultTotal is more than 6 then make a next link ?
on the "next page" it can be similar code except it gets all results after $limitNumber (the rest of the results)

but where does the next link go? wouldn't the next page actually be the same gallery.php reloaded, how does this work :confused:

mat,

[Spookster]

This really belongs in the MySQL forum as stated by my Must Read thread so I will move it over there.....

[mat]

yeah sorry, i didn't know it would involve changing the SQL i thought it would all be php. (how php outputs data not how php interacts with a DB)

[mat]

well this is the code i am using now in case anyone cares. It works but i wonder if it could be done using less code (1 sql query instead of 2) this is the only way i can think of ?


code has now been fixed



<?php

include "dbConnect.php";


if(!isset($page)){
$page = 1;
}


$sqlNum = "SELECT COUNT(*)
FROM pots p, collection c
WHERE p.collection = c.collectionID
AND $collection = c.collectionID";

$resultNum = mysql_query($sqlNum);

$numRows = mysql_num_rows($resultNum);



$sql = "SELECT p.potID, p.potName, p.image, c.collectionID
FROM pots p, collection c
WHERE p.collection = c.collectionID
AND $collection = c.collectionID LIMIT ".(($page-1)*6).",".($page*6);

$result = mysql_query($sql);

$pictures="";

echo ("

<table border=\"1\" width=\"630\" height=\"600\">
<tr height=\"90\" width=\"630\">
<td height=\"90\" colspan=\"3\">
nav
<td>
</tr>
<tr>
");

$currentDone = $totalDone = 0;

while ($row = mysql_fetch_array($result)){
$potName = $row['potName'];
$pic = $row['image'];
$potID = $row['potID'];
$collection = $row['collectionID'];

if ($currentDone == 3){
$pictures .="
</tr>
<tr >";
}

$pictures .="

<td height=\"200\" width=\"210\">
<table align=\"center\" border=\"1\" width=\"160\">
<tr>
<td colspan=\"2\"><img src=\"{$pic}_small.jpg\"></td>
</tr>
<tr>
<td>$potName</td>
<td><A HREF='detail.php?pot=$potID' target='popup' onClick=\"window.open('detail.php?pot=$potID','popup','width=400,height=500'); return false\">DT</a></td>
</tr>
</table>
</td> ";

$currentDone ++;
$totalDone ++;
}

if ($totalDone == 0) $pictures ="Nothing returned";
echo $pictures;
echo ("
<tr height=\"100%\">
<td colspan=\"3\">&nbsp;</td>
</tr>
</tr>
</table>
");

if($page > 1){
echo "<a href=\"output.php?page=".($page-1)."&collection=$collection\">Previous</a>";
}
if($numRows > ($page*6)){
echo "<a href=\"output.php?page=".($page+1)."&collection=$collection\">Next</a>";
}

?>





I'd be interested if anyone knows a more efficient way of doing it :confused:

mat.

[Spookster]

For your first query you seem to just want the number of records so instead of actually pulling in all the data and then using PHP to count it you can use SQL to count the records:

$sqlNum = "SELECT COUNT(*)
FROM pots p, collection c
WHERE p.collection = c.collectionID
AND $collection = c.collectionID";

If this is going to be a common hardcoded query and there will be many records what you could do is create another table to store a number value corresponding to the number of records of that query. So any time a record is added increment the number or when a record is deleted decrement the number. This is a common practice in large systems. I use it at work, of course our main DB is around 2 TeraBytes in size and has billions of records. We call our tracking table a LoadControlStatus. It keeps track of what records were processed previously so that the next time only the newest records get processed.

Anyways that would save you some time and use less server resources as you would only have to do short query for that single value of that tracking table vereses having to either pull in all the records and count them or use SQL to count them.