MySQL UPDATE not working

Question

I have a bit of code which updates a table called job, but once the the page is executed it does not update the table. Here is the code:

$item = isset($_POST['item']);
$ref = isset($_POST['ref']);

$con = mysql_connect("$host","$username","$password");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

mysql_select_db("$db_name", $con);

$sql="UPDATE job SET item = '$item' WHERE ref='$ref'";
if (!mysql_query($sql,$con))
  {
  die('Error: ' . mysql_error());
  }
header("location:index.php");

I have echoed out the $ref variable and it is there but it won't work if I put it in the WHERE clause.

What is the value of `$sql` just before you execute the query? Also, beware of SQL Injection (you probably want to escape `$item` and `$ref` before putting them in a query). — Dominic Rodger, Nov 04 '11 at 12:11
You realise that isset() returns a boolean true/false, not the actual value of the argument? — Mark Baker, Nov 04 '11 at 12:11
Please use bind variables to avoid severe SQL injection vulnerabilities! — a'r, Nov 04 '11 at 12:11
@james The $ref is a string, i can echo out the value $ref correctly but it wont accept it in the clause. — SebastianOpperman, Nov 04 '11 at 12:16
@Dominic Rodger Yes i will escape and sanitise the data thanks (-: — SebastianOpperman, Nov 04 '11 at 12:17

score 3 · Accepted Answer · edited May 23 '17 at 11:47

3

$ref = isset($_POST['ref']);

I have echoed out the $ref variable and it is there

You aren't assigning the actual value of $_POST['ref'], you're only assigning whether or not it is set. Try:

$ref = isset($_POST['ref']) ? mysql_real_escape_string($_POST['ref']) : NULL;

You can check your query by reading the SQL string you've created: exit($sql)

See also: What is SQL injection?

edited May 23 '17 at 11:47

Community

1
1

answered Nov 04 '11 at 12:12

Wesley Murch

98,378
36
187
224

score 1 · Answer 2 · answered Nov 04 '11 at 12:18

$item = isset($_POST['item']);
$ref = isset($_POST['ref']);

by this two statements, variables will have 0 or 1 as values ...better write this way..

$item = (isset($_POST['item']) == 1 ? $_POST['item'] : '');
$ref = (isset($_POST['ref']) == 1 ? $_POST['ref'] : '');

if($item !='' && $ref !=''){
   // your update query
}

MySQL UPDATE not working

2 Answers2