Insert array into db table

Question

I'm trying to learn how to use an array to insert multiple entries into a database table. This was my attempt. What am I doing wrong?

$servername = "localhost";
$username = "#";
$password = "#";
$dbname = "hosts";
$conn = new mysqli($servername, $username, $password, $dbname);
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}
$client = array(
    "1" => array("Jerry Garcia", "193.169.5.11"),

    "2" => array("Bill Graham", "193.169.5.12"),

    "3" => array("Arlo Guthrie", "193.169.5.13")
     );

if(is_array($client) {
    $DataArr = array();
    foreach($client as $row) {
        $fieldVal1 = mysqli_real_escape_string($client[$row][1]);
        $fieldVal2 = mysqli_real_escape_string($client[$row][2]);
        $fieldVal3 = mysqli_real_escape_string($client[$row][3]);

        $DataArr[] = "('fieldVal1', 'fieldVal2', 'fieldVal3')";

    }

    $sql = "INSERT INTO ip_data (field1, field2, field3) values ";
    $sql .= implode(',' , $DataArr);

    mysqli_query($conn, $query);
}

I tried this but it still doesn't work. What am I missing?

$servername = "localhost";
$username = "#";
$password = "#";
$dbname = "hosts";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}
$client = array(
    "0" => array("Jerry Garcia"),

    "1" => array(""193.169.5.11"),
     );

if(is_array($client)) {
    $DataArr = array();
    foreach($client as $row) {
        $fieldVal1 = mysqli_real_escape_string($client[$row][0]);
        $fieldVal2 = mysqli_real_escape_string($client[$row][1]);


        $DataArr[] = "('$fieldVal1', '$fieldVal2')";

    }

    $sql = "INSERT INTO ip_data (field1, field2) values ";
    $sql .= implode(',' , $DataArr);

    mysqli_query($conn, $query);
}

Thanks for your advice.

Next try.

$servername = "localhost";
$username = "#";
$password = "#";
$dbname = "hosts";

$conn = new mysqli($servername, $username, $password, $dbname);

if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}
$client = array(
    "0" => array("name" => "Peter Maxx", "ip" => "193.169.5.16"),
    "1" => array("name" => "Ravi Shankar", "ip" => "193.169.5.17")
     );

if(is_array($client)) {
        $DataArr = array();         
        foreach($client as $row) {

            $DataArr[] = "('". mysqli_real_escape_string($conn, $row[0]) ."', '". mysqli_real_escape_string($conn, $row[1]) ."')";
        }
        $sql = "INSERT INTO ip_data (name, ip)
VALUES 
( 'Peter Maxx', '193.169.5.16'),
('Ravi Shankar', '193.169.5.17')";
        $sql .= implode(", " , $DataArr);
        mysqli_query($conn, $sql);  

}

I get these error messages.

PHP Notice:  Undefined offset: 0 in php shell code on line 5
PHP Notice:  Undefined offset: 1 in php shell code on line 5
PHP Notice:  Undefined offset: 0 in php shell code on line 5
PHP Notice:  Undefined offset: 1 in php shell code on line 5

Look w3schools.com/sql/sql_insert.asp - your values should be in () — Alexey Shatrov
– Alexey Shatrov, Commented Feb 20, 2017 at 18:51
@AlexeyShatrov - he is intending to, but incorrectly. See the answer. — Jacob S
– Jacob S, Commented Feb 20, 2017 at 18:52

Michael Krikorev · Accepted Answer · 2017-02-23 18:40:04Z

1

It is unclear what your purpose is since the SQL-query in your question specify 3 fields (field1, field2, field3) to be inserted into your table, but you only have 2 values in your clients array. If you want to insert multiple rows in one single query, lets say for the "name" and "ip" value in your clients array, you can do this:

    if(is_array($client)) {
        $DataArr = array();         
        foreach($client as $row) {
            //CREATE ARRAY WITH name AND ip VALUES FOR EACH USER...               
            $DataArr[] = "('". mysqli_real_escape_string($conn, $row[0]) ."', '". mysqli_real_escape_string($conn, $row[1]) ."')";
        }
        $sql = "INSERT INTO ip_data (name, ip) VALUES ";
        $sql .= implode(", " , $DataArr);
        mysqli_query($conn, $sql);
    }

The $sql variable will contain the following query compliant with the syntax for a multiple insert:

INSERT INTO ip_data (name, ip) 
VALUES ('Jerry Garcia', '193.169.5.11'), 
       ('Bill Graham', '193.169.5.12'), 
       ('Arlo Guthrie', '193.169.5.13')

