email or username login

I want to ask. I have fields called email and passwrod

<code>$email = $_POST['email'];
$password = $_POST['password'];
$login     = mysql_query("SELECT * FROM `users`
JOIN `users_profile` USING (id)
WHERE `email` = '".$email."' AND `password` = '".$password."' 
OR `username` = '".$email."' AND `password` = '".$password."'
</code>

The above is my login syntax
Assuming the user enters the email or password pair, the login is successful:

<code>`email` = '".$email."' AND `password` = '".$password."' 
</code>

But if the user enters the username and password, they can also log in

<code>`username` = '".$email."' AND `password` = '".$password."'
</code>

It’s just that the above syntax can only be used to enter email and password to log in.
If you enter username and password to log in, it will fail.
Confirm JOIN users_profile and use id. There is no problem.
What is the problem?

Reply content:

I want to ask. I have fields called email and passwrod

<code>$email = $_POST['email'];
$password = $_POST['password'];
$login     = mysql_query("SELECT * FROM `users`
JOIN `users_profile` USING (id)
WHERE `email` = '".$email."' AND `password` = '".$password."' 
OR `username` = '".$email."' AND `password` = '".$password."'
</code>

The above is my login syntax
Assuming the user enters the email or password pair, the login is successful:

<code>`email` = '".$email."' AND `password` = '".$password."' 
</code>

But if the user enters the username and password, they can also log in

<code>`username` = '".$email."' AND `password` = '".$password."'
</code>

It’s just that the above syntax can only be used to enter email and password to log in.
If you enter username and password to log in, it will fail.
Confirm JOIN users_profile and use id. There is no problem.
What is the problem?

Be careful to add parentheses when using OR.

<code class="sql">SELECT * FROM `users`
JOIN `users_profile` USING (id)
WHERE (`email` = '".$email."' AND `password` = '".$password."') 
OR (`username` = '".$email."' AND `password` = '".$password."')</code>

However, it is not recommended to use such sql.

You can first use regular expressions in the code to determine whether the user inputs email or username. If it is email, use the email field to query, otherwise use the username field.

Reference: http://tool.oschina.net/regex#

First of all, Your code can be easily SQL injected, so please use at least mysqli. I strongly recommend PDO.

Then, my personal idea is to assume that the form on the front-end page has an input similar to this:

<code><input name="login" type="text" class="form-control" placeholder="用户名或者邮箱"></code>

We use name="login" here to replace email or username, then:

<code>$field = filter_var($_POST['login'],FILTER_VALIDATE_EMAIL) ? 'email':'username';
// 这里的 $field 就是你的 欄位 （这边翻译为 字段 ）</code>

Then the SQL statement is probably like this:

<code>$email = $_POST['email'];
WHERE `".$field."` = '".$email."'</code>

Once again, the way you write is very dangerous.

Information reference: http://php.net/manual/en/func...