Use array in mysql insert query

Question

I got a Bash/PHP script that retrieves records from a table called 'assess_2012' and stores records that need to be written to a table called 'assess_2012_err' in a multidimensional array:

#!/usr/bin/php -q

<?php
$host = "localhost";
$user = "username";
$pass = "password";
$name = "reassess";

$conn = mysqli_connect($host, $user, $pass, $name) OR die ("Could not connect to database: " . mysqli_error($conn) . "\n");

$q = "SELECT su_id, ass_date, ind_d FROM assess_2012";
$r = mysqli_query ($conn, $q);

if ($r) {
    while ($row = mysqli_fetch_array($r, MYSQLI_ASSOC)) {

        // Check for errors:
        if ($row['ind_d'] == 'Y') {
            // Add to the array:
            $sql[] = array('su_id' => $row['su_id'], 'err_code' => 1);
        }
    }
    var_dump($sql);
}
else {
    // SELECT query failed:
    echo "Error: " . mysqli_error($conn) . "\n";
}
?>

The 'var_dump' looks like this (shortened version - the actual query returns hundreds of records):

array(1) {
  [0]=>
  array(2) 
    ["su_id"]=>
    string(1) "5"
    ["err_code"]=>
    int(1)
  }
  [1]=>
  array(2) {
    ["su_id"]=>
    string(4) "1492"
    ["err_code"]=>
    int(1)
  }
}

What I can't figure out is how I can use the array to produce a query like this:

INSERT INTO assess_2012_err (su_id, err_code) VALUES (5, 1), (1492, 1)

start with learning php.net/foreach. After that create a string with INSERT INTO assess_2012_err (su_id, err_code) VALUES and append it with actual values in a loop — zerkms
– zerkms, Commented Oct 24, 2012 at 23:20

William The Dev · Accepted Answer · 2012-10-24 23:31:57Z

2

Use a foreach loop to loop through data as you enter the values into the database.

Like:

foreach($sql as $key=>$value){

        foreach($value as $key_p=>$value_p){

            //Implement Query here
           //R.g
          //$key will have 'su_id'
         //$value_p will have 1

            }
}

edited Oct 24, 2012 at 23:31

answered Oct 24, 2012 at 23:22

William The Dev

5051 gold badge6 silver badges16 bronze badges

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

Comments

Martin Lyne · Accepted Answer · 2012-10-24 23:27:05Z

1

You'll be better off doing one by one, like so

foreach($sql as $variables)
{
  // Insert here: 
  INSERT INTO asses_2012_err (su_id, err_code) VALUES ($variables['su_id'], $variables['err_code']);

}

Some important things to bear in mind:

Treat those inputs as user inputs, sanitise them!
Try to use PDO as mysql_* functions are going to be deprecated
Consider a data abstraction layer (so all your queries are hidden away inside nice functions)

answered Oct 24, 2012 at 23:27

Martin Lyne

3,0952 gold badges24 silver badges29 bronze badges

1 Comment

unhack Over a year ago

Worked - thanks so much for your help. As an aside and for the benefit of other who are trying to do something similar, when assigning the insert query to a variable you need curly brackets around the values; i.e. $query = "INSERT INTO assess_2012_err (su_id, err_code) VALUES ({$vars['su_id']}, {$vars['err_code']})";

Collectives™ on Stack Overflow

Use array in mysql insert query

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related