Looping through DB until quota reached.

[Tom_Cash]

Hi there,

I have a database bringing back results for items in a table. However, I don't want to bring all of my results back -- just five. I have tried looping using a while and a for but have nothing but logical errors.

Looping before I start my database loop brings back five of each item.

Looping after I start my database loop brings back an unlimited amount of the first item.

Is this common or am I just missing something?

Thanks for the help,
kindest regards,
Tom.

[cameraman]

Paste some code that we can look at. You can also add a LIMIT clause to your sql. This gives you the first five records:
SELECT * FROM sometable LIMIT 5

This gives you five records starting at the tenth record:
SELECT * FROM sometable LIMIT 10,5

[Tom_Cash]

That's great, it works a treat - thanks! Much more simple than my approach. Is this the recommended approach?

Also, is this how I would display pages of results? Starting the query at result number #?

[edited by: Tom_Cash at 10:19 am (utc) on Oct. 28, 2008]

[cameraman]

Is this the recommended approach?

To retrieve the first x records it's pretty much 6 of one half dozen of the other. Note, I used * to retrieve all fields. If you only want a few fields it's more efficient to name them specifically in the sql than to use * and pick them out the result.

Also, is this how I would display pages

Yep!

You didn't post any code; to do it with a for loop, something like this would work:
$result = mysql_query("SELECT * FROM sometable");
if($result && mysql_num_rows($result)) {
for($i = 0; $i < 5; $i++) {
if(($row = mysql_fetch_assoc($result)) === false)
break; // We ran out of data
// Here, either do something with the row or put it into an array for use later
$row_array[] = $row;
} // EndFor retrieve records
mysql_free_result($result);
} // EndIf have result & data
elseif($result) {
echo "No matching data";
mysql_free_result($result);
} // EndIf no rows
else echo mysql_error();

[Tom_Cash]

Thanks a lot! I never posted my code because your first answer fixed it, hehe. Having seen your version, mine was far off working!