Re: [sqlite] Checking that a row exists?
On Thu, 21 Feb 2008 11:40:45 -0700, Dennis Cote <[EMAIL PROTECTED]> wrote: >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. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Checking that a row exists?
Fin Springs wrote: > > 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? 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 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Checking that a row exists?
> 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? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Checking that a row exists?
Florian Weimer wrote: > > This query stops after the first match: > > 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 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Checking that a row exists?
* 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 > $query = sprintf($query,$row['calls_phones_tel']); Danger: SQL injections lurks here. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Checking that a row exists?
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.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
