Ultimate checkbox solution?

komlos

Hi,

I've been searching the threads for weeks now looking for a solution for inserting multiple values into MySql field but those that I found (there are many) were too specific, couldn't apply to my case. Please help, so others also could use the examples as an ultimate solution for their checkbox related problems.

I'm trying to make a code that does auto email notification based on categories. User could check the categories that he wants to receive notification from.

I have a form that builds checkboxes dinamically from the table "category". Checkboxes are using the name and id of the categories. (see below)

fields in "category" table: root_id, papa_id, cat_name, add_date

Now when a user check the boxes and hit "Add" it would be saved in table "notice". I'd like to have the checkbox values go to one field: "cat_id" (with "implode" function?)

fields in "notice" table: notice_id, cat_id, country, email

The same form would be used for adding and removing categories with e.g. two buttons: "ADD" "REMOVE". That means when user access the form later the checboxes that he has previously checked should be checked, so he could see which category he is subscribed to.

It would be nice to have an error checking when someone tries to subscribe to the same category like: "You have already subscribed to this cat"

Thanks for the patience and your help.

This is the code that builds the form, should save and remove the categories:

if( isset( $action ) )
{
if( $action == "add" )
{

			//////  THIS IS THE PART WHERE I NEED YOUR HELP ////////

					mysql_query( "INSERT INTO $notice_tbl ( cat_id, country, email ) VALUES ( '$cat_id[]', '$country', '$email' )" ) or error( mysql_error() );

					displayHeader( "$ADVT_NAME > Auto Notify" );
					echo "Congratulations! This category has been added to you list successfully.\n";
					echo "<br><a href=\"./index.php?cat=$cat\"><b>Back</b></a> | <a href=\"./index.php\"><b>Home</b></a></p>\n";
				}
				else
				{
					displayHeader( "$ADVT_NAME > Auto Notify" );
					echo "Sorry, this category is already existed in your list.\n";
					echo "<br><a href=\"./index.php?cat=$cat\"><b>Back</b></a> | <a href=\"./index.php\"><b>Home</b></a></p>\n";
				}
		displayFooter();
	}
}
else
{
	displayHeader( "$ADVT_NAME > Auto Notify" );
	$cats = mysql_query( "SELECT cat_name, root_id FROM $category_tbl WHERE papa_id='' ORDER BY add_date DESC" ) or error( mysql_error() );
	if( mysql_num_rows( $cats ) == 0 )
		echo "<p align=\"center\"><font size=\"4\">No Category Found</font></p>\n";
	else
	{
		include("$full_path/templates/member_menu.php");
		echo "\n<div align=\"center\">\n";
		echo "  <center>\n";
		echo "  <table border=\"0\" cellpaddig=\"0\" cellspacing=\"0\">\n";
		echo "  <form enctype=\"multipart/form-data\" method=\"post\" action=\"$PHP_SELF?action=add\">\n";
		$i = 1;
		while( $catsrow = mysql_fetch_array( $cats ) )
		{
			echo "    <tr>\n";
			echo "      <td>";
			echo "<input type=\"checkbox\" name=\"cat_id[]\" value=\"{$catsrow['root_id']}\">{$catsrow['cat_name']}\n";
			echo "      <br>&nbsp;\n";
			echo "      </td>\n";
			echo "    </tr>\n";
			$i++;
		}

				echo "    <tr>\n";
				echo "      <td>Country</td>\n";
				echo "      <td><select size=\"1\" name=\"country\">\n";
				echo "        <option>Budapest</option>\n";
				echo "        <option>USA</option>\n";
				echo "        <option>Canada</option>\n";
				echo "        <option>Mexico</option>\n";
				echo "        <option>EU</option>\n";
				echo "        <option>Hungary</option>\n";
				echo "        <option>UK</option>\n";
				echo "        <option>Austria</option>\n";
				echo "      </select></td>\n";
				echo "    </tr>\n";

		echo "      <td><input type=\"submit\" value=\"Submit\">";
		echo "  </table>\n";
		echo "  </form>\n";
		echo "  </center>\n";
		echo "</div>\n";
	}
	displayFooter();
}

eoghain

Well you basically mention the solution in your question. Use [man]implode/man to create a list of the id's, probably comma separated, before your sql runs.

if( isset( $action ) ) { 
   if( $action == "add" ) { 
      ////// THIS IS THE PART WHERE I NEED YOUR HELP //////// 

  $cat_list = implode(",", $cat_id);  // Do This

  mysql_query( "INSERT INTO $notice_tbl ( cat_id, country, email ) VALUES ( '$cat_list', '$country', '$email' )" ) or error( mysql_error() ); 

  displayHeader( "$ADVT_NAME > Auto Notify" ); 
  echo "Congratulations! This category has been added to you list successfully.\n"; 
  echo "<br><a href=\"./index.php?cat=$cat\"><b>Back</b></a> | <a href=\"./index.php\"><b>Home</b></a></p>\n"; 
   }

komlos

I did try that before but it only insert one record in the table. I think there is a while or for loop missing somewhere.

eoghain

Ok I missed something in your question. It looks like you are pulling multiple rows out of your table when you display the data, so you need to put multiple rows back in. I thought you wanted to put in a single row and have all of the categories listed on that single row.

if( isset( $action ) ) { 
   if( $action == "add" ) { 
      ////// THIS IS THE PART WHERE I NEED YOUR HELP //////// 

  for ($i = 0; $i < count($cat[]); $i++) {
     mysql_query( "INSERT INTO $notice_tbl ( cat_id, country, email ) VALUES ( '" . $cat[$i] . "', '$country', '$email' )" ) or error( mysql_error() ); 
  }

  displayHeader( "$ADVT_NAME > Auto Notify" ); 
  echo "Congratulations! This category has been added to you list successfully.\n"; 
  echo "<br><a href=\"./index.php?cat=$cat\"><b>Back</b></a> | <a href=\"./index.php\"><b>Home</b></a></p>\n"; 
   }

komlos

Yes you were right! I want to put all the checked categories on a single row. I tried with the implode but it only inserts one category id in the cat_id field, despite there is more than one checked.

BTW which do you think is more efficient, faster?
1. Input category ids on one row for one member OR
2. Separate rows for each category for one member

(When submitting ad, code has to select members from notice table that subscribed to that particular cat and send email each of them)

Thanks

eoghain

Post your table structure and some output from a trial run. Show me what the query looks like before it runs. To do that build your query as a string instead of building it in the call to mysql_query(), then echo it. Cause the first code I showed you should work.