PHP: put each data into single quote(' ') while inserting in SQL

Question

I have a code that allows me to import a data from csv files to my database. However, there's a problem inserting the data since the values that i am going to insert has no single quote (''). I tried to echo out the sql query and i got this:

INSERT INTO bill_of_materials(allotment_code, category_name) VALUES(Site Electrical,Aldea Electrical Work ),(CM-S24,Assembly ),(CM-S4,Assembly ),(CM-S4,Assembly ),(CM-S8,Assembly ),(CM-S3,Assembly ),(CM-S3,Assembly ),(CM-S3,Assembly ),(CM-S8,Assembly ),(CM-S4,Assembly ),(CM-S24,Assembly ),(CM-S8,Assembly ),(CM-S23,Assembly ),(CM-S23,Assembly ),(CM-S23,Assembly ),(CM-S22,Assembly ),(CM-S22,Assembly ),(CM-S22,Assembly ),(CM-S24,Assembly ),(CM-D2,Assembly ),(CM-D18,Assembly ),(CM-D18,Assembly ),(CM-D14,Assembly ),(CM-D14,Assembly ),(CM-D14,Assembly ),(CM-D20,Assembly ),(CM-D20,Assembly ),(CM-D20,Assembly ),(CM-D13,Assembly ),(CM-D13,Assembly ),(CM-D10,Assembly ),(CM-D18,Assembly ),(CM-D10,Assembly ),(CM-D13,Assembly ),(CM-D2,Assembly ),(CM-D2,Assembly ),(CM-D21,Assembly ),(CM-D21,Assembly ),(CM-D21,Assembly ),(CM-D11,Assembly ),(CM-D11,Assembly ),(CM-D11,Assembly ),(CM-D12,Assembly ),(CM-D12,Assembly ),(CM-D12,Assembly ),(CM-D10,Assembly ),(CM-D19,Assembly ),(CM-D17,Assembly ),(CM-D17,Assembly ),(CM-D19,Assembly ),(CM-D16,Assembly ),(CM-D15,Assembly ),(CM-D15,Assembly ),(CM-D15,Assembly ),(CM-D17,Assembly ),(CM-D19,Assembly ),(CM-D1,Assembly ),(CM-D1,Assembly ),(CM-D16,Assembly ),(CM-D16,Assembly ),(CM-D1,Assembly ),(CM-S17,Assembly ),(CM-S18,Assembly ),(CM-S18,Assembly ),(CM-D26,Assembly ),(CM-D26,Assembly ),(CM-D26,Assembly ),(CM-S16,Assembly ),(CM-S16,Assembly ),(CM-D4,Assembly ),(CM-D4,Assembly ),(CM-D3,Assembly ),(CM-D3,Assembly ),(CM-D25,Assembly ),(CM-S17,Assembly ),(CM-S21,Assembly ),(CM-D9,Assembly ),(CM-D9,Assembly ),(CM-D9,Assembly ),(CM-S17,Assembly ),(CM-D8,Assembly ),(CM-D8,Assembly ),(CM-D8,Assembly ),(CM-S12,Assembly ),(CM-S12,Assembly ),(CM-S12,Assembly ),(CM-D25,Assembly ),(CM-D25,Assembly ),(CM-D3,Assembly ),(CM-D5,Assembly ),(CM-S13,Assembly ),(CM-S13,Assembly ),(CM-S13,Assembly ),(CM-S19,Assembly ),(CM-S19,Assembly ),(CM-S19,Assembly ),(CM-S20,Assembly ),(CM-S20,Assembly ),(CM-S20,Assembly ),(CM-D7,Assembly ),(CM-D7,Assembly ),(CM-D7,Assembly ),(CM-S18,Assembly ),(CM-D5,Assembly ),(CM-S21,Assembly ),(CM-D22,Assembly ),(CM-D22,Assembly ),(CM-D22,Assembly ),(CM-S15,Assembly ),(CM-S15,Assembly ),(CM-S15,Assembly ),(CM-S11,Assembly ),(CM-S11,Assembly ),(CM-S11,Assembly ),(CM-D23,Assembly ),(CM-S21,Assembly ),(CM-D4,Assembly ),(CM-D5,Assembly ),(CM-D24,Assembly ),(CM-D24,Assembly ),(CM-D23,Assembly ),(CM-D23,Assembly ),(CM-D6,Assembly ),(CM-S14,Assembly )

i have my PHP codes below:

<form method="post" enctype="multipart/form-data">
<input type="file" name="csv" value="" />
<input type="submit" name="submit" value="Save" /></form>

<?php

$new_conn = mysqli_connect('localhost', 'root', '153624123', 'db_lazvasmunhomesinc');

