Am I a SQL Idiot?
CrookedNumber - July 19, 2007 - 16:58
I need to first find out if a certain value X exists in a certain column in a certain table. Ideally, I just want a binary value returned, so I can proceed with other stuff, depending on whether or not X exists. I've reduced it to one line of Drupal code:
$value_exists = db_result(db_query("SELECT IF (%d in (SELECT id FROM {some_table}), 1, 0)", $value));
It works! But it still seems pretty convoluted. Anyone know if there's a more elegant way to test if a value exists?
THANKS!
how about?
db_result(db_query("select count(id) from {some_table} where id=%d", $value))
should return 0 if not exist, some number >=1 if any available.
Thanks, panis
Apologies for my brain cramp. Though this did lead me to some interesting research on the COUNT keyword. It seems one could also perform the query like so:
db_result(db_query("select count(*) from {some_table} where id=%d", $value))Though this would (I think) provide NO performance improvement, as there is still a WHERE clause involved. (MySQL site: "COUNT(*) is optimized to return very quickly if the SELECT retrieves from one table, no other columns are retrieved, and there is no WHERE clause.") I guess the only advantage is that the syntax seems a tiny bit less redundant?
Thanks again.