Update form with checkboxes from many to many relation db

[JackRabbitSlims]

hey, been struggling with this for a few hours now and cant seem to get anywhere! ahhh (hehe)

my db: tbl_dept -< tbl_deptprinter >- tbl_printer

designed to resolve the problem of a many to many relation between departments and printers as one printer can be used by many departments and one department can have more than one printer.

my add department form:

[code]
<form action="processDept.php" method="post">
<fieldset id="info"><legend>Department</legend>
<p>
<label for="deptName" class="label">Name:</label>
<input type="text" name="deptName" id="deptName" size="30" />
</p>
<p>
<label for="description" class="label">Description:</label>
<textarea id="description" name="description" rows="10" cols="30"></textarea>
</p>
</fieldset>
<fieldset id="info"><legend>Accounts</legend>
<p>
<label for="deptBal" class="label">Current Balance: &pound;</label>
<input type="text" name="deptBal" id="deptBal" size="30" /><br />
</p>
<p>
<label for="minBal" class="label">Minimum Balance: &pound;</label>
<input type="text" name="minBal" id="minBal" size="30" /><br />
</p>
</fieldset>

<fieldset id="info"><legend>iPrinters</legend>
<p>
<label for="deptBal" class="label">Select Printer:</label>
</p>
<p>
<?php
include("connection.php");

$sql = "select * from tbl_printer";
$pResult=mysql_query($sql) or die(mysql_error());

if(mysql_num_rows($pResult) == 0)
{
    print "There are No Records";   
}
else
{
    while($tmp = mysql_fetch_assoc($pResult))
    {
    echo $tmp['printerName'];
        echo "<input type=\"checkbox\" name=\"NA[]\" value=\"".$tmp["printerId"]."\">";
        echo $tmp['printerId'];
        echo "<br />";
    }
}
?>
</p>
</fieldset>

<p>
  <input name="Submit" type="submit" class="button" value="Add Department" />
  <input name="reset" type="reset" class="button" value="Reset Form" />
</p>
</form>
[/code]

update form pages:

departmentList.php -> updateForm.php -> processUpdate.php

in the updateForm.php form i want to display the contents of the tbl_dept + tbl_deptprinter by deptId (by passing the Id in the URL)

I want to be able to display all the printers but have the printers already associated with the dept ticked in the checkboxes.

This sql statment will return the checkboxes that should be highlighted, but only the checkboxes that should be highlighted and not the list of printers in full(which would include the printers that havent been ticked when the user first created the department):


SELECT *
FROM tbl_deptprinter
LEFT JOIN (tbl_dept, tbl_printer)
ON (tbl_dept.deptId = tbl_deptprinter.deptRef
AND tbl_printer.printerId=tbl_deptprinter.printerRef)
WHERE tbl_dept.deptId='1'";


Would anybody be able to help me create the php to make the full list of checkboxes appear and have the checkboxes that should be highlighted, well highlighted?

alot to read, hope it makes sense!

[Psycho]

Try this:

[code]<?php
$sql = "SELECT p.*, (dp.printerID = $printerID) as checked
        FROM tbl_printer
        INNER JOIN tbl_deptprinter as dp
            ON p.printerID = dp.printerID
        ORDER BY p.printerName ASC";

$pResult=mysql_query($sql) or die(mysql_error());

if(mysql_num_rows($pResult) == 0) {
    print "There are No Records";   
}

else {
    while($tmp = mysql_fetch_assoc($pResult)) {
    echo $tmp['printerName'];
        echo "<input type=\"checkbox\" name=\"NA[]\" value=\"".$tmp['printerId'];
        if ($tmp['checked']) { echo " checked"; }
        echo "\">";
        echo $tmp['printerId'];
        echo "<br />";
    }

}
?>[/code]

[Psycho]

That query should have a group by clause otherwise you will get duplicates. It should be like this:

SELECT p.*, (dp.printerID = $printerID) as checked
FROM tbl_printer
INNER JOIN tbl_deptprinter as dp
    ON p.printerID = dp.printerID
GROUP BY p.printerID
ORDER BY p.printerName ASC

[sasa]

try[code]$sql = "SELECT p.printerId, IF(SUM(r.deptRef = $dep_id),'cheked=\"cheked\"','') as cheked FROM tbl_printer AS p left join tbl_deptprinter as r ON r.printerRef = p.printerId GROUP BY p.printerId";[/code]

[Psycho]

Using sasa's query (with 'checked' spelled correctly) you can just echo the value of $tmp['checked'] instead of doing the IF conditional = however you need to either add a space to 'checked' as returned by the query or when echoing the value to the input.

[JackRabbitSlims]

hey, i cant get either of the examples you've given me to work im affraid which is be possible to explain any/all steps to help me, if not thanks for trying to help ;D

[Psycho]

[quote]i cant get either of the examples you've given me to work[/quote]

Can you at lest tell us what IS happening? Are you gtting error messages or what?

[Barand]

Join dept and printer without a join condition, to get each dept joined to each printer, then left join to tbl_deptprinter.

Where dp.deptref is NULL, the checkbox will be unchecked
[code]
<?php
$sql = "SELECT d.deptname, p.printername, dp.deptref
        FROM tbl_dept d
        INNER JOIN tbl_printer p
        LEFT JOIN tbl_deptprinter dp
            ON d.deptid = dp.deptref AND p.printerid = dp.printerref
        ORDER BY d.deptname, p.printername";

$res = mysql_query($sql) or die(mysql_error());
$prevDept = '';
while (list ($dname, $pname, $ref) = mysql_fetch_row($res)) {
    if ($prevDept != $dname) {
        echo "<h3>$dname</h3>";
        $prevDept = $dname;
    }
    $chk = is_null($ref) ? '' : 'checked';
    echo "<input type='checkbox' name='prtr[]' value='$pname' $chk> $pname<br>" ;
}
?>
[/code]