How to output a value that was recently added into mysql database?

Question

I want to create a table that will output the data from mysql database that was recently added.

Example :

+-----+---------+-----------+---------------+-------------+
| id  | item_id | item_name | borrowed_date | expiry_date |
+-----+---------+-----------+---------------+-------------+
| B01 | N01     | book      | 12/05/2017    | 10/06/2017  |
+-----+---------+-----------+---------------+-------------+

I have tried using ORDER BY but it does not output according to newly added row.

<?php

include"connection.php"; //contain $conn

$query = "SELECT * FROM `database` ORDER BY item_id ASC ;";
$result = mysqli_query($conn,$query);



?>

The output was not according to newly added data

What has this got to do with HTML, JavaScript, or PhpMyAdmin? — Jack Bashford, Jun 29 '19 at 07:57
Do you want to output the results in reversed order? In that case use `DESC` instead of `ASC`. — KIKO Software, Jun 29 '19 at 08:01
how do you understand that a line has been added more or less recently ?? .. based on what value can be established which row is the last ??? — ScaisEdge, Jun 29 '19 at 08:27
as an example i make a form that will sent a value to the database then i want to output that value back to a table — Xenon Zox, Jun 29 '19 at 08:39

score 0 · Answer 1 · edited Jun 29 '19 at 14:45

0

Set your id column to auto increment Give your table another name than Database.

Then use:

SELECT * FROM `yourtable` ORDER BY `id` DESC LIMIT 0,1;

edited Jun 29 '19 at 14:45

Dharman

30,962
25
85
135

answered Jun 29 '19 at 09:02

Clément Tengip

618
6
19

showdev · Accepted Answer · 2019-06-29T08:54:35.853

-1

I suggest including one column (like a primary key) that's set to AUTO_INCREMENT. That way you can sort in descending order (DESC) by that ID to get the latest entry.

Something like this:

CREATE TABLE `database` (
    id MEDIUMINT NOT NULL AUTO_INCREMENT,
    [other columns here]
    PRIMARY KEY (id)
);

And then:

SELECT * FROM `database` ORDER BY `id` DESC LIMIT 0,1;

Incidentally, I'm not sure if your table is really named "database". If so, I suggest not using a reserved word. It will work since you're using backticks, but it might be a good idea to change it anyway for various reasons.

edited Jun 29 '19 at 08:54

answered Jun 29 '19 at 08:48

showdev

28,454
37
55
73

You don't resolve the issue, you hide it by changing the database. What about the Foreign Keys? All already existing data? – JessGabriel Jun 29 '19 at 09:41
@JessGabriel The existing data can remain. Which foreign keys? Which issue is being hidden? The OP asked for a way to find the latest database entry, specifically how to determine if "a line has been added more or less recently" and "based on what value can be established which row is the last". Recording the order of entries is the [only way](https://dba.stackexchange.com/a/5775) to determine the last one. If you have a better way, please post it; I'd be interested to see your ideas. – showdev Jun 29 '19 at 10:08

How to output a value that was recently added into mysql database?

2 Answers2