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

Suppose I have this table:

假设我有这张桌子:

id | name | city
------------------
1  | n1   | c1
2  | n2   | c2
3  | n3   | c3
4  | n4   | c4

I want to check if the value c7 exists under the variable city or not.

我想检查变量city下的值c7是否存在。

If it does, I will do something.
If it doesn't, I will do something else.

如果是的话，我会做点什么。如果没有，我就做点别的。

5 个解决方案

#1

preferred way, using MySQLi extension:

使用MySQLi扩展名:

$mysqli = new mysqli(SERVER, DBUSER, DBPASS, DATABASE);
$result = $mysqli->query("SELECT id FROM mytable WHERE city = 'c7'");
if($result->num_rows == 0) {
     // row not found, do stuff...
} else {
    // do other stuff...
}
$mysqli->close();

deprecated:

弃用:

$result = mysql_query("SELECT id FROM mytable WHERE city = 'c7'");
if(mysql_num_rows($result) == 0) {
     // row not found, do stuff...
} else {
    // do other stuff...
}

#2

For Exact Match

"SELECT * FROM yourTable WHERE city = 'c7'"

For Pattern / Wildcard Search

"SELECT * FROM yourTable WHERE city LIKE '%c7%'"

Of course you can change '%c7%' to '%c7' or 'c7%' depending on how you want to search it. For exact match, use first query example.

当然，你可以根据你的搜索方式，将“%c7%”改为“%c7”或“c7%”。对于精确匹配，请使用第一个查询示例。

PHP

$result = mysql_query("SELECT * FROM yourTable WHERE city = 'c7'");
$matchFound = mysql_num_rows($result) > 0 ? 'yes' : 'no';
echo $matchFound;

You can also use if condition there.

你也可以用if条件。

#3

Assuming the connection is established and is available in global scope;

假设连接已经建立并在全局范围内可用;

//Check if a value exists in a table
function record_exists ($table, $column, $value) {
    global $connection;
    $query = "SELECT * FROM {$table} WHERE {$column} = {$value}";
    $result = mysql_query ( $query, $connection );
    if ( mysql_num_rows ( $result ) ) {
        return TRUE;
    } else {
        return FALSE;
    }
}

Usage: Assuming that the value to be checked is stored in the variable $username;

用法:假设要检查的值存储在变量$username中;

if (record_exists ( 'employee', 'username', $username )){
    echo "Username is not available. Try something else.";
} else {
    echo "Username is available";
}

#4

SELECT
    IF city='C7'
    THEN city
    ELSE 'somethingelse'
    END as `city`
FROM `table` WHERE `city` = 'c7'

#5

I tried to d this for a while and $sqlcommand = 'SELECT * FROM database WHERE search="'.$searchString.'";';
$sth = $db->prepare($sqlcommand); $sth->execute(); $record = $sth->fetch(); if ($sth->fetchColumn() > 0){} just works if there are TWO identical entries, but, if you replace if ($sth->fetchColumn() > 0){} with if ($result){} it works with only one matching record, hope this helps.

我尝试了一段时间，$sqlcommand = 'SELECT * FROM database WHERE search= ' '.$searchString ' ';$ sth = $ db - >准备($ sqlcommand);使用$ sth - > execute();$记录= $ sth - > fetch();如果有两个相同的条目，如果($sth->fetchColumn() >){}只工作，但是，如果你替换if ($sth->fetchColumn){}，如果它只使用一个匹配记录，希望这能有所帮助。