I have two database tables that are joined at a common field: One is a TV Station, and one is a tv show's air time. Both tables are joined at the call letters of the station when they match. Some stations just have one airtime, some have multiple airtimes. I want to create output so that each station produces multiple airtimes, and so there are multiple rows that create an array like airtime1, airtime2, airtime3 depending on the amount of airtimes the station has for the tv show. This is what I have so far. ----------------------------------------- $query = "SELECT * FROM stations JOIN (airtimes) ON (airtimeCallLetters = stationCallLetters) "; ---------------------------------------- What this produces is separate outputs for each airtime. How do I make it so each station has every show time associated with the station all together and not in separate instances? GROUP BY sort of works, but in the output only produces one of the airtimes, not all of them. In other words, is there a query (or subquery) that can group all of the airtimes with a particular station as an array, or is there PHP variable manipulation that will do this better?
You need to specify the columns on the join. Also, you need to select the airtimes table first, which will allow for selecting all of the airtimes for the station. Lastly, since you are dealing with multiple tables, SELECT * is a really bad idea. Definitely specify which columns you want to select. SELECT airtimes.time, airtimes.other_columns, stations.name_or_other_columns FROM airtimes LEFT JOIN stations ON airtimes.call_letter = stations.call_letter; After this, you would need to use application logic, to appropriately group the airtimes with the appropriate station.