How to select the latest record in mysql?

Question

I want to select the most recent entry from the table and see if this entry matches the one that the user is trying to enter. How to execute the query "select" from the last posting post ??

$query="Select * FROM //confused here (SELECT * FROM posting ORDER BY date_added DESC) WHERE user_id='{$_SESSION['user_id']}' AND title='$title' AND price='$price' AND city='$city' AND state='$state' AND detail='$detail' "; $data = mysqli_query($dbc, $query); $row = mysqli_fetch_array($data); if(mysqli_num_rows($data)>0) { echo "You already posted this ad. Most likely caused by refreshing too many times."; echo "<br>"; $linkposting_id=$row['posting_id']; echo "See the <a href='ad.php?posting_id=$linkposting_id'>Ad</a>"; } else { ...insert into the dbc } //would this query work? or how do i use it to select the last ID in the table 'posting'? $query="SELECT LAST_INSERT_ID(posting) WHERE user_id='{$_SESSION['user_id']}' AND title='$title' AND price='$price' AND city='$city' AND state='$state' AND detail='$detail' "; 

Answer 1

The orderby element should appear at the end of your request. So the query you are requesting is something like this:

 select * from posting where .... order by date_added desc limit 1; 

One-way tokens can also help prevent duplication of views.

Answer 2

Based on your comments, one of the following queries will retrieve the only last record:

 SELECT * FROM posting ORDER BY date_added DESC LIMIT 1 SELECT * FROM posting ORDER BY posting_id DESC LIMIT 1 

Answer 3

Usually you will have a unique identifier in the table and specify this column auto_increment . You may already have such an identifier.

Then you can get the last record identifier using PHP mysql_insert_id () or mySQL LAST_INSERT_ID () .

The latter would be preferable because, according to the manual, it stores the connection bill, so other processes or web pages cannot ruin the bill between them. It is not entirely clear from the PHP manual whether the PHP function does the same.

Answer 4

I suggest creating a unique key on your desk, rather than doing what you described. This way you avoid duplicating content no matter how many users are using your application at the same time. Your solution will not work if A inserts something, B inserts something else, then A sends again.

An example of adding a unique key to an existing table:

 ALTER TABLE posting add UNIQUE KEY uk_posting (title, price, city) 

Replace title, price, city combination of fields, which should be unique. Then all you have to do is handle the insert error.

Answer 5

Limit your query to the last (last) record with LIMIT 1