INSERT IGNORE INTO - Number of rows inserted

Question

I'm going to insert about 500 records in a table using one query :

$sql = "INSERT IGNORE INTO `table_name` (`field1`,`field2`)
        VALUES ('val1','val2') ('val3','val4') ... ";
// php_mysql_insert_function

How can I find out haw many rows are inserted in after executing query ?

refer this one maybe helpful http://stackoverflow.com/questions/12623778/count-how-many-rows-inserted-from-last-sql-query — Aravin, Feb 01 '14 at 17:08
@Aravinth No it doesn't, my question is about `insert ignore` — Alireza Fallah, Feb 01 '14 at 17:10
affected_rows should work for insert ignore as well => http://stackoverflow.com/questions/5254799/insert-ignore-is-there-way-to-know-if-statement-was-ignored-mysql — Wikunia, Feb 01 '14 at 18:42
@Wikunia thats for one record, and returns 0 or 1, not for 500 insert in one query — Alireza Fallah, Feb 01 '14 at 19:19
@AlirezaFallah I tested my answer so it works for more than 1 inserted row in one query. — Wikunia, Feb 01 '14 at 19:58

Wikunia · Accepted Answer · 2016-08-19T13:09:11.357

4

The answer is affected_rows

$db = new mysqli('127.0.0.1','...','...','...');
$sql = "INSERT IGNORE INTO Test (id,test) VALUES (1,2),(1,3),(2,2),(3,4)";
$ins_test = $db->prepare($sql);
$ins_test->execute();
echo $db->affected_rows;

In this example Test has 2 columns id and test (both integer) and id is the primary key. The table is empty before this insert.

The programm echos 3.

edited Aug 19 '16 at 13:09

answered Feb 01 '14 at 19:35

Wikunia

1,564
1
16
37

Mr. Radical · Answer 2 · 2014-02-01T20:08:51.857

Try this:

Procedural style of coding:

<?php
$host = ''; 
$user = '';
$password = '';
$database = '';


$link = mysqli_connect($host, $user, $password, $database);

if(!$link)
{
    echo('Unable to connect to the database!');
}
        ELSE {
           $sql = "INSERT IGNORE INTO `table_name` (`field1`,`field2`) VALUES ('val1','val2'), ('val3','val4')"; 
           $result = mysqli_query($link, $sql);
                    echo mysqli_affected_rows($link);
                }

        mysqli_close($link);
?>

mysqli_affeccted_rows counts the number of inserts. I think that @wikunia's answer will probably yield the same result. I was in the process of answering you question, before wikunia beat me to it. I place it anyway.

INSERT IGNORE INTO - Number of rows inserted

2 Answers2