如何检查MySQL中是否存在一行?(即检查 MySQL 中是否存在电子邮件) [英] How to check if a row exists in MySQL? (i.e. check if an email exists in MySQL)

问题描述

我需要帮助检查数据库中是否存在一行.就我而言，该行包含一个电子邮件地址.我得到了结果:

I need help checking if a row exists in the database. In my case, that row contains an email address. I am getting the result:

email no longer exists [e-mail]

这是我目前使用的代码:

This is the code I'm currently using:

if (count($_POST)) {
    $email = $dbl->real_escape_string(trim(strip_tags($_POST['email'])));

    $query = "SELECT `email` FROM `tblUser` WHERE `email` = '$email'";
    $result = mysqli_query($dbl, $query);
    if (is_resource($result) && mysqli_num_rows($result) == 1) {
        $row = mysqli_fetch_assoc($result);
        echo $email . " email exists " .  $row["email"] . "\n";
    } else {
        echo "email no longer exists" . $email . "\n";
    }
}

是否有更好的方法来检查 MySQL 中是否存在一行(在我的情况下，检查 MySQL 中是否存在电子邮件)?

Is there a better way to check if a row exists in MySQL (in my case, check if an email exists in MySQL)?

推荐答案

以下是经过尝试、测试和验证的方法，用于检查行是否存在.

The following are tried, tested and proven methods to check if a row exists.

(其中一些是我自己用的，或者过去用过的).

(Some of which I use myself, or have used in the past).

我在语法中犯了一个错误，我使用了两次 mysqli_query().请查阅修订版.

I made an previous error in my syntax where I used mysqli_query() twice. Please consult the revision(s).

即:

if (!mysqli_query($con,$query)) 应该简单地读为 if (!$query).

• 我很抱歉忽略了那个错误.

旁注: '".$var."' 和 '$var' 都做同样的事情.您可以使用任何一种，两者都是有效的语法.

Side note: Both '".$var."' and '$var' do the same thing. You can use either one, both are valid syntax.

以下是两个已编辑的查询:

Here are the two edited queries:

$query = mysqli_query($con, "SELECT * FROM emails WHERE email='".$email."'");

    if (!$query)
    {
        die('Error: ' . mysqli_error($con));
    }

if(mysqli_num_rows($query) > 0){

    echo "email already exists";

}else{

    // do something

}

在你的情况下:

$query = mysqli_query($dbl, "SELECT * FROM `tblUser` WHERE email='".$email."'");

    if (!$query)
    {
        die('Error: ' . mysqli_error($dbl));
    }

if(mysqli_num_rows($query) > 0){

    echo "email already exists";

}else{

    // do something

}

您还可以将mysqli_ 与准备好的语句一起使用 方法:

You can also use mysqli_ with a prepared statement method:

$query = "SELECT `email` FROM `tblUser` WHERE email=?";

if ($stmt = $dbl->prepare($query)){

        $stmt->bind_param("s", $email);

        if($stmt->execute()){
            $stmt->store_result();

            $email_check= "";         
            $stmt->bind_result($email_check);
            $stmt->fetch();

            if ($stmt->num_rows == 1){

            echo "That Email already exists.";
            exit;

            }
        }
    }

或带有准备好的语句的 PDO 方法:

<?php
$email = $_POST['email'];

$mysql_hostname = 'xxx';
$mysql_username = 'xxx';
$mysql_password = 'xxx';
$mysql_dbname = 'xxx';

try {
$conn= new PDO("mysql:host=$mysql_hostname;dbname=$mysql_dbname", $mysql_username, $mysql_password); 
     $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch (PDOException $e) {
     exit( $e->getMessage() );
}

// assuming a named submit button
if(isset($_POST['submit']))
    {

        try {
            $stmt = $conn->prepare('SELECT `email` FROM `tblUser` WHERE email = ?');
            $stmt->bindParam(1, $_POST['email']); 
            $stmt->execute();
            while($row = $stmt->fetch(PDO::FETCH_ASSOC)) {

            }
        }
        catch(PDOException $e) {
            echo 'ERROR: ' . $e->getMessage();
        }

    if($stmt->rowCount() > 0){
        echo "The record exists!";
    } else {
        echo "The record is non-existant.";
    }


    }
?>

• 准备好的语句最好用于帮助防止 SQL 注入.

注意:

在处理上面使用/概述的表单和 POST 数组时，请确保 POST 数组包含值，POST 方法用于表单并匹配输入的命名属性.

When dealing with forms and POST arrays as used/outlined above, make sure that the POST arrays contain values, that a POST method is used for the form and matching named attributes for the inputs.

• 仅供参考:如果没有明确指示，表单默认使用 GET 方法.

注意: - $_POST['var'] 匹配.$_POST['Var'] 不匹配.

Note: <input type = "text" name = "var"> - $_POST['var'] match. $_POST['Var'] no match.

• POST 数组区分大小写.

咨询:

• http://php.net/manual/en/tutorial.forms.php

错误检查引用:

• http://php.net/manual/en/function.error-报告.php
• http://php.net/manual/en/mysqli.error.php
• http://php.net/manual/en/pdo.error-处理.php

请注意，MySQL API 不会混合使用，以防您访问此问答并使用 mysql_ 连接(和查询).

Please note that MySQL APIs do not intermix, in case you may be visiting this Q&A and you're using mysql_ to connect with (and querying with).

• 从连接到查询，您必须使用相同的方法.

咨询以下内容:

• 我可以在 PHP 中混合 MySQL API 吗?

如果您正在使用 mysql_ API 并且无法选择使用它，请参考以下堆栈问答:

If you are using the mysql_ API and have no choice to work with it, then consult the following Q&A on Stack:

• MySql php:检查行是否存在

mysql_* 函数已被弃用，并将从未来的 PHP 版本中删除.

The mysql_* functions are deprecated and will be removed from future PHP releases.

• 是时候进入 21 世纪了.

您还可以向 (a) 行添加 UNIQUE 约束.

参考文献:

• http://dev.mysql.com/doc/refman/5.7/en/constraint-primary-key.html
• http://dev.mysql.com/doc/refman/5.7/en/alter-table.html
• 如何检查一个值是否已经存在存在以避免重复?
• 如何向现有表添加唯一键(具有非唯一行)

这篇关于如何检查MySQL中是否存在一行?(即检查 MySQL 中是否存在电子邮件)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！