CODEWITHSUNDEEP
phpmysqlarrays
Vytautas Paulauskas
Vytautas Paulauskas

Reputation: 71

Insert array into mysql table

I have a csv file formatted as follows:

a;a1;1;0;1
b;b1;1;0;0

And using a php script below I can convert it to this:

a;a1;1;
a;a1;0;
a;a1;1;
b;b1;1;
b;b1;0;
b;b1;0;

Using this:

$data = array();
foreach($lines as $value) {
    $value = explode(";", $value);

    $first = array_splice($value, 0, 2);

    foreach($value as $x){
        $row = $first;
        $row[] = $x;
        $data[] = implode(';', $row);
    }
}

Where the output is an array:

array(6) {
  [0]=>
  string(6) "a;a1;1"
  [1]=>
  string(6) "a;a1;0"
  [2]=>
  string(6) "a;a1;1"
  [3]=>
  string(6) "b;b1;1"
  [4]=>
  string(6) "b;b1;0"
  [5]=>
  string(6) "b;b1;0"
}

Now I'm trying to insert this array to a mysql table that I have set up, but I'm having trouble...

$file = "_ss.csv";
$lines = file($file);
$count = count($lines);
$data = array();
$i = 0;
foreach($lines as $value){
    $value = explode(";", $value);
    $first = array_splice($value, 0, 2); 

    foreach($value as $x){

        $row = $first;
        $row[] = $x;
        $data[] = implode(',', $row);
        $dump = implode(',', $data);
        $query="INSERT INTO csv_test2 VALUES ('$dump')";
        $init=mysql_query($query);
$i++;
echo $dump;

    }
}

echo "<pre>";
print_r($dump);
echo "</pre>";

Any ideas how to make it work?

Upvotes: 1

Views: 2358

Answers (6)

Vytautas Paulauskas
Vytautas Paulauskas

Reputation: 71

OK, So here is the solution I came up with for exactly what I needed.

foreach(explode($lineseparator,$csvcontent) as $value){
      $lines++;
      $value = trim($value," \t");
      $value = str_replace("\r","",$value);
      $value = str_replace("'","\'",$value); 
      $linearray = explode($fieldseparator,$value);
      $linemysql = implode("','",$linearray); // sitas rdy siuntimui i db
      $first = array_splice($linearray, 0, 2); 
    foreach($linearray as $x){
            $row = $first;
            $row[] = $x;
            $data[] = implode("','",$row);    
            }
}
foreach ($data as $id) {
    echo "<pre>";
    var_dump($id);
    echo "</pre>";
    $query="INSERT INTO csv_test3 VALUES ('$id')";
    $init=mysql_query($query);
}

This is a part of a code that basically takes a csv file, formatted as stated @OP, converts it and dumps everything into a mysql table.

Upvotes: 1

sreng
sreng

Reputation: 153

First, I suggest you print out query. I have made some modification on your code to print query out:

$file = "_ss.csv";
$lines = file($file);
$count = count($lines);
$data = array();
$i = 0;
foreach($lines as $value){
    $value = explode(";", $value);
    $first = array_splice($value, 0, 2); 

    foreach($value as $x){

        $row = $first;
        $row[] = $x;
        $data[] = implode(',', $row);
        $dump = implode(',', $data);
        $query="INSERT INTO csv_test2 VALUES ('$dump')";
        echo $query."<br/>";
        //$init=mysql_query($query);

        $i++;
    //echo $dump."<br/>";
    }
}

At my side, it print similar to this:

INSERT INTO csv_test2 VALUES ('a,a1,1')
  • If your table csv_test2 contains only one field of type that store text (can be varchar, char, ...), this query would be OK.

  • But if it contains more fields that is/are required (not null, unique, ...), this query won't work. You need to write:

    INSERT INTO csv_test2(a_field_name) VALUES ('a,a1,1')

  • But if you want to insert each element of array correspond to each fields, your code won't work at this case. You need to make query like this:

    INSERT INTO csv_test2 VALUES ('a','a1','1') So, you need to do $dump = implode("','", $data);

Upvotes: 0

Again
Again

Reputation: 1

There's better way without query in foreach:

$data = array();

foreach ($lines as $value){
  // do something
  $data[] = "('".serialize($value)."')";
}

$query = mysql_query("INSERT INTO csv_test2 VALUES".implode(',', $data)."");
  • The MySQL extension is deprecated and will be removed in the future, please use MySQLi or PDO.

Upvotes: 0

Stephan
Stephan

Reputation: 8090

You have 2 options :

  1. store the array as serialized in mysql (using blob type for the column)
  2. encode the array into a json and store it in mysql in a blob column

UPDATE: sample code for serialization , keep in mind that blob_test2 table has a blob column where the serialized array is inserted

$file = "_ss.csv";
$lines = file($file);
$count = count($lines);
$data = array();
$i = 0;
foreach($lines as $value){
    $value = explode(";", $value);
    $query="INSERT INTO blob_test2 VALUES ('".serialize($value)."')";
    $init=mysql_query($query);
}

Upvotes: 0

JOE LEE
JOE LEE

Reputation: 1058

$data = array();
foreach($lines as $value) {
    $value = explode(";", $value);

    $first = array_splice($value, 0, 2);
    unset($value[3]); // <---------------------- point
    foreach($value as $x){

        $first[]=$x;

        $data[] = $first;
    }
}

foreach($data as $v){
    $dump = implode(',', $v);
    $query="INSERT INTO csv_test2 VALUES ('$dump')";
    $init=mysql_query($query);
}

//mysql not good pls mysqli

Upvotes: 0

Ganesh Bora
Ganesh Bora

Reputation: 1153

you can folllow below steps

  1. convert the data into excel format
  2. escape the required characters with \
  3. again convert it into csv
  4. then import using program or any other import tool

Upvotes: 0

Related Questions