How to Check if value exists in a MySQL database

Question

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.

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

Reinder Wit · Accepted Answer · 2016-08-05T12:52:52.800

71

preferred way, using MySQLi extension:

$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...
}

edited Aug 05 '16 at 12:52

answered Jul 02 '12 at 11:16

Reinder Wit

6,336
1
25
33

3

Wouldn't be better to use SQL's EXISTS statement like http://stackoverflow.com/a/1676573/204634 ? – lorenzog Mar 15 '16 at 17:24
This is unfortunaly outdated, do you maybe have an mysqli example? – Jason Aug 04 '16 at 18:04
Speed-wise, would it be faster by adding `LIMIT 1`? – Ian Y. Jun 01 '18 at 02:56
1

@IanY. that depends on the query but usually yes. Have a look at this: https://stackoverflow.com/questions/455476/does-adding-limit-1-to-mysql-queries-make-them-faster-when-you-know-there-will – Reinder Wit Jun 01 '18 at 06:40

Blaster · Answer 2 · 2012-07-02T11:24:08.390

18

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.

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.

edited Jul 02 '12 at 11:24

answered Jul 02 '12 at 11:11

Blaster

9,286
1
27
25

2

Use id instead of * (star). And `PDO` or `MYSQLi` should be used. – Jo Smo Jul 05 '14 at 16:13

score 1 · Answer 3 · edited Apr 27 '17 at 02:58

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;

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

score 0 · Answer 4 · edited Jul 03 '12 at 04:41

0

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

edited Jul 03 '12 at 04:41

Praveen Kumar Purushothaman

160,666
24
190
242

answered Jul 02 '12 at 11:14

Muhammad Raheel

19,645
7
66
101

score 0 · Answer 5 · answered Aug 20 '14 at 05:34

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.

score 0 · Answer 6 · edited Apr 09 '19 at 10:43

0

For Matching the ID:

Select * from table_name where 1=1

For Matching the Pattern:

Select * from table_name column_name Like '%string%'

edited Apr 09 '19 at 10:43

Suraj Rao

28,850
10
94
99

answered Apr 09 '19 at 10:36

Hassam Satti

1
2

score 0 · Answer 7 · answered Jan 01 '21 at 15:43

This works for me :


$db = mysqli_connect('localhost', 'UserName', 'Password', 'DB_Name') or die('Not Connected');
mysqli_set_charset($db, 'utf8');

$sql = mysqli_query($db,"SELECT * FROM `mytable` WHERE city='c7'");
$sql = mysqli_fetch_assoc($sql);
$Checker = $sql['city'];

if  ($Checker != null) {
    
    echo 'Already exists';
    
} else {

    echo 'Not found';
}

score 0 · Answer 8 · edited Sep 13 '21 at 05:08

0

use

select count(city) from mytable where city = 'c7'

This will send only a single value from the query. If it is 0 it is not present else it is present. Since you will not be use the other column values.

edited Sep 13 '21 at 05:08

Suraj Rao

28,850
10
94
99

answered Sep 13 '21 at 05:07

Mahesh Chikkanna

1
1

score 0 · Answer 9 · answered Mar 21 '22 at 08:45

0

$result = mysqli_query($conn, "SELECT * FROM WHERE = ''"; $found = mysqli_num_rows($result) > 0 ? 'Yes' : 'no' ; echo $found;

answered Mar 21 '22 at 08:45

Gaurav Kumar

1

1

Your answer could be improved with additional supporting information. Please [edit] to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Mar 21 '22 at 08:55

How to Check if value exists in a MySQL database

9 Answers9

For Exact Match

For Pattern / Wildcard Search

PHP

Linked