CODEWITHSUNDEEP
phpmysql
Wilest
Wilest

Reputation: 1860

Inserting multiple records into mysql and mysql_affected_rows()

I'm uploading a csv from a form. I'm trying to use mysql_affected_rows to determine if it was successful. The data is uploading into table but the mysql_affected_rows does not evaluate to true. Please help:

<?php
session_start();
$idnewtest1 = $_GET['idnewtest1'];

      move_uploaded_file($_FILES["fileCSV"]["tmp_name"],
      "quiz/" . $_FILES["fileCSV"]["name"]);

    $objConnect = mysql_connect("localhost","root","...") or die(mysql_error()); // Conect to MySQL
    $objDB = mysql_select_db("testing");

$objCSV = fopen("quiz/".$_FILES["fileCSV"]["name"], "r");
fgetcsv($objCSV, 1000, ","); // skip first row

        while (($objArr = fgetcsv($objCSV, 1000, ",")) !== FALSE) {
        $strSQL = "INSERT INTO ex_question1 ";
        $strSQL .="(id,test_name,q_nr,....) ";
        $strSQL .="VALUES ";
        $strSQL .="('0','".$idnewtest1."','".$objArr[0]."'....) ";
        $objQuery = mysql_query($strSQL);
    }
    fclose($objCSV);

if (mysql_affected_rows() > 1) {
echo "Import completed.";
}    

?>

Upvotes: 1

Views: 655

Answers (3)

maxjackie
maxjackie

Reputation: 23312

you can use this INSERT query

INSERT INTO ex_question1
(id,test_name,q_nr,....)
VALUES (...............),
(...............),
(...............),
(...............);

this will insert all rows in one go

Upvotes: 1

Sudhir Bastakoti
Sudhir Bastakoti

Reputation: 100195

Do:


<?php
$affectedRows = 0;
    while (($objArr = fgetcsv($objCSV, 1000, ",")) !== FALSE) {
        $strSQL = "INSERT INTO ex_question1 ";
        $strSQL .="(id,test_name,q_nr,....) ";
        $strSQL .="VALUES ";
        $strSQL .="('0','".$idnewtest1."','".$objArr[0]."'....) ";
        $objQuery = mysql_query($strSQL);
        $affectedRows +=mysql_affected_rows();
    }
    fclose($objCSV);

if ($affectedRows > 1) {
echo "Import completed.";
}    
?>

Upvotes: 2

Sonal Khunt
Sonal Khunt

Reputation: 1894

There are multiple transaction are processing via while loop so multiple insert statement, each return 1 to mysql_affected_row(),

and second thing you use function out side while loop and condition >1 is never satisfy bcz every time mysql_affected_row() return 1...

Upvotes: 2

Related Questions