PHP/MYSQL - MYSQLI - Prepared Statements - How to get the value of this query?

Question

I have a query that checks a database for a match against the input of a username and password, the corresponding values of this query would be either 0 (no match) or 1 (a match). This would output in a column (itDoesExist) with one value, either the 1 or the 0.

What I would like to know, is how do I get that value in PHP? I guess I am missing something here and I cant quite seem to figure out what that is.

Here is the code:

$stmt = $link->prepare("
    SELECT CASE WHEN
    EXISTS (

    SELECT 1
    FROM user_details
    INNER JOIN user_info 
        ON user_details.id = user_info.id
    WHERE user_info.emailContact = ?
    AND user_details.password = ?
    )
    OR EXISTS (

    SELECT 1
    FROM user_details
    INNER JOIN user_business_info 
        ON user_details.id = user_business_info.id
    WHERE user_business_info.emailContact = ?
    AND user_details.password = ?
    )
    THEN 1
    ELSE 0
    END AS itDoesExist
");

if (!$stmt)
{
    $error = "{$link->errno}  :  {$link->error} (Error Searching For User)";
    include "C:/wamp/www/includes/html/main/error.html.php";
    exit();
}
if (!$stmt->bind_param("ssss", $username, $password, $username, $password))
{
    $error = "{$stmt->errno}  :  {$stmt->error}";
    include "C:/wamp/www/includes/html/main/error.html.php";
    exit();
}
if (!$stmt->execute())
{
    $error = "{$stmt->errno}  :  {$stmt->error} (Cant execute?)";
    include "C:/wamp/www/includes/html/main/error.html.php";
    exit();
}

Thanks in advance for any help or information in this regard!

@Ignas Kidding me? :( How would I make the statement return a Boolean value? — Craig van Tonder, Jul 06 '12 at 19:53
You can't if you're talking about outputting the query with the '?' replaced with values. — Ignas, Jul 06 '12 at 19:58
Are you trying to find the query that executed with the replacement values or are you trying to get the result of the `SELECT` statement? — pstrjds, Jul 06 '12 at 20:00
@Ignas What I mean, is how would I do something like `if (!$stmt->fetch()) { echo 'error'; }`? — Craig van Tonder, Jul 06 '12 at 20:01
@pstrjds I am trying to see if the query runs or not so i can log the user in. — Craig van Tonder, Jul 06 '12 at 20:02
@Ignas just get the result of the query, the value, e.g. "0" or "1". — Craig van Tonder, Jul 06 '12 at 20:02

pstrjds · Accepted Answer · 2012-07-06T20:07:15.427

1

If you are looking for the result of query you can use bind_result and fetch, see the example on the prepare statement

$stmt->bind_result($itDoesExist);
$stmt->fetch();

if ($itDoesExist == 1)
{
    /* Handle the exists case */
}

edited Jul 06 '12 at 20:07

answered Jul 06 '12 at 20:01

pstrjds

16,840
6
52
61

Perfect, thanks a lot for your answer and the links. As a side note, would you be able to comment on my use of the error pointers. – Craig van Tonder Jul 06 '12 at 20:09
@BlackberryFan - I don't do a lot of php programming and I am not sure what the accepted standard is for showing those, but that looks okay by me. I think you could improve your query a bit by just doing a select with two inner joins and returning a value if you find either. Also, you should probably not be storing plain text passwords in the database, that is a giant security hole. You should do some form of salted hash on them, probably multiple rounds of hashing, and then fetch the hashed password for a user id from the database and match it against the hashing of the supplied password – pstrjds Jul 06 '12 at 20:13

PHP/MYSQL - MYSQLI - Prepared Statements - How to get the value of this query?

1 Answers1