SQLite Forum

Return boolean if item EXISTS in database
Login

Return boolean if item EXISTS in database

(1) By PongoZ on 2020-04-09 22:08:07 [link] [source]

$EntryExistsBoolean = $db->query("SELECT name FROM myTable WHERE EXISTS (SELECT name FROM myTable WHERE company=SmartCo");

if ($EntryExistsBoolean === false)
    {
        echo "Item does not exist in the database";
    }
    else
    {
        echo "Item found in the database";
    }

Problem: no boolean is ever returned! What is the correct syntax?

(2) By Gunter Hick (gunter_hick) on 2020-04-10 09:28:42 in reply to 1 [link] [source]

SELECT EXISTS (SELECT 1 FROM myTable WHERE Company='SmartCO');

Your query would return *all* of the names in myTable if at least one of them is named 'SmartCo'.

(3) By PongoZ on 2020-04-10 10:50:59 in reply to 2 [link] [source]

I've tried but it does not return a BOOL..

(4) By Donald Griggs (dfgriggs) on 2020-04-10 11:15:39 in reply to 3 [link] [source]

You may already know that SQLite does not have a separate boolean type, and that typing, in general, differs from strongly-typed databases.

https://www.sqlite.org/datatype3.html#boolean_datatype

(5) By Tim Streater (Clothears) on 2020-04-10 11:20:49 in reply to 3 [link] [source]

No, it'll return a result set. You then need to look in the result set and see what is there.

(6) By PongoZ on 2020-04-10 11:29:47 in reply to 4 [link] [source]

Yes, I know. Thank you. However even using 0/1 instead of FALSE/TRUE, it never returns that the record is missing...

Maybe the syntax that I use to make the call to the db is incorrect?

$EntryExistsBoolean = $db->query("SELECT EXISTS (SELECT 1 FROM myTable WHERE Company_ID = 'SmartCo')");

if ($EntryExistsBoolean === 0) //FALSE
{
    echo "Record does not exist"; //Never triggered!
}
else if ($EntryExistsBoolean === 1) //TRUE
{
    echo "Item found in the database";
}

(7) By PongoZ on 2020-04-10 12:24:22 in reply to 5 [source]

Can you please explain how. I've been stuck on this for over a day now!

(8) By Stephan Beal (stephan) on 2020-04-10 12:37:19 in reply to 7 [link] [source]

Can you please explain how. I've been stuck on this for over a day now!

i am absolutely certain that the PHP documentation for the PHP API you are asking about explains how this works in PHP. This forum is for the core sqlite C library and a couple of closely-related bits, not the hundred or more 3rd-party bindings like the multiple PHP variants - those are supported in their own documentation and forums.

(9.1) By Ryan Smith (cuz) on 2020-04-10 12:55:00 edited from 9.0 in reply to 7 [link] [source]

The reason why you are having short answers and not a lot of information from us is that this forum is an SQLite forum and your question is really a PHP question.

SQLite does not in any way hold any sway over how the types are interpreted or used in the upstream wrappers for PHP (or indeed any other language).

That said, to put you on the right track, in PHP you are setting $EntryExistsBoolean to a DB Query result (not any variable):

