www.webdeveloper.com
Results 1 to 6 of 6

Thread: Using a PHP Array to Select Data from MySQL Db

  1. #1
    Join Date
    Dec 2010
    Location
    Newton Aycliffe, Co Durham, England
    Posts
    72

    Using a PHP Array to Select Data from MySQL Db

    I'm going round in circles on this. I need another brain to help. (Not literally - yours might do!)

    I am trying to use the contents of an array to set the criteria of a select statement which is something I have never done before. The code I have been fiddling with follows. I have left some of the commented-out stuff because I want to eventually loop through the results and use them to display images referred to in the loop. (It's a set of icons that bookmarks products which is why I'm storing the uniqieID in a session array)

    PHP Code:
    $showiconsimplode(", "$_SESSION['icons']); 
    mysql_select_db($database_watch50co3$watch50co3);
    $query_rs_icons = ("SELECT Items_Table.ItemID, Items_Table.Image4 FROM Items_Table WHERE Items_Table.ItemID IN $showicons");
    /* $rs_icons = mysql_query($query_rs_icons, $watch50co3) or die(mysql_error());
    $row_rs_icons = mysql_fetch_assoc($rs_icons);
    $totalRows_rs_icons = mysql_num_rows($rs_icons); 
    */ 
    Then I want to echo out the icons on the page using a loop such as:
    PHP Code:
    <?php #do { ?>
    <?php 
    echo $query_rs_icons['Image4'];?><br/>
    <?php #} while ($row_rs_icons = mysql_fetch_array($rs_icons)); ?>
    To save complications I have commented out the loop to test that the query is working, but it isn't. I have tried variations on the basic theme, but nothing has worked so far. I know roughly what I want to achieve, but I just haven't quite got the experience in this case to nail the problem.

    A helping hand would be greatly appreciated. Thanks.

  2. #2
    Join Date
    Dec 2005
    Posts
    2,984
    You're close, here's what the syntax that you're looking to create:

    Code:
    select * from `tbl` where `somefield` in ('val1','val2','val3'...,'valn')
    here's what you syntax looks like now:

    Code:
    SELECT Items_Table.ItemID, Items_Table.Image4 FROM Items_Table WHERE Items_Table.ItemID IN val1, val2, val3 .... ,valn"
    Can you see the difference?
    I've switched careers...
    I'm NO LONGER a scientist,
    but now a web developer...
    awesome.

  3. #3
    Join Date
    Dec 2010
    Location
    Newton Aycliffe, Co Durham, England
    Posts
    72
    Missing parentheses? I assume that the wildcard to select all fields in not required? The table in question has 50 or so fields.

    I'll give it a try.

  4. #4
    Join Date
    Dec 2005
    Posts
    2,984
    Yes, missing parentheses. It's also worth noting that if you are comparing integers, you don't need the single quotes (') around the values.
    I've switched careers...
    I'm NO LONGER a scientist,
    but now a web developer...
    awesome.

  5. #5
    Join Date
    Dec 2010
    Location
    Newton Aycliffe, Co Durham, England
    Posts
    72
    Thankyou aj_nsc.

    Got it. Just needed a nudge in the right direction. The only thing that does raise a question in my mind would be how do you get apostrophes/quotes around the very first character and the very last character if you were searching for a text string? That can't be done, can it? The apostrophes need to be incorporated into the array?

    Anyway, that's theory. I can get my project moving again!

    Thanks again

  6. #6
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    19,392
    You could use array_map() to format/sanitize the values in the array, then implode() the result for use in your query.
    PHP Code:
    $values array_map(
        
    create_function(
            
    '$value',
            
    'return (is_numeric($value)) ? $value : "\'".mysql_real_escape_string($value)."\'";'
        
    ),
        
    $showicons
    );
    $sql "SELECT Items_Table.ItemID, Items_Table.Image4 FROM Items_Table WHERE Items_Table.ItemID IN (".
    implode(','$values).")"
    (Untested, use at your own risk)
    "Please give us a simple answer, so that we don't have to think, because if we think, we might find answers that don't fit the way we want the world to be."
    ~ Terry Pratchett in Nation

    eBookworm.us

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

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



Recent Articles