We help IT Professionals succeed at work.

MySQL INSERT IGNORE - Check if insert happened

How can I check if the insert happened when using IGNORE - can it be done in one statement?
Comment
Watch Question

Jim HornSQL Server Data Dude
CERTIFIED EXPERT
Most Valuable Expert 2013
Author of the Year 2015

Commented:
<SQL Server answer, may not be MySQL answer>

Use @@ROWCOUNT in the statement immediately after the INSERT>

INSERT INTO Table1 (goo, foo, boo)
SELECT gooey, fooey, booey
FROM Table2

SELECT 'You just inserted ' + CAST(@@ROWCOUNT as varchar(max)) + ' rows.'
CERTIFIED EXPERT
Expert of the Year 2014
Top Expert 2014

Author

Commented:
Its SELECT ROW_COUNT();
Which runs fine when I execute the resulting sql directly in MySQL but from PHP I get
check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT ROW_COUNT(); INSERT INTO
There is a insert statement before the SELECT ROW_COUNT()
CERTIFIED EXPERT
Expert of the Year 2014
Top Expert 2014
Commented:
Switched over to PDO which has rowCount and it returns the correct result even though there are two inserts in the one statement.
First one may insert, may not, but the second one will always insert, but still the rowcount will only ever return 0 or 1 depending on the first statement - interesting...
Jim HornSQL Server Data Dude
CERTIFIED EXPERT
Most Valuable Expert 2013
Author of the Year 2015

Commented:
>there are two inserts in the one statement.
Please copy-paste that statement into this question.

Also, I recommend removing VB.NET, and adding PHP zone to this question.
Not every MySQL dude will PHP.
CERTIFIED EXPERT
Expert of the Year 2014
Top Expert 2014

Author

Commented:
Well it's working will just leave it open a little while longer so someone maybe able to explain it to me.
Never added .net to the zones...
Most Valuable Expert 2011
Top Expert 2016

Commented:
In my experience, this function returns values that make sense.  I think it would be worth testing to see if it gives you what you want.
http://php.net/manual/en/function.mysql-affected-rows.php
CERTIFIED EXPERT
Expert of the Year 2014
Top Expert 2014

Author

Commented:
The PDO rowcount is what I'm using.
Just curious why the row count only returns the number of rows (either 1 or 0) for the first statment (which might not insert) and not for the second insert (which always inserts)
How its working now is what I want.
Just curious why it doesnt count the rows for the second insert - is this a reliable method for getting the row count.
Hi,

Could you post your code showing how you are using pdo to do the inserts and get the rowcount? It would probably help in determining what is going on.
CERTIFIED EXPERT
Expert of the Year 2014
Top Expert 2014

Author

Commented:
$del = $pdo->prepare(
"INSERT IGNORE INTO table1 (col1,col2) values ($col1,$col2);
INSERT INTO table2 (col3,col4) values ($col3,$col4);"
);

$del->execute();

$count = $del->rowCount();

Open in new window

CERTIFIED EXPERT
Expert of the Year 2014
Top Expert 2014

Author

Commented:
Solved myself