PHP表单SQL格式错误

Question

I am trying to make a simple form that checks based on the correct email. If the email is correct, it then updates the database with the new time. When I run it, I get a format error.. I am not an expert with PHP, so I may have missed something here...

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

$email= $_POST['email'];
$time= $_POST['time'];

$sql = "UPDATE users".
       "SET time= $time".
       "WHERE email = $email" ;

mysql_select_db('dbname');
$retval = mysql_query( $sql, $conn );
if(! $retval )
{
  die('Could not update data: ' . mysql_error());
}
echo "Updated data successfully
";
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="email" type="text" id="email"></td>
</tr>
<tr>
<td width="100">Time:</td>
<td><input name="time" type="text" id="time"></td>
</tr>
<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>

Answer 1

Your query has the wrong quotes.

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

mysql_select_db('dbname');

$email= $_POST['email'];
$time= $_POST['time'];

$sql = "UPDATE users SET time= '$time' WHERE email = '$email'";

$retval = mysql_query( $sql, $conn );
if(! $retval )
{
  die('Could not update data: ' . mysql_error());
}
echo "Updated data successfully
";
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="email" type="text" id="email"></td>
</tr>
<tr>
<td width="100">Time:</td>
<td><input name="time" type="text" id="time"></td>
</tr>
<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>

---

Sidenote: Your present code is open to SQL injection. Use mysqli_* functions. (which I recommend you use and with prepared statements, or PDO)

---

Footnotes:

mysql_* functions deprecation notice:

http://www.php.net/manual/en/intro.mysql.php

This extension is deprecated as of PHP 5.5.0, and is not recommended for writing new code as it will be removed in the future. Instead, either the mysqli or PDO_MySQL extension should be used. See also the MySQL API Overview for further help while choosing a MySQL API.

These functions allow you to access MySQL database servers. More information about MySQL can be found at » http://www.mysql.com/.

Documentation for MySQL can be found at » http://dev.mysql.com/doc/.

---

Quick note(s)

You could shorten your code by doing the following all in one go:

$dbhost = 'localhost';
$dbuser = 'user1';
$dbpass = 'password';
$db = 'dbname';
$conn = mysql_connect($dbhost, $dbuser, $dbpass, $db);

so you won't have to use mysql_select_db('dbname'); but that's purely opinion-based/preference and will save you a few keystrokes at the same time.

---

Changing:

$email= $_POST['email'];
$time= $_POST['time'];

to:

$email= mysql_real_escape_string($_POST['email']);
$time= mysql_real_escape_string($_POST['time']);

will help add a bit of security until you get into prepared statements or PDO.