Check whether value exist in database

Question

What's the best way to check whether the value is in the database?

Am I doing it correct?

$result = mysql_query("SELECT COUNT(*) FROM table WHERE name = 'John'"); $count = count($result);

If you mean 'does it work?'....it does... (if the returned value is >1 then rows with name='John' exist, and if it's 0 then they don't) — mathematical.coffee
– mathematical.coffee, Commented Jan 9, 2012 at 7:06
What @mathematical.coffee says is correct but using LIMIT might be faster. A SELECT COUNT(*) is a complete table or index scan while using SELECT name FROM table WHERE name = 'John' LIMIT 1 would stop when the first match is found. — Lieven Keersmaekers
– Lieven Keersmaekers, Commented Jan 9, 2012 at 7:12

Vijeenrosh P.W · Accepted Answer · 2012-01-09 07:23:10Z

you could use straight forward ,

mysql_num_rows() ;

eg :

$con = mysql_connect($host,$uname,$passwd) mysql_select_db($dbase,$con); $result = mysql_query($query,$con);// query : SELECT * FROM table WHERE name='jhon'; if( ! mysql_num_rows($result)) { echo " Sorry no such value "; }

Rajesh Pantula · Accepted Answer · 2012-01-09 07:10:17Z

Yes you are doing it right, if you are only concerned with checking if there are any records where name='john'

SELECT COUNT(*) FROM table WHERE name = 'John'

will return the no. of records where name field is 'John'. if there are no records then it will return 0, and if there are any records it will return the number of records.

But the above query will miss the entries where name is 'John Abraham' or 'V john', to include even these

you can modify your query like this.

SELECT COUNT(*) FROM table WHERE name like '%John%'

Pierre de LESPINAY · Accepted Answer · 2012-01-09 07:08:50Z

I'd say yes.

$result = mysql_query("SELECT COUNT(*) AS 'nb' FROM table WHERE name = 'John'"); $line = mysql_fetch_array($result, MYSQL_ASSOC); $count = $line['nb'];

Will give you the number of matching rows.

Manigandan Arjunan · Accepted Answer · 2012-01-09 07:49:17Z

$result = mysql_query("SELECT COUNT(*) as user FROM table WHERE name = 'John'"); $line = mysql_fetch_array($result, MYSQL_ASSOC); $count = $line['user']; if($count!=0) { echo "user exists"; } else { echo "There is no such user"; }

but using the code which is suggested above by @Vijeenrosh P.W is appreciated

Collectives™ on Stack Overflow

Check whether value exist in database

4 Answers 4

Comments

Comments

Comments

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

1 Comment

Related