Mysql database does not show query result in php

Question

I am trying to run a beginner mysql program which checks two arguments against stored values in the database. I have one database named 'sl493' on the server, which has a table 'metauser' and 1 row of data. When i try to run the program, i only get "Connected successfully" and no 'login ok'

<html>
<head>
<title>Connecting MySQL Server</title>
</head>
<body>
<?php
$username = 'fahad';
$password = '2';

   $dbhost = '*********.edu';
   $dbuser = '*******';
   $dbpass = '*******';
   $conn = mysql_connect($dbhost, $dbuser, $dbpass); // connect to server
   if(! $conn )
   {
     die('Could not connect: ' . mysql_error());
   }

   echo 'Connected successfully';
   mysql_select_db('sl493',$conn); //pick sl493 database

$result = mysql_query("SELECT * 
        FROM metauser 
        WHERE metauser.username = $username;
        AND metauser.password = $password") ; //select data from metauser table

        $row = mysql_fetch_assoc($result);

        if($row['username'] == $username)
{
    echo 'Login ok';
}
else
{
    'Login failed';
}
?>
</body>
</html>

Here's a snapshot of the database: https://i.stack.imgur.com/0PUPG.jpg

Any suggestions about what's going wrong ?

Add error reporting to the top of your file(s) right after your opening PHP tag for example ` — Funk Forty Niner, Mar 15 '15 at 21:44
I got this: Connected successfullyYou have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AND metauser.password = 2' at line 4. So it' a syntax error, wonder what's it is. — slk, Mar 15 '15 at 21:58
There you go. You're using strings and not treating those as such. They require quoting. Plus, as stated; a semi-colon. — Funk Forty Niner, Mar 15 '15 at 22:00

score 1 · Accepted Answer · edited May 23 '17 at 10:29

You're not treating strings as they should be and require to be quoted.

You also have a semi-colon at the end of $username in your where clause which needs to be removed.

$result = mysql_query("SELECT * 
        FROM metauser 
        WHERE metauser.username = '$username'
        AND metauser.password = '$password'") ;

More on string literals:

http://dev.mysql.com/doc/refman/5.0/en/string-literals.html

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

For password storage, use CRYPT_BLOWFISH or PHP 5.5's password_hash() function.
For PHP < 5.5 use the password_hash() compatibility pack.

You should store hashed passwords, instead of literal strings.

Add error reporting to the top of your file(s) which will help find errors.

<?php 
error_reporting(E_ALL);
ini_set('display_errors', 1);

// rest of your code

Also add or die(mysql_error()) to mysql_query().

Sidenote: Error reporting should only be done in staging, and never production.

Thanks, it worked. One more thing however, as the poster above mentioned, mysql_query and mysql_fetch_assoc are deprecated. Is it advisable I use mysqli instead. I am assuming the syntax will remain the same either way. edit: I just read the second half of your answer about mysqli, so again, thanks. — slk, Mar 15 '15 at 22:19
@slk You're welcome. Similar but `mysqli_` functions require to pass the DB connection to them. For example: `mysqli_query($connection, $query);` same thing for `mysqli_real_escape_string($connection, $variable);` the connection comes first in `mysqli_` functions, as opposed to `mysql_` which comes first. — Funk Forty Niner, Mar 15 '15 at 22:20

score 0 · Answer 2 · answered Mar 15 '15 at 21:54

According to php.net:

$query = sprintf("SELECT * FROM metauser 
    WHERE metauser.username='%s' AND metauser.password='%s'",
    mysql_real_escape_string($username),
    mysql_real_escape_string($password));

// Perform Query
$result = mysql_query($query);
$row = mysql_fetch_assoc($result);
if($row['username'] == $username)
{
    echo 'Login ok';
}

FYI, that page also says that mysql_query and mysql_fetch_assoc are deprecated.

Mysql database does not show query result in php

2 Answers2