INSERT into database using multidimensional array PHP

Question

I got array like this :

Array
(
  [0] => Array
     (
       [0] => A
       [1] => 1
       [2] => Too High
     )
  [1] => Array
     (
       [0] => AB
       [1] => 7
       [2] => OK
     )
   [2] => Array
     (
       [0] => B
       [1] => 10
       [2] => Too High
      )
)

I'm trying to insert array above into my db using this code, i want to do it without column name, because the column could be changed anytime (user can add new field into table), array lengths is same with the amount of fields :

if(is_array($FINALRESULT)) {
    $queryi = "INSERT INTO tempresult VALUES ";

    $value = array();
    for ($i = 0; $i < 4; ++$i) {
       for ($j=0; $j < 3; $j++) { 
          $value[] = mysql_real_escape_string("($FINALRESULT[$j])");
       }
    }
    $queryi .= implode(',', $value);

    mysql_query($queryi) or exit(mysql_error()); 
}

But i got notice like these :

Notice: Array to string conversion in C:\xampp\htdocs\Livestockmapping(edit)\dsssawprocess.php on line 104 Unknown column 'Array' in 'field list'

Please start using mysqli_* or PDO as mysql_* is deprecated and is removed in PHP7 — SuperDJ
– SuperDJ, Commented Aug 27, 2016 at 13:38
you are trying to insert an array into a string, you need to use either implode, json_encode or serialize in order to convert from array to stirng. Moreover, when you will get the data from the database you must convert the data back to array. — Cobuz Alexandru
– Cobuz Alexandru, Commented Aug 27, 2016 at 14:11

Kostas Mitsarakis · Accepted Answer · 2016-08-27 14:36:57Z

3

You can try this one:

$FINALRESULT = [
    ['A', 1, 'Too High'],
    ['AB', 7, 'OK'],
    ['B', 10, 'Too High'],
];

$all_values = [];
$query = "INSERT INTO tempresult VALUES ";

foreach($FINALRESULT as $key) {
    $row_values = [];
    foreach($key as $s_key => $s_value) {
        $row_values[] = '"'.$s_value.'"';
    }
    $all_values[] = '('.implode(',', $row_values).')';
}

//Implode all rows
$query .= implode(',', $all_values);

echo $query;

Result:

INSERT INTO tempresult VALUES ("A","1","Too High"), ("AB","7","OK"), ("B","10","Too High")

Also use PDO and prepared statements to avoid SQL injection.

In your code you need $FINALRESULT[$i][$j] because it's a 2 level array.

edited Aug 27, 2016 at 14:36

answered Aug 27, 2016 at 13:44

Kostas Mitsarakis

4,7473 gold badges26 silver badges38 bronze badges

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

6 Comments

lacyeex Over a year ago

I just tried yours, and it appears this error (You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'high..), I have tried using $FINALRESULT[$i][$j] but no luck..

Kostas Mitsarakis Over a year ago

Ok, I had the parenthesis in the wrong place. Try this one.

lacyeex Over a year ago

i just tried it again, and there's no error. But the tempresult table still empty, which is the data not inserted..

lacyeex Over a year ago

I'm pretty sure that yours should work. But I don't know where the mistake why the data inserted

Kostas Mitsarakis Over a year ago

You are welcome. But you really have to use prepared statements to avoid SQL Injections and because mysql_* functions are deprecated. You can use mysqli instead or even better PDO.

|

Collectives™ on Stack Overflow

INSERT into database using multidimensional array PHP

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related