how to store php multi dimensional array data into database in their fields

Question

i have Multi array data like "cars name & cars modal"

Car name match with car model. both are different column in database (cars_name,cars_model). I want to store data from this array into database in their fields

Output:

    Array
(
    [car_name] => Array
        (
            [0] => Honda
            [1] => Ford Mustang
            [2] => Volvo
        )

    [car_modal] => Array
        (
            [0] => 2015
            [1] => 2016
            [2] => 2014
        )

)

i want to store array values into single column in each row using "mysql". For this purpose i like query like this but it shows error.

$sql = "INSERT INTO cars_data (cars_name,cars_modal)
VALUES ($cars_name,$cars_modal)";

nothing happened. But errors show like this...

Notice: Array to string conversion in E:\xampp\htdocs\car_records\modal_data.php on line 45 Error: INSERT INTO cars_data (cars_name,cars_model)

VALUES (Array,Array)
Unknown column 'Array' in 'field list'

The question is how to fix it. Please help me

BlackNetworkBit · Answer 1 · 2018-09-08T14:51:59.903

0

you can insert multiple elements in one row, you just need to bring it in the right format :

insert into x (columns) VALUES (x1),(x2),(x3)

$string = "";
foreach($cars_name as $car){
    $string .= "(" . $car . "), ";
}
$sql = "INSERT INTO car_data (cars_name) VALUES $string";

Pleas note that you should never accept user input without sanitizing it.

edited Sep 08 '18 at 14:51

answered Sep 08 '18 at 14:43

BlackNetworkBit

768
11
21

Nigel Ren · Accepted Answer · 2018-09-09T16:06:58.347

0

To use one statement and mysqli prepared statements (comments in code)...

$cars_name = ["Honda", "Volvo"];
// Create an entry for each name
$params = str_repeat("(?),", count($cars_name));
// Build a bind for a list of strings
$binds = str_repeat("s", count($cars_name));
// Add the params to the insert (remove the last ,)
$sql = "INSERT INTO car_data (cars_name)
             VALUES ".rtrim($params, ",");

$insert = $conn->prepare ( $sql );
// Bind the parameters, using ... is the argument unpacking operator
$insert->bind_param($binds, ...$cars_name);
// Execute the SQL
$insert->execute();

Update:

If you had two data items in the array, you would be able to adapt the above to something like...

// Source data - ensure that the two sets of data have the same number of entries
$car_data = [ 'cars_name' => ["Honda", "Volvo"],
        'cars_modal' => [ '2015', '2016' ]];
$car_count = count($car_data['cars_name']);
// Create an entry for each name (2 binds per entry)
$params = str_repeat("(?,?),", $car_count);
// Build a bind for a list of strings
$binds = str_repeat("ss", $car_count);
// Reformat data for binding (needs to be a single list of the data
// with cars_name followed by cars_modal for each entry)
$merged_data = [];
foreach ( $car_data['cars_name']  as $key => $name )    {
    $merged_data[] = $name;
    $merged_data[] = $car_data['cars_modal'][$key];
}   
// Add the params to the insert (remove the last ,)
$sql = "INSERT INTO car_data (cars_name,car_model)
                 VALUES ".rtrim($params, ",");

$insert = $conn->prepare ( $sql );
// Bind the parameters, using ... is the argument unpacking operator
$insert->bind_param($binds, ...$merged_data);
// Execute the SQL
$insert->execute();

edited Sep 09 '18 at 16:06

answered Sep 08 '18 at 15:00

Nigel Ren

56,122
11
43
55

Thanks for answering.It worked, you solved my problem. but if i stored multiple array how it possible? – Sep 09 '18 at 08:10
Not sure what you mean, perhaps ask another question with the full details and you will also get other peoples help as well. – Nigel Ren Sep 09 '18 at 14:11
Array ( [car_name] => Array ( [0] => Honda Accord [1] => Ford Mustang [2] => BMW ) [car_modal] => Array ( [0] => 2015 [1] => 2016 [2] => ) ) – Sep 09 '18 at 15:06
if i have array data like this how to store into database. Means how to write query? – Sep 09 '18 at 15:08
Does the car model array match the car_name one ( as in Honda Accord - 2015?) – Nigel Ren Sep 09 '18 at 15:14
yes car name match with car model. both are different column in database (car_name,car_model). I want to store data from this array into database in their fields. – Sep 09 '18 at 15:44
There are quite a few changes, but this should help. – Nigel Ren Sep 09 '18 at 16:07
thanks a lot bro, you solved my problem. – Sep 09 '18 at 16:13
Friend, i want to use this query in codeigniter. How is possible. i trying to execute in codeigniter but i am failed to execute this. Kindly please solve my problem. i stuck here. Thanks if you solve my problem. – Sep 26 '18 at 14:25

score 0 · Answer 3 · answered Sep 08 '18 at 15:12

0

When I want to do so, I first implode this array to get a normal string separated by (,) then when I retrieve data I implode them again.

   $cars_name = implode(',', $_POST['cars_name']);

The result will be

   Honda,Volvo,Mercedes,Toyota,BMW

Then if you want to get back the array from database again just do as following:

   $cars_array = explode(',', $databaseObject['cars']);

The result will be the same as the first array of yours.

answered Sep 08 '18 at 15:12

Slamnka

122
1
16

Please have a read of https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad – Nigel Ren Sep 08 '18 at 15:16
His answer was specific, he wants to store an array in one column, I didn't say if that is the best way or not. – Slamnka Sep 08 '18 at 15:20
The link is more a general comment on if this is a good way of dealing with storing multiple items into one column. With any answer there may be issues with the suggested solution and at the end of the day it's still up to the original asker which answer they choose. I just wanted to highlight the issues that may help others choose. – Nigel Ren Sep 08 '18 at 15:24

how to store php multi dimensional array data into database in their fields

3 Answers3