CODEWITHSUNDEEP
phphtmlsqlformssql-update
Nick
Nick

Reputation: 21

Update Query using Select name attribute

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

?>

Upvotes: 1

Views: 157

Answers (3)

Nick
Nick

Reputation: 21

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

?>

Upvotes: 0

user8757663
user8757663

Reputation:

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

?>

Upvotes: 2

Grzegorz Adam Kowalski
Grzegorz Adam Kowalski

Reputation: 5565

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

Upvotes: 1

Related Questions