在准备好的语句中两次使用变量 [英] Use a variable twice in prepared statement

问题描述

我开始在PHP的sql查询中使用预准备语句，并以此为起点提出一个问题.

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.

我有一个在登录时从表中获取用户ID的功能.我希望用户能够使用其用户名或电子邮件地址进行登录.

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.

所以我的sql语句是:

So my sql statement is:

SELECT * FROM`login`在哪里`username` =?或`emailAddress` =?

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

现在基本上在此查询中，当username和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);

因此，username和emailAddress的值必须相同.本质上，我希望$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.

我的问题是:我做得正确吗? 有没有更有效的方法?

推荐答案

是的，您必须将其绑定两次.如果由于某种原因而反对，则可以将查询改写为:

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.

这篇关于在准备好的语句中两次使用变量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！