Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

how to limit a while loop with a mysql query

Status
Not open for further replies.

benedictbutlin

Technical User
Oct 12, 2012
16
GB
what is the best way to limit the results of a while loop? i know i can do this as a 'for' loop, but reason i'm using a while loop is that i'm looping a mysql query like so...

Code:
while ($row = mysql_fetch_array($query)) { 

}

what i'd like to do is limit the loop to 10 results, and the reason i'm not using LIMIT in my mysql query, is because i need to get both all the results, as well as reduce the results in my loop. if i were to use LIMIT in my mysql query then i'd need to query the database twice... and that would be take ages doing double the work
 
just solved it with this... but i'll be back very soon with more questions!

$count = 0;

while ($count < 4 && $row = mysql_fetch_array($query)) {
//stuff

$count++;
}
 
Why do you need both a limited dataset and a full dataset? If it is to get a count then there are better ways.
 
i need the full data set for a total sum, and i need the limited data set for looping through to display the top 10 values in a chart... i'm all ears if there are other efficient ways to do this, as well as consider something different that i might need to do later
 
See MySQL's COUNT and SUM functions.

SELECT SUM(columnname) FROM tablename

SELECT COUNT(columnname) FROM tablename
 
to do that in one query you use SQL_CALC_FOUND_ROWS. this is memory efficient within mysql. although it looks like two queries, the second simply returns a resultset that is already stored in mysql's memory; so the overhead is very low. this is considerably more efficient than returning the whole dataset and using only a part of it. likewise it is often more efficient than using a LIMIT query and then a COUNT query (although not always - this depends on your indexing, engine choice, mysql version etc; it is way that is recommended by mysql, however).

so, assuming you want the first four rows from a table

Code:
[COLOR=#009900]$sql[/color] [COLOR=#990000]=[/color] [COLOR=#FF0000]"SELECT SQL_CALC_FOUND_ROWS * FROM tablename LIMIT 4"[/color][COLOR=#990000];[/color]
[COLOR=#009900]$result[/color] [COLOR=#990000]=[/color] [b][COLOR=#000000]mysql_query[/color][/b][COLOR=#990000]([/color][COLOR=#009900]$sql[/color][COLOR=#990000])[/color] [b][COLOR=#0000FF]or[/color][/b] [b][COLOR=#0000FF]die[/color][/b][COLOR=#990000]([/color][b][COLOR=#000000]mysql_error[/color][/b][COLOR=#990000]());[/color]
[COLOR=#009900]$totalResult[/color] [COLOR=#990000]=[/color] [b][COLOR=#000000]mysql_query[/color][/b][COLOR=#990000]([/color]SELECT [b][COLOR=#000000]FOUND_ROWS[/color][/b][COLOR=#990000]());[/color]
[COLOR=#009900]$row[/color] [COLOR=#990000]=[/color] [b][COLOR=#000000]mysql_fetch_array[/color][/b][COLOR=#990000]([/color][COLOR=#009900]$totalResult[/color][COLOR=#990000]);[/color]
[COLOR=#009900]$total[/color] [COLOR=#990000]=[/color] [COLOR=#009900]$row[/color][COLOR=#990000][[/color][COLOR=#993399]0[/color][COLOR=#990000]];[/color]
[b][COLOR=#0000FF]while[/color][/b] [COLOR=#990000]([/color][COLOR=#009900]$row[/color] [COLOR=#990000]=[/color] [b][COLOR=#000000]mysql_fetch_assoc[/color][/b][COLOR=#990000]([/color][COLOR=#009900]$result[/color][COLOR=#990000])):[/color]
  [b][COLOR=#0000FF]echo[/color][/b] [COLOR=#FF0000]'<pre>'[/color] [COLOR=#990000].[/color] [b][COLOR=#000000]print_r[/color][/b][COLOR=#990000]([/color][COLOR=#009900]$row[/color][COLOR=#990000],[/color] true[COLOR=#990000])[/color] [COLOR=#990000].[/color] [COLOR=#FF0000]"</pre>';[/color]
[COLOR=#FF0000]endwhile;[/color]
[COLOR=#FF0000]echo "[/color]Total Number of Records in the dataset[COLOR=#990000]:[/color] [COLOR=#009900]$total[/color][COLOR=#FF0000]";[/color]
 
hey jpadie,

i haven't yet tried your code example, but here's what i'd like to do (but do tell me if i should examine and think about your code first before i ask this question)...

is it possible in one query...
to get back a result where i can sort by two different columns?
which would save me from querying the DB twice
 
i don't see how that is related to the first post as qualified by your explanation.

but yes - you can do whatever sorting you want within php. however it is inefficient

a well designed set of indices and a well designed query will be faster. pretty much every time.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top