Page 1 of 1

Loops and mySQL

Posted: Fri Jan 23, 2004 6:02 am
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

Posted: Fri Jan 23, 2004 1:32 pm
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

Posted: Fri Jan 23, 2004 1:58 pm
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