how to limit a while loop with a mysql query

[benedictbutlin]

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...

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

 

[benedictbutlin]

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++;
}

 

[jpadie]

Why do you need both a limited dataset and a full dataset? If it is to get a count then there are better ways.

 

[benedictbutlin]

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

 

[spamjim]

See MySQL's COUNT and SUM functions.

SELECT SUM(columnname) FROM tablename

SELECT COUNT(columnname) FROM tablename

 

[spamjim]

COUNT returns how many rows/records apply.

http://www.w3schools.com/sql/sql_func_count.asp

SUM adds the values of the rows/records.

http://www.w3schools.com/sql/sql_func_sum.asp

 

[jpadie]

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

[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]

 

[benedictbutlin]

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

 

[jpadie]

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.