Use a variable twice in prepared statement

Question

I'm beginning to use prepared statements with my sql queries in php and in starting with this I have come up with a question.

I have a function that grabs a user's id from a table at login. I want the user to be able to use either their username or email address for their login.

So my sql statement is:

SELECT * FROM `login` WHERE `username`=? OR `emailAddress`=?

Now essentially when in this query username and emailAddress will be the same because it can be either or.

So when binding my statements do I bind my variable twice:

bind_param('ss', $user, $user);

So the value for username and emailAddress needs to be the same. Essentially I want $user to be the value of both the placeholders.

My questions are: Am I doing this correctly? Is there a more efficient way?

Answer 1

Yes. There have to be as many variables in the bind_param() call as there are placeholders in the query. Consider if you had:

SELECT * FROM login
WHERE username = ? and emailAddress = ? and country = ?

and you tried to bind too few of them:

bind_param("ss", $user, $country);

How is it supposed to know which variable should be repeated for the extra placeholder?

There's no problem with using the same variable twice. I wouldn't recommend it with bind_result, though -- it will presumably allow it, but I don't know if it's predictable which column will be put into the variable.

Answer 2

Yes, you would have to bind it twice. If you are opposed to that for some reason, you could rephrase the query as:

SELECT *
FROM `login` l cross join
      (select ? as thename) const
WHERE l.`username` = thename OR `emailAddress` = thename;

This is using a subquery to name the parameter so it can be referred to multiple times in the query.