SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Evangelist
    Join Date
    Oct 2005
    Posts
    405
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Issue with Loop foreach

    I'm trying to build an sql query using data that I have built in an array to filter the query on.

    The issue I have is how do i remove the last OR statement once it has looped through all the values?

    PHP Code:
        $super_cats_id '';
        
        foreach(
    $data as $k=>$v)
        {
        
            
    $super_cats_id .= $v['SuperCatId'];
            
    $super_cats_id .= ' OR ';    
        }
        
        
        
        
    $sql            "    Select * FROM Table WHERE id = ".$super_cats_id.""
    Thanks

  2. #2
    SitePoint Guru Ize's Avatar
    Join Date
    Nov 2005
    Location
    The Netherlands
    Posts
    808
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    PHP Code:
    if ($k < (count($data)-1)) {
        
    $super_cats_id .= ' OR ';


  3. #3
    Theoretical Physics Student bronze trophy Jake Arkinstall's Avatar
    Join Date
    May 2006
    Location
    Lancaster University, UK
    Posts
    7,062
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Or, a different approach:
    PHP Code:
    $IDs = array(-1);
    foreach(
    $data as $Key=>$Value){
        
    $IDs[] = (int)$Value['SuperCatId'];
    }
    $IDsQueryPart implode(', '$IDs);
    $sql "Select Column1, Column2 FROM Table WHERE id IN({$IDsQueryPart})"
    The initial -1 is there to stop the query having an empty IN(), which would throw an error. The bare minimum would be IN(-1), which should return nothing if no ID is -1. If that's a possibility, change -1 to an ID which can't be used.


    Another approach, based on your current code, could be (though I'd rather the above...):

    PHP Code:
    $super_cats_id '';
    foreach(
    $data as $k=>$v){
        
    $super_cats_id .= ' OR id = '  . (int)$v['SuperCatId'];
    }
    $sql "Select * FROM Table WHERE 1 = 0 {$super_cats_id}"
    That will mean the query could be:
    Code:
    Select * FROM Table WHERE 1 = 0
    (no results)
    Code:
    Select * FROM Table WHERE 1 = 0 OR id=1
    (results)
    Last edited by Jake Arkinstall; Jul 31, 2009 at 07:26.
    Jake Arkinstall
    "Sometimes you don't need to reinvent the wheel;
    Sometimes its enough to make that wheel more rounded"-Molona

  4. #4
    SitePoint Evangelist
    Join Date
    Jun 2006
    Location
    Wigan, Lancashire. UK
    Posts
    523
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    $super_cats_id = implode(', ',$data);
    $sql = "Select * FROM Table WHERE id in ($super_cats_id)";
    ---
    Development Projects:
    PHPExcel
    PHPPowerPoint

  5. #5
    Theoretical Physics Student bronze trophy Jake Arkinstall's Avatar
    Join Date
    May 2006
    Location
    Lancaster University, UK
    Posts
    7,062
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    The data isn't a straight array of numbers, so the above wouldn't work.

    Also note that:
    Code:
    SELECT something FROM atable WHERE id = 1 OR 2
    Will fetch all the results - the equal sign is only applied to the 1, the 2 is a different clause. As 2 equates to 'true', what that is is effectively:
    Code:
    SELECT something FROM atable WHERE id = 1 OR true
    which will always equate to true.

    You need to either use the IN() function (recommended) or put ' id = ' infront of all of them.
    Jake Arkinstall
    "Sometimes you don't need to reinvent the wheel;
    Sometimes its enough to make that wheel more rounded"-Molona

  6. #6
    SitePoint Member
    Join Date
    Nov 2008
    Posts
    23
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Everyones solutions are good. But I have my own way of doing it.

    Here is the answer to your original question. But that will
    run you into problems as everyone suggests:
    PHP Code:
    $super_cats_id '';
    foreach(
    $data as $k=>$v)

        {
            
    $super_cats_id .= ($super_cats_id)? ' OR '$v['SuperCatId']:$v['SuperCatId'];
        }

        
    $sql            "    Select * FROM Table WHERE id = ".$super_cats_id.""
    Now here is my solution
    PHP Code:
    $super_cats_id '';
    foreach(
    $data as $k=>$v)
        {
            
    $super_cats_id .= ($super_cats_id && $v['SuperCatId'])? ' , '$v['SuperCatId']:$v['SuperCatId'];
        }    
      
    $sql            "    Select * FROM Table WHERE id IN($super_cats_id)"
    Well just another solution. Good luck!


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
  •