Unable to retrieve data from MySQL table

Question

Firstly, I am new to php. I am also new to MySQL, so be gentle with me. Secondly, I know mysql_* is depreciated and this will be fixed at a later point once I understand more.

So I have the following code:

        if(isset($_POST['email']) && !empty($_POST['email']) AND isset($_POST['password']) && !empty($_POST['password'])){
            $email = mysql_escape_string($_POST['email']);
            $password = mysql_escape_string($_POST['password']);

            $search = mysql_query("SELECT * FROM users WHERE email='".$email."' AND password='".$password."' AND active='1'") or die(mysql_error()); 
            $match  = mysql_num_rows($search);

            if($match > 0){
                $user=$search['forename'] .' '.$search['surname'];
                $_SESSION['username']=$user;
                $msg = 'Login Complete! Thanks, '.$user.'!';
            }else{
                $msg = 'Login Failed!<br /> Please make sure that you enter the correct details and that you have activated your account.';
            }
        }

Quite simply, I am checking that the email and password match(I know it's not a hashed password...again, not an issue as it's a test). If they do, and the account has been activated, then I want to return the users first and last name (forename/surname in the users table) and store them in a session variable. If that variable isset, I want to use this information to confirm that the user has logged in(and so have access to certain pages). However, this test doesn't return the user name, instead outputting:

Login Complete! Thanks, !

Any help would be appreciated.

you need mysql_fetch_array: while ($row = mysql_fetch_array($search)) { $user=$row['forename'] .' '.$row['surname']; } — Bojan Kovacevic, Feb 27 '13 at 14:12
What happens when you run this? Do you get a sql connection error? or nothing at all? — Husman, Feb 27 '13 at 14:13
Please read post. I know it's depreceated(I actually state this). Also, I list the output of the script near the bottom of the post. — Glenn, Feb 27 '13 at 14:20
**First, do not use mysql_* please. This extension is deprecated as of PHP 5.5.0.** http://php.net/manual/en/function.mysql-query.php You can use mysqli_query() or PDO::query(). — F__M, Feb 27 '13 at 15:46

score 1 · Accepted Answer · edited May 23 '17 at 12:03

1

Remember the the value return by mysql_query is resource so you need to fetch the result row as an associative array.

while ($row = mysql_fetch_assoc($search)) 
{
    $user=$row['forename'] .' '.$row['surname'];
    $_SESSION['username']=$user;
}

As a sidenote, the query is vulnerable with SQL Injection if the value(s) of the variables came from the outside. Please take a look at the article below to learn how to prevent from it. By using PreparedStatements you can get rid of using single quotes around values.

How to prevent SQL injection in PHP?

edited May 23 '17 at 12:03

Community

1
1

answered Feb 27 '13 at 14:10

John Woo

258,903
69
498
492

This works perfectly. I removed the loop as it should only be 1 record max (email is the primary key). Now I just need to get to work on checking that the session works well. I've been meaning to read up on stopping injections more, so I will definitely check the link, thank you! – Glenn Feb 27 '13 at 14:17
How does this differ from mysql_fetch_array? – Glenn Feb 27 '13 at 14:17
[mysql_fetch_array, mysql_fetch_assoc, mysql_fetch_object](http://stackoverflow.com/questions/1536813/mysql-fetch-array-mysql-fetch-assoc-mysql-fetch-object) – John Woo Feb 27 '13 at 14:19

score 0 · Answer 2 · answered Feb 27 '13 at 14:10

0

You need to do $row = mysql_fetch_array($search);

And then

$user=$row['forename'] .' '.$row['surname'];

answered Feb 27 '13 at 14:10

Perry

11,172
2
27
37

score 0 · Answer 3 · answered Feb 27 '13 at 14:14

You replace your code /*************Your Code****/ if(isset($_POST['email']) && !empty($_POST['email']) AND isset($_POST['password']) && !empty($_POST['password'])){ $email = mysql_escape_string($_POST['email']); $password = mysql_escape_string($_POST['password']);

        $search = mysql_query("SELECT * FROM users WHERE email='".$email."' AND password='".$password."' AND active='1'") or die(mysql_error()); 
        $match  = mysql_num_rows($search);

        if($match > 0){
            $user=$search['forename'] .' '.$search['surname'];
            $_SESSION['username']=$user;
            $msg = 'Login Complete! Thanks, '.$user.'!';
        }else{
            $msg = 'Login Failed!<br /> Please make sure that you enter the correct details and that you have activated your account.';
        }
    }

To

/*************MY Code****/

    if(isset($_POST['email']) && !empty($_POST['email']) AND isset($_POST['password']) && !empty($_POST['password'])){
        $email = mysql_escape_string($_POST['email']);
        $password = mysql_escape_string($_POST['password']);

        $search = mysql_query("SELECT * FROM users WHERE email='".$email."' AND password='".$password."' AND active='1'") or die(mysql_error()); 
        $match  = mysql_num_rows($search);

        if($match > 0){
            $search = mysql_fetch_array($search); 
            $user=$search['forename'] .' '.$search['surname'];
            $_SESSION['username']=$user;
            $msg = 'Login Complete! Thanks, '.$user.'!';
        }else{
            $msg = 'Login Failed!<br /> Please make sure that you enter the correct details and that you have activated your account.';
        }
    }

Please do not give example with deprecated function like mysql_*. — F__M, Feb 27 '13 at 15:48

Unable to retrieve data from MySQL table

3 Answers3