I have a php site which uses css for positioning of elements. In one column I have displayed the results of a query to MySQL. This list can be quite long.
How can I limit the number of records displayed to say, the first 10 returned?
Appreciate any suggestions.
Thanks
David
two ways - limit the records returned in the query or in the loop used to display
query : "select * from tablename limit 0,10"
loop
count = 0;
while($row = mysql_fetch_array($result) && count < 10){
.
.
.
.
.
.
count++;
}
reg
kevin
SELECT * FROM table LIMIT 10; # Retrieve first 10 rows
If you use LIMIT, just remember that it stops searching once it gets the number you are looking for, and won't search the whole table. Thus, your results may be a bit skewed...
You would definitely be better off using a for or while loop, since your query returns all of the results first, and the loop just stops displaying them at your designated number.
~DR
your results won't be skewed if you organize a decent sql select statement and implementing the limit clause
and say if you archiving a ton of records and only want to show the last ten, why suck up server resources with a while loop when you can do it with sql // much faster using sql //
if you aren't worried about a lot of records, then you can use the while loop way
but keep in mind if you have an id to sort on, then the limit will be just as accurate
All I am saying is that you may not get the desired results. (Not really giving too much consideration to the actual number of records searched)
In this context, a lot may be defined as 100 or 1000...
just curious what you think the difference is between
select * from table limit 10
or
select * form table
then doing a while loop for 10 records
they will both give you ten records, and in no particular order either way, thats why you would need to do something like order by id desc or something like that, in either one
I am not trying to be confrontational here, so please don't get defensive or sound so rude and abrupt in your posts. I am just trying to be helpful in making sure the best solution is provided. This is pulled directly from the mySQL manual (regarding how mySQL optimizes LIMIT)
5.2.8 How MySQL Optimises LIMIT
In some cases MySQL will handle the query differently when you are using LIMIT # and not using HAVING:
If you are selecting only a few rows with LIMIT, MySQL will use indexes in some cases when it normally would prefer to do a full table scan.
If you use LIMIT # with ORDER BY, MySQL will end the sorting as soon as it has found the first # lines instead of sorting the whole table.
When combining LIMIT # with DISTINCT, MySQL will stop as soon as it finds # unique rows.
In some cases a GROUP BY can be resolved by reading the key in order (or do a sort on the key) and then calculate summaries until the key value changes. In this case LIMIT # will not calculate any unnecessary GROUP BYs.
As soon as MySQL has sent the first # rows to the client, it will abort the query (if you are not using SQL_CALC_FOUND_ROWS).
LIMIT 0 will always quickly return an empty set. This is useful to check the query and to get the column types of the result columns.
When the server uses temporary tables to resolve the query, the LIMIT # is used to calculate how much space is required.
In other words: Once it gets 10 results, it orders those, rather than sorting thoe whole table and then giving the first 10 results.
whos getting rude
as i said i "was just curious" i am not pissed or anything
i wanted your opinion and i think i know how to post, when to post, and what to post, so there is no need to incinuate that i am being rude // which i wasn't
and wow, glad you could check out the manual, most peeps don't
Sorry, I just took your post the wrong way. I honestly believe you are a great source of info.
My military trainnig has led me to manuals manuals manuals... Also, I run mySQL locally on my PC to test scripts and whatnot, so I basically have been through that manual with a fine toothed comb.
Sorry about the mixup in attitudes...
~DR
