#1
  1. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2013
    Posts
    311
    Rep Power
    8

    Using an array as WHERE clause in query


    hi,

    I'm trying to use an array of user_id's as a WHERE clause in a query, and order it after that to get a kind of legend where I can search for a user_id (index) and getting the username as value. It all works if I remove the WHERE clause (so it retrieves all the usernames and user_id's. But I want to work efficiently and retrieve only the usernames I will use. This is a problem, I already tried to use the implode() statement, but after all the query gives me back the username matching the first user_id.

    This comes down to this, $unique_user_ids is an array with unique user_id's, I got an other function for that:
    PHP Code:
    $imploded_user_ids implode(','$unique_user_ids);
    $prepared_usernames_stmt $db->prepare('                      
        SELECT
                        user_id,
                        username
        FROM
            users
        WHERE user_id IN (:user_ids)
    '
    );
            
    $prepared_usernames_stmt->execute(array(
        
    ':user_ids' => $imploded_user_ids
    ));

    $prepared_usernames $prepared_usernames_stmt->fetchAll(); 
    Please help
  2. #2
  3. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,921
    Rep Power
    1045
    Hi,

    you cannot pass a list to a prepared statement. Prepared statements expect values. A list is no value, it's a piece of syntax.

    You basically have two options: You can fall back to unsecure dynamic queries and write the list directly into the query string. This of course means that you have to escape and quote the list elements and double-check for any errors.

    Or you can create a list of parameters (using the "?" syntax) and then pass the list elements to the parameters as usual.

    PHP Code:
    <?php

    $prepared_usernames_stmt 
    $db->prepare('
        SELECT
            user_id,
            username
        FROM
            users
        WHERE
            user_id IN (' 
    implode(','array_fill(0count($unique_user_ids), '?')) . ')
    '
    );

    $prepared_usernames_stmt->execute($unique_user_ids);
    The 6 worst sins of security How to (properly) access a MySQL database with PHP

    Why cant I use certain words like "drop" as part of my Security Question answers?
    There are certain words used by hackers to try to gain access to systems and manipulate data; therefore, the following words are restricted: "select," "delete," "update," "insert," "drop" and "null".
  4. #3
  5. No Profile Picture
    Lost in code
    Devshed Supreme Being (6500+ posts)

    Join Date
    Dec 2004
    Posts
    8,301
    Rep Power
    7170
    Here is an example of the other method. One advantage of this method is that it doesn't preclude the use of other named parameters in the query. That isn't relevant in the query you gave, but it could be in others.
    PHP Code:
    <?php

        $prepared_usernames_stmt 
    $db->prepare('
            SELECT
                user_id,
                username
            FROM
                users
            WHERE
                user_id IN (' 
    implode(','array_map('intval'$unique_user_ids)) . ')
        '
    );
        
    $prepared_usernames_stmt->execute();
    PHP FAQ

    Originally Posted by Spad
    Ah USB, the only rectangular connector where you have to make 3 attempts before you get it the right way around
  6. #4
  7. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2013
    Posts
    311
    Rep Power
    8
    So the query Oreo gave is better because it's "allround", it still uses prepared statements, right?
  8. #5
  9. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,921
    Rep Power
    1045
    No, it doesn't. It's a dynamic query constructed from user input -- the thing we used when we didn't know about prepared statements. And this means you again have to worry about escaping. Don't let the "prepare()" confuse you. As you can see, the values are not passed as parameters. They're injected directly into the string.

    If you're ready to take this risk, go ahead. But I'd rather be safe and stick to prepared statements.
    The 6 worst sins of security How to (properly) access a MySQL database with PHP

    Why cant I use certain words like "drop" as part of my Security Question answers?
    There are certain words used by hackers to try to gain access to systems and manipulate data; therefore, the following words are restricted: "select," "delete," "update," "insert," "drop" and "null".
  10. #6
  11. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2013
    Posts
    311
    Rep Power
    8
    Originally Posted by Jacques1
    If you're ready to take this risk, go ahead. But I'd rather be safe and stick to prepared statements.
    I agree, but it's also nice to see the other option, even if I won't use it. Thanks again
  12. #7
  13. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2013
    Posts
    311
    Rep Power
    8
    ok, I will use this in the context of retrieving the username(s) of the users that have sent those messages, if there is 1 message (or 0), should I then use an other query?
  14. #8
  15. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2013
    Posts
    311
    Rep Power
    8
    So if I have 1 or 0 user_id's, what should I do then? And how do I control how many unique id's there are?
  16. #9
  17. No Profile Picture
    Dazed&Confused
    Devshed Novice (500 - 999 posts)

    Join Date
    Jun 2002
    Location
    Tempe, AZ
    Posts
    501
    Rep Power
    127
    Originally Posted by derplumo
    So if I have 1 or 0 user_id's, what should I do then? And how do I control how many unique id's there are?
    If there are 0 IDs then there's no reason to even run the query. Encase it in a conditional dictating that the count must be > 0. That way you're not running a query you know won't have results, and you sidestep how to handle it in the query.

    The same concept that applies to many IDs should apply to 1 as well. It'll just be "IN (5434)" or whatever. That's functional.
  18. #10
  19. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2013
    Posts
    311
    Rep Power
    8
    EDIT: never mind, already found the fault...
    Last edited by derplumo; July 12th, 2013 at 01:40 PM.
  20. #11
  21. No Profile Picture
    Dazed&Confused
    Devshed Novice (500 - 999 posts)

    Join Date
    Jun 2002
    Location
    Tempe, AZ
    Posts
    501
    Rep Power
    127
    EDIT: Then never mind my response.
  22. #12
  23. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2013
    Posts
    311
    Rep Power
    8
    Yes, I made a function to put every unique value in an array and implode it, but I don't have to implode it now, resolved

IMN logo majestic logo threadwatch logo seochat tools logo