Update Query using Select name attribute

Question

I am trying to get an update query to work using the name attribute of the select boxes on a form. Everything gets passed through to the $_POST and the INSERT query works fine but I cannot work out why the update part of my if statement does not go through to my database. Any advice is much appreciated.

<?php

global $wpdb;

$Call_Number = $_POST['Call_Number'];
$datas = $_POST['REG'];
$columns = implode(",",array_keys($datas));
$values = implode("','",$datas);

$result = $wpdb->get_results ("SELECT Call_Number FROM DG_Pro_Coach WHERE Call_Number = '".$Call_Number."'");

if (count ($result) > 0) {
    $row = current ($result);
    $wpdb->query ("UPDATE DG_Pro_Coach SET ".$columns."='".$values."' WHERE Call_Number = '".$Call_Number."'");
} else {
    $wpdb->query("INSERT INTO DG_Pro_Coach (".$columns.",Call_Number ) VALUES ('".$values."','$Call_Number' )");
}

?>

Unrelated to the question, but important nonetheless: Please [parameterize your queries](https://stackoverflow.com/questions/332365/how-does-the-sql-injection-from-the-bobby-tables-xkcd-comic-work)! — waka, Oct 12 '17 at 11:46
your update clause syntax is wrong it should be like this `UPDATE t1 SET col1 = val1, col2 = val2 where conditions`, yours is `UPDATE t1 SET col1, col2 = val1, vla2 where condition` — Samer Abu Gahgah, Oct 12 '17 at 11:53

score 2 · Answer 1 · 2017-10-13T14:28:47.513

2

Problem

Your query should look like this:

UPDATE database SET column1=value1, column2=value2(...) WHERE condition

But it looks like this:

UPDATE database SET column1,column2(...) = value1,value2(...) WHERE condition

Solution

<?php

global $wpdb;

$Call_Number = $_POST['Call_Number'];
$datas = $_POST['REG'];
$columns = implode(",",array_keys($datas));
$values = implode("','",$datas);
$updatelist = "";
foreach($datas as $key=>$value){
    $updatelist .= $key."=".$value.",";
}
$updatelist = substr($updatelist,0,strlen($updatelist)-1);

$result = $wpdb->get_results ("SELECT Call_Number FROM DG_Pro_Coach WHERE Call_Number = '".$Call_Number."'");

if (count ($result) > 0) {
    $row = current ($result);
    $wpdb->query ("UPDATE DG_Pro_Coach SET ".$updatelist."' WHERE Call_Number = '".$Call_Number."'");
} else {
    $wpdb->query("INSERT INTO DG_Pro_Coach (".$columns.",Call_Number ) VALUES ('".$values."','$Call_Number' )");
}

?>

edited Oct 13 '17 at 14:28

answered Oct 12 '17 at 11:58

Thanks for the input, logically the code makes perfect sense however when I try to use the solutions suggested the page stops working (error 500). I have tried several ways to adapting the code but without any luck. – Nick Oct 13 '17 at 13:43
No errors, removing the UPDATE query itself does not solve the issue so a am assuming it must be an issue with the PHP – Nick Oct 13 '17 at 13:57
@Nick activate you php error output by adding this to your code: `ini_set("display_errors", 1);` and `error_reporting(E_ALL);` – Oct 13 '17 at 13:59
Yes, I have about 50 select boxes names as such (name="REG[something]") and my INSERT query worked without issue. – Nick Oct 13 '17 at 14:01
Error reporting shows: Invalid script interpreter, Syntax error in script & Script is trying to use a module not installed on the server, – Nick Oct 13 '17 at 14:16
I have managed to solve the errors and can run the SQL however nothing updates in the my DB. To solve the error I had to update this 'for($datas as $key=>$value)' to 'foreach($datas as $key=>$value)' – Nick Oct 13 '17 at 14:28
@Nick of course... Its a foreach loop... my bad. sorry – Oct 13 '17 at 14:29
@Nick I am not sure about this line: `$updatelist = substr($updatelist,0,strlen($updatelist)-1);` maybe you have to change it to `$updatelist = substr($updatelist,0,strlen($updatelist)-2);` – Oct 13 '17 at 14:30
can you print the sql query so you can see if its ok? – Oct 13 '17 at 14:35
UPDATE DG_Pro_Coach SET Team_Leader=3,Agent=4,Colab=No,Conv=25.... etc. – Nick Oct 13 '17 at 14:43
I have solved the issue. Just needed to add a few singles quotations into the code. Many thanks for all your help – Nick Oct 13 '17 at 14:58

score 1 · Answer 2 · answered Oct 12 '17 at 11:55

Your update query has bad syntax because you include data from your PHP arrays in a wrong way.

For those input parameters:

$Call_Number = '123456789';
$datas = array('one' => 1, 'two' => 2);

This query is created:

UPDATE DG_Pro_Coach SET one,two='1','2' WHERE Call_Number = '123456789'

Possible solution:

$update_array = array();
foreach ($datas as $column=>$value) {
  $update_array[] = "$column = '$value'";
}
$update_string = implode(', ', $update_array);

$wpdb->query ("UPDATE DG_Pro_Coach SET ".$update_string.' WHERE Call_Number = '".$Call_Number."'");

score 0 · Answer 3 · answered Oct 13 '17 at 15:00

Many thanks for all the assistance, this is the working code.

<?php

global $wpdb;

$Call_Number = $_POST['Call_Number'];
$datas = $_POST['REG'];
$columns = implode(",",array_keys($datas));
$values = implode("','",$datas);
$updatelist = "";
foreach($datas as $key=>$value){
$updatelist .= $key."='".$value."', ";
}
$updatelist = substr($updatelist,0,strlen($updatelist)-2);

$result = $wpdb->get_results ("SELECT Call_Number FROM DG_Pro_Coach WHERE Call_Number = '".$Call_Number."'");

if (count ($result) > 0) {
$row = current ($result);
$wpdb->query ("UPDATE DG_Pro_Coach SET ".$updatelist." WHERE Call_Number = '".$Call_Number."'");
} else {
$wpdb->query("INSERT INTO DG_Pro_Coach (".$columns.",Call_Number ) VALUES ('".$values."','$Call_Number' )");
}

?>

Update Query using Select name attribute

3 Answers3