Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

MySQL INSERT IGNORE - Check if insert happened

Posted on 2012-08-13
11
Medium Priority
?
1,209 Views
Last Modified: 2012-09-27
How can I check if the insert happened when using IGNORE - can it be done in one statement?
0
Comment
Question by:Gary
10 Comments
 
LVL 66

Expert Comment

by:Jim Horn
ID: 38288085
<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
 
LVL 58

Author Comment

by:Gary
ID: 38288220
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
 
LVL 58

Accepted Solution

by:
Gary earned 0 total points
ID: 38288264
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 66

Expert Comment

by:Jim Horn
ID: 38288283
>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
 
LVL 58

Author Comment

by:Gary
ID: 38288317
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
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 38290061
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
 
LVL 58

Author Comment

by:Gary
ID: 38290117
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
 
LVL 17

Expert Comment

by:jrm213jrm213
ID: 38291921
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
 
LVL 58

Author Comment

by:Gary
ID: 38291971
$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
 
LVL 58

Author Closing Comment

by:Gary
ID: 38440057
Solved myself
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
The viewer will receive an overview of the basics of CSS showing inline styles. In the head tags set up your style tags: (CODE) Reference the nav tag and set your properties.: (CODE) Set the reference for the UL element and styles for it to ensu…
The viewer will learn the basics of jQuery, including how to invoke it on a web page. Reference your jQuery libraries: (CODE) Include your new external js/jQuery file: (CODE) Write your first lines of code to setup your site for jQuery.: (CODE)
Suggested Courses

581 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question