Select... Insert

[bokeh]

SELECT cc FROM ip_table WHERE start <= $start AND end >= $end

INSERT INTO online (ip, when, cc)
VALUES ($ip, NOW(), result_of_select_query)
ON DUPLICATE KEY update when = NOW()How do I make this one query so the result of the SELECT query will be used as a value in the INSERT query

[chazzy]

INSERT INTO online (ip, when, cc)
VALUES ($ip, NOW(), SELECT cc FROM ip_table WHERE start <= $start AND end >= $end)
ON DUPLICATE KEY update when = NOW();

that should be it.

[bokeh]

What would happen if the select query returned zero or multiple results?

[chazzy]

actually i wrote that wrong :-D


INSERT INTO online (ip, when, cc)
SELECT '$ip', NOW(), cc FROM ip_table WHERE start <= $start AND end >= $end
ON DUPLICATE KEY update when = NOW();


0 rows = 0 inserts, multiple rows = multiple inserts.

[bokeh]

Well it couldn't do multiple insert because that would be a duplicate key situation.

[chazzy]

Well it couldn't do multiple insert because that would be a duplicate key situation.

You made the date column your primary key? i've typically had so many problems w/ that it's not even funny.

[bokeh]

You made the date column your primary key? i've typically had so many problems w/ that it's not even funny.No. The IP is the primary key and the datetime is an index. I don't know if that was necessary but I am going to run the majority of queries based on that column.

CREATE TABLE `online_cc` (
`ip` INT UNSIGNED NOT NULL ,
`when` DATETIME NOT NULL ,
`cc` CHAR( 2 ) NOT NULL ,
PRIMARY KEY ( `ip` ) ,
INDEX ( `when` )
)

[bokeh]

Ok... it worked... except when is reserved it would seem.

[chazzy]

indexing on a single column is almost always fruitless... index ip, when and just when...

[bokeh]

Well I thought it worked but it does not.
Query:INSERT INTO `online_cc` (`ip`, `updated`, `cc`)
SELECT 3232235801, NOW(), `cc` FROM `ip_table_condensed` WHERE `start` <= 3232235801 AND `end` >= 3232235801
ON DUPLICATE KEY UPDATE `updated` = NOW()Error:Column 'updated' specified twice

[aussie girl]

I would have an ID column that auto_increments, then you wont have duplicate keys.

[bokeh]

I would have an ID column that auto_increments, then you wont have duplicate keys.
The whole purpose of having `ip` as a key is so it cannot be duplicated. This is not an error.

[aussie girl]

So of the two tables which is the parent and which is the child? It's a little confusing as well, is 3232235801 a column name as well as a value?

[russell]

what happens if u comment out

ON DUPLICATE KEY UPDATE `updated` = NOW()

[aussie girl]

The whole purpose of having `ip` as a key is so it cannot be duplicated. This is not an error.

Have a read here
http://bugs.mysql.com/bug.php?id=8732

[bokeh]

So of the two tables which is the parent and which is the child? It's a little confusing as well, is 3232235801 a column name as well as a value?They are not a parent and child. They are two completely seperate tables. 3232235801 is a value that is the first value used in the insert statement and has nothing to do with the select. Anyway there is nothing wrong with that part of the query.

what happens if u comment out
ON DUPLICATE KEY UPDATE `updated` = NOW()That is correct. As long as the select returns a result the query does the insert fine. It would seem there is some strange interaction between line 3 and the rest of the query although there is no parse error.

[aussie girl]

They are not a parent and child. They are two completely seperate tables. 3232235801 is a value that is the first value used in the insert statement and has nothing to do with the select. Anyway there is nothing wrong with that part of the query.

That is correct. As long as the select returns a result the query does the insert fine. It would seem there is some strange interaction between line 3 and the rest of the query although there is no parse error.

Did you check out the link I put in a previous post?

[bokeh]

Well the bug is not identical but it looks relevant. I was trying to avoid two queries but for the time being I've done the following (unless anyone has got a better idea).$query = "INSERT INTO `online_cc` (`ip`, `updated`, `cc`) ".
"SELECT $ip, NOW(), `cc` FROM `ip_table_condensed` ".
"WHERE `start` <= $ip AND `end` >= $ip";

if(!mysql_query($query))
{
if(preg_match('/duplicate.*key/i', mysql_error()))
{
$query = "UPDATE `online_cc` ".
"SET `updated` = NOW() ".
"WHERE `ip` = $ip";
mysql_query($query);
}
}

[chazzy]

Well the bug is not identical but it looks relevant. I was trying to avoid two queries but for the time being I've done the following (unless anyone has got a better idea).

I have a better idea, it's called a stored procedure. PHP shouldn't need to know if you had to do an insert or an update.

but then again, I don't think you're running mysql >= 5 so... nevermind.