Mysql Select From Table, Get Newest/last 10 Rows In Table

What's the best, and easiest way to do this? My query currently is: SELECT * FROM chat WHERE (userID = $session AND toID = $friendID) OR (userID = $friendID AND

Solution 1:

to reverse the order (therefore get last 10 instead of first 10), use DESC instead of ASC

EDIT

Based on your comment:

SELECT*FROM (
  SELECT*FROM chat 
  WHERE (userID = $session AND toID = $friendID) 
    OR (userID = $friendID AND toID = $session)  
  ORDERBY id DESC
  LIMIT 10
) AS `table` ORDERby id ASC

Solution 2:

If you want the last 10 then just change ASC to DESC

SELECT * 
FROM 
chat 
WHERE 
(userID=$session AND toID=$friendID) 
OR 
(userID=$friendID AND toID=$session) 
ORDER BY id 
DESC
LIMIT 10

Solution 3:

$con = mysqli_connect("localhost","my_user","my_password","my_db");$limit = 10;$query = "SELECT * FROM  $table";$resource = mysqli_query($con,$query);$total_rows = mysqli_num_rows($resource);$start = $total_rows-$limit;$query_limit= $query." LIMIT $start,$limit";

First I have set the limit

$limit = 10;

then

$total_rows = mysqli_num_rows($resource);

Here I have taken total number of rows affected.

$start = $total_rows-$limit;

then substracted limit from number of rows to take starting record number

$query_limit= $query." LIMIT $start,$limit";

and then added limit to the query. For more information about limit see this link https://www.w3schools.com/php/php_mysql_select_limit.asp

Solution 4:

First select the last 10 from the table, then re-order them in ascending order.

SELECT*FROM (SELECT*FROMtableORDERBY id DESC LIMIT 10) sub ORDERBY id ASC