SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Member
    Join Date
    Sep 2011
    Posts
    24
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Combines two or more `SELECT * from` together?

    How can combines(merger) two or more "SELECT * from ..." together by PHP and get from it output?(i use CodeIgniter)

    For example my database is as:

    table.png

    This is my try(Not work):

    Code:
    $find1 = 'Boston'; // This is a input post $_POST[]
    $query = $this->db->query('SELECT name FROM array('Store_Information', 'Geography') WHERE name LIKE "%' . $find1 . '%"');
    
        $query_out = $query->row();
        echo $query_out->order;// This output should is this: "New Mexico"
    How is it?

  2. #2
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,495
    Mentioned
    161 Post(s)
    Tagged
    4 Thread(s)
    What you need is a JOIN

  3. #3
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,748
    Mentioned
    69 Post(s)
    Tagged
    0 Thread(s)
    And a field for joining them on. I dont see a natural join there.
    Never grow up. The instant you do, you lose all ability to imagine great things, for fear of reality crashing in.

  4. #4
    SitePoint Member
    Join Date
    Sep 2011
    Posts
    24
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    How should echo output following query?
    PHP Code:
        $query $this->db->query('SELECT geo.order FROM Store_Information si LEFT JOIN Geography AS geo ON geo.id = si.id WHERE si.name LIKE "%' $find1 '%"'); 
    My try not work:
    PHP Code:
        if($query->num_rows() > 0){
                foreach(
    $query as $val){
                    
    $query_out $query->row();
                    echo 
    $query_out->order.'<br>';
                    
    //echo $val.'<br>';
                
    }
                }else{
                    echo 
    '0';
                } 

  5. #5
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,495
    Mentioned
    161 Post(s)
    Tagged
    4 Thread(s)
    Try
    PHP Code:
    while ($query_out $query->row()) {
      echo 
    $query_out->order.'<br>';


  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,216
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Code:
    SELECT geo.order 
      FROM Store_Information si 
    LEFT 
      JOIN Geography AS geo 
        ON geo.id = si.id 
     WHERE si.name LIKE "%' . $find1 . '%"'
    if you join on the id columns, you are making an error

    that would match Los Angeles with "East, Louisiana Territory"

    it would also match Los Angeles with "West, Florida"

    so clearly, that approach won't work

    you need a foreign key
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •