How can I check If a certain cell is equal to 0 in MySQL from PHP?

Question

Sorry to bother you but I have spent hours browsing the internet for an answer to my question, I have searched Google and even asked on yahoo answers but I cannot find a solution to my problem.

I have a database, and in that database is a column called active and it is default to 0. In PHP I want to check to see if when a person is logging in if there account has a 0 in that column if so I log the user in but if not an error message appears saying their account has been banned. The problem is no matter what the message always appears:

Your account has been banned, if you think this is in error email: admin@website.com.

Here is the part of the code causing the problem:

$active = mysql_query("SELECT active FROM users WHERE username='$username'");
if (mysql_num_rows($active) == 0)
{
    session_start();
    $_SESSION['username'] = $username;
    echo "You've been logged in. <a href='http://techhelpandhowtos.tk'>Home</a>";        
}
else
{
    echo "<font color=Red>Your account has been banned, if you think this is in error email: admin@website.com</font>";
}

A connection to the database is made earlier in the script so that is not the problem. Any help is appreciated. Please, can anyone find answer.

Have you verified the query is correct? What happens when you run it from the command line? What does `mysql_error()` say? — John Conde, Sep 25 '13 at 18:04
You're only checking if the number of rows returned are == 0 not the actual value — ElefantPhace, Sep 25 '13 at 18:04
I don't have access to the command line so I ran It through the SQL version of PHPMyAdmin and it displays one column (active) and one row with 0 in it. — D.Wilson, Sep 25 '13 at 18:08
You need to actually FETCH the row of result data from your query before you can determine anything about the `active` value. — Marc B, Sep 25 '13 at 18:09
@ElefantPhace & Marc B So what should I change the script to? — D.Wilson, Sep 25 '13 at 18:09

hynner · Accepted Answer · 2013-09-25T18:11:47.170

1

As long as your user exists (and his username is uniqe) mysql_num_rows will always return 1.

Use mysql_fetch_array like this:

$active = mysql_query("SELECT active FROM users WHERE username='$username'");
$res = mysql_fetch_array($active);
if($res["active"] === "1")
...

I should also mention, that you shouldn´t be using mysql extensions for new code, as it is currently deprecated and will be removed in the future. Use PDO or mysqli instead.

edited Sep 25 '13 at 18:11

answered Sep 25 '13 at 18:06

hynner

1,332
1
10
20

I'll add that once you get started using PDO or mysqli, you'll probably be glad you did. It's not a huge learning curve to change over. – TecBrat Sep 25 '13 at 18:09
Make sure you use your `$res` to look at the result, not your query resource ;) – SamT Sep 25 '13 at 18:09
Thank-you this resolved the problem. Also I have tried using mysqli but I always seem to revert back to mysql as I find it easier as it is the way I learnt. What is easier to learn mysqli or PDO? – D.Wilson Sep 25 '13 at 18:20
well, if you only care about mysql then mysqli might be a better choice for you (it also offers procedural-style usage unlike PDO, so the usage can be similar to the usage of mysql extension and thus "easier" to learn) if by any chance you can end up supporting multiple databases then you should consider PDO. Take a look at [this](http://stackoverflow.com/questions/13569/mysqli-or-pdo-what-are-the-pros-and-cons) – hynner Sep 25 '13 at 18:26

SamT · Answer 2 · 2013-09-25T18:12:01.000

0

You were checking to see if there were matching records, NOT if they are active or not. The code below will select the user that matches the username, and check if they are banned or not.

$active = mysql_query("SELECT active FROM users WHERE username='$username'");
$row = mysql_fetch_assoc($active);
if (isset($row['active']) && $row['active'] == 1)
{
    session_start();
    $_SESSION['username'] = $username;
    echo "You've been logged in. <a href='http://techhelpandhowtos.tk'>Home</a>";        
}
else
{
    echo "<font color=Red>Your account has been banned, if you think this is in error email: admin@website.com</font>";
}

edited Sep 25 '13 at 18:12

answered Sep 25 '13 at 18:06

SamT

9,464
2
29
37

mysql_fetch_assoc only returns one row at the time! – hynner Sep 25 '13 at 18:07
well you still have it wrong ;-) one missing ")" and isset($row) will always return true, so no point in having it. – hynner Sep 25 '13 at 18:10
It was actually suppose to check if `$row['active']` was set – SamT Sep 25 '13 at 18:12

score 0 · Answer 3 · answered Sep 25 '13 at 18:18

i Think this should solve your problem

if (mysql_num_rows($active) == 1)
{
    $result = mysql_result($active,0,'active');
    if($result == 0)
    {
       session_start();
       $_SESSION['username'] = $username;
       echo "You've been logged in. <a href='http://techhelpandhowtos.tk'>Home</a>";
    }else
    {
        echo "<font color=Red>Your account has been banned, if you think this is in error email: admin@website.com</font>";

    }
}

How can I check If a certain cell is equal to 0 in MySQL from PHP?

3 Answers3