Loops and mySQL

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
eludlow
Forum Newbie
Posts: 13
Joined: Thu Jan 22, 2004 3:48 pm

Loops and mySQL

Post by eludlow »

I hate writing loops, and I've been trying to work this one out for a while now. I know it's <span style='color:blue' title='I'm naughty, are you naughty?'>smurf</span> simple to write, and I should be able to do it, but brain often shuts down while writing loops, so I'm calling on you good people for help!

Bascially, I've an SQL table, each row having a unique "id" field, a simple integer. The first row in the table is 1, and when each new row is added, that number gets incremented. That's all fine and working.

What I want to produce is a simple php script that will write the latest five rows in the table to the screen, and then produce the rest of the rows on another page - a sort of archive.

How would you go about producing:
1) the loop to print JUST the first five
and
2) the loop to write all the rows after the first five.

Many thanks,
Ed Ludlow
User avatar
kendall
Forum Regular
Posts: 852
Joined: Tue Jul 30, 2002 10:21 am
Location: Trinidad, West Indies
Contact:

Post by kendall »

Ed Ludlow,

I think you want a "pagination" type technique...and i can tell you there is some work here pal...

First you need to set some page variables
maximum results to be viewed at a time
limit is a point to which to start reading the next set of results so to speak

Code: Select all

// set maximum amount of results
$max = 5;
// get page
if($_GET['page'])
	$page = $_GET['page'];
else $page = 1;
// set limit
$limit = $max * $page - ($max);
now you get a total of the amount of results you will be displaying

Code: Select all

$Results = mysql_query("SELECT * FROM table [condition if any] ",$Connection);
$num_results = mysql_num_rows($Results);
now that you have the results we can start getting results bit by bit (in fives) using the LIMIT function in MYSQL

Code: Select all

$Results = mysql_query("SELECT * FROM table [condition if any] LIMIT $limit, 5",$Connection);
$result = mysql_fetch_assoc($Results);
now when you do your for loop you got to calculate the page you are on, the amount left and test wheather you've reached the end or your at the begining...to demonstrate

Code: Select all

// output the 5 results
// then put your "back" and "next" links
if($page != 1){ 
echo '<a href="'.$location.'&page='.($page-1).'"> '.$max.'</a>&nbsp;'; }else{ echo '&nbsp;'; 
} 

if(($total  = $num_results - ($page*$max)) > 0){ echo '<a href="'.$location.'&page='.($page+1).'"> '.$max.'</a>'; }else{ echo '&nbsp;'; }
the above works out the next and previous links that will display either the next 5 results or previous 5 results depending on the amount of the results...yada yada

hope dat helps
eludlow
Forum Newbie
Posts: 13
Joined: Thu Jan 22, 2004 3:48 pm

Post by eludlow »

Cheers mate - one of the guys on another forum helped me solve it earlier today.

Your break down of the "pagination" is bloody good though, thank you!

Ed
Post Reply