How can I loop through this block with my checkbox values?

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
User avatar
tvs008
Forum Commoner
Posts: 29
Joined: Wed May 03, 2006 10:46 pm
Location: Seattle

How can I loop through this block with my checkbox values?

Post by tvs008 »

So this is one in a series of difficulties I've had with checkbox values going to the db. I have about 40 checkboxes and the value I'm inserting is a 1 or 0 corresponding to active/inactive into a lookup table with a composite pk/fk for pID and lID. First I want to see if the checkbox is checked (not sure if a checkbox value posts if its unchecked?). If it posts, then I'm trying an insert in case the value for the pair hasn't been inserted before; If that result fails, then I update Active to 1; If it doesn't post, then I'm trying inserting 0, then upon fail updating to 0.

This first part seems excessively long and I would appreciate any tips to shorten it. But the next part is even more tedious, and I'm wondering how I can loop through the block of code for all 40 checkboxes. The word 'Albanian' is the one I'm hoping to substitute for some sort of variable corresponding to each checkbox value...

Code: Select all

if (isset($_POST['Albanian'])) { //first see if lcheckbox was checked
			//if so, then try to insert values as if it is the first time being entered
			$query_insert1= "INSERT INTO prod_lang_lookup (Product_ID, Language_ID, ActiveLang) 
			VALUES (".$pID.", (SELECT Language_ID FROM languages WHERE LanguageName = 'Albanian'), 1)";
			$result1 = mysql_query($query_insert1,$myConn);
			//if the data for that product and language is already in the table, then update active to 1
				if (!$result1) {
					$query_activate1= "UPDATE prod_lang_lookup SET ActiveLang = 1 WHERE Product_ID = ".$pID." AND Language_ID = (SELECT Language_ID FROM languages WHERE LanguageName ='Albanian')";        
					$result1 = mysql_query($query_activate1,$myConn);		
								}
		//otherwise, if checkbox is not checked, update active to 0						
		} else {
		// first try insert to 0
			$query_insert1= "INSERT INTO prod_lang_lookup (Product_ID, Language_ID, ActiveLang) 
			VALUES (".$pID.", (SELECT Language_ID FROM languages WHERE LanguageName = 'Albanian'), 0)";
			$result1 = mysql_query($query_insert1,$myConn);
				// if data already exists, then we do an update to 0
				if (!$result1) {
					$query_activate1= "UPDATE prod_lang_lookup SET ActiveLang = 0 WHERE Product_ID = ".$pID." AND Language_ID = (SELECT Language_ID FROM languages WHERE LanguageName ='Albanian')";        
					$result1 = mysql_query($query_activate1,$myConn);			
								}
		}
User avatar
JayBird
Admin
Posts: 4524
Joined: Wed Aug 13, 2003 7:02 am
Location: York, UK
Contact:

Post by JayBird »

I think what you are looking for is the MYSQL REPLACE command. If the data exists, the row is deleted and replaced witht he new data. If the data doesn't exist, it is inserted.

You could then shorten the above code to soemthing like this (untested)

Code: Select all

$albanian = (isset($_POST['Albanian']) ? "1":"0";

$query_insert1 = "REPLACE INTO prod_lang_lookup (Product_ID, Language_ID, ActiveLang)
		  VALUES (".$pID.", (SELECT Language_ID FROM languages WHERE LanguageName = 'Albanian'), ".$albanian.")";
		
$result1 = mysql_query($query_insert1,$myConn);
User avatar
tvs008
Forum Commoner
Posts: 29
Joined: Wed May 03, 2006 10:46 pm
Location: Seattle

Post by tvs008 »

Thanks JayBird- I didn't know about REPLACE; That's very useful. That's not standard SQL is it?

My new code is a bit cleaner without that insert/update testing; here it is:

Code: Select all

if ($_POST["Albanian"] == "on")
			{
			echo "Albanian is checked<br />";
			$query_insert1 = "REPLACE INTO prod_lang_lookup (Product_ID, Language_ID, ActiveLang)
                  			  VALUES (".$pID.", (SELECT Language_ID FROM languages WHERE LanguageName = 'Albanian'), 1)";
            $result1 = mysql_query($query_insert1,$myConn); 
				if ($result1) {
								echo "Albanian Language set to active for product " .$pID;
								}
			}
		else 
			{
			echo "Albanian not checked<br />";
			$query_insert1 = "REPLACE INTO prod_lang_lookup (Product_ID, Language_ID, ActiveLang)
                  			  VALUES (".$pID.", (SELECT Language_ID FROM languages WHERE LanguageName = 'Albanian'), 0)";
            $result1 = mysql_query($query_insert1,$myConn);
				if ($result1) {
								echo "Albanian Language set to Inactive for product " .$pID;
								}
			}
However, I'm still trying to figure out how to loop through this one block for all 40 of my check boxes. My rough idea is to post all the checkboxes as an array

Code: Select all

name="checkbox[]"
, then loop through the REPLACE block for each item in the array. Only problem then is, how do I know which checkbox I am REPLACEing? I tried name=checkbox[] value='Albanian' but this no longer posts on/off. Any suggestions?
Post Reply