如何在 PHP 中检查 MySQL 数据库中是否存在值？

How to check if value exists in MySQL Database in PHP?

提问by David G?lzh?user

I am new to PHP but I like to create a script that checks if an email is in my MySQL Database, the Database name is "Fily_Registrations", the table is "users" and the value is called "email". So basically if an the email "[email protected]"exists in my database and I call the php script like "http://path/to/[email protected]"it should echo out "YES"if it don't exists it should echo out "NO".

我是 PHP 新手，但我喜欢创建一个脚本来检查电子邮件是否在我的 MySQL 数据库中，数据库名称是"Fily_Registrations"，表是“用户”，值是"email"。所以基本上，如果"[email protected]"我的数据库中存在电子邮件并且我调用 php 脚本，就像"http://path/to/[email protected]"它应该回显一样，"YES"如果它不存在，它应该回显"NO"。

This is how fare I am now, but it always echoes out "NO":

这就是我现在的票价，但它总是回应“NO”：

<?php

$email = $_GET["email"];
$DataBase = "Fily_Registrations";
mysql_connect("server", "username", "password") or die(mysql_error());
mysql_select_db($DataBase) or die(mysql_error());
$string = sprintf("SELECT '$DataBase' FROM users WHERE email = '$email'");
$query = msql_query($string);
if($query == false) {
     echo("No");
} else {
    echo("Yes");
}
?>

Does anyone know how to fix this?

有谁知道如何解决这一问题？

回答by Pigmej

If database is Fily_Registrations then the query is wrong. Try this:

如果数据库是Fily_Registrations 那么查询是错误的。尝试这个：

$email = mysql_real_escape($_GET["email"]);
$DataBase = "Fily_Registrations";
mysql_connect("server", "username", "password") or die(mysql_error());
mysql_select_db($DataBase) or die(mysql_error());
$query = "SELECT * FROM users WHERE email = '{$email}'";
$result = mysql_query($query);
echo (mysql_num_rows($result) == 0) ? 'NO' : 'YES';

mysql_query returns false if query is not correct. Read first http://pl1.php.net/mysql_queryand consider using PDO (http://pl1.php.net/pdo) instead of normal mysql_query.

如果查询不正确，mysql_query 返回 false。首先阅读http://pl1.php.net/mysql_query并考虑使用 PDO ( http://pl1.php.net/pdo) 而不是普通的 mysql_query。

回答by Miguel Q.

Your code should be something like this:

你的代码应该是这样的：

<?php

$email = $_GET["email"];
$DataBase = "Fily_Registrations";
mysql_connect("server", "username", "password") or die(mysql_error());
mysql_select_db($DataBase) or die(mysql_error());
$string = mysql_real_escape("SELECT * FROM users WHERE email = '$email'");
$query = msql_query($string);
if($query) {
     echo mysql_num_rows($query) > 0 ? "YES" : "NO";
} else {
    echo("====some error===");
}
?>

mysql_num_rows : http://pt1.php.net/manual/en/function.mysql-num-rows.phpmysql_real_escape: http://pt2.php.net/mysql_real_escape_string

mysql_num_rows : http://pt1.php.net/manual/en/function.mysql-num-rows.phpmysql_real_escape: http://pt2.php.net/mysql_real_escape_string

回答by Kari Knuuttila

Not yet super safe, but better.

还不是超级安全，但更好。

• Email checked for unsafe quotes with mysql_real_escape. Don't place too much trust on this thought. Some additional regex checking could be in order.

• Prepared statements don't allow hidden mysql code execution from within parameters.

  $email = mysql_real_escape($_GET["email"]);
  try {
   $connect = new PDO("mysql:host=server;dbname=Fily_Registrations;port=3306", "user", "password");
  }catch(PDOException $e) {
    print "Error!: " . $e->getMessage();
    die();
  }
  
  $q= "SELECT * FROM users WHERE email = :EMAIL";
  $statement = $connect->prepare($q);
  $status = $statement->execute(array(":EMAIL"=>$email));
  
  if (($status) && ($statement->rowCount() > 0))
  {
    echo "YES";
  } else {
    echo "NO";
  }
  

• 使用 mysql_real_escape 检查电子邮件是否存在不安全引号。不要太相信这个想法。一些额外的正则表达式检查可能是有序的。

• 准备好的语句不允许从参数中执行隐藏的 mysql 代码。

  $email = mysql_real_escape($_GET["email"]);
  try {
   $connect = new PDO("mysql:host=server;dbname=Fily_Registrations;port=3306", "user", "password");
  }catch(PDOException $e) {
    print "Error!: " . $e->getMessage();
    die();
  }
  
  $q= "SELECT * FROM users WHERE email = :EMAIL";
  $statement = $connect->prepare($q);
  $status = $statement->execute(array(":EMAIL"=>$email));
  
  if (($status) && ($statement->rowCount() > 0))
  {
    echo "YES";
  } else {
    echo "NO";
  }
  

Br, Kari

布，卡里