Checking if data exists in database

Question

What is the right way of checking if the required data is in the database?

What I use currently is,

mysql_query("SELECT anyfield FROM table WHERE field='$data'");

and then check the if any rows are affected.

But I dont really have any use with the extracted data anyfield. Eventhough the resource usage is so minor here, what is the right way to check if data exists in a db without extracting any other fields from the table?

that suggestion was for mysql_num_rows right? I updated the question a bit, so now you know what am looking for :) — Kishor, Apr 12 '12 at 08:06
One little detail about using of `count()` it's better to use `count(id)` - it is take less time. Or just `select id from table where filed = 'data'`. — rdo, Apr 12 '12 at 08:09
No, what @Zohaib is talking about the standart SQL `count()` function which is the way to go. If you don't need the data, don't bring it back from the database, mysql_num_rows require the rows back. And to do that just use `count()` function in your SQL. — Erkan Haspulat, Apr 12 '12 at 08:10
my aim is make my codes better, like I dont want to extract datas when it isnt needed. So was wondering if existence of `$data` in `field` can be checked without selecting any data from other fields. — Kishor, Apr 12 '12 at 08:12

score 5 · Accepted Answer · answered Apr 12 '12 at 08:12

Let the database count and retrieve the count data from the query.

$result = mysql_query('SELECT COUNT(*) FROM `table` WHERE `field` = ...');
if (!$result) {
    die(mysql_error());
}
if (mysql_result($result, 0, 0) > 0) {
    // some data matched
} else {
    // no data matched
}

472084 · Answer 2 · 2012-04-12T08:15:33.120

2

$result = mysql_query("SELECT `field` FROM `table` WHERE `field` = '".$data."'");
if (mysql_num_rows($result)){
    // Rows exist
}

OR

$result = mysql_query("SELECT COUNT(`field`) as count FROM `table` WHERE `field` = '".$data."'");
$row = mysql_fetch_array($result);
if ($row ['count']){
    // Rows exist
}

edited Apr 12 '12 at 08:15

answered Apr 12 '12 at 08:08

472084

17,666
10
63
81

lol. You misread the question. I know how to do this, but I dont have any use with `anyfield`. I just have to check if `$data` exists in `field`. I was asking if this could be done without grabbing `anyfiled` from the database. – Kishor Apr 12 '12 at 08:10

score 0 · Answer 3 · answered Apr 12 '12 at 08:18

I would ensure, if I was checking for data, that as little data was returned as possible.

Fetch one field (I usually do id) and ensure you use LIMIT, eg. LIMIT 1... assuming you only want to know if any record exists.

Because you're using a PHP variable to find a record, if it suits your situation, you could perhaps do a LEFT JOIN on the first SQL line that gets $data, but that might just as easily be overkill for what you need.

Checking if data exists in database

3 Answers3

Linked