Note that you have $query instead of $slqin your question. It should be: mysqli_query($conn, $sql);.

Another thing: Using mysqli_real_escape_string() the procedural way instead of the object oriented way, as in your example, requires to pass a link identifier to the connection as parameter: mysqli_real_escape_string($conn, $row[0])

UPDATE: In your latest attempt you have changed the array to an associative array, so this should do it:

 $servername = "localhost";
 $username = "#";
 $password = "#";
 $dbname = "hosts";

 $conn = new mysqli($servername, $username, $password, $dbname);

 if ($conn->connect_error) {
     die("Connection failed: " . $conn->connect_error);
 }
 $client = array(
     "0" => array("name" => "Peter Maxx", "ip" => "193.169.5.16"),
     "1" => array("name" => "Ravi Shankar", "ip" => "193.169.5.17")
 );

 if(is_array($client)) {
    $DataArr = array();         
    foreach($client as $row) {

        $DataArr[] = "('". mysqli_real_escape_string($conn, $row["name"]) ."', '". mysqli_real_escape_string($conn, $row["ip"]) ."')";
    }
    $sql = "INSERT INTO ip_data (name, ip) VALUES ";
    $sql .= implode(", " , $DataArr);
    mysqli_query($conn, $sql);  

 }

edited Feb 23, 2017 at 18:40

answered Feb 20, 2017 at 20:07

Michael Krikorev

2,1661 gold badge18 silver badges28 bronze badges

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

4 Comments

S. Van Tuyl Over a year ago

Thank you so Michael! This looks good. I will make these edits as soon as I get back to my desk. This is much appreciated!

S. Van Tuyl Over a year ago

Hi Michael. Apparently, I'm still doing something wrong. See my above edits.

Michael Krikorev Over a year ago

@S.VanTuyl You changed the array to an associative array, so just change $row[0] to $row["name"] and $row[1]to $row["ip"] I have updated my answer with a working example..

S. Van Tuyl Over a year ago

Bingo! Thanks so much for your patience!

michail_w · Accepted Answer · 2017-02-20 18:51:06Z

1

Please change append to $DataArr[] to this one:

$DataArr[] = "('$fieldVal1', '$fieldVal2', '$fieldVal3')";

answered Feb 20, 2017 at 18:51

michail_w

4,4814 gold badges30 silver badges45 bronze badges

Comments

Akshay Hegde · Accepted Answer · 2017-02-20 19:35:08Z

0

Correct

From

   if(is_array($client) {

To

   if(is_array($client)){

And your array $client will have only 2 elements in each iteration

From

    # You will get PHP Warning:  Illegal offset type
    # since $row is already array

    $fieldVal1 = mysqli_real_escape_string($client[$row][1]);
    $fieldVal2 = mysqli_real_escape_string($client[$row][2]);
    $fieldVal3 = mysqli_real_escape_string($client[$row][3]);

to

    // For first iteration 
    // $row[0] = "Jerry Garcia"
    // $row[1] = "193.169.5.11"

    $fieldVal1 = mysqli_real_escape_string($conn, $row[0]);
    $fieldVal2 = mysqli_real_escape_string($conn, $row[1]);

    // $row[3] does not exists so comment it and set $fieldVal3 some data
    // $row[3] not exists in your array and even $row[2]
    // or add one value to your $client array and access using $row[2]
    // $fieldVal3 = mysqli_real_escape_string($row[3]);

    $fieldVal3 ='somedata';

and Finally

From

 $DataArr[] = "('fieldVal1', 'fieldVal2', 'fieldVal3')";

To

 $DataArr[] = "('$fieldVal1', '$fieldVal2', '$fieldVal3')";

edited Feb 20, 2017 at 19:35

answered Feb 20, 2017 at 19:05

Akshay Hegde

17.1k2 gold badges25 silver badges39 bronze badges

3 Comments

Akshay Hegde Over a year ago

@S.VanTuyl: I edited please read where all you need correction

Akshay Hegde Over a year ago

@S.VanTuyl: In your latest edit as you can see array("Jerry Garcia") contains 1 value, but you are trying to insert 2 values "('$fieldVal1', '$fieldVal2')", how its possible ?

S. Van Tuyl Over a year ago

Thanks. I can see I need to do a lot more studying before I can get a handle on this. Sorry for wasting your time.

Collectives™ on Stack Overflow

Insert array into db table

3 Answers 3

4 Comments

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related