Using php to run a MYSQL SELECT query

Question

I'm trying to run a select statement on a table called test2. It's a very simple table with a username and password column. I don't understand how to access the result of the query. I want to be able to check if the username and password sent through the POST form match a username and password in my test2 table. Right now I'm just trying to get the query to work and print out the result. I got most of this code from the documentation here http://php.net/manual/en/mysqli-stmt.get-result.php

    $link = mysqli_connect(HOST,USERNAME,PASSWORD,DBNAME) or die ("Connect Error" . mysqli_error($link));

    $query = "SELECT username, password FROM test2 where username =? AND password =?";

    $stmt = mysqli_stmt_init($link);
    if(!mysqli_stmt_prepare($stmt, $query))
    {
            print "Failed to prepare statement\n";
    }

    else{
        mysqli_stmt_bind_param($stmt,"ss", $_POST['user'], $_POST['pw']);

        mysqli_execute($stmt);

        $result = mysqli_stmt_get_result($stmt);


        $numRows = $result->mysqli_num_rows;
        if ($numRows == 1){
            echo "User exists";
        }else{
            echo "No users";
        }
    }
    mysqli_close($stmt);

    mysqli_close($link);

Ok, there's no obvious problems here. What errors are you getting? — Machavity, Nov 01 '16 at 22:58
I changed my code to what @TheValyreanGroup suggested. When I submit the form nothing is being printed to the screen. I ran a test and the form data is being passed just fine so the problem must be in what I posted. — , Nov 01 '16 at 23:12
You need to [turn error reporting on](http://stackoverflow.com/questions/19626013/php-white-screen-of-death) — Machavity, Nov 01 '16 at 23:17
When I debug my program with the php command I get no errors. I'm running linux on a remote server. All my html elements are being displayed properly after I submit. Is there a way to check for errors in my query? — , Nov 01 '16 at 23:32

score 0 · Answer 1 · answered Nov 01 '16 at 23:04

0

For that query you have there, all you need to know is if any rows have been returned, not the value of the row objects.

So, you can just do...

$numRows = $result->mysqli_num_rows;
if ($numRows == 1){
    echo "User exists";
}else{
    echo "No users";
}

Then you can handle what do do with it from there.

However, if you really want to authenticate a user, your query should be...

 SELECT ID from test2 WHERE username=? AND password=?

Then pass in the password from POST in your prepared statement as well.

answered Nov 01 '16 at 23:04

TheValyreanGroup

3,554
2
12
30

I updated my original post with your suggestion. Nothing is being printed out when I submit my post form – Nov 01 '16 at 23:18
Check the PHP error log and see what errors there are. – TheValyreanGroup Nov 01 '16 at 23:26
When I debug my program using the php command there are no errors. I'm running linux on a remote server. Is there another way to check for errors? – Nov 01 '16 at 23:32
cat or nano to either /var/log/httpd/error_log or /var/log/apache2/error.log – TheValyreanGroup Nov 01 '16 at 23:43
Debugging the file only shows syntax errors, not execution errors – TheValyreanGroup Nov 01 '16 at 23:43
I'm getting "permission denied" when I run cat /var/log/httpd/error_log – Nov 01 '16 at 23:55
You need root permissions. So either su or sudo – TheValyreanGroup Nov 01 '16 at 23:55

Using php to run a MYSQL SELECT query

1 Answers1