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?
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.
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.
