MySQL & PHP, mysqli_num_rows always returns 0

Question

I have a little problem with a basic register script that im writing and i cant seem to be able to fix it.

Well, here is the code:

<?php

//MySQLi connection

$con = mysqli_connect("-","-","-","users");

if (mysqli_connect_errno())

{

echo "MySQLi Connection was not established: " . mysqli_connect_error();

}

//Reading the userdata from the registerp.php page

$usr = mysqli_real_escape_string($con,$_POST['username']);

$email = mysqli_real_escape_string($con,$_POST['email']);

$pass_unhashed = mysqli_real_escape_string($con,$_POST['pass']);

$pass = password_hash($pass_unhashed, PASSWORD_DEFAULT);

//Checking if user exists

$check_usr = mysqli_query($con,"SELECT FROM users WHERE user_name = $usr");

if (mysqli_num_rows($con,$check_usr)>=1)
{
echo "This Username already exists";
}
else
{
echo "This Username is available";
}

?>

Problem is that i cant get the verification (so that people cant register the same name twice) to work:

$check_usr = mysqli_query($con,"SELECT FROM users WHERE user_name = $usr");

if (mysqli_num_rows($con,$check_usr)>=1)
    {
    echo "This Username already exists";
    }
    else
    {
    echo "This Username is available";
    }

Always returns "This Username is available", even though the User i used to test it with (nevondrax), is in the MySQL table (thats how it looks like click )

Issues specific to programming and software development are off topic, see On-Topic. Try Stack Overflow but please first read How do I ask a good question?. You can flag your question and ask a moderator to migrate it. — DavidPostill
– DavidPostill, Commented Jul 20, 2016 at 12:12
You need to verity manually that mysqli_query() succeeds, mysqli won't do it for you. Once you do,you'll notice the SQL parse error. — Álvaro González
– Álvaro González, Commented Jul 22, 2016 at 20:03

Seth · Accepted Answer · 2016-07-20 10:32:55Z

1

Your query (SELECT FROM users WHERE user_name = $usr) doesn't select any lines so there are no lines returned, hence mysqli_num_rows is always zero.

In addition you're also missing some quotes around your parameter. Right now your query would look like this: SELECT FROM users WHERE user_name = dummy

Correct would/should be: SELECT * FROM users WHERE user_name = 'dummy'

If we take your code block and adjust it, it might look like this:

$check_usr = mysqli_query($con,"SELECT user_name FROM users WHERE user_name = '$usr'");
if($check_usr === false)
{
    echo(mysqli_error($con));
}
else
{
    if (mysqli_num_rows($check_usr)>=1)
    {
        echo "This Username already exists";
    }
    else
    {
        echo "This Username is available";
    }
}

answered Jul 20, 2016 at 10:32

Seth

1,25316 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

Nevondrax Over a year ago

So i would have to query ( SELECT * FROM users WHERE user_name = $usr) ?

Devsi Odedra Over a year ago

it's SELECT usr FROM users WHERE user_name = $usr

Seth Over a year ago

Sorry for not including the correction. What @devodedra wrote is correct. You need to include some kind of field or an asterisk to select every available field. So it would be either SELECT field FROM table or SELECT * FROM table.

Nevondrax Over a year ago

Hrm, i just tried to use the asterisk and also the field name, but the result is still the same. Could the error be in my register page? (The html page) pastebin.com/hhaFfu5R

Seth Over a year ago

In addition you are/were missing some quotes around the username. I edited my answer accordingly. Using mysqli_error($con) report it as well as an invalid query.

|

Devsi Odedra · Accepted Answer · 2016-07-20 10:36:27Z

1

Your syntaxt of select query is wrong.

$check_usr = mysqli_query($con,"SELECT FROM users WHERE user_name = $usr");

you have to write syntax as

$check_usr = mysqli_query($con,"SELECT fieldname FROM users WHERE user_name = $usr");

you missed to give field name after SELECT

answered Jul 20, 2016 at 10:36

Devsi Odedra

5,3421 gold badge25 silver badges38 bronze badges

Collectives™ on Stack Overflow

MySQL & PHP, mysqli_num_rows always returns 0

2 Answers 2

8 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

8 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related