How to insert array value in Implode form in mysql

Question

I have a value in below format and i want to convert this key and value in implode form to insert value in database in mysql.

 Array
(
[users_ids] => 
[key_name] => Total_Cubic_Metres
[value_name] => 3.46m³
)

Array
(
[users_ids] => 
[key_name] => Bedroom_Bassinette
[value_name] => 2
)

Array
(
[users_ids] => 
[key_name] => Bedroom_Bedside_Drawers
[value_name] => 2
)

Array
(
[users_ids] => 
[key_name] => Bedroom_Bedside_Table
[value_name] => 2
)

I have 2 COLUMN in MYSQL "key" and "value" and store the above value in this field in implode form something like this key:Total_Cubic_Metres,Bedroom_Bassinette,Bedroom_Bedside_Drawers,Bedroom_Bedside_Drawers,Bedroom_Bedside_Table

Value:3.46m³, 2, 2,2

score 0 · Accepted Answer · answered Mar 14 '19 at 03:37

0

You could use bindParam (PHP docs) and write your queries like so:

$my_Insert_Statement = $my_Db_Connection->prepare("
INSERT INTO TABLE_NAME (
  Total_Cubic_Metres,
  Bedroom_Bassinette,
  Bedroom_Bedside_Drawers,
  Bedroom_Bedside_Drawers,
  Bedroom_Bedside_Table) 
VALUES (
 :Total_Cubic_Metres,  
 :Bedroom_Bassinette,
 :Bedroom_Bedside_Drawers,
 :Bedroom_Bedside_Drawers,
 :Bedroom_Bedside_Table
)");

and you could add them like:

foreach($arr as $colData){
  $my_Insert_Statement->bindParam($colData['key_name'], $colData['value_name']);
}

and execute the prepared statement:

if ($my_Insert_Statement->execute()) {
  echo "New record created successfully";
} else {
  echo "Unable to create record";
}

answered Mar 14 '19 at 03:37

Miroslav Glamuzina

4,472
2
19
33

in form there are so many value like "Bedroom_Bassinette" so how can i define this – Sheenu Mar 14 '19 at 03:43
@Sheenu multiple inserts? You could iterate through this code a couple of times or you could build an insert multiple function to handle the multiple rows you would like to insert. – Miroslav Glamuzina Mar 14 '19 at 03:51
I have a form in which there are 150 fields and i want to insert only that value which have data and i have that value which have data in above array format but i want to store that data in 1 row , because currently its stored in multiple row – Sheenu Mar 14 '19 at 03:55
You would have to select the row you would like to insert and then build and prepare a query for it. How do you determine which row[s] you would like to insert? Perhaps you are looking for `array_filter()`(to filter out the record)? – Miroslav Glamuzina Mar 14 '19 at 03:57
foreach($_POST as $key=>$value) { if ($key != 'submit') { if($value!='') { $info = array( 'key_name'=>mysql_real_escape_string($key), 'value_name'=>mysql_real_escape_string($value), );}}} – Sheenu Mar 14 '19 at 04:01
from above code i found the key and value which have data – Sheenu Mar 14 '19 at 04:01
Then why can't you insert this row? – Miroslav Glamuzina Mar 14 '19 at 04:03
its stored in multiple row but i want to store all value in one row – Sheenu Mar 14 '19 at 04:11
Take a look at this answer here, this may be what you are looking for https://stackoverflow.com/a/2098689/5066625. If it is, let me know and I will rescind my answer here. – Miroslav Glamuzina Mar 14 '19 at 04:16

score 0 · Answer 2 · answered Mar 14 '19 at 04:22

Try below code

 function insertQuery($table, $dataArray, $connection){
    $dataArray = array_map(array($connection, 'real_escape_string'), $dataArray);
    $keys = array_keys($dataArray);
    $colmuns = implode(",",$keys);
    $values =array_values($dataArray);
    $valueData = implode("','",$values);
    $query = "INSERT INTO $table ($colmuns) VALUES ('$valueData')";
    $result = mysqli_query($connection, $query);

    if($result){
      $id = mysqli_insert_id($connection);
      return $id;
    }
    return false;
 }

How to insert array value in Implode form in mysql

2 Answers2