CODEWITHSUNDEEP
phpmysqlarrays
Zohaib
Zohaib

Reputation: 159

How storing array values in different columns of table using php

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.

Upvotes: 2

Views: 1732

Answers (4)

Zohaib
Zohaib

Reputation: 159

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);";

Upvotes: 1

Lawrence Cherone
Lawrence Cherone

Reputation: 46602

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.

Upvotes: 0

mcv
mcv

Reputation: 1460

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');";

Upvotes: 1

Darshan Jain
Darshan Jain

Reputation: 499

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

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

Upvotes: 0

Related Questions