[SOLVED] showing checkboxes as checked when using while to get checkbox titles from db

[pixelgirl]

Hi,

 

I have a question about PHP. I am showing some checkboxes in an array on a webpage, with the checkboxes getting their values and names from a database table. This all works fine. However, I want to show another page where you can edit these checkboxes, as well as show values already selected.

 

This involves showing the checkbox as checked if a row in a different database table is populated with a particular checkbox id.

 

I dont know if this makes much sense, so see code below. The bit that is commented out doesnt work. The rest of it just shows the list of checkboxes with name and id, but doesnt check them if they are checked in the db:

 

<?php
                                $sql  = "SELECT category, horseCatID
						FROM horsecat";
				$result = mysql_query($sql);


				$sql3 = "SELECT catID
						FROM lookuphorsecat
						WHERE userID = '$dbOutput'";
				$result3 = mysql_query($sql3);
			?>

<form method="post" name="frmRegister" id="frmRegister">
				<table width="500" align="left" cellspacing = "7">
					<?php
						while ($row = mysql_fetch_assoc($result)) 
						{
							extract($row);
					?>
					<tr>
						<td><p class="text3"><?php echo str_replace(" ", " ", $category); ?></p></td>
							<?PHP

								/*while ($row2 = mysql_fetch_assoc($result3))
									{
									extract($row);

										if ($catID == $horseCatID)
										{
											echo '<td><input type="checkbox" name="categories[]" value = "<?php echo $horseCatID; ?>" checked=true></td>';
										}
										else 
										{
											echo '<td><input type="checkbox" name="categories[]" value = "<?php echo $horseCatID; ?>"></td>';
										}
									}*/
							?>
						<td><input type="checkbox" name="categories[]" value = "<?php echo $horseCatID; ?>"></td>
					</tr>
					<?php
						} // end while
					?>

[stuffradio]

Maybe,

in the section commented out... you're extracting row, but shouldn't you be extracting row2 since you already extracted row?

[Psycho]

There is a problem in the logic. For the first checkbox it iterrates through all of the results from the second query. Then for subsequent checkboxes there are no more results to extract from row 2. You are better off doing a single query. I made some assumptions on field names since I don't know your table designs.

 

<form method="post" name="frmRegister" id="frmRegister">
<table width="500" align="left" cellspacing = "7">

<?php

$sql  = "SELECT horsecat.category, horsecat.horseCatID, lookuphorsecat.userID
        FROM horsecat
        LEFT JOIN lookuphorsecat ON horsecat.id = lookuphorsecat.catID
        WHERE lookuphorsecat.userID = '$dbOutput'";

$result = mysql_query($sql);

while ($fields = mysql_fetch_assoc($result)) 
{
extract($fields);
$checked = ($userID)?'checked="true" ':'';

echo "		<tr>\n";
echo "			<td><p class=\"text3\">".str_replace(" ", " ", $category)."</p></td>\n";
echo "			<td><input type=\"checkbox\" name=\"categories[]\" value=\"$horseCatID\" $checked\></td>\n";
echo "		</tr>\n";

} // end while

?>

[pixelgirl]

The left join idea is good as it does get the results im after.

 

However, i also need to display the checkboxes that arent checked in case a user wants to select those, and here lies my problem. I want to display all the checkboxes, and then have the ones that are checked as checked, with the others still showing. That was what I was trying to get the while loops to do, and why I used two.

 

I understand your comment about the logic being wrong, and why you put the sql in to one, but then that only shows the results that are checked, not all checkbox names, plus ones that are checked.

Thats why I was trying to get the while loops to show all check box names, then loop through the second query, check if the entry in the db is there for a checked box, if it is show it as checked, then move to the next checkbox.

But i could see there was something not quite right. Can you suggest anything else?

 

Thanks for your help so far

[Psycho]

You are not understanding what the JOIN does in this instance. With a LEFT JOIN ALL records from the first table are returned even when there is no matching records in the second table.

 

In the instances where there are no matching records from the second table the values that would be pulled from that table are returned as NULL.

 

Did you even try running the code I posted? I created a quick test and it works exactly how you say you want it to - all records from the first table are returned and if the record exists in the second table the usesrID field has a value. If it doesn't exist then userID is NULL. Using that knowledge the value of $checked is set appropriately.

 

In my test I did need to do a GROUP by on the first table because I had duplicate entries in teh second table. But, as I said, I don't know enough about your table structure to give a full solution. But, the logic I gave above will work as you need, it just needs to be modified to fit your table structure.

[pixelgirl]

I do understand joins, but was fixing some other stuff on my site yesterday so only had time to run the sql.

 

When I ran it it only came back with the records that were checked, but ill have another go as I know left joins are supposed to bring back everything on the left table.

 

Thanks again for your help, ill look in to it in more detail.

[lordfrikk]

As far as I know, correct combination is "checked='checked'", "checked=true' is Javascript way of doing it.

[pixelgirl]

You are not understanding what the JOIN does in this instance. With a LEFT JOIN ALL records from the first table are returned even when there is no matching records in the second table.

 

In the instances where there are no matching records from the second table the values that would be pulled from that table are returned as NULL.

 

Did you even try running the code I posted? I created a quick test and it works exactly how you say you want it to - all records from the first table are returned and if the record exists in the second table the usesrID field has a value. If it doesn't exist then userID is NULL. Using that knowledge the value of $checked is set appropriately.

 

In my test I did need to do a GROUP by on the first table because I had duplicate entries in teh second table. But, as I said, I don't know enough about your table structure to give a full solution. But, the logic I gave above will work as you need, it just needs to be modified to fit your table structure.

 

this is the result I get when I run the left join sql in phpmyadmin:

SELECT horsecat.category, horsecat.horseCatID, lookuphorsecat.userID
FROM horsecat
LEFT JOIN lookuphorsecat ON horsecat.horseCatID = lookuphorsecat.catID
WHERE lookuphorsecat.userID =51
   

category 	   horseCatID 	   userID
School Master 	    17 	              51
All Rounder 	       9 	         51
Show Jumping 	    3 	              51
Cross Country 	     2 	               51
Dressage 	      1 	        51

 

If i run the sql without the last line it returns all rows with nulls for userID, but if i include the last row (condition userid = 51) it only returns the rows above. I know this is now turning in to more of a sql question than php, but ive had a think about it myself and still cant get it to work.

 

My table structure is as follows:

 

table horsecat:

 

fields: horsecat id, category.

 

contents: 1, dressage,

              2, show jumping

              3, cross country

              (etc..)

 

(table horsecat contains all the categories for people to select. the categories are shown in the list of checkboxes on my webpage)

 

table lookuphorsecat:

fields: lookuphorsecatid, horseID, userID, catID.

contents:            78,      74,      51,      17

                        77,      74,      51,      9

                        77,      74,      51,      3

                        77,      74,      51,      2

                        77,      74,      51,      1

 

(table lookuphorsecat contains references to horse id so can relate to a particular horse. It should contain 5 references for each horse id. user id relates to user and cat id relates to horsecat table)

 

I want to show all rows from horsecat, whilst getting rows from lookuphorsecat that correspond to particular userid.

 

Thanks for all your help so far, I think the problem is nearly solved, I just need to work out how to get the userid condition without removing the other rows.

[Psycho]

OK, as I alluded to before I had to mock up a couple of tables to test on since I don't have your database. This should be easily corrected by moving the WHERE clause into the JOIN as follows:

SELECT horsecat.category, horsecat.horseCatID, lookuphorsecat.userID
FROM horsecat
LEFT JOIN lookuphorsecat ON horsecat.horseCatID = lookuphorsecat.catID
      AND lookuphorsecat.userID = '$dbOutput'

 

On further review I would probably go one step further with an IF/ELSE in the select clause like this (also added table references for clarity):

SELECT hc.category, hc.horseCatID, IF(luhc.userID='$dbOutput',1,0) as checked
FROM horsecat hc
LEFT JOIN lookuphorsecat luhc ON hc.horseCatID = luhc.catID
      AND luhc.userID = '$dbOutput'

 

Now the query will return a value for "checked" 1=true/0=false

[pixelgirl]

Thanks, ill try it and let you know. Joins are confusing  :-\

 

A.t.m im trying to get the unlink function to work, but when ive done that ill get on to it.

 

Thanks for all your help

 

[pixelgirl]

OK, as I alluded to before I had to mock up a couple of tables to test on since I don't have your database. This should be easily corrected by moving the WHERE clause into the JOIN as follows:

SELECT horsecat.category, horsecat.horseCatID, lookuphorsecat.userID
FROM horsecat
LEFT JOIN lookuphorsecat ON horsecat.horseCatID = lookuphorsecat.catID
      AND lookuphorsecat.userID = '$dbOutput'

 

On further review I would probably go one step further with an IF/ELSE in the select clause like this (also added table references for clarity):

SELECT hc.category, hc.horseCatID, IF(luhc.userID='$dbOutput',1,0) as checked
FROM horsecat hc
LEFT JOIN lookuphorsecat luhc ON hc.horseCatID = luhc.catID
      AND luhc.userID = '$dbOutput'

 

Now the query will return a value for "checked" 1=true/0=false

 

Thats brilliant, thanks a lot The SQL does exactly what I wanted to do (and more with the 1/0 stuff). I knew there was a way of doing it, i just didnt know what it was!

 

Im sure I can get the checkbox bit working now the SQL is sorted.

 

Though if i have any more problems I know where to come 

 

Thanks again.