Cannot Update MYSQL using PHP

Question

I have a simple MYSQL DB where field_3 is a varchar Key value. I am trying to update database posting to two TIME fields called start and end.

However I keep getting this error

Notice: Undefined variable: empd_end in C:\xampp\htdocs\b1\update.php on line 25 Could not update data: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ':12:00, end = WHERE field_3 = [email protected]' at line 1

<html>
<head>
<title>Update a Record in MySQL Database</title>
</head>
<body>

<?php
if(isset($_POST['update']))
{
$dbhost = 'localhost';
$dbuser = 'root';
$dbpass = 'root';
$conn = mysql_connect($dbhost, $dbuser, $dbpass);
if(! $conn )
{
  die('Could not connect: ' . mysql_error());
}

$emp_id = $_POST['emp_id'];
$emp_salary = $_POST['emp_salary'];
$emp_end= $_POST['emp_end'];


$sql = "UPDATE usezas ".
       "SET start = $emp_salary, end = $empd_end".
       "WHERE field_3 = $emp_id" ;

mysql_select_db('db1');
$retval = mysql_query( $sql, $conn );
if(! $retval )
{
  die('Could not update data: ' . mysql_error());
}
echo "Updated data successfully\n";
mysql_close($conn);
}
else
{
?>
<form method="post" action="<?php $_PHP_SELF ?>">
<table width="400" border="0" cellspacing="1" cellpadding="2">
<tr>
<td width="100">EMAIL</td>
<td><input name="emp_id" type="text" id="emp_id"></td>
</tr>
<tr>
<td width="100">Start TIME</td>
<td><input name="emp_salary" type="text" id="emp_salary"></td>
</tr>
<tr>
<td width="100">END TIME</td>
<td><input name="emp_end" type="text" id="emp_end"></td>
</tr>
<td width="100"> </td>
<td> </td>
</tr>
<tr>
<td width="100"> </td>
<td>
<input name="update" type="submit" id="update" value="Update">
</td>
</tr>
</table>
</form>
<?php
}
?>
</body>
</html>

For debugging issues with SQL statements, you could echo $sql; before submitting to the database. (You are going to find that string and datetime literals need to be enclosed in single quotes within a SQL statement. Those look like variables in your code, but in the SQL text you're sending to the database, they're literals.) Also, mysql interface is deprecated; new development should use either mysqli or PDO. Potentially unsafe values need to properly escaped when they are incorporated in SQL text. A better pattern is to use prepared statements with bind placeholders. — spencer7593
– spencer7593, Commented Jul 4, 2015 at 23:47

Jaylen · Accepted Answer · 2015-07-04 23:35:04Z

1

You are missing a space after the value of end also, you will need to wrap your variables with a quotes like the query below.

$sql = "UPDATE usezas ".
       "SET start = '$emp_salary', end = '$empd_end' ".
       "WHERE field_3 = $emp_id" ;

However, your code is vulnerable to SQL injections. You sure prepare your query and should be using either PDO or MySQLi extensions not the old mysql_query extension.

answered Jul 4, 2015 at 23:35

Jaylen

40.5k43 gold badges140 silver badges228 bronze badges

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

1 Comment

user3856234 Over a year ago

It says data updated sucessfully but for some reason the second value is not being posted into my db. The second value is $emp_end.

Drew · Accepted Answer · 2015-07-04 23:34:47Z

1

you need to put your php vals to ''

$sql = "UPDATE usezas ". "SET start = '$emp_salary', end = '$empd_end'". " WHERE field_3 = '$emp_id'" ;

edited Jul 4, 2015 at 23:34

Drew

25k10 gold badges47 silver badges81 bronze badges

answered Jul 4, 2015 at 23:31

Vody

787 bronze badges

Collectives™ on Stack Overflow

Cannot Update MYSQL using PHP

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related