[Resolved] Array/Query Result Combination Help (I can't think clearly)

jasonburns

Hey all, I am sure this is easy but I just can't seem to wrap my head around it... and I apologize in advance if I don't post this question right, I am rather new.

I have a table listing Applications:

AppID | AppName

1 | AppName1
2 | AppName2
3 | AppName3
etc...

I have a table listing Devices:

DeviceID | Devices

1 | DeviceName1
2 | DeviceName2
3 | DeviceName3
etc...

And a table with some pairings based on the two tables above, AppDev:

AppID | DeviceID | Note

1(ID for AppName1) | 2(ID for DeviceName2) | Note
2(ID for AppName2) | 2(ID for DeviceName2) | Note

So, notice that I am listing DeviceName2 as being paired up with both AppName1 & 2

I know how to run a query against each table to get results, but my problem is about trying to combine the results into the format I need.

AppName1 - Checkbox checked (because it appeared in the AppDev table) - note
AppName2 - Checkbox checked (because it appeared in the AppDev table) - note
AppName3 - Checkbox empty

As you can see, I would like a master list of all the applications, but some of the checkboxes checked and display the note, based on if the item appeared in the AppDev table.

The best I have been able to come up with so far is:

//This query gets all the applications
	$query_AppList = "SELECT * FROM apps ORDER BY AppName";
	$AppList = mysql_query($query_AppList) or die(mysql_error());
	$row_AppList = mysql_fetch_assoc($AppList);

That will get me the entire list of applications

//This query gets all the applications this device is enabled on
$query_DevApp = "SELECT * FROM devapp WHERE DeviceID = 2";
	$DevApp = mysql_query($query_DevApp) or die(mysql_error());
	while ($row_DevApp = mysql_fetch_array($DevApp)) { $DevAppArray[ ] = $row_DevApp['AppID']; }

This will create an array of AppIDs from AppDev where DeviceID=2

Then I can use the inarray() method to compare the arrays, and check the box where appropriate, but cannot figure out how to get the note to appear because I don't really have the not field in the array anywhere.

<table>
<?php do { ?>
	<tr>
			<th><?php echo $row_AppList['AppName']; ?>:</th>
			<td><input type="checkbox" name="" <?php if (in_array($row_AppList['AppID'], $DevAppArray)) { echo 'checked'; } ?> value=""></td>
	</tr>
<?php } while ($row_AppList = mysql_fetch_assoc($AppList)); ?>
</table>

Maybe I am going about this all wrong. Is there a better way, like joining the arrays or something and making a new array?

Any help would be greatly appreciated, sorry for the long post.

eoghain

What you want to do is run a LEFT OUTER JOIN query that gets you all of the information from your Applications table and the information that exists in your other tables.

SELECT a.AppID, a.AppName, d.DeviceID, d.DeviceName, ad.Note
  FROM Applications a
  LEFT JOIN AppDev ad ON (a.AppID = ad.AppID)
  LEFT JOIN Devices d ON (ad.DeviceID = d.DeviceID)

This query should give you all of your AppID's and AppName's in your Applications table. In addition it will try to retrieve all of the Notes that exist in the AppDev table. Finally if you have a AppDev record it will attempt to get the DeviceID and DeviceName associated with that record.