whitesova93 whitesova93 - 2 months ago 8
MySQL Question

how to show highest id from table array

Currently I am using this:

<div class="post-detail">
<?php
if($_SESSION["ses"]=="")
{
$cont="SELECT * FROM content WHERE id < ( SELECT MAX( id ) FROM content) ORDER BY id desc limit 1";
$cont_row=mysql_query($cont);
$cont_res=mysql_fetch_assoc($cont_row);

?>
<h3 class="post-title"><?php echo $cont_res["page_title"]?></h3>
<p class="post-description">
<?php
echo $cont_res["details"];
?>
</p>
<?php } ?>
<div class="read-more">
<a href="detail.html">Вижте повече...</a>
</div>
<div class="sign-up"></div>
</div>


But I would like to show the array with the highest id from table content.
I've already tryed using max but it's not working and giving me some error:

<?php
if($_SESSION["ses"]=="")
{
$cont="select * from content where id='MAX(id)'";
//echo $cont;
//exit;
$cont_row=mysql_query($cont);
$cont_res=mysql_fetch_assoc($cont_row);
}

?>


This is not working at all. Could someone show the right way of showing the highest id of array in table contents?

Answer

The best and simplest answer is to sort the data, and then limit the returned values. @shubham did just that in the first line of this answer, and to expand it to cover your comment as well simply use the offset parameter for limit
Like this:

// Returns the second highest ID (skip 1)
SELECT id, name, data FROM content ORDER BY id DESC LIMIT 1,1

// Return the top 3
SELECT id, name, data FROM content ORDER BY id DESC LIMIT 3

// Return sixth to eighth place.
SELECT id, name, data FROM content ORDER BY id DESC LIMIT 5, 3

Edit: Example from a database:

mysql> select id,created FROM task ORDER by ID DESC limit 1;
+------+---------------------+
| id   | created             |
+------+---------------------+
| 7602 | 2016-09-23 12:28:04 |
+------+---------------------+
1 row in set (0.00 sec)

mysql> select id,created FROM task ORDER by ID DESC limit 1,1;
+------+---------------------+
| id   | created             |
+------+---------------------+
| 7601 | 2016-09-23 10:01:36 |
+------+---------------------+
1 row in set (0.00 sec)

mysql> select id,created FROM task ORDER by ID DESC limit 5,3;
+------+---------------------+
| id   | created             |
+------+---------------------+
| 7597 | 2016-09-22 12:56:49 |
| 7596 | 2016-09-22 12:41:44 |
| 7595 | 2016-09-22 11:22:02 |
+------+---------------------+
3 rows in set (0.00 sec)

So, as you can see this works.