php 如何使用PHP检查MySQL中是否存在一行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6344779/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to check if a row exists in MySQL using PHP
提问by kitenski
I am trying to read in an XML file and compare it to fields in an existing database.
我正在尝试读取 XML 文件并将其与现有数据库中的字段进行比较。
If the ID in the database doesn't exist in the XML file, then the whole row corresponding to the Id is no longer valid and will be deleted.
如果数据库中的 ID 在 XML 文件中不存在，则该 ID 对应的整行不再有效，将被删除。
To do this I read in each line of the XML from start to finish in a while statement.
为此，我在 while 语句中从头到尾读取了 XML 的每一行。
As step one I am trying to do a simple compare, and echo if it finds an Id in the database that doesn't exist in the XML.
作为第一步，我试图做一个简单的比较，如果它在数据库中找到 XML 中不存在的 Id，则回显。
I know there are some Ids in the database that don't exist in the XML, but the following code is not displaying them.
我知道数据库中有一些 Id 在 XML 中不存在，但下面的代码没有显示它们。
I've got three questions, firstly how would I display the Id that is pulled from the database, and secondly why isn't this code finding any ids that are not in the XML?
我有三个问题，首先我将如何显示从数据库中提取的 Id，其次为什么这段代码没有找到任何不在 XML 中的 id？
The final question is am I going about this completely the wrong way and is there a better way to do it!
最后一个问题是我是否以完全错误的方式处理这个问题，是否有更好的方法来做到这一点！
$sql_result = mysql_query("SELECT id FROM `list` WHERE id = $id") or die(mysql_error());
if($sql_result)
{
// echo $id . " Id exists " . $sql_result["id"] . "\n";
}
else
{
echo "Id no longer exists" . $id . "\n";
}
回答by Michael Berkowski
Your code isn't finding what you expect because even though the id may not be found, $sql_resultstill holds a TRUEvalue because the query was successful. Instead, check if myqsl_num_rows() > 0
您的代码没有找到您期望的内容，因为即使可能找不到 id，$sql_result但TRUE由于查询成功，它仍然保留一个值。相反，检查是否myqsl_num_rows() > 0
if($mysql_num_rows($sql_result) > 0)
{
// echo $id . " Id exists "\n";
//Now, to print the id, you need to fetch it from `$sql_result`,
//which is just a resource at this point:
$row = mysql_fetch_assoc($sql_result);
echo $row['id'];
}
回答by Shef
This is the proper way to check:
这是检查的正确方法：
$sql_result = mysql_query("SELECT `id` FROM `list` WHERE `id` = ".intval($id,10)." LIMIT 0,1");
if(is_resource($sql_result) && mysql_num_rows($sql_result) > 0 ){
$sql_result = mysql_fetch_assoc($sql_result);
echo $id . " Id exists " . $sql_result["id"] . "\n";
}
else{
echo "Id no longer exists" . $id . "\n";
}
回答by Gerep
You can use NOT IN() on your select with the IDs that exist on you XML like:
您可以在您的选择中使用 NOT IN() 和 XML 上存在的 ID，例如：
SELECT id FROM `list` WHERE id NOT IN($your_id_list)
With this you'll have a list of IDs that are not in the list.
有了这个，您将拥有一个不在列表中的 ID 列表。
Your IDs must be separated with a comma like:
您的 ID 必须用逗号分隔，例如：
SELECT id FROM `list` WHERE id NOT IN(123,654,987,45)
回答by John Cartwright
You should check the number of rows returned using mysql_num_rows(). Otherwise, you are simply checking to see if the query executed without any error.
您应该检查使用 mysql_num_rows() 返回的行数。否则，您只是检查查询执行是否没有任何错误。
if($sql_result)
to
到
if(mysql_num_rows($sql_result))
回答by Abhay
Question 1: how would I display the Id that is pulled from the database?
问题 1：如何显示从数据库中提取的 ID？
$sql_result = mysql_query("SELECT `id` FROM `list` WHERE `id` = $id") or die(mysql_error());
$sql_row = mysql_fetch_assoc($sql_result);
if(!empty($sql_row['id'])) {
echo "Id exists - " . $sql_row['id'] . "\n";
} else {
echo "Id no longer exists - " . $sql_row['id'] . "\n";
}
Question 2: why isn't this code finding any ids that are not in the XML?
问题 2：为什么这段代码没有找到任何不在 XML 中的 id？
I think in your code the if() condition will always return true irrespective if the Id exists in the database or not. And secondly as you might have guessed from my code above, you are missing to fetch the data from the SQL resultset
我认为在您的代码中，无论 Id 是否存在于数据库中， if() 条件将始终返回 true。其次，您可能已经从我上面的代码中猜到了，您没有从 SQL 结果集中获取数据
Question 3: am I going about this completely the wrong way and is there a better way to do it?
问题 3：我是否以完全错误的方式解决这个问题，是否有更好的方法？
You are doing it the right way by browsing through the XML and checking each entry in the database for existence. A better way might be to first retrieve all IDs from the XML and then use them in the single SQL query:
通过浏览 XML 并检查数据库中的每个条目是否存在，您这样做是正确的。更好的方法可能是首先从 XML 中检索所有 ID，然后在单个 SQL 查询中使用它们：
SELECT `id` FROM `list` WHERE `id` NOT IN ($list);
Please note that this query might run slow if there are a very large number of IDs in the XML file, say a few hundreds.
请注意，如果 XML 文件中的 ID 数量非常多，比如几百个，则此查询可能运行缓慢。
回答by dynamic
mysql_num_rows()
Or
或者
SELECT COUNT(*) [...]
