CODEWITHSUNDEEP
phpmysql
ggfan
ggfan

Reputation: 2492

How do I select the most recent entry in mysql?

i want to select the most recent entry from a table and see if that entry is exactly the same as the one the user is trying to enter. How do I do a query to "select * from the most recent entry of 'posting'"?

    $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'
     ";

Upvotes: 5

Views: 16571

Answers (5)

jweyrich
jweyrich

Reputation: 32251

I suggest creating a unique key on your table instead of doing what you described. This way you avoid duplicated content independent of how many users are using your application concurrently. Your solution would fail if A inserts something, B inserts something else, then A submits again.

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 with the fields combination that must be unique. Then all you have to do is to handle the error upon insert.

Upvotes: 1

Dolph
Dolph

Reputation: 50720

Based on your comments, either of the following queries will retrieve the single latest record:

SELECT * FROM posting ORDER BY date_added DESC LIMIT 1

SELECT * FROM posting ORDER BY posting_id DESC LIMIT 1

Upvotes: 4

grossvogel
grossvogel

Reputation: 6782

The orderby piece needs to come at the end of your query. So the query you're asking for is something like this:

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

Single-use form tokens can help prevent duplicate submissions, too.

Upvotes: 10

Pekka
Pekka

Reputation: 449823

You would usually have a unique identifier in your table and give that column an auto_increment. It's possible you already have such an identifier.

You can then fetch the latest record's ID using PHP's mysql_insert_id() or mySQL's LAST_INSERT_ID().

The latter would be preferable because according to the manual, it keeps the count per connection, so other processes or web pages can't screw up the count in between. It's not entirely sure from the PHP manual whether the PHP function does the same.

Upvotes: 1

Brian Roach
Brian Roach

Reputation: 76918

Limit your query to the last (most recent) entry with LIMIT 1

Upvotes: 0

Related Questions