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?
LVL 58
GaryAsked:
Who is Participating?
 
GaryAuthor 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...
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
<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.'
0
 
GaryAuthor 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()
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>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.
0
 
GaryAuthor 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...
0
 
Ray PaseurCommented:
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
0
 
GaryAuthor 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.
0
 
jrm213jrm213Commented:
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.
0
 
GaryAuthor 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

0
 
GaryAuthor Commented:
Solved myself
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.