Getting variable from first table in Left Join

Question

So I have a basic question of how to get a variable from the a table of a outer join statement.

mysql_query("SELECT * 
               FROM (cal_events 
          left join cal_cities on cal_events.city_id = cal_cities.id) 
          left join cal_genre on cal_events.genre_id = cal_genre.id 
              WHERE start_day BETWEEN DATE_ADD(CURDATE(), INTERVAL -1 DAY) 
                                  AND DATE_ADD(CURDATE(), INTERVAL 5 DAY) 
           order by start_day");

while($info = mysql_fetch_array( $data )) {
  echo $info['id'];
}

This will echo out the genre's id, but I need the id from cal_events... using $info['cal_events.id'] just throws errors

HELP!

Answer 1

With pretty much all the interfaces to mysql any time you do a join and the tables have the same column names only the last occurence of that name is in the array. To get arround this alias your columns:

SELECT cal_events.id as cal_events_id, cal_cities.* FROM (cal_events left join cal_cities on cal_events.city_id = cal_cities.id) left join cal_genre on cal_events.genre_id = cal_genre.id WHERE start_day BETWEEN DATE_ADD(CURDATE(), INTERVAL -1 DAY) AND DATE_ADD(CURDATE(), INTERVAL 5 DAY) order by start_day

Obviously that wont get all the columns in call i just ommited them because i dont know what they are. You should express them all individually and alias any that need to be aliased.

Answer 2

   mysql_query("SELECT * , cal_events.id as cal_id
                       FROM (cal_events 
                  left join cal_cities on cal_events.city_id = cal_cities.id) 
                  left join cal_genre on cal_events.genre_id = cal_genre.id 
                      WHERE start_day BETWEEN DATE_ADD(CURDATE(), INTERVAL -1 DAY) 
                                          AND DATE_ADD(CURDATE(), INTERVAL 5 DAY) 
                   order by start_day");
    while($info = mysql_fetch_array( $data )) {
      echo $info['cal_id'];
    }