SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Addict
    Join Date
    May 2005
    Location
    London, ON
    Posts
    360
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    How to send an array of values to an SQL statement?

    At the moment I have a database with a table called 'rankings'. In this table are unique IDs and a netValue which I use to rank people by.

    I show 20 people at a time, so my current query looks like this:
    PHP Code:
    $query "SELECT * 
              FROM rankings 
              WHERE comp_id='"
    .$comp_id."' 
              ORDER BY netWorth DESC"
    ;
    (
    $limit != "" $query .= " LIMIT ".$limitStart.", ".$limit ""); 
    What I have is an array of user ids. I'm wondering if there is something in SQL similar to the in_array() function. Essentially I want to do the query above but add something like:

    AND uid = in_array($myArrayOfUIDs)

    How should I go about this? I'm guessing there's a more efficient way of doing this then looping the array and adding a bunch of
    PHP Code:
    "OR uid = '".$myArrayOfUIDs[$x]."'"
    Thanks in advance.
    Ryan Price
    Subscribe to my blog for regular tips and tricks

  2. #2
    Worship the Krome kromey's Avatar
    Join Date
    Sep 2006
    Location
    Fairbanks, AK
    Posts
    1,621
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The quickest way I've found to do this is to use PHP's implode function to turn your array into a comma-delineated string, then use that in a MySQL IN clause, like so:
    Code php:
    $string = implode(",", $array);
    $query = "SELECT * FROM table1 WHERE field1 IN ($string)";
    Of course, this will only work for numerical values. If you have an array of strings, then use "','" as your implode glue, and don't forget to add the quotes in your query as well ("...IN ('$string')"). Oh, and as always, run your string through mysql_real_escape_string first (you must do this before you implode them, otherwise you'll escape the quotes you added and will end up with just one really long string).
    PHP questions? RTFM
    MySQL questions? RTFM

  3. #3
    SitePoint Wizard Sillysoft's Avatar
    Join Date
    May 2002
    Location
    United States :)
    Posts
    1,691
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Jeanco View Post
    At the moment I have a database with a table called 'rankings'. In this table are unique IDs and a netValue which I use to rank people by.

    I show 20 people at a time, so my current query looks like this:
    PHP Code:
    $query "SELECT * 
              FROM rankings 
              WHERE comp_id='"
    .$comp_id."' 
              ORDER BY netWorth DESC"
    ;
    (
    $limit != "" $query .= " LIMIT ".$limitStart.", ".$limit ""); 
    What I have is an array of user ids. I'm wondering if there is something in SQL similar to the in_array() function. Essentially I want to do the query above but add something like:

    AND uid = in_array($myArrayOfUIDs)

    How should I go about this? I'm guessing there's a more efficient way of doing this then looping the array and adding a bunch of
    PHP Code:
    "OR uid = '".$myArrayOfUIDs[$x]."'"
    Thanks in advance.
    PHP Code:
    $uidarray implode(',',$array);
     
    $query "SELECT * FROM rankings 
                   WHERE comp_id='"
    .$comp_id."' 
                   AND uid IN(
    $uidarray)
                   ORDER BY netWorth DESC"

    Silly

  4. #4
    SitePoint Wizard Sillysoft's Avatar
    Join Date
    May 2002
    Location
    United States :)
    Posts
    1,691
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Im too slow!

    Silly

  5. #5
    SitePoint Addict
    Join Date
    May 2005
    Location
    London, ON
    Posts
    360
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks guy, didn't think of that.
    Ryan Price
    Subscribe to my blog for regular tips and tricks

  6. #6
    SitePoint Addict
    Join Date
    May 2005
    Location
    London, ON
    Posts
    360
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I've got this set up now, but its only returning the first occurrence it finds instead of all of them.

    Any ideas?
    Ryan Price
    Subscribe to my blog for regular tips and tricks

  7. #7
    SitePoint Addict
    Join Date
    May 2005
    Location
    London, ON
    Posts
    360
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Nevermind, figured it out. Even though I'm using numbers, they are being treated as strings. I needed to change the implode glue.

    Thanks
    Ryan Price
    Subscribe to my blog for regular tips and tricks


Bookmarks

Posting Permissions

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