检查用户名是否已经存在于MySQLi中

问题描述

在创建新用户之前，我尝试检查输入字段中的用户名和电子邮件是否已经存在于数据库中.

I try to check if the username and email from input fields already exists in my database before to create a new user.

这是login.php:

this is login.php :

<?php
session_start();
$pseudo = $_POST['pseudo'];
$mail = $_POST['mail'];

$pseudo=mysql_real_escape_string($pseudo);
$pseudo=ltrim($pseudo);
$pseudo=rtrim($pseudo);

$mail=mysql_real_escape_string($mail);
$mail=trim($mail);

$sql=mysqli_connect('localhost','root','','bdd_name');
$query=("SELECT COUNT(*) FROM t_people WHERE 'PEO_PSEUDO'='".$pseudo."' OR 'PEO_MAIL'='".$mail."'");
$result = mysqli_prepare($sql,$query);

    mysqli_stmt_execute($result);
    mysqli_stmt_store_result($result);

if (mysqli_stmt_num_rows($result) == 0) {
  echo 1;
 }
 else {
 echo 2;
 }
mysqli_stmt_free_result($result);
mysqli_stmt_close($result);
?>

这是我的JavaScript的一部分:

And this is a part of my JavaScript :

var pseudo=$("#pseudo").val();
var mail=$("#mail").val();
    $.ajax({
        type: "POST",
        url: "login.php",
        data: {pseudo:pseudo, mail:mail}
        }).done(function(result) {
            if (result==1) {
                good();
            }
            else if (result==2) {
                bad();
            }
        });

有人可以告诉我这是怎么回事吗?

Can anyone tell me what is wrong with this?

我几个小时以来一直在这方面，我一无所知...

I'm on this since hours now and I'm clueless...

推荐答案

有些地方出了问题.不要使用mysql_real_escape_string，因为您正在使用mysqli_*.使用mysqli_real_escape_string代替.但最好使用mysqli_stmt_bind_param，因为您正在使用准备好的语句.而且，如果您使用COUNT(*)，您将始终获得1行.

There are some things going wrong.Don't use mysql_real_escape_string cause you're working with mysqli_*. Use mysqli_real_escape_string instead. But better use mysqli_stmt_bind_param because you're working with prepared statements. And if you work with COUNT(*) you always get 1 row.

$pseudo = $_POST['pseudo'];
$mail = $_POST['mail'];

$query = "SELECT * FROM t_people WHERE PEO_PSEUDO = ? OR PEO_MAIL = ? LIMIT 1";
$stmt = mysqli_prepare($sql, $query);
mysqli_stmt_bind_param($stmt, 'ss', $pseudo, $mail);
mysqli_stmt_execute($stmt);
mysqli_stmt_store_result($stmt);
$numRows = mysqli_stmt_num_rows($stmt);
mysqli_stmt_close($stmt);

使用COUNT(*)(效率更高)时，它就像:

With COUNT(*) (which is more efficient) it goes like:

$query = "SELECT COUNT(*) as numrows FROM t_people WHERE PEO_PSEUDO = ? OR PEO_MAIL = ?";
...
mysqli_stmt_execute($stmt);
mysqli_stmt_bind_result($stmt, $numRows);
mysqli_stmt_fetch($stmt);
mysqli_stmt_close($stmt);

// work with $numRows

这篇关于检查用户名是否已经存在于MySQLi中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！