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']\'");}