0

This is my working query for inserting to mysql from my php page:

$query = "INSERT INTO postings (title, description, timeStamp) VALUES ( '$title', '$description', STR_TO_DATE('$date', '%a, %d %M %Y %H:%i:%s'))";

What I'm hoping to do is now only insert if $date is greater than the most recent date in mysql.timeStamp (type of timestamp).

Can I do this? I've tried the following:

$query = "INSERT INTO postings (title, description, timeStamp) VALUES ( '$title', '$description', STR_TO_DATE('$date', '%a, %d %M %Y %H:%i:%s')) where STR_TO_DATE('$date', '%a, %d %M %Y %H:%i:%s') > timeStamp";

1) Can I compare a date from my php page to a mysql date on insert?
2) If so... How would I finish this query and make " > timeStamp" only evaluate the most recent date in the table (assuming the rest of my where clause works)?

Andi
  • 295
  • 2
  • 6
  • 15
  • if you use unix timestamps what your trying to do will be alot easier/efficient . At least for this sort of thing. Much easier to compare int values than all the work converting strings. – Rooster Dec 03 '11 at 21:14

2 Answers2

1

I think it is better if you first run a query to check if there are posts with a higher timestamp in your table before inserting the row. You can do this with this query:

$query = "SELECT 1 FROM postings 
    WHERE STR_TO_DATE('$date', '%a, %d %M %Y %H:%i:%s') < timeStamp
    LIMIT 1";

Then if no results are returned you can insert your post as normal.

rabusmar
  • 4,084
  • 1
  • 25
  • 29
  • great idea! i'll head down this road. Thanks! – Andi Dec 03 '11 at 21:25
  • I'm trying to surround my normal insert statement with the following. I have 1 result that I know should be true.. but nothing is being inserted. Any idea what I'm doing wrong? – Andi Dec 03 '11 at 22:12
  • `$querycheck = "SELECT 1 FROM postings WHERE city=$city and STR_TO_DATE('$date', '%a, %d %M %Y %H:%i:%s') > timeStamp LIMIT 1"; if ($querycheck === true) { my original insert statement here }` – Andi Dec 03 '11 at 22:24
0

1) No, you can't. You can check the sintax here

You can use a transaction to avoid concurrency of @rabusmar solution, check here some samples

You can also use a stored procedure, but is more complicated than a transaction and has same result.

Community
  • 1
  • 1
Paulo H.
  • 1,228
  • 10
  • 15