if(isset($_FILES['csv']['tmp_name'])) {
    $data = $_FILES['csv']['tmp_name'];
    $handle = fopen($data, "r");
    $test = file_get_contents($data);

    if(!empty($data)) {

        if ($handle) {
            $counter = 0;
            //instead of executing query one by one,
            //let us prepare 1 SQL query that will insert all values from the batch
            $sql ="INSERT INTO bill_of_materials(allotment_code, category_name) VALUES";
            while (($line = fgets($handle)) !== false) {
              $sql .="($line),";
              $counter++;
            }
            $sql = substr($sql, 0, strlen($sql) - 1);
             if (mysqli_query($new_conn, $sql) === TRUE) {
                echo 'success';
            } else {
                echo $sql;
             }
            fclose($handle);
        } else {  
        } 
        //unlink CSV file once already imported to DB to clear directory
        unlink($data);
    } else
        echo '<script>alert("EMPTY!");</script>';
}
?>

I tried to use another code which is:

if(!empty($data)) {

if ($handle) {
    $counter = 0;
    //instead of executing query one by one,
    //let us prepare 1 SQL query that will insert all values from the batch
    $sql ="INSERT INTO bill_of_materials(allotment_code, category_name) VALUES";
    while (($line = fgets($handle)) !== false) {
      $sql .= "('".implode("', '", explode(",", $line))."'),";
      $counter++;
    }
    $sql = substr($sql, 0, strlen($sql) - 1);
     if (mysqli_query($new_conn, $sql) === TRUE) {
        echo 'success';
    } else {
        echo $sql;
     }
    fclose($handle);
}

it works but with a little problem. since it will split the data after comma, some of the data from CSV FILE has a comma which will result to this:

INSERT INTO bill_of_materials(allotment_code, category_name) VALUES('"OH:Fuel', ' Oil and Accessories"', 'Avanza Gray-OBNO-1782 ')

The data from the csv file is: oh: Fuel, Oil and Accessories and Avanza Gray-OBNO-1782

the expected output should be:

INSERT INTO bill_of_materials(allotment_code, category_name) VALUES('"OH:Fuel Oil and Accessories"', 'Avanza Gray-OBNO-1782')

Another code that i tried is this:

    $sql ="INSERT INTO bill_of_materials(allotment_code, category_name) VALUES";
    while (($line = fgets($handle)) !== false) {
      $sql .= "('{$line[0]}', '{$data[1]}'),";
      $counter++;
    }

but the inserted data is incorrect and it results to this:

my csv file looks like this:

some of the data from my csv has a comma.

the data is not inside the single quote. I hope you can help me. thanks in advance.

Possible duplicate of How can I prevent SQL injection in PHP? — chris85
– chris85, Commented Aug 2, 2017 at 4:09
^ that is the answer, although not the topic you are asking about. — chris85
– chris85, Commented Aug 2, 2017 at 4:10

Dan · Accepted Answer · 2017-08-02 05:03:58Z

1

Since you're using CSV files you can parse every line in your while loop with str_getcsv():

$values = str_getcsv('"OH:Fuel, Oil and Accessoires",Avanza Gray-OBNO-1779');

... and insert the values back into the sql statement:

$sql .= "('" . array_shift($values) . "', '" . (isset($values) ? implode(",", $values) : "") . "'),";

And dont forget to apply rtrim() to the sql statement, so the last comma gets removed:

$sql = rtrim($sql, ",");

You can also use fgetcsv() to read and parse the CSV file line by line as an array:

Example:

$file = __DIR__ . "/data.csv";
$sql = "INSERT INTO `bill_of_materials` (`allotment_code`, `category_name`) VALUES ";

$handle = fopen($file, "r");

while ($data = fgetcsv($handle)) {
    $sql .= "('" . array_shift($data) . "', '" . (isset($data) ? implode(",", $data) : "") . "'),";
}

fclose($handle);

$sql = rtrim($sql, ",");

edited Aug 2, 2017 at 5:03

answered Aug 2, 2017 at 3:29

Dan

5,3782 gold badges21 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

13 Comments

Nibiru Nibiru Over a year ago

this actually works but with a little problem.. some of my data inside the csv file has a comma which will also split as another data. how can i fix it sir?

Dan Over a year ago

Is this data surrounded by single or double quotes? Can you post one of this specific lines?

Nibiru Nibiru Over a year ago

the data is from the csv files which has no single quote or double quotes. i used your code: $sql .= "('".implode("', '", explode(",", $line))."'),"; and it actually work. there's just a little problem.. there's some data that has a comma like for example: OH: Fuel, Oil and Gas. since there's a comma, PHP will split it as another data.

Dan Over a year ago

Okay, you read one line per iteration. Does some of this lines include more than one comma which is used to split values by each other? Because it's technically not possible in csv files not to surround commas with quotes which must not get splitted.

Nibiru Nibiru Over a year ago

some of the data from csv file has a comma sir. which is the problem

|

meda · Accepted Answer · 2017-08-02 03:30:44Z

0

Add the single quotes:

$sql .="('$line'),";

answered Aug 2, 2017 at 3:30

meda

45.5k14 gold badges96 silver badges123 bronze badges

2 Comments

Dan Over a year ago

This causes something like this INSERT INTO (col1, col2) VALUES ('value1, value2') and would end in an mysql error.

Nibiru Nibiru Over a year ago

@meda thanks for your answer sir.. but unfortunately, this won't work.

Collectives™ on Stack Overflow

PHP: put each data into single quote(' ') while inserting in SQL

2 Answers 2

13 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

13 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related