Combines two or more `SELECT * from` together?

PHP
1

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:

This is my try(Not work):


$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

What you need is a JOIN

3

And a field for joining them on. I dont see a natural join there.

4

How should echo output following query?

    $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:


    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

Try


while ($query_out = $query->row()) {
  echo $query_out->order.'<br>';
}
6 
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