add paging if possible

[dazz_club]

Hi all,

 

I have a script that selects specific products which only are identified with a "cat_id" number of 1.

 

I have several categories where i have labeled them from 1 to 7. The number 1 category is "Game cards $ labels" (see the site http://img170.imageshack.us/img170/937/flexmockdrinkpromotg0.jpg ). This has the highest amount of products, intotal 26. I was orginally happy with displaying all the products in one column. Now im think it would be nice to some how break the results up, so there can be 5 displayed and possibly have links informing the user there are more products to see, something like, 1 2 3 4 > NEXT.

 

I think this is called pagniation, but i could be wrong.

 

On my script i have limited it to display the first 4, and thats where it ends. If anyone can give me a hint of what to do next, that would be great.

 

-----

<?php 
			$query = "SELECT `name` , `type` , `image` , `category` FROM products WHERE cat_id = 1 LIMIT 4" ;
			If ($r = mysqli_query ($connection, $query)) { 
			//sending the query to the mySQL server
			While ($row = mysqli_fetch_array($r)) { 
			//inputs the data into the table

			$name = $row['name'];
			$type = $row['type'];
			$image = $row['image'];
			$category = $row['category'];
			?>
			<div class="productBox">
			<ul style="padding:0px;margin:0px;list-style-type:none;">	
			<li><img src="images/product-images/<?php echo $image; ?>" style="border:1px solid black;padding:3px;" /></li>
				<li><span style="font-weight:bold;">Product Name:</span> <?php echo $name; ?></li>
				<li><span style="font-weight:bold;">Type:</span> <?php echo $type; ?></li>
				<li><span style="font-weight:bold;">Category:</span> <?php echo $category; ?></li>
			</ul>	
			</div>
			<?php }

   } else { //query did not run.
        die ();
        }
mysqli_close($connection);

?>

 

Kind regards

Dazzclub

[wobbit]

Hi Dazz,

 

I found a pretty good tutorial which shows how to do paging with PHP adn MySQL.

 

If can be found here:

 

http://www.php-mysql-tutorial.com/php-mysql-paging.php

[dazz_club]

Hi there,

 

Thanks for the link. I followed the tutorial and ammened it to fit my script and it works. I still need to refine (or define) the search as at the moment if i type in e and then press search it will bring everything that has an e in it back.

 

thanks again mate.

[dazz_club]

Ok, i have searched on the net and have failed to find (probably me entering the wrong sort of query) a tutorial or some documentation on how to define or perform a better method of searching and retrieving from a database from my website.

 

Currently on my site when i simply highlight the input field and press enter (so i basicly dont enter a search query) results show (my products).

 

I would like to somehow filter this out where by notifying the user to perform a more structured query and also inform them their query failed to retrieve any data.

 

kind regards

Darren

[deadonarrival]

The LIMIT x,y part of your mysql is what you want

 

add LIMIT $start,$num_per_page to your sql, then just before it

 

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

$start = 1 + ($num_per_page * $_GET['page']) - $num_per_page;

if we say $num_per_page is 10.

 

page 1 -> 1+(10*1)-10 = 1+ 10-10 = 1

so we start from 1 and get the first 10.

(1,2,3,4,5,6,7,8,9,10)

page 2-> 1+(10*2)-10 = 1+ 20-10 = 11

so we start from 11 and get 11-20th results

(11,12,13,14,15,16,17,18,19,20)

page 99 -> 1+(10*99) -10 = 1+990-10 = 981

So we start from 981 and get the 981st->990th results

(981,982,983,984,985,986,987,988,989,990)

 

Then just make forwards and backwards links, and maybe a few pages each side as direct links.

 

The only thing you need to think about is ending when the row number is greater then the number of rows. You need another (unlimited) query to count the rows.

$query = mysql_query("SELECT `id` FROM `table` where 1=1");

$num_rows = mysql_num_rows($query);

 

then when printing the results, check if the row > $num_rows, if so, stop.

 

With making the pages, you want to go up to the page the last one is on, but no higher.

 

So we need one page for every 10 results,

 

if($num_rows%$num_per_page == 0)

{

//We need the one page for every x many results, exactly

$num_pages = $num_rows/$num_per_page;

}

else

{

//We need one page for every x many results

//Round it up using ceil, so we get an extra page.

$num_pages = ceil($num_rows/$num_per_page);

}

 

You can expand on this in plenty of ways, but that should do the basic functionality.

[dazz_club]

hmmm looks interesting I`ll do some reading and get back to you if thats ok.

 

kind regards

Dazzclub