How storing array values in different columns of table using php

Question

Here is the piece of code so far I've tried:

$month = array('red','green','red');
$values = array();
foreach($month as $dataset)
{
   $values[] = ($dataset);
}

$columns = implode(", ",array_keys($values));
$escaped_values = array_values($values);
$valu  = implode(", ", $escaped_values);

$sql = "INSERT INTO abc (col1,col2,col3) VALUES ('$valu');";

Here is the output:

Error: INSERT INTO abc (col1,col2,col3) VALUES ('red, green, red'); Column count doesn't match value count at row 1

What I am trying to do is to store values in the array where the value of the array may vary depending upon the value the user gave, and then store it in different columns. For example, if the total columns are 3 and array value is 2 then store values in col1 and col2 and null value in col3.

Thanks for upvote. Feel free to accept this as answer if the solution addressed your concern. — Darshan Jain
– Darshan Jain, Commented Mar 22, 2018 at 13:18
just add ' -> $values[] = "'{$dataset}'"; and remove the ' in $sql — kip
– kip, Commented Mar 22, 2018 at 13:19
You should be using prepared statements and bind variables rather than trying to get something inherently insecure to work. — Nigel Ren
– Nigel Ren, Commented Mar 22, 2018 at 13:21
Do you want a correct answer? Showing use of prepared queries or the one which is a quick fix thats open to SQL injection like the answers below? — Lawrence Cherone
– Lawrence Cherone, Commented Mar 22, 2018 at 13:26

mcv · Accepted Answer · 2018-03-22 13:24:57Z

1

With the single quotes around the whole string 'red, green, red' that is the value for col1 only.

It should look more like this 'red','green','red'.

So quick fix is this $valu = implode("','", $escaped_values);

Added a single quotes inside your implode.

The outside quotes will be captured in the final statement as detailied in the problem above:

$sql = "INSERT INTO abc (col1,col2,col3) VALUES ('$valu');";

edited Mar 22, 2018 at 13:24

answered Mar 22, 2018 at 13:19

mcv

1,4703 gold badges19 silver badges42 bronze badges

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

4 Comments

u_mulder Over a year ago

@kip after passing $valu to query text everything will be fine.

mcv Over a year ago

Yes that is correct but the code has the final piece with the outside qoutes in this statement: $sql = "INSERT INTO abc (col1,col2,col3) VALUES ('$valu');";

kip Over a year ago

@mcv I forget the query text, sorry

mcv Over a year ago

@kip no worries. it's easy to overlook things when I didn't include it. So I added it to ensure that there was no confusion. :)

Zohaib · Accepted Answer · 2018-04-18 05:39:53Z

1

Here the Code after making it work properly

$month = array('red','green','red');
$values = array();
foreach($month as $dataset)
{
  $values[] = "'{$dataset}'";
}

$columns = implode(", ",array_keys($values));
$escaped_values = array_values($values);
$valu  = implode(", ", $escaped_values);

$sql = "INSERT INTO abc (col1,col2,col3) VALUES ($valu);";

answered Apr 18, 2018 at 5:39

Zohaib

1591 silver badge14 bronze badges

Comments

Darshan Jain · Accepted Answer · 2018-03-22 13:16:33Z

0

The values are into the single quote. Please check below example.

INSERT INTO abc (col1,col2,col3) VALUES ('red', 'green', 'red');

answered Mar 22, 2018 at 13:16

Darshan Jain

4972 silver badges10 bronze badges

3 Comments

Zohaib Over a year ago

value will be pass in an array

Darshan Jain Over a year ago

@Zohaib Yes correct, I have just given you example so you can understand the issue.

Zohaib Over a year ago

i knew the problem but didn't knew how to solve it @kip gave the correct solution problem was here $values[] = "'{$dataset}'";

Lawrence Cherone · Accepted Answer · 2018-03-22 14:01:54Z

No one else seems to have addressed the actual issue.

What I am trying to do is to store values in the array where the value of the array may vary depending upon the value the user gave, and then store it in different columns. For example, if the total columns are 3 and array value is 2 then store values in col1 and col2 and null value in col3.

So if the values come in as an array at different lengths, and you want to insert as null or limit to the max length of the columns then you can do it like the following.

Ideally, you want to produce an array which looks like:

$data = [
    'col1' => 'red',
    'col2' => 'green',
    'col3' => null
];

To do that without any looping define the database columns, then create an array of the same length of null values, then slice the input array to the same length and merge, this will produce an array like above.

<?php
$columns = [
    'col1',
    'col2',
    'col3',
];
$month = [
    'red',
    'green'
];

$data = array_combine(
    $columns, 
    array_slice($month, 0, count($columns))+array_fill(0, count($columns), null)
);

Now you simply need to implode the array into your query, using ? for placeholders for the prepared query.

$sql = '
INSERT INTO abc (
    '.implode(', ', array_keys($data)).'
) VALUES (
    '.implode(', ', array_fill(0, count($data), '?')).'
)';

Will produce:

INSERT INTO abc (
    col1, col2, col3
) VALUES (
    ?, ?, ?
)

Then just do your query, for example:

$stmt = $pdo->prepare($sql);
$stmt->execute($data);

Simple clean and safe.

Collectives™ on Stack Overflow

How storing array values in different columns of table using php

4 Answers 4

4 Comments

Comments

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

4 Comments

Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related