php where query not working

Question

I am new to PHP and my requirement is to check the user email id in database and if exists , navigate to next page. But by SQL query is not working in PHP , and working in SQL database.

My database structure :

enter image description here

My HTML Code : index.html

<!DOCTYPE html>
<html>
<body>
<form action="checkform.php" method="post">
E-mail: <input type="email" name="email" autocomplete="off"><br>
<input type="submit">
</form>
</body> 
</html>

PHP: checkform.php

<?php
try 
{
$connection = mysql_connect("localhost:3306","root","");
mysql_select_db("mobileblog", $connection);  
$emailid = $_POST['email']; 
echo $emailid;
$sql = "SELECT Name from table WHERE email=" . $_POST['email'];
$result = mysql_query($sql);
echo $result;
if(!$result) { echo "<p class='error'>Error: No such email address</p>"; }
// note that the if is asking if there is no result
else {
while ($row = mysql_fetch_assoc($result)) { 
echo "<p class='success'>Welcome " . $row['Name'] . "!!</p>";
}
} // end

//mysql_query(" // suggest here to validate against emails in db");
mysql_close($connection);
echo "SUCCESS";
}
catch(Exception $e)
{
echo $e->getMessage();
// Note: Log the error or something
}
?>

My page2.html

<!DOCTYPE html>
<html>
<body>
<h1> Welcome to Page 2</h1>
</body> 
</html>

But when running the index.html page , facing the error: (which eventually does nt execute the mysql query),But running the sql query is successful

Error: No such email address
SUCCESS

PHP Query (failed)

$sql = "SELECT `Name` FROM `table` WHERE email=\'jxxx@gmail.com\'";

SQL Query (Success)

SELECT `Name` FROM `table` WHERE email='jxxx@gmail.com'

Why does it fail to run the query ?

I would recommend into learning PDO MYSQL because the method you are using now is the old way. Here you have a nice tutorial: http://wiki.hashphp.org/PDO_Tutorial_for_MySQL_Developers — Andy, Sep 04 '14 at 13:14
additional short comment to WHY it is failing: you used `"` as string delimiter, but tried to escape the `'`s. But the single quote has no special meaning in a double quoted string, so an escape is not necessary, so the php interpreter treated `\'` as a string (two characters): the sql server got a request with `...email=\'jx...\'` — cypherabe, Sep 04 '14 at 13:23

score 6 · Accepted Answer · edited May 23 '17 at 12:33

6

As requested by John:

use quotes

WHERE email= '".$emailid."'

$emailid = stripslashes($_POST['email']);
$emailid = mysql_real_escape_string($emailid);
$sql = "SELECT `Name` from `table` WHERE `email`= '".$emailid."'";

in conjunction with $emailid = $_POST['email'];

instead of

WHERE email=" . $_POST['email']

which would both cause an error and is open to SQL injection in using that method.

Your present code is open to SQL injection. Use mysqli with prepared statements, or PDO with prepared statements, they're safer.

Sidenote:

If your table name is indeed called table, wrap it in backticks (just an insight).

$sql = "SELECT Name from `table`...

It's a reserved keyword

Add error reporting to the top of your file(s).

error_reporting(E_ALL);
ini_set('display_errors', 1);

and or die(mysql_error()) to mysql_query()

Edit: rewrite

<?php
try 
{
$connection = mysql_connect("localhost:3306","root","");
mysql_select_db("mobileblog", $connection);  
$emailid = stripslashes($_POST['email']);
$emailid = mysql_real_escape_string($emailid); 
echo $emailid;
$sql = "SELECT `Name` from `table` WHERE `email`= '".$emailid."'";
$result = mysql_query($sql);
echo $result;
if(!$result) { echo "<p class='error'>Error: No such email address</p>"; }
// note that the if is asking if there is no result
else {
while ($row = mysql_fetch_assoc($result)) { 
echo "<p class='success'>Welcome " . $row['Name'] . "!!</p>";
}
} // end

//mysql_query(" // suggest here to validate against emails in db");
mysql_close($connection);
echo "SUCCESS";
}
catch(Exception $e)
{
echo $e->getMessage();
// Note: Log the error or something
}
?>

Edit #2 (helping out)

<?php

$connection = mysql_connect("localhost:3306","root","");
mysql_select_db("mobileblog", $connection);  
$emailid = stripslashes($_POST['email']);
$emailid = mysql_real_escape_string($emailid); 
$sql = "SELECT `Name` from `table` WHERE `email`= '".$emailid."'";
$result = mysql_query($sql);

//  if($result) {
if(mysql_num_rows($result) > 0){
header("Location: page2.php");
exit;
}

else {

echo "<p class='error'>Error: No such email address</p>"; 

} // brace for else

mysql_close($connection);

?>

edited May 23 '17 at 12:33

Community

1
1

answered Sep 04 '14 at 13:08

Funk Forty Niner

74,450
15
68
141

A better answer than mine. – John Conde Sep 04 '14 at 13:11
using WHERE email= '".$emailid."' results with Parse error: syntax error, unexpected 'class' (T_CLASS) in C:\xampp\htdocs\email\checkform.php on line 11 – user3592479 Sep 04 '14 at 13:19
@user3592479 My answer does not contain `using WHERE email=" . $_POST['email']` - it reads as `WHERE email= '".$emailid."'` – Funk Forty Niner Sep 04 '14 at 13:20
edited . changing to WHERE email= '".$emailid."' results with Parse error: syntax error, unexpected 'class' (T_CLASS) in C:\xampp\htdocs\email\checkform.php on line 11 – user3592479 Sep 04 '14 at 13:21
@user3592479 what exactly are you entering in the form field? – Funk Forty Niner Sep 04 '14 at 13:22
@user3592479 and reload my answer. I've made a few modifications in adding `stripslashes` and `mysql_real_escape_string` – Funk Forty Niner Sep 04 '14 at 13:24
@user3592479 edit is `$emailid = stripslashes($_POST['email']); $emailid = mysql_real_escape_string($_POST['email']); $sql = "SELECT `Name` from `table` WHERE `email`= '".$emailid."'";` - reload the answer. – Funk Forty Niner Sep 04 '14 at 13:26
@user3592479 did you reload my answer and tried it? That should work. – Funk Forty Niner Sep 04 '14 at 13:33
i am confused , please let me know which part of code is to be replaced with your code ? – user3592479 Sep 04 '14 at 13:40
@user3592479 Reload and look at the bottom under **Edit: rewrite** – Funk Forty Niner Sep 04 '14 at 13:42
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/60622/discussion-between-user3592479-and-fred-ii). – user3592479 Sep 04 '14 at 13:42

Punitha Subramani · Answer 2 · 2014-09-04T13:49:39.153

3

Can you try it? It may concatenation issue

$sql = "SELECT Name from table WHERE email=" . $_POST['email'];

to

$sql = "SELECT Name from table WHERE email='". $_POST['email']."'";

Its also getting failure due to single quotes inside POST

Have to do create variable and cal for the safe like fred post

$email=$_POST['email'];

$sql = "SELECT Name from table WHERE email='". $email."'";

or we can do something like this

$sql = "SELECT Name from table WHERE email='".mysql_real_escape_string(stripslashes($_POST['email']))."'";

edited Sep 04 '14 at 13:49

answered Sep 04 '14 at 13:09

Punitha Subramani

1,467
1
8
6

my current query $sql = "SELECT Name from table WHERE email='". $_POST['email']."'"; also fails – user3592479 Sep 04 '14 at 13:18
can you try it? $sql = "SELECT Name from table WHERE email='". mysql_real_escape_string($_POST['email'])."'"; updated in my post – Punitha Subramani Sep 04 '14 at 13:23
using $sql = "SELECT Name from 'table' WHERE email='". $email."'"; throws Notice: Undefined variable: email in C:\xampp\htdocs\email\checkform.php on line 8 and $sql = "SELECT Name from table WHERE email='".mysql_real_escape_string($_POST['email'])."'"; results Error: No such email address – user3592479 Sep 04 '14 at 13:31
Undefined variable: email means you want to declare this line $email=$_POST['email']; before this line $sql = "SELECT Name from 'table' WHERE email='". $email."'"; – Punitha Subramani Sep 04 '14 at 13:34
Error: No such email address Is this server error? or your custom error? – Punitha Subramani Sep 04 '14 at 13:36
No such email address is a custom error from echo $result; if(!$result) { echo "
Error: No such email address
"; } – user3592479 Sep 04 '14 at 13:39
Then result is empty. you posted email is in sql? – Punitha Subramani Sep 04 '14 at 13:40
yes it is . my default question also throws the same error . It contains the email in the database – user3592479 Sep 04 '14 at 13:42
did u tried '".stripslashes(mysql_real_escape_string($_POST['email']))."'" ? – Punitha Subramani Sep 04 '14 at 13:43
you just echo this.. echo mysql_real_escape_string($_POST['email']; – Punitha Subramani Sep 04 '14 at 13:46
can you check.. Its working for me email='".mysql_real_escape_string(stripslashes($_POST['email']))."'"; – Punitha Subramani Sep 04 '14 at 13:50

score 0 · Answer 3 · answered Aug 24 '16 at 07:49

0

You can also use simply:

$emailid = $_POST['email'];
// or beter: $emailid = mysql_real_escape_string($_POST['email']);

$sql = "SELECT `Name` FROM `table` WHERE email='$emailid'";

Function mysql_real_escape_string() is for safe use $emailid.

answered Aug 24 '16 at 07:49

simhumileco

31,877
16
137
115

php where query not working

3 Answers3