Multipule Checkbox with Mysql

[barniegilly]

Hi  I have tried to adapt code from another topic on this subject and have got this far.  (I have omited parts that should not be relevant to enable you to focus on the relevant code)

 

My checkbox data is coming from a table called discipline and then the selected check box is stored in eventsdisciplines with a foreign key to the events table.  Each selected checkbox will have its own row in the eventdiscipline table. ( I understand this would be the best way of handling the query.

table `discipline`	(
  `dis_id` int( NOT NULL AUTO_INCREMENT,
  `dis_description` varchar(20) NOT NULL,

table `eventdisciplines`(
  `evdis_id` int( NOT NULL AUTO_INCREMENT,
  `dis_id` int( NOT NULL,
  `event_id` int( NOT NULL,
  
table `events` 		(
  `event_id` int( NOT NULL AUTO_INCREMENT,
  `event_title` varchar(20) NOT NULL,
  `dis_id` int( DEFAULT '999999',

 

My page displays fine with my list of disciplines, however I have not coded the insert query correctly and could do with a little help.

 

My page reads as follows

 

  
<?php	  
 if (intval($_GET['url_userid']) == 0) {
	redirect_to('controlpanel.php');
}

  include_once("includes/form_functions.inc.php");
// make sure the subject id sent is an integer

// START FORM PROCESSING
// only execute the form processing if the form has been submitted
		if (isset($_POST['submit'])) {
// initialize an array to hold our errors
		$errors = array();

// perform validations on the form data
		$required_fields = array('title', 'status_id',);
		$errors = array_merge($errors, check_required_fields($required_fields, $_POST));

		$fields_with_lengths = array('title' =>36, 'dis_id' <= 6, 'type_id' <= 6, 'champ_id' 
						<= 6, 'dis_id' <= 6, 'org_id' <= 6, 'venue_id' <= 6, 'status_id' <= 6);
		$errors = array_merge($errors, check_max_field_lengths($fields_with_lengths, $_POST));

// clean up the form data before putting it in the database

		$url_userid = mysql_prep($_GET['url_userid']);
		$title = trim(ucwords(mysql_prep($_POST['title'])));
		$event_details = mysql_prep($_POST['event_details']);
		$type_id = mysql_prep($_POST['type_id']);
		$champ_id = mysql_prep($_POST['champ_id']);		
		$dis_id = mysql_prep($_POST['dis_id']);
		$org_id = mysql_prep($_POST['org_id']);
		$venue_id = mysql_prep($_POST['ven_id']);
		$level_id = mysql_prep($_POST['level_id']);
		$status_id = mysql_prep($_POST['status_id']);
?>

 

IN THE NEXT BIT OF CODE IS WHERE IT THINK  I AM WRONG AND THINK IT IS MY BRACKETING?

 

<?php // Database submission only proceeds if there were NO errors.
		if (empty($errors)) 
		{
		$query = "INSERT INTO events (title, event_details, type_id,  champ_id, dis_id, org_id, ven_id,
                                                                       user_id,  startdate, level_id, status_id) \n"
   						 . "VALUES 	('{$title}', '{$event_details}', '{$type_id}', 
								'{$champ_id}', '{$dis_id}',  '{$org_id}','{$venue_id}', 
								'{$url_userid}',  '{$startdate}', '{$level_id}', '{$status_id}');";	

		$user_id = mysql_insert_id();

		// Insert Disciplines
		foreach ($_POST['discipline'] AS $key => $value) {
			mysql_query("INSERT INTO eventdisciplines SET event_id = '" . (int)$event_id . "', dis_id = '" . (int)$key . "'");
		}

		if ($result = mysql_query($query, $connection)) 
		{
// as is, $message will still be discarded on the redirect
			$message = "The page was successfully created.";

		} else {
			$message = "The page could not be created.";
			$message .= "<br />" . mysql_error();
		}
		} else 
		{
		if (count($errors) == 1) 
		{
			$message = "There was 1 error in the form.";
		} 
		else 
		{
			$message = "There were " . count($errors) . " errors in the form.";
		}
		}
		} else { // Form has not been submitted.

		$url_userid = "";
		$title = "";
		$event_details = "";
		$type_id = "";
		$champ_id = "";
		$dis_id = "";
		$org_id = "";
		$venue_id = "";
		$level_id = "";
		$status_id = "";

		}
		 $url_userid = mysql_prep($_GET['url_userid']);

?>
</head>

This is within the body and a table along with other fields which I have omitted as they are not relevant to this question.

 

                                <tr>
                                    <td><span class="compuls">*</span></td>
                                    <td class="heading">Discipline</td>
                                    <td><?php
								// Select All Records from Database
								$query = mysql_query("SELECT * FROM discipline ORDER BY dis_description ASC") or die('Error: Unable to execute query: ' . mysql_error());

						if ($query) {
							if (is_resource($query)) {
								$i = 0;
								$data = array();

								while ($result = mysql_fetch_assoc($query)) {
									$data[$i] = $result;
									$i++;
								}

								mysql_free_result($query);

								$results = array();
								$results = $data;

								unset($data);
							}
						} else {
							echo '<div class="warning">No results returned</div>';
						}

						$rowOne = 'rowOne';
						$rowTwo = 'rowTwo';
						$rowCount = 0;

					?>
					<?php foreach ($results as $discipline) { ?>
						<?php $rowClass = ($rowCount % 2) ? $rowTwo : $rowOne; ?>
							<div class="<?php echo $rowClass; ?>"><input name="discipline[<?php echo $discipline['dis_id']; ?>]" type="checkbox" /><?php echo $discipline['dis_description']; ?></div>
						<?php $rowCount++; ?>
					<?php } ?>
</td>
								</tr>

 

 

 

[barniegilly]

Just one other thing,  there are two tables being updated in this code and the insert for the events table works fine it is just the insert into the eventdisciplines table that is not quite right,  the dis_id is inserted and obviously the evdis_id is created but the event_id is not being passed into the database.

 

I image that as the event_id is being created by the first insert that I am not capturing the event_id to then pass into the next insert? 

 

 

 

[barniegilly]

Seem to be working it out!!  I have moved changed the $event_id variable to after the if and it seems to work?

 

 

<?php			$query = "INSERT INTO events (title, event_details, type_id,  champ_id, org_id, ven_id, user_id, 
								 startdate, level_id, status_id) \n"
   						 . "VALUES 	('{$title}', '{$event_details}', '{$type_id}', 
								'{$champ_id}',  '{$org_id}','{$venue_id}', 
								'{$url_userid}',  '{$startdate}', '{$level_id}', '{$status_id}');";	

		if ($result = mysql_query($query, $connection)) 
		{

		$event_id = mysql_insert_id();

		// Insert Diseases
		foreach ($_POST['discipline'] AS $key => $value) {
			mysql_query("INSERT INTO eventdisciplines SET event_id = '" . (int)$event_id . "', dis_id = '" . (int)$key . "'");
		}
?>

 

Now just got to work out how I do an update if anyone has some advice it would be greatly appreciated.