Query returning results when database table is empty

PHP
1

I have been dealing with this for a while now. My user submission form now returns a result when it queries whether a UserName exists or not. It worked at one point but it stopped working and I can’t figure out why. I’ve stopped and restarted my apache and mysql in XAMPP. So, I did that.

I’ve gone over the code over and over and can’t see what the problem is. I even emptied the database table so there are NO users in the table.

Here is the code:

// Check for existing user with the new id

$sql = "SELECT COUNT(*) FROM user WHERE user_name = '$_POST[newid]'";
$result = mysqli_query($conn, $sql);

if (!$result) {	
    error('A database error occurred in processing your '.
          'submission.\\nIf this 1 error persists, please '.
          'contact you@example.com.');
}
        $resultCheck = mysqli_num_rows($result);
        printf("Result set has %d rows.\n",$resultCheck);
        echo "$_POST[newid]";
   if  ($resultCheck > 0) {
    error('A user already exists with your chosen userid.\\n'.
          'Please try another.');
}

I put the printf statement in there to see what it is returning and it always returns 1 result when there are NO results to return. I set the ($resultCheck > 0) to ($resultCheck > 1) and it works… of course.

The echo statement lets me see what it is actually inputting to $_POST[newid]. It is sending the form input as it should.

It’s pretty straight forward. Am I overlooking something obvious?

I know it isn’t the database table because I changed the query to query another/different table and it returned a result = 1

As always, any help is appreciated.
Steve

2

http://php.net/manual/en/mysqli.query.php

Returns FALSE on failure. For successful SELECT, SHOW, DESCRIBE or EXPLAIN queries mysqli_query() will return a mysqli_result object. For other successful queries mysqli_query() will return TRUE.

You are SELECTing a COUNT
A count of zero is a result of one row.

1 Like
3

Thank you for the reply. I found the problem. It turned out to be a typo of all things.

I have mysqli_num_rows() when that is not an existing function (for the version of php I’m using). It should be mysql_num_rows() without the ‘i’.

Now it’s allowing the data to be written to the database but it is also giving me another error: Warning: mysql_num_rows() expects parameter 1 to be resource, object given in C:\xampp\htdocs…

Just another problem to track down.

thanks again
Steve

4

Update…

mysqli_num_rows is correct but it doesn’t work well with SELECT COUNT(*).

Here is the new code that works GREAT:

// Check for existing user with the new id

$sql = "SELECT * FROM user WHERE user_name = '$_POST[newid]'";
$result = mysqli_query($conn, $sql);

if (!$result) {	
    error('A database error occurred in processing your '.
          'submission.\\nIf this 1 error persists, please '.
          'contact you@example.com.');
}
        $num_rows = mysqli_num_rows($result);
        if  ($num_rows > 0) {
    error('A user already exists with your chosen userid.\\n'.
          'Please try another.');
}
5

You’re wide open to SQL Injection Attacks there, you’ve plugged user submitted data into the query that hasn’t been validated or sanitized

6

It does work perfectly well, as long as you accept that you will always get one row as a result from a COUNT query, as @mittineague said earlier. That row will contain the count value, which in turn might be zero. So to use it as a count, you then have to retrieve the count and look at that, rather than the number of rows.

You should get into the habit of retrieving only the columns you require when doing a query, rather than a SELECT *. Particularly in this case where you’re only really checking to see whether the username already exists, you don’t actually do anything with the results from the SELECT.

7

This is the initial stage where I’m learning PHP and building a website. After the website is complete (or I should say functional) I will return and work on the sites security. I’m learning in segments. After the PHP is done then I will move on to CSS.

8

AKA, not until after you have been hacked and the message hits home.

2 Likes
9

The security of a website is more important than what it looks like. So if you plan on being a back-end developer understanding security vulnerabilities and preventing them is of the upmost importance – not css. Using prepared statements to prevent SQL injection is a very basic skill to learn but one of most impactful when it comes to preventing security vulnerabilities.

1 Like
10

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.