SQL INSERT why does it add only the first element from array?

Question

I have a loop foreach in it, I execute queries. I have an associative array, the key is the name of the column. If id is not AUTO_INCREMENT, only the first request is performed true, and all the other false. If you put an AUTO_INCREMENT in db, all queries are executed, but in a cascade style.

$namePhones = ["phone_1", "phone_2", "phone_3", "phone_4", "phone_5"];
$jsonPhones = $userInfo;
$jsonPhones = array_splice($jsonPhones, 0, 1);
$phones = new stdClass();

$phonesCount = count($jsonPhones[0]);    
 foreach ($jsonPhones[0] as $key => $value) {
   if($key < $phonesCount){
      $phones->{$namePhones[$key]} = $jsonPhones[0][$key];
    } else { return; }
  }
  $phonesDB = json_decode(json_encode($phones), true);

//$phonesDB//this is array
// Array
// (
//    [phone_1] => 1
//    [phone_2] => 2
//    [phone_3] => 3
//    [phone_4] => 4
//)

    foreach ($phonesDB as $key => $value) {
        $queryInsertPhones = 
            "INSERT INTO 
               `phones_users` ($key) 
            VALUES  ($value)";
            $resultPhones =  mysqli_query($con, $queryInsertPhones); 
    }

 cascade style.
 +--------------------------------+
 |id | phone_1 | phone_2 | phone_3|
 |---+---------+---------+--------|
 |1  |  1      | ---     |  ---   |
 |---+---------+---------+--------|
 |2  |  ---    | 2       |  ---   |
 |---+---------+---------+--------|
 |3  |  ---    | ---     |  3     |
 +---+----------------------------+

 I want this.
  +--------------------------------+
 |id | phone_1 | phone_2 | phone_3|
 |---+---------+---------+--------|
 |1  |  1      | 2       |  3     |
 |---+---------+---------+--------|
 |2  |  ---    | ---     |  ---   |
 +---+----------------------------+

Because you are looping through the array, and in each iteration you insert the data into a new row. — Spoody
– Spoody, Commented Apr 15, 2018 at 18:01
First of all this is bad practice. If most people have 1 phone number you end up with a table which is 50% nulls — apokryfos
– apokryfos, Commented Apr 15, 2018 at 18:06

Almog · Accepted Answer · 2018-04-15 18:15:04Z

4

The reason for this is that you execute query per key and not for all keys.

This is how you need to do it:

// Make a variables that will conaints all keys and values seprate by comma
$keyList = '';
$keyValue = '';
// Add key and value to their variable with comma
foreach ($phonesDB as $key => $value) {
    $keyList .= '`'.$key.'`,';
    $keyValue .= '\''.$value.'\',';
}
// Remove the last comma
$keyList = substr($keyList, 0, -1);
$keyValue = substr($keyValue, 0, -1);
// Build the SQL query
$queryInsertPhones = 
    "INSERT INTO 
       `phones_users` ($keyList) 
    VALUES  ($keyValue)";
// Execute the query
$resultPhones =  mysqli_query($con, $queryInsertPhones);

I guess this code is in the main loop if you want more than one insert.

edited Apr 15, 2018 at 18:15

answered Apr 15, 2018 at 18:09

Almog

2201 silver badge10 bronze badges

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

3 Comments

Michael Podlevskykh Over a year ago

I did not understand what you mean. A user can send from one to five phones. the array itself is formed. I added some code. @Almog

Almog Over a year ago

@МишаПодлевских your code is executing a query every run of the loop instead of taking the key and build the query for all keys. In my answer, I show to you how to build the query for all keys.

Michael Podlevskykh Over a year ago

Thank you! It is work, and i understood this! @Almog

Reg Smith · Accepted Answer · 2018-04-15 18:09:11Z

0

I'm having a slight difficulty seeing exactly what you are doing, but generally when you are inserting into an SQL database you should specify a where clause to specify the the row you want to insert in based on the primary key for that row. If you are trying to add data to a row that has already been inserted then you should use update instead of insert.

The simplest solution for you would be to have a nested loop. The inside loop should go through a single row and get all the phone numbers for the row then insert all of the values at once. Your outer loop should iterate across all rows.

answered Apr 15, 2018 at 18:09

Reg Smith

1761 silver badge15 bronze badges

Collectives™ on Stack Overflow

SQL INSERT why does it add only the first element from array?

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related