PHP如何检查MySQL数据库中已存在的电子邮件? [英] PHP how to check for email already in MySQL database?

问题描述

我正在Stackoverflow上向所有PHP Gods寻求帮助:)

Hi I'm calling out for help from all the PHP Gods on Stackoverflow :)

我已经创建了一个电子邮件注册表单(电子邮件只有1个字段)，该表单能够使用Ajax进行验证并将新的电子邮件从我发现的基本PHP脚本发布到数据库中.

I've created an email signup form (just 1 field for email), that is able to validate with Ajax and post a new email to the database from a basic PHP script I found.

但是，我下一步要做的是在添加数据库之前检查数据库中是否已存在电子邮件.在Stack上有几个完全像这样的问题，但是我尝试了所有答案，但无济于事:(我不是PHP专家，还无法正确破解它.

However the next step I have to do is check if an email is already in the database before adding it. There are several questions exactly like this on Stack and I've tried all the answers however to no avail :( I'm not a PHP guy and haven't been able to hack it right yet.

下面是我当前的insert.php文件，该文件可以正常工作，并且确实在数据库中添加了新的电子邮件字段.但是，下面的代码是我尝试用来检查现有电子邮件的最新代码，但出现发送数据错误.

Below is my current insert.php file which does work and does add a new email field into the database. However the code below that is the latest I've tried to use to check for an already existing email, but I get a send data error.

正在使用PHP文件添加电子邮件

<?php
$con = mysql_connect("localhost","root","root");
if (!$con)
{
    die('Could not connect: ' . mysql_error());
}

mysql_select_db("mydatabase", $con);

$sql="INSERT INTO newsletter (email)
    VALUES
    ('$_POST[mail]')";

    if (!mysql_query($sql,$con)) {
        die('Error: ' . mysql_error());
    }

    echo "Thanks for subscribing!"; //Text on page
    //header("Location: /thankyoupage.php"); //Redirect page

mysql_close($con)
?>

使用PDO更新代码下面的代码可以添加电子邮件，但是仍然允许重复...

UPDATED CODE using PDO Code below works to add emails, however still allows duplicates...

<?php
/*** mysql hostname ***/
$hostname = 'localhost';
/*** mysql username ***/
$username = 'root';
/*** mysql password ***/
$password = 'root';
/*** email ***/
$email    = '$_POST[mail]';

try {
$dbh = new PDO("mysql:host=$hostname;dbname=mydatabase", $username, $password);

//$query = SELECT count(*) AS `total` FROM `data` WHERE `email` = '{$request}'

$query = SELECT COUNT(*) as 'count' FROM `data` WHERE email = '$_POST[mail]';
$row = mysql_fetch_assoc(mysql_query($query));

if($row['total']) {
    echo 'Sorry email already exists';
}
else {
    /*** echo a message saying we have connected & added email ***/
    echo 'Thanks for subscribing!';

    /*** INSERT data ***/
    $count = $dbh->exec("INSERT INTO newsletter(email) VALUES ('$_POST[mail]')");
}

/*** echo a message saying we have connected & added email ***/
//echo 'Thanks for subscribing!';

/*** INSERT data ***/
//$count = $dbh->exec("INSERT INTO newsletter(email) VALUES ('$_POST[mail]')");

/*** echo the number of affected rows ***/
/*echo $count;*/

/*** close the database connection ***/
$dbh = null;
}
catch(PDOException $e)
{
echo $e->getMessage();
}
?>

在此先感谢任何有时间的人:)

Thanks in advance for anyone with the time to take a look at this :)

附加说明:我的数据库表称为新闻通讯，有2个字段(仅ID-仅数字)&(电子邮件)

推荐答案

如果电子邮件是唯一密钥，那将很简单

if email is an unique key, that would be simple

<?php
mysql_connect("localhost","root","root");
mysql_select_db("howdini");
$email = mysql_real_escape_string($_POST['mail']);
$sql="INSERT IGNORE INTO newsletter (email) VALUES ('$email')";
mysql_query($sql) or trigger_error(mysql_error()." ".$sql);
if (mysql_affected_rows()) {
  header("Location: /thankyoupage.php"); //Redirect page
} else {
  //already exists
}

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