PHP: Get Yesterday's Date

echo date("Y-m-d", strtotime("-1 day") );

For some specific date:

echo date("Y-m-d", strtotime("2010-Jan-1 -1 day") );

Play around with this function to get your desired results. The PHP date parser is very smart.

MySQL/SQL: 'ON DUPLICATE KEY UPDATE' for multiple rows insert in single query

Hello All.

I had a sql query where I wanted to insert multiple rows in single query. so I used something like:

$sql = "INSERT INTO beautiful (name, age)
  VALUES
  ('Helen', 24),
  ('Katrina', 21),
  ('Samia', 22),
  ('Hui Ling', 25),
  ('Yumie', 29)";

mysql_query( $sql, $conn );

The problem was when I executed this query, I wanted to check whether a UNIQUE key (which is not the PRIMARY KEY), for eg. 'name' in above case, should be checked and if such a 'name' already exists, the corresponding whole row should be updated otherwise inserted.

For instance, in below eg., if 'Katrina' is already present in database, the whole row, irrespective of number of fields, should be updated. Again if 'Samia' is not present, the row should be inserted.

I thought of using:

INSERT INTO beautiful (name, age)
      VALUES
      ('Helen', 24),
      ('Katrina', 21),
      ('Samia', 22),
      ('Hui Ling', 25),
      ('Yumie', 29) ON DUPLICATE KEY UPDATE

Here is the trap. I got stuck and confused how to proceed. I had multiple rows to insert/update at a time. Then StackOverflow came to my rescue. Thanks to Peter Lang.

The Solution:

INSERT INTO beautiful (name, age)
      VALUES
      ('Helen', 24),
      ('Katrina', 21),
      ('Samia', 22),
      ('Hui Ling', 25),
      ('Yumie', 29)
ON DUPLICATE KEY UPDATE
     age = VALUES(age),
     ...