Update if record already exists for todays date

Is there is an easy way开发者_如何学Python to change the following query to check to see if a record already exists for todays date if it does to update it with the newest count value.

mysql_query("INSERT INTO daily_record (PageID, count)
             VALUES (".$array['page_id'].",".$array['count'].")");

The column I want to check is a CURRENT_TIMESTAMP field (record_date - part of daily_record table) if a todays date exists for a pageID then an update needs to happen rather than a new insert.

If someone can help that would be amazing!!!

---

Well if you build the daily_record table like this:

CREATE TABLE daily_record (
    pageID INT,
    record_date DATE,
    count INT,
    PRIMARY KEY (pageID,record_date),
        INDEX idxPageID (pageID)
)

You could then use the command:

INSERT INTO daily_record (
    pageID,record_date,`count`
) VALUES (
    1,'2011-03-31',32
) ON DUPLICATE KEY UPDATE `count`=32;

Obviously pageID/record_date/count would be supplied by the calling code. This effectively creates a record for the pageID/day with the given count, or if a record for the pageID/day already exists, then it sets the count to the supplied value.

Using the DATE column type prevents you getting free timestamping BUT that's not particularly useful for this table - the way you describe it - since you don't care about the hours/minutes/seconds.

The key here is the unique index created by the PRIMARY KEY... line. If it's uniqueness would be violated by an insert then an update on it can occur instead.

---

Best I can come up with is either use a select with if ... then to check for the existance ahead of time, or run the update statement first, check @@rowcount (records affected) and do an insert if it comes back with 0)

i.e. No.

---

[Edit - this is a little more complex than it seemed at first, because of the DATE thing.]

To UPDATE the record you MUST get the count that you want to update, so that requires you to use a SELECT first. But you need to select the records that are only for the current date.

Let's assume that you have done the code to get the current date into $today.

Then as Kendrick said,

$result=mysql_query("SELECT * from daily_record where PageID=\'$array['page_id']\' and date_field BETWEEN '$today'.' 00:00:00' AND '$today'.' 23:59:59'");

if (!$result)
   { mysql_query("INSERT into daliy_record (PageID,count} VALUES (\'$array['page_id']\',\'$array['count']\')"); }
else
   {mysql_query("UPDATE daily_record (count) VALUES (\'$array['count']\')  where PageID=\'$array['page_id']\' and date_field=\'$result['date_field']\'");}