PHP SQL Update Query syntax

Question

I'm working on my first PHP/MySQL project, and I've gotten basic logins and INSERT queries working, but not updates. This is my first update, which is just one row with a state and zipcode. Is anything wrong?

$dbc = mysql_connect("$host", "$username", "$password")or die("cannot connect");
mysql_select_db("$db_name")or die("cannot select DB");

$state=$_POST['state'];
$zip=$_POST['zip'];

$custnum = 0;
$sql="UPDATE $tbl_name SET state = '$state', zip = '$zip', WHERE custnum = '$custnum'";
$result = mysqli_query($dbc, $sql)
or die('Error querying database.');

You are mixing mysql_ and mysqli_ functions, which won't work. — Pekka
– Pekka, Commented Mar 8, 2012 at 16:33
Also, the code you show is vulnerable to SQL injection. Use the proper sanitation method of your library (= in this case, mysql_real_escape_string()), or switch to PDO and prepared statements. — Pekka
– Pekka, Commented Mar 8, 2012 at 16:34

Matt K · Accepted Answer · 2012-03-08 16:42:10Z

4

$sql="UPDATE {$tbl_name} SET state='{$state}', zip='{$zip}' WHERE custnum='{$custnum}'";

Remove the last comma before "WHERE" clause. Also, if you're just starting out it's good to put parenthesis around variables names when using double-quotes for strings. Helps you to distinguish the variables better.

Pekka is also correct in his comments, you are mixing mysql and mysqli functions. Use mysql_query() instead.

edited Mar 8, 2012 at 16:42

answered Mar 8, 2012 at 16:36

Matt K

6,6883 gold badges41 silver badges64 bronze badges

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

Comments

Alex Howansky · Accepted Answer · 2012-03-08 16:33:51Z

2

I think you need to get rid of the comma just before the WHERE.

answered Mar 8, 2012 at 16:33

Alex Howansky

54k8 gold badges85 silver badges102 bronze badges

Comments

Laud Randy Amofah · Accepted Answer · 2017-03-08 06:04:07Z

2

$suitno =mysqli_real_escape_string($ecms,$_POST['suitno']);//protecting sql injection 
$defendant=mysqli_real_escape_string($ecms,$_POST['defendant']);//protecting sql injection 
$casenature=mysqli_real_escape_string($ecms,$_POST['casenature']);//protecting sql injection 

$sql="UPDATE causelist SET suitno='{$suitno}', 
casenature='{$casenature}' WHERE suitno='{$suitno}'";
$result = mysqli_query($ecms, $sql)
or die('Error querying database.');

edited Mar 8, 2017 at 6:04

answered Mar 8, 2017 at 5:58

Laud Randy Amofah

292 bronze badges

1 Comment

Massimo Petrus Over a year ago

you should detail your answer

Ofir Baruch · Accepted Answer · 2012-03-08 16:34:53Z

1

    $dbc = mysql_connect($host, $username, $password)or die("cannot connect"); //don't need quotes
    mysql_select_db($db_name,$dbc)or die("cannot select DB"); //added the $dbc (connection link) as a second parameter

    $state=mysql_real_escape_string($_POST['state']); //Should make it safe!
    $zip=mysql_real_escape_string($_POST['zip']); //Should make it safe!

    $custnum = 0;
    $sql="UPDATE $tbl_name SET state = '$state', zip = '$zip' WHERE custnum = '$custnum'"; 

//removed an extra comma

    //Notice that $tbl_name isn't defined!
    u
    $result = mysql_query($sql)
    or die('Error querying database.'); //from mysqli to mysql

answered Mar 8, 2012 at 16:34

Ofir Baruch

10.3k2 gold badges29 silver badges39 bronze badges

Comments

David Kirui · Accepted Answer · 2012-03-08 16:36:57Z

0

Looks like a sql syntax error:Remove the comma before WHERE

answered Mar 8, 2012 at 16:36

David Kirui

11 bronze badge

Comments

dev4092 · Accepted Answer · 2014-02-04 12:12:22Z

0

if(isset($_POST['update'])) { $name=$_POST['name']; //echo $name; die; $surname=$_POST['surname'];

 $upd="update table_name SET  name='$name',surname='$surname' where id=$id";
mysql_query($upd);

}

answered Feb 4, 2014 at 12:12

dev4092

2,9111 gold badge18 silver badges15 bronze badges

Collectives™ on Stack Overflow

PHP SQL Update Query syntax

6 Answers 6

Comments

Comments

1 Comment

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

Comments

Comments

1 Comment

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related