How to use 'or' inside mysqli prepare statement query

75 Views Asked by At

I'm new to mysqli prepare statement and was surprised to find out the || doesn't work as expected in mysqli prepare statement query. I have a function which check whether the user registered, and its evaluation should be based upon the unique row id and email address.Besides, since the id column and the email column are of different type, which are Int(11) and VARCHAR respectively, so I assumed it's going to have some trouble using bind_param because the different data type

public function checkUserRegistered($iden){
 //The iden can be either a id or a email address
            $stmt = $this->connection->prepare("SELECT `id` FROM `$this->table_name` WHERE (`email`= ?  || `id`= ?) LIMIT 1 ");
            $stmt->bind_param('s',$iden); // s or i ?
            if($stmt->execute()){
                $result = $stmt->get_result();
                if($result->num_rows ==1){
                    $stmt->close();
                    return true;
                }else{
                    return false;
                }
            }
        }

What is the right way of doing this, do I have to use separate functions instead?

2

There are 2 best solutions below

1
On BEST ANSWER

You need to bind two params like this:

$stmt->bind_param('si',$iden, $iden);

as you have set two '?'.

s means string, i means integer, b is blob and d is double.

0
On

The problem is you have specified that in the prepare statement you want to bind two values. But in the bindValue statement you are only binding one value. Also coming to the issue that $iden can be the email or number then I think the best way is to generate conditional statements for them. So if I was you, I will go with:

public function checkUserRegistered($iden){
//making sure $iden is not empty
if ($iden) {
    //if $iden is a number is equal to id
    if (is_numeric($iden)) {
        $stmt = $this->connection->prepare("SELECT `id` FROM `$this->table_name` WHERE `id`= ? LIMIT 1 ");

        $stmt->bind_param('i',$iden); //because id is an integer
    } else {
        //then $iden is a varchar which is the email
        $stmt = $this->connection->prepare("SELECT `id` FROM `$this->table_name` WHERE `email`= ? LIMIT 1 ");

        $stmt->bind_param('s',$iden); //because email is a string
    }

    if ($stmt->execute()) {
        $result = $stmt->get_result();

        if($result->num_rows ==1){
            $stmt->close();
            return true;
        }else{
            return false;
        }
    }
}

}