Selecting checkboxes using PHP and MySQL

PHP
1

I have the following code


$sql2 = "SELECT ID, AID, NoteText, NoteDate FROM lectures WHERE ID = " . $id;
$sql3 = "SELECT CID, LID FROM lecturelookup WHERE LID = " . $id; 
$row = @mysql_query($sql2); 
$row2 = @mysql_query($sql3); 
$authors = mysql_query('SELECT ID, Name FROM authors'); 
$cats = mysql_query('SELECT ID, Name FROM categories'); 

$result = @mysql_fetch_array($row); 
$result2 = @mysql_fetch_array($row2); 



<?php			 
			while ($cat = mysql_fetch_array($cats)) {
				$cid = $cat['ID'];
				$cname = htmlspecialchars($cat['Name']);
				$resultcat = $result2['CID'];
				echo("result2: " . $result2['CID'] . ", cid: " . $cid);
				if ($result2['CID'] == $cid) {
					echo("<input type='checkbox' name='cats[]' value='$cid' checked='checked' />" . "$cname<br />\
");
				} else {
					echo("<input type='checkbox' name='cats[]' value='$cid' />" . "$cname<br />\
");
				}
				//$cat = mysql_fetch_assoc($cats); 
			}
		?>

I am using the above MySQL queries within this piece of code to get the following values into some checkboxes. Here’s a screenshot of what I have so far:

The relevant SQL queries return this info:

categories


ID 	Name
1 	General
2 	Mathematics
3 	English

lecturelookup


CID 	LID
1 	2
2 	2

As you can see, CID matches the ID in categories, and I would like to have it so that the checkboxes for both General and Mathematics are clicked.

How can I amend my code to do this?

2

I couldn’t edit my last post, so here’s another.

Could the problem stem from the query I am using within the while loop? It seems that the results there are returning fine, so would it be wise to fit it all into its own query and put that through the while loop?

3

Gonna bump this to see if someone can help, hopefully it’ll get noticed soon.

4

As you’ve identified you could change your query to include a join between the two tables would be a decent starting point. Only the first item in the $result2 array is being evaluated by


if ($result2['CID'] == $cid)
5

The problem I am currently facing is how to do that, as well as using nested loops to achieve the same thing. If I write a join how would I get every category as well as if the box should be checked or not? I could write cases within my query but that seems like overkill for what should be a simple problem.

6 

SELECT categories.Name
     , lecturelookup.LID
  FROM categories
LEFT OUTER
  JOIN lecturelookup
    ON lecturelookup.CID = categories.ID
   AND lecturelookup.LID = 2

Notice you always get a row for every category. But, if a LID wasn’t related to that category, the LID will be null in that row. When you loop over the resulting rows, test to see if it’s null to decide whether or not to tick the checkbox on.

7

Brilliant, that worked perfectly. Thank you very much!