Insert array into MySQL DB with PHP

Question

Hello everyone im new with php so i need some help.

I have a .txt with some information, let's say this:

970,3584,D53,20170102,OL7GU9,9607,1AT03U2,A,
970,3588,F7A,20170102,OL7GY6,9607,1AT03U2,A,

Each one of this values i want to insert them into my DB (i already have the connection).

My code so far is this:

$myfile = fopen("crawler.txt", "r") or die("Unable to open file!");

    if ($myfile) {
        $columns = explode(",", fread($myfile, filesize("crawler.txt")))
    }

At this point i have an array like this:

array(8825) { [0]=> string(3) "970" [1]=> string(4) "3584" [2]=> string(3) "D53" [3]=> string(8)
"20170102" [4]=> string(6) "OL7GU9" ....and so goes

How do i do the insert each element in the array to the DB?

My current code:

foreach ($columns as $key => $value) { 
    $var1= $columns[0]; 
    $var2= $columns[1]; 
    $var3= $columns[2]; 
    $var4= $columns[3]; 
    $var5= $columns[4]; 
    $var6= $columns[5]; 
    $var7= $columns[6]; 
}

Are you using PDO or MySqli? What have you tried? Do you have the database structure to support these info? — Antony
– Antony, Commented Jan 23, 2017 at 17:49
Im using MYSQL, i'm trying these right now: foreach ($columns as $key => $value) { $var1= $columns[0]; $var2=$columns[1]; $var3=$columns[2]; $var4=$columns[3]; $var5=$columns[4]; $var6=$columns[5]; $var7=$columns[6]; } and yes i have the DB structure to support these info. — Pablo Figueroa
– Pablo Figueroa, Commented Jan 23, 2017 at 18:12

Stjubit · Accepted Answer · 2017-01-23 21:00:34Z

Basically you just have to build the insert query and execute it like this. I used PDO for this example:

$myfile = fopen("crawler.txt", "r") or die("Unable to open file!");

if ($myfile)
{
  while (($line = fgets($myfile)) !== false)
  {
    // remove the comma at the end of every line
    $line = rtrim($line, ',');

    // example with PDO
    $sql = 'INSERT INTO table_name (column_name1, column_name2, ...) VALUES (:param1, :param2, ...)';

    // build the parameter array
    $elements = explode(',', $line);

    // prepare the parameter array
    $parameters = array();
    for($i=0;$i<len($elements); $i++)
    {
      $parameters["param$i"] = $elements[$i];
    }

    $insertquery = $connection->prepare($sql);
    $insertquery->execute($parameters);
  }

  fclose($myfile);
}

We iterate through every line in the text file. Every line has a comma (,) at the end of it. We need to remove it, because explode would return another empty array element if we wouldn't. Next we just have to build the insert query and pass it our array elements.

Collectives™ on Stack Overflow

Insert array into MySQL DB with PHP

1 Answer 1

4 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Related