How do you guys use LIMIT in MySQL ?

I print some records from my DB that are sorted by date, but i only want to print the last 10 records....

have tryed LIMIT 10, but it seems like everything is printed anyway

SELECT TOP 10 fieldlist FROM table ...

Read this:

MySQL 5.0 Reference Manual :: 13.2.7 SELECT Syntax

Originally Posted by chrishirst

SELECT TOP 10 fieldlist FROM table ...

I cant get that to work....


when you say fieldlist.... do you then mean my attributs ? .... have tryed to insert them there but it will not work.

Should it not be possible to show the last 10 records in a table by using LIMIT..... have tryed LIMIT 0,10 and 10,10 but non of it gives the right output.

fieldlist is comma seperated list of the columns you want to retrieve from the table (or tables in the case of a JOIN query)
TOP is not supported in MySQL apologies obviously missed the MySQL bit

LIMIT with retrieve a number of rows starting from a known point.
So;

SELECT columns FROM table LIMIT 0, 10 will return the first 10 records as it will start at position 0
LIMIT 20, 15 will return records 21 through to 35

using LIMIT to return the last records in natural order you need to know how many records there are in the table and start from 10 before.

to get the last 10 records you will have to reverse the table order.
so;
SELECT fieldlist FROM table ORDER BY field DESC LIMIT 10

if your version of MySQL supports subqueries (V5 on) it should be possible to do this,

SELECT fieldlist FROM table LIMIT (SELECT COUNT(*) FROM table)-10 ,10
Not tested BTW

other than that you can do 2 queries and use server side code to set the limits

pseudocode (convert into whatever server side code you are using)

SELECT COUNT(*) as rec_count FROM table
var = RS("rec_count) - 10
SELECT fieldlist FROM table LIMIT var , 10

I just get errors when runnig any of that... - mybe it is the fieldlist thing....


But this code will run:

PHP Code:

<?
$test= mysql_db_query("$datab", "SELECT * FROM ADD WHERE DATE_SUB(CURDATE(),INTERVAL $expire DAY) >= date AND groupid=1  ORDER by date desc LIMIT 10");
while ($row = mysql_fetch_array ($test)) { 
$test1 = "".$row["id"];
$test2 = "".$row["test2"];
$test3= "".$row["test3"];

print("$test2<br>");

?>
}

Put when that runs, arround 20 records are printet even though limit is set to 10...... ORDER by date desc LIMIT 10"); should give me the last 10 record inserted into the DB ?

if you have a table called add it will cause problems

add is a reserved word in SQL

using SELECT * is a very inefficient way of querying the DB, the server has to parse the tables twice, once to get the column names and then again to get the data.

in your example above fieldlist would look like

SELECT id,test2,test3 FROM ...

Well i did translate some faroese words in my table last time, but here is how it looks right now:

PHP Code:

<?
$bilar = mysql_db_query("$datab", "SELECT id, yvirskrift, lysingbolkid, date FROM lysingar WHERE DATE_SUB(CURDATE(),INTERVAL $numberdays DAY) >= date AND lysingbolkid=1 ORDER BY date DESC LIMIT 10");
while ($row = mysql_fetch_array ($bilar)) { 
$lysing_id2 = "".$row["id"];
$yvirskrift2 = "".$row["yvirskrift"];
$bolkid2 = "".$row["lysingbolkid"];
$bolkid2 = "".$row["date"];

print("$yvirskrift2<br>");


} ?>

it prints arround 25 records, so there is a problem.... only want 10

now i'v tryed the TOP thing again....

I get this error message:

Warning:  mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /usr/home/web/web128130/kikarin/newcar.php on line 6

My code looks like this:

PHP Code:

<?
$bilardays = mysql_db_query("$datab", "SELECT TOP 10 id, yvirskrift, lysingbolkid, date FROM lysingar where lysingbolkid=1");
while ($row = mysql_fetch_array ($bilardays)) {   //this is line6
$lysing_id = "".$row["id"];
$expire = "".$row["dagar"];
$yvirskrift = "".$row["yvirskrift"];
$bolkid = "".$row["lysingbolkid"];



$bilar = mysql_db_query("$datab", "SELECT TOP 10 id, yvirskrift, lysingbolkid, date FROM lysingar WHERE DATE_SUB(CURDATE(),INTERVAL $expire DAY) >= date AND lysingbolkid=1 ORDER BY date DESC");
while ($row = mysql_fetch_array ($bilar)) { 
$lysing_id2 = "".$row["id"];
$yvirskrift2 = "".$row["yvirskrift"];
$bolkid2 = "".$row["lysingbolkid"];
$date2 = "".$row["date"];

print("$yvirskrift2<br>"); ?>
}
}

I usualy get a error message on that spots in other files as well when there is something wrong with the line abow

I don't know if it makes a difference, but mysql_db_query() is depreciated, use mysql_query() instead.

PHP: mysql_query - Manual

NOw i got this message:

Warning: mysql_query(): supplied argument is not a valid MySQL-Link resource in /usr/home/web/web128130/kikarin/newcar.php on line 5

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /usr/home/web/web128130/kikarin/newcar.php on line 6

PHP Code:

$bilardays = mysql_query("SELECT TOP 10 id, yvirskrift, lysingbolkid, date FROM lysingar where lysingbolkid=1"); 
while ($row = mysql_fetch_array($bilardays)) { . . . 


When i use that one i get:

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /usr/home/web/web128130/kikarin/newcar.php on line 6


I was wondering if i'v made misplaced a " ' ( ) { or } but can't see anything like that

TOP does not exist in MySQL so will cause an error.

So.... anyone got a clue what to do ?


Maybe a good ide to use MIN or MAX instead on E.G. the ID or DATE attribut, and SORT BY something DESC LIMIT 10?

Originally Posted by chrishirst

TOP does not exist in MySQL so will cause an error.

Oops! My bad!

What where the people thinking that made MySQL ?

So... it looks like im stuck

What version of PHP and MySQL is running on your server?

the PHP is v.5.something, but i dont know what version MySQL is..... but i guess my host uses the newest version

There goes that theory...
Can you run the query in phpMyAdmin, or something similar? Then you would eliminate the mysql_query vs mysql_db_query question. If it works in phpMyAdmin, you should be able to get it to work in PHP, but it may be a syntax error, or incorrect usage of a function. If it _doesn't_ work in phpMyAdmin, (meaning using "LIMIT 10" still returns more than 10 records), then there is something else wrong with MySQL.