PHP Form SQL Format Error

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\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="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>

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 '= 12WHERE email = test@gmail.com' at line 1 — Aubtin Samai, Mar 26 '14 at 04:45
Use `$sql = "UPDATE users SET time= '$time' WHERE email = '$email'";` — Funk Forty Niner, Mar 26 '14 at 04:46
Now I get the error: Could not update data: No database selected. I am sure I have the right database name... — Aubtin Samai, Mar 26 '14 at 04:48
Consult [`my answer`](http://stackoverflow.com/a/22651714/) below. @AubtinSamai — Funk Forty Niner, Mar 26 '14 at 04:50

score 3 · Accepted Answer · edited May 23 '17 at 10:31

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\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="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.

although bad form, why does the order matter if he is just defining $sql before he selects the db. he doesn't run the query until he has selected the db — Cameron Aziz, Mar 26 '14 at 04:53
It works now.. Thanks a lot! I will work on the security later, just getting it to function properly first. — Aubtin Samai, Mar 26 '14 at 04:56
I always thought that DB selection made a difference. @CameronAziz I've modified my answer. — Funk Forty Niner, Mar 26 '14 at 04:56
Thanks @Hanky웃Panky I will also. I've learned a bit more tonight. — Funk Forty Niner, Mar 26 '14 at 04:57

score 1 · Answer 2 · answered Mar 26 '14 at 04:51

1

you don't have spaces in your sql script.

change $sql to:

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

although this will work just fine:

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

keep in mind, your page is vulnerable to sql injection because you have not escaped time and email.

answered Mar 26 '14 at 04:51

Cameron Aziz

487
1
4
24

2

And what about enclosing string values in quotes? – Hanky Panky Mar 26 '14 at 04:55

PHP Form SQL Format Error

2 Answers2