$EntryExistsBoolean = $db->query(...

To read anything from that query result (Boolean or otherwise)you need to dereference the data (rows) inside that query result. One way to do this might be:

    $qResult = $db->query("SELECT EXISTS (SELECT 1 FROM myTable WHERE Company_ID = 'SmartCo')");

    $EntryExistsBoolean = False;  // Initialize.

    if ($qResult) { // Ensure the returned result is not null
        // Get a single row as an array from the result
        $row = sqlite_fetch_row($qResult);
        // Check if a row is returned (else the query has no more rows)
        if ($row) {
            // Now translate DB 1/0 to PHP True/False
            $EntryExistsBoolean = ($row[0] <> 0);  
        }
    }

    // And now this all should work as usual
    if (!$EntryExistsBoolean) //FALSE
    {
        echo "Record does not exist"; //Never triggered!
    }
    else  //TRUE
    {
        echo "Item found in the database";
    }

Note that I am not familiar with the direct SQLite functions in PHP, nor sure if you use a wrapper or which wrapper you use, the above is simply example code. Your wrapper may well have a built-in way of translating SQL 1/0 to PHP True/False (or even making it all more easy) - the point here is to simply show the two things (SQLite and PHP) are Worlds apart and you cannot assume anything in one system translates directly to the other. (Though good wrappers do make it fit hand-in-glove).

Find the PHP documentation for your API or wrapper, and if it still is unclear, ask some PHP folks, you will have much more helpful responses there I think.

(10) By David Raymond (dvdraymond) on 2020-04-10 12:53:16 in reply to 7 [link] [source]

Not sure of the syntax for your specific language, but remember that this is a regular select statement. It's going to return 0 to many rows, each with 1 or more columns. YOU know that this "should" return 1 row with 1 column, but the library doesn't know that.

So treat it like a normal query to fetch data. Get the first row returned, and look at the first column of that row, and that's where your 0/1 value is going to be.

(11) By Tim Streater (Clothears) on 2020-04-10 13:07:43 in reply to 7 [link] [source]

Doesn't appear that you have tried the documentation. Here's an opener, look at the example:

https://www.php.net/manual/en/sqlite3.query.php

(12) By PongoZ on 2020-04-10 14:18:48 in reply to 9.1 [link] [source]

Many thanks for pointing me in the right direction as I am very new to both SQLITE and PHP.

I am indeed making the query via PHP and I am using Apache server on a Mac. I don't think I have installed any wrapper.

I've tried your code but still it never returns "item does not exist". If I dump $EntryExistsBoolean results the Javascript console in Safari shows the following error:

[Log] RESPONSE, – "
Warning: SQLite3::query(): Unable to prepare statement: 1, incomplete input i…" (mySite.html, line 473)

It mentions "incomplete input". Any idea what may be wrong?

Thanks again. In the future I will ask these questions on PHP fora.

(13) By Jim Morris (jimmorris33215) on 2020-04-10 15:53:55 in reply to 12 [link] [source]

You can use an aggregate to force a response that is easy to convert to a boolean

SELECT count(*) FROM myTable WHERE company=SmartCo

(14) By David Raymond (dvdraymond) on 2020-04-10 16:49:49 in reply to 13 [link] [source]

The difference though is that count(*) will keep running through the table/index now matter how many it finds. EXISTS will stop the moment it finds the first one. So if you don't care about the actual number and only if there are any, then EXISTS will always be quicker than a full count(*).

(15) By Keith Medcalf (kmedcalf) on 2020-04-10 18:49:06 in reply to 12 [link] [source]

Sounds like PHP is complaining that it does not think is has a complete input. Have you RTFM to see what that error message means (it is not an SQLite message, it is from your wrapper).

(16) By anonymous on 2020-04-11 12:20:26 in reply to 12 [link] [source]

Which query are you running here?

$EntryExistsBoolean = $db->query("SELECT name FROM myTable WHERE EXISTS (SELECT name FROM myTable WHERE company=SmartCo");

In this version of the query, you are missing the closing parenthesis for your EXISTS clause as well as the string quotes around SmartCo, which would result in SQLite returning that "incomplete input" error message.

Also, query returns a result object that you iterate through to fetch each row. https://www.php.net/manual/en/sqlite3.query.php However, in this case, you probably want to use querySingle instead. https://www.php.net/manual/en/sqlite3.querysingle.php

Lastly, as others have mentioned, your original query will not work the way you probably intend. So bringing it all together:

$EntryExistsBoolean = $db->querySingle("SELECT EXISTS (SELECT 1 FROM myTable WHERE company='SmartCo')");
...

(17) By PongoZ on 2020-04-14 10:30:47 in reply to 16 [link] [source]

Thanks but still it does not work...

$EntryExistsBoolean = $db->querySingle("SELECT EXISTS (SELECT 1 FROM myTable WHERE company='SmartCo'))");

if ($EntryExistsBoolean === FALSE)
    {
        echo "Record does not exist";
    }
    else
    {
        echo "Record exists";
    }

(18) By Clemens Ladisch (cladisch) on 2020-04-14 19:23:08 in reply to 17 [link] [source]

SQLite does not have a boolean type, and returns 0 or 1.

Compare against 0 or 1, or use if ($EntryExistsBoolean) {...}.

(19) By PongoZ on 2020-04-16 21:09:23 in reply to 18 [link] [source]

Already tried. No difference

(20) By Larry Brasfield (LarryBrasfield) on 2020-04-16 23:04:36 in reply to 19 [link] [source]

That querySingle(...) method is returning a 'mixed' type. $EntryExistsBoolean then has a type that depends dynamically (meaning at runtime) on what came back from the call. So what type is it? And what is its value? From reviewing this thread, it appears that your problem boils down to converting $EntryExistsBoolean from whatever it is to an honest boolean. Let's see what has to be converted.

And a kindly intended word of advice: A response such as

Already tried. No difference

does not help anybody's thinking to progress on this. What happened? (In detail, rather than "It did not work.") When you make it look like you are unwilling to help unpaid volunteers to help you, they are likely to find better uses for their charitable efforts.

Use whatever PHP's type revealing mechanism is to see what you've got. Use a print to a log file if that's what it takes. Without more data, more conjecture as to what is happening is futile.