Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 3 of 3
  1. #1
    New to the CF scene
    Join Date
    Feb 2009
    Posts
    8
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Mysqli - using select case but other data is not bound

    I'm using Mysqli OO to search the database for 2 specific values using the MySQL statement CASE. If both values are found then it will return the value 'true', if only one is found the value is 'false', if neither are found then the result is neither. Anyway I've got this working but my problem is I need it to also display the values that are related in the column.

    PHP Code:
    $query "SELECT 'userName','email','code',
    CASE 
        WHEN (email=? AND code=?) THEN 'true'
        WHEN (email=?) THEN 'false'
    END AS answer
    FROM users WHERE email=?"
    ;
    $stmt $mysqli->prepare($query);
    $stmt->bind_param("ssss"$email,$code$email,$email);
    $email $mysqli->real_escape_string($email);
    $code $mysqli->real_escape_string($code);
    $stmt->execute();
    $stmt->bind_result($user,$email,$code,$answer);
    $stmt->fetch();
    print_r($stmt->fetch);
    $stmt->store_result();
    echo 
    "User: ".$user." Email: ".$email." Code: ".$code." Answer: ".$answer
    Okay so when I run the code it should display as:

    Code:
    User: ryan Email: local@localhost Code: 12 Answer: true
    But instead it displays it as:

    Code:
    User: userName Email: email Code: code Answer: true
    So I think the error is more to do with the SQL side of it but not sure, I'm stuck and would like some help. Thanks

    BTW I have posted it in the PHP area as well just for the simple reason I'm not entirely sure which part it is, so sorry if I'm not allowed to do this
    Last edited by kenno69; 06-16-2012 at 08:10 PM.

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,020
    Thanks
    75
    Thanked 4,323 Times in 4,289 Posts
    Has nothing to do with bind or mysqli.

    When you use apostrophes around anything in SQL (any SQL, not just MySQL), you are creating a STRING. A literal string. Exactly what is in the apostrophes.

    In other words, if you use
    Code:
        SELECT 'username';
    You *WILL* get a result of exactly username.

    You surely need to and meant to use backticks (the ` character that shares a keyboard key with the ~ tilde).

    Code:
    $query = "SELECT `userName`,`email`,`code`, ...
    But, really, there is no reason to even use them here. You would get the same (correct) results using
    Code:
    $query = "SELECT userName,email,code, ...
    You only need to use the backticks when you use a MySQL keyword as a field name or when your field name contains characters (such as spaces, minus signs, periods) that aren't normally valid in field (and table) names.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • Users who have thanked Old Pedant for this post:

    kenno69 (06-17-2012)

  • #3
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,020
    Thanks
    75
    Thanked 4,323 Times in 4,289 Posts
    You have another problem in the SQL, by the by.

    Code:
    CASE 
        WHEN (email=? AND code=?) THEN 'true'
        WHEN (email=?) THEN 'false'
    END AS answer
    What happens if email is *NOT* equal to the given value?

    You have no WHEN to handle that, so answer will be NULL.

    Not sure your CASE really makes sense, as given.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.


  •  

    Tags for this Thread

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •