INSERT INTO and ON DUPLICATE KEY

[jtw]

I need to update a statistics table through out the day. I would like
to insert the first data sampling of the data, then update the
existing record for the rest of the day. The problem is that I do not
alway know when the first sampling will start.

Keys for the table are username and date. So each user will have only
one entry per day.

I have been checking to see if the record exists - select .. where
username=... AND date =.... Seems like there is a better way.
I found the 'ON DUPLICATE KEY' in the mysql manual. Just can not get
this to work with PHP.
Sample would be.

INSERT INTO usagestats (username, date, usageinfo)
VALUES(username = 'jon', date = '2003-09-19', useageinfo='250315')
ON DUPLICATE KEY UPDATE usageinfo = '250315'
In this case if the record with username='jon' and date='2003-09-19'
already exists, then the ON DUPLICATE KEY would update this record
with usageinfo = '...'
What am I missing?

jtw

--

http://www.UsenetRocket.com

[Andy Hassall]

On Fri, 19 Sep 2003 16:46:58 -0400, jtw <jw*****@mailboxspot.com> wrote:

I need to update a statistics table through out the day. I would like
to insert the first data sampling of the data, then update the
existing record for the rest of the day. The problem is that I do not
alway know when the first sampling will start.

Keys for the table are username and date. So each user will have only
one entry per day.

I have been checking to see if the record exists - select .. where
username=... AND date =.... Seems like there is a better way.
Depending on whether inserts or updates are more likely:

INSERT the row - catch the key violation (1036) error - if you got one, do an
UPDATE instead.

or

UPDATE the row - check mysql_affected_rows - if zero, do an INSERT.
I found the 'ON DUPLICATE KEY' in the mysql manual. Just can not get
this to work with PHP.

Sample would be.

INSERT INTO usagestats (username, date, usageinfo)
VALUES(username = 'jon', date = '2003-09-19', useageinfo='250315')
ON DUPLICATE KEY UPDATE usageinfo = '250315'

In this case if the record with username='jon' and date='2003-09-19'
already exists, then the ON DUPLICATE KEY would update this record
with usageinfo = '...'

What am I missing?

The error message or unexpected behaviour you're seeing, and your MySQL
version.

The manual says that syntax isn't available until MySQL 4.1.

--
Andy Hassall (an**@andyh.co.uk) icq(5747695) (http://www.andyh.co.uk)
Space: disk usage analysis tool (http://www.andyhsoftware.co.uk/space)

[jtw]

Thanks Andy. Sometimes the simple solutions are the best.

jtw

On Fri, 19 Sep 2003 22:04:30 +0100, Andy Hassall <an**@andyh.co.uk>
wrote:

On Fri, 19 Sep 2003 16:46:58 -0400, jtw <jw*****@mailboxspot.com> wrote:

I need to update a statistics table through out the day. I would like
to insert the first data sampling of the data, then update the
existing record for the rest of the day. The problem is that I do not
alway know when the first sampling will start.

Keys for the table are username and date. So each user will have only
one entry per day.

I have been checking to see if the record exists - select .. where
username=... AND date =.... Seems like there is a better way.

Depending on whether inserts or updates are more likely:

INSERT the row - catch the key violation (1036) error - if you got one, do an
UPDATE instead.

or

UPDATE the row - check mysql_affected_rows - if zero, do an INSERT.

I found the 'ON DUPLICATE KEY' in the mysql manual. Just can not get
this to work with PHP.

Sample would be.

INSERT INTO usagestats (username, date, usageinfo)
VALUES(username = 'jon', date = '2003-09-19', useageinfo='250315')
ON DUPLICATE KEY UPDATE usageinfo = '250315'

In this case if the record with username='jon' and date='2003-09-19'
already exists, then the ON DUPLICATE KEY would update this record
with usageinfo = '...'

What am I missing?

The error message or unexpected behaviour you're seeing, and your MySQL
version.

The manual says that syntax isn't available until MySQL 4.1.

--

http://www.UsenetRocket.com