Comparing if values exists in a MySQL table using PHP

Question

Given the following code:

$checkuname = $connect->prepare('SELECT * FROM user WHERE username = ?');
$checkuname->bind_param("s", $uname);

$checkemail = $connect->prepare('SELECT * FROM user WHERE email = ?');
$checkemail->bind_param("s", $email);

$match = 0;

if ($checkuname->execute()) {
    //if username matches//
    $erroruname = "This username exists, please enter a new one";
    $match = $match + 1;
    }
if ($checkemail->execute()) {
    //if email matches//
    $erroremail = "This email has been used, please enter another one";
    $match = $match + 1;
    }
if ($match == 0) { //if no match, good to push data into database// }

No matter what happens, it always returns me saying that username exists (when it doesn't).
Is there any way to correct this?

Or if you think there would be an easier or clearer way to check if both username and email exists in a database, please do share too.

Just to mention too: Most tutorials I have found uses a single variable to check, but I need to check 2 variables

PDO::execute always returns TRUE regardless of amount of data found. — olegsv, Jan 29 '17 at 14:05
Seems what you are checking is if there's an error or not to execute the query, not whether or not the query will return anything. — Shiping, Jan 29 '17 at 14:05
@Shiping so how do I check if the query would return something? — Timothy Wong, Jan 29 '17 at 14:07
@Fred-ii- you're right, but in mysqli execute() behaves in the same way. — olegsv, Jan 29 '17 at 14:08
@olegsv so you mean that as long as the command is executed it returns a true? — Timothy Wong, Jan 29 '17 at 14:08
here http://stackoverflow.com/questions/22252904/check-if-row-exists-with-mysql/22253579#22253579 *done like dinner* ;-) just add to it. — Funk Forty Niner, Jan 29 '17 at 14:09
@TimothyWongGlash you have to fetch the result and check if it's empty or not. — Shiping, Jan 29 '17 at 14:10
@TimothyWongGlash - I removed "" from the title. Either delete the question, post your own answer or invite me to post one and accept that one. — Funk Forty Niner, Jan 29 '17 at 14:37
@Fred-ii- I'll invite you to post an answer and I'll mark it as solved — Timothy Wong, Jan 31 '17 at 01:33

score 1 · Accepted Answer · answered Jan 31 '17 at 23:52

"@Fred-ii- I'll invite you to post an answer and I'll mark it as solved – Timothy Wong Glash"

As requested by the OP:

You can do this in one query.

$query = "SELECT `email`, `username` FROM `user` WHERE email=? AND username=?";

if ($stmt = $connect->prepare($query)){

        $stmt->bind_param("ss", $email, $uname);

        if($stmt->execute()){
            $stmt->store_result();

            $email_check= "";

            // Number of binded results must match the number of columns in SELECT
            $stmt->bind_result($email_check, $username_check); 
            $stmt->fetch();

            // or num_rows >0
            if ($stmt->num_rows == 1){
                echo "That records already exists.";
                exit;
            }

        }else{ echo "Error: " . mysqli_error($connect); }
    }

score 0 · Answer 2 · answered Jan 29 '17 at 14:14

Well, you are checking if the query executes, but you are not checking if the values returned are correct or not. What you need to do is verify how many rows are returned after executing the query, if a row is returned the user has been found. You can do that with num_rows.

Comparing if values exists in a MySQL table using PHP

2 Answers2