Php mysql insert array key values for the same table id

Question

I have an array like below.

Where i need to insert all the values of ActionKey and ActionValue corresponding to each index for the same AdId

ie)
0th index - sms - 213123 and

1st index call - 12313

I am trying something like, but not working out.

$sDate = date("Y-m-d H:i:s");
$values = array();
$d_actionKey = $this->params()->fromPost('d_ActionKey');
$d_actionValue = $this->params()->fromPost('d_ActionValue');

$sql = "INSERT INTO `AdActions` (`CreatedDate`,`ActionKey`,`ActionValue`) VALUES";

foreach($values as $value) {
    $values[] = "($sDate, $d_actionKey, $d_actionValue)";
}

My table data should look like.

UPDATED CODE:

I need to get the AdId from another table's last inserted value. Then take that last inserted AdId and insert into AdActions

$adActionsInsert = $this->getAdLibraryTable()->saveAdd($dataArray);

$query='INSERT INTO `AdActions` (`AdId`,`CreatedDate`,`ActionKey`,`ActionValue`) VALUES ';
        for($i=0; $i < count($adActionsArray['ActionKey']); $i++)
        {
            if($i!=0)
                $query .= ', ';

            $query .= sprintf("(%d,'%s', '%s', '%d')",
            $adActionsInsert,
            $adActionsArray['CreatedDate'],
            $adActionsArray['ActionKey'][$i],
            $adActionsArray['ActionValue'][$i]);
        }

I am able to get the last insert value like below (from the model file)

$this->tableGateway->lastInsertValue;

related to https://stackoverflow.com/questions/6889065/inserting-multiple-rows-in-mysql you should have commas between parentheses. And your code is vulnerable to SQL injection, use prepared statements. — Jules R, Dec 12 '18 at 08:20

score 1 · Accepted Answer · answered Dec 12 '18 at 09:19

If you want to insert the $data array into the table AdActions. You can build your query like this.

$data = [
    'CreatedDate' => '2018-12-12 08:04:32',
    'ActionKey' => [
        0=>'sms',
        1=>'call'
    ],
    'ActionValue' => [
        0 => 213123,
        1 => 12313
    ]
];
$query='INSERT INTO `AdActions` (`CreatedDate`,`ActionKey`,`ActionValue`) VALUES ';
for($i=0; $i < count($data['ActionKey']); $i++)
{
    if($i!=0)
        $query .= ', ';
    $query .= sprintf("('%s', '%s', '%d')",
        $data['CreatedDate'],
        $data['ActionKey'][$i],
        $data['ActionValue'][$i]);
}
echo $query;

This should give you a query like this

INSERT INTO `AdActions` (`CreatedDate`,`ActionKey`,`ActionValue`) VALUES ('2018-12-12 08:04:32', 'sms', '213123'), ('2018-12-12 08:04:32', 'call', '12313')

Jules R · Answer 2 · 2018-12-12T08:53:32.223

0

According to Inserting multiple rows in mysql, sets of data when inserting multiple rows at once should be separated by commas, like

INSERT INTO table
    (col1, col2)
VALUES
    (data11, data12), -- first set of data, ended by comma
    (data21, data22); -- second set of data

That means, add a comma at the end of $values[] = "($sDate, $d_actionKey, $d_actionValue) and you can use substr to delete the comma of the last statement as it is not needed. (and be aware of SQL injection)

Edit

dataxy would be $d_actionKey and $d_actionValue

edited Dec 12 '18 at 08:53

answered Dec 12 '18 at 08:35

Jules R

553
2
18

The array that i posted is not limited only to 2 indexes, it may be more, so as per your query, should i have to use (data11, data12), (data21, data22) as many times? – Sushivam Dec 12 '18 at 09:11
The query I wrote is the typical query that should be generated by the PHP code. Each `(datax1, datax2),` line is generated by an iteration of your `foreach` loop. – Jules R Dec 12 '18 at 09:16

score 0 · Answer 3 · answered Dec 12 '18 at 09:23

I donot understand why you are using

foreach($values as $value)
        {

            $values[] = "($sDate, $d_actionKey, $d_actionValue)";
        }

section since you declared $values = array();

If your array is like

$arr = array(
 'date' => '',
 'ActionKeys' = > array(
                    [0] => 'sdsdsd',
                    [1] => 'fghfgh',
                  ),
'ActionValues' = > array(
                    [0] => '345345',
                    [1] => '455496',
                  )
)
you can create values as 
$ActionKeys   = $arr[0]['ActionKeys'];
$ActionValues = $arr[0]['ActionValues'];
$date = $arr[0]['date']
$values = '';
for($i=0;$i<count();$i++){
   if($i!=0)
    $values.=','
    else
    $values.=' ($date, $ActionKeys[i], $ActionValues[i])';
}

then insert inside command add values as $values;

loop $arr if it is an array;

Thanks for the code @Golwin, can you please update how to get the last inserted value of AdId(from another table) and insert into my AdActions table — Sushivam, Dec 12 '18 at 11:13
so thatt will be 2 queries right? you will get insert_id after execution of first query and use that in the second query — Golwin, Dec 12 '18 at 11:42

Php mysql insert array key values for the same table id

3 Answers3