Checking that a row exists?

[Gilles Ganault]

Hello

Is this the standard way to check that a row exists in PHP/SQLite, or
is there a better syntax?

========
$query = "SELECT count(*) FROM contacts WHERE contacts_phones_tel GLOB
'%s*';
$query = sprintf($query,$row['calls_phones_tel']);
$query = $dbh->query($sql)->fetch();
if(!$query[0]) {
//Not found in table
========

Thank you.

[Florian Weimer]

Post by Gilles Ganault
Is this the standard way to check that a row exists in PHP/SQLite, or
is there a better syntax?
$query = "SELECT count(*) FROM contacts WHERE contacts_phones_tel GLOB '%s*';

This query stops after the first match:

SELECT 1 FROM contacts WHERE contacts_phones_tel GLOB ? LIMIT 1

Post by Gilles Ganault
$query = sprintf($query,$row['calls_phones_tel']);

Danger: SQL injections lurks here.

[Dennis Cote]

Post by Florian Weimer
SELECT 1 FROM contacts WHERE contacts_phones_tel GLOB ? LIMIT 1

Yes, but it returns a 1 if it exists and a null result if doesn't. It is
usually better to get a 1 or 0, i.e. true or false result for an
existence check.

This query returns a boolean result and stops as soon as it has
determined the result

select exists (select * from contacts where contacts_phone_tel glob ?);

HTH
Dennis Cote

[Fin Springs]

Post by Dennis Cote
This query returns a boolean result and stops as soon as it has
determined the result
select exists (select * from contacts where contacts_phone_tel glob ?);
HTH
Dennis Cote

I have been using:

SELECT NULL FROM sqlite_master WHERE type='table' AND lower(name)=?

to determine whether a table exists and looking at the number of rows
returned (I'm using sqlite3_get_table through an API). I get one row
back if the table exists and no rows when it doesn't. There wouldn't be
multiple rows to LIMIT in this case though.

Is that bad?

[Dennis Cote]

Post by Fin Springs
SELECT NULL FROM sqlite_master WHERE type='table' AND lower(name)=?
to determine whether a table exists and looking at the number of rows
returned (I'm using sqlite3_get_table through an API). I get one row
back if the table exists and no rows when it doesn't. There wouldn't be
multiple rows to LIMIT in this case though.
Is that bad?

It isn't good or bad, just different.

Your application has to perform the extra logic of counting the returned
rows to determine existence. If you use a subselect and exists, SQLite
will effectively do that for your application and return a boolean value
directly.

select exists (SELECT * FROM sqlite_master WHERE type='table' AND
lower(name)=?)

This will always return one row with one column with a value that is
either 0 or 1.

HTH

[Gilles Ganault]

On Thu, 21 Feb 2008 11:40:45 -0700, Dennis Cote

Post by Dennis Cote
select exists (SELECT * FROM sqlite_master WHERE type='table' AND
lower(name)=?)
This will always return one row with one column with a value that is
either 0 or 1.

Thanks guys for the help.