Checkboxes, arrays, & 2 queries to mysql

[adrocker55]

I cannot for the life of me figure this out and I know what I've done so far isn't right. (obviously if its not working!!!)

Okay....

This is what I want to do...
I want to be able to view the systems that each printer prints to by using the $systems query below. I want these systems to be recognized as systems the specific printer prints to by having a checkbox checked. I also want the remaining systems that the printer doesn't print to, to also show up in the list of systems that the printer could print to, if I decide to check it, it will update the DB (after hitting submit) in that fashion. The problem I am having is that when I use the code below, it loops through both queries and echos accordingly, one result after the other, for each query, NOT when $system["SID"] == $allsys["SID"], which is what I want it to do...Basically, I need all the systems to be displayed w/checkbox, and ONLY check the appropriate selection IF the $system["SID"] == $allsys["SID"] (translated to system IDs that this printer already prints to is equal to which of all system IDs, and then check the box if this is true.)

HOW THE HECK DO I DO THIS?

This is what I have done so far...

Here are the queries:
<?
$systems=mysql_query("
SELECT SYSTEM, SYSTEMS.SID
FROM SYSTEMS, SYSRELATIONS, PRINTERS
WHERE SYSRELATIONS.SID=SYSTEMS.SID AND SYSRELATIONS.PID=PRINTERS.PID AND PRINTERS.PID=$ID");
if(!$systems){
echo("Can't retrieve data Mysql error message for systems query " . mysql_error());
exit();

$allsystems=mysql_query("SELECT SYSTEM, SID FROM SYSTEMS");
if(!$allsystems){
echo("Can't retrieve data Mysql error message for allsystems query " . mysql_error());
exit();
}
?>

Here is the incorrect code:

<?//displays the row values from the table
while($allsys=mysql_fetch_array($allsystems)){
$system=mysql_fetch_array($systems);?>
<input type="checkbox" class="checkbox" name="system[]" value="<? echo ($allsys["SID"]) ?>" <?php if($system["SID"] == $allsys["SID"]){echo " CHECKED";}?>><? echo ($allsys["SYSTEM"]) ?><br>
<?}?>

***what this code does is only check the box when the result from each query matches the exact row that is echoed, so if the printer printed to systems 1,2,5,6 when using this code only 1 and 2 would be checked, not 5 and 6 because the first 2 match up and the rest dont (1-1, 2-2, 3-5, 4-6, 5-NULL, 6-NULL etc...)

Please help...it would be much appreciated!

[tomhath]

As I understand it, you want to loop through every system and set a checkbox indicating whether or not the printer identifed by $ID will print to the system. All you need is a LEFT JOIN to get all the systems and whether there is a relation to that system for the printer (LEFT JOIN will leave the PID null if there is no match):

<?php
$query = "SELECT SYSTEMS.SYSTEM, SYSTEMS.SID, SYSRELATIONS.PID
FROM SYSTEMS
LEFT JOIN SYSRELATIONS
ON SYSRELATIONS.SID=SYSTEMS.SID
AND SYSRELATIONS.PID=$ID");

$result = mysql_query($query) or die("Query failed: " . mysql_error());

while ($system = mysql_fetch_array($result, MYSQL_ASSOC)) {
?>
<input type="checkbox" class="checkbox" name="system[]" value="<? echo ($system["SID"]) ?>"
<?
if($system["PID"] != null ){
echo " CHECKED ";
}
?>

<? echo $system["SYSTEM"] ?>
<br>
<?
}
?>

[adrocker55]

Thanks a bunch! That worked great...I learn something new every day!!! =)