display query results across multiple pages...

chamlis

Hi guys.

I have a bit of php code here that displays the results of a MySQL query. At the moment it displays all the results on the one page. There could be anything up to 60 odd entries at this stage, and pretty soon there will be a whole lot more.

I want to be able to display 10 at a time, and then move on to the next page etc.

I've removed the header and footer from this snippet and changed some table names, but you get the gist.

I have a few more pages like this, but once I see how it's done I should be able to manage myself.

Any ideas welcome

Thanks

<?
   
   
$county = $_REQUEST['countyID'];


$query= "SELECT * FROM items WHERE countyID = '$county'";

	if(isset($_REQUEST['cat_id'])){
	$cat = $_REQUEST['cat_id'];
		$query.=" AND cat_id = '$cat'";
	}


$result = mysql_query($query) or die (mysql_error());

?>

<?	while ($row_p=mysql_fetch_array($result,MYSQL_ASSOC)){ ?>

<table align="center" width="80%" border="0" cellspacing="1" cellpadding="4" class="resBorder">
        <tr>
          <td class="colText">&nbsp;Address</td>
          <td class="colText">&nbsp;Town/Area</td>
          <td class="colText">&nbsp;County/City</td>
          <td class="colText">&nbsp;Price</td>
          <td class="colText">&nbsp;things</td>
          <td class="colText">&nbsp;yokes</td>
          <td class="colText">&nbsp;blobs</td>
        </tr>

        <tr valign="top">
          <td class="rowText"><a href="detail.php?id=<? echo $row_p['id']; ?>" class="rowLinkBold" ><? echo $row_p['address']; ?></a></td>
          <td class="rowText"><? echo $row_p['area']; ?></td>
          <td class="rowText"><? echo $row_p['county']; ?></td>
          <td class="rowText">&euro; <? echo $row_p['price']; ?></td>
          <td class="rowText"><? echo $row_p['things']; ?></td>
          <td class="rowText"><? echo $row_p['yokes']; ?></td>
          <td class="rowText"><? echo $row_p['blobs']; ?></td>
        </tr>
        <tr valign="top">
          <td class="descText" colspan="8"><table border="0" cellspacing="0" cellpadding="4">
        <tr valign="top">
           
<? //fetch photo from database
	$sql = "select * from photos where ptid = ". $row_p['id']." and porder = '1' ";
	//echo $sql; //debug sql output
	$pRS = mysql_query($sql) or die(mysql_error());
	//fetch assoc array
	$row_photo = mysql_fetch_assoc($pRS);
	// if image exist
	if ( mysql_num_rows($pRS) > 0 )
		$imageSRC = $row_photo['location'];
	else //no photo
		$imageSRC = "noimage.jpg";
?>

                <td class="descText"><a href="detail.php?id=<? echo $row_p['id']; ?>"><img src="admin/photos/uploads/small_thumbs/tn_<? echo $imageSRC; ?>" border="0" alt="<? echo $row_photo['caption']; ?>"></a></td>
                <td class="descText"><? echo substr($row_p['description'],0,150); ?>...<a href="detail.php?id=<? echo $row_p['id']; ?>" class="rowLink">more..</a></td>
              </tr>
			  <tr><td>&nbsp;</td></tr>
            </table></td>
        </tr>
	<? } ?>
      </table>


<!------------------------------- END CENTER CONTENT --------------------------------------->

seamus

The LIMIT keyword is your friend.

SELECT * FROM MyTable LIMIT 0, 10

Will select * from mytable (surprisingly), starting with record zero, and returning 10 records.

What I do is within the PHP script, maintain two variables - $startrow and $returnrows.

Then use a few calculations to determine how many pages of information there are (You may need a SELECT COUNT(*)... for this), and provide a link for each page, which pass get variables for the startrow and/or the number of rows to display. Remember to validate & escape the data!

I have an example of this in work. If you're having trouble with it, I'll get it for you and post it up tomorrow.

louie

do a quick search in google for "php records paging" and you'll get exactly what you are looking for.

forbairt

I would have said have a look for ...

"php mysql pagination"

Pretty much as Séamus said though ..

chamlis

Thanks guys.

I've been looking through various tutorials. All pretty basic.

I don't have time to learn it myself really. Would anyone be able to apply something to the code above so I can see it?

Like I said, I have a good few more pages of code, but when I see it applied to that I'll figure out the others.

Thanks

rsynnott

Please note that as currently written, this code allows easy SQL injection and should absolutely not be used on an internet-connected website. Fix this.

chamlis

rsynnott wrote:

Please note that as currently written, this code allows easy SQL injection and should absolutely not be used on an internet-connected website. Fix this.

Noted. Added real escape string.

chamlis

rsynnott wrote:

Please note that as currently written, this code allows easy SQL injection and should absolutely not be used on an internet-connected website. Fix this.

Noted. Added real escape string. Looking into other options