[RESOLVED] The Challenge (Part Deux)

[NFClimber]

Well realizing I posted the wrong code, this might make more sense now. Like I said before, I'm making a registration program for a Boy Scout Council. What I've got in my database is a roster table, listing all of the scouts' info. I have a new registration form that will list the scouts' first and last names, accompanied with six combo boxes with options for merit badges. Right now my code looks like this:

<?php
require_once ('./includes/config.inc.php');

$page_title = 'Woodland Trails Scout Reservation';
include ('./includes/header.html');

require_once ('../mysql_connect.php');

if (!isset($_SESSION['troop_num'])) {

$url = 'http://' . $_SERVER['HTTP_HOST'] . dirname($_SERVER['PHP_SELF']);
if ((substr($url, -1) == '/') OR (substr($url, -1) == '\\') ) {
	$url = substr ($url, 0, -1); // Chop the slash
}
$url .= '/index.php';

ob_end_clean(); // Delete buffer
header("Location: $url");
exit(); // Quit

} else {

if (isset($_POST['submitted'])) {

$query = "INSERT into merit_reg (scout_id, scout_first, scout_last, merit9, merit10, merit11, merit2, merit3, meritalt) VALUES ('$scout_id', '$scout_first', '$scout_last', '$meritnine', '$meritten', '$meriteleven', '$merittwo', '$meritthree', '$meritalt')";
$result = mysql_query($query);

if (mysql_affected_rows() == scoutcount) { // If it ran OK.

	echo "Your scouts were registered successfully. Click here to view your selections.";

} else {

	echo "Your scouts were unable to be registered due to a system error. Please try again later.";

};

}

echo '<h2>Merit Badge Registration</h2>';
echo '<p>Please select a merit badge schedule for each of your scouts. Remember, <b>these selections are only tentative</b>, and may not actually represent what merit badges your scouts will take at Woodland Trails. Feel free to print out this tentative schedule for your own records. As soon as an official schedule for your troop is available, it will be sent to you.<br /><br />As you make these seletions, remember to follow the program guide for appropriate times. Some merit badges span multiple time slots, and require special selections. See the example below:<br /><br />
<center><table width="400" border="3">
  <tr>
    <td width="99">&nbsp;</td>
    <td width="69">9 AM </td>
    <td width="102">10 AM </td>
    <td width="98">11 AM </td>
  </tr>
  <tr>
    <td>Johnny Doe </td>
    <td>Climbing</td>
    <td><strong>Climbing Cont. </strong></td>
    <td>Nature</td>
  </tr>
</table></center><br />Note that you must select a "continued" option when a merit badge spans multiple time slots. Also, if your scout has elected to leave a merit badge session open, please select "None". Should a merit badge class be unavailable to your scout, due to class size or other cicumstances, select a merit badge in the "Alternate" box, and we will do our best to replace the preferred merit badge with the alternate.<br /><br />';
echo '<h3><table width="750" border="0" cellspacing="0" cellpadding="2" align="center">
	  <tr bgcolor="#C1FFC1">
	    <td width="150" align="center"><b>SCOUT NAME</b></td>
		<td width="200" align="center"><b></b></td>
		<td width="200" align="center"><b>Merit Badge Schedule</b></td>
		<td width="200" align="center"><b></b></td>
	  </tr>
	  </table></h3>';

echo '<form action="test2.php" method="POST">';
$scoutcount=0;

$query = "SELECT scout_id, scout_first, scout_last FROM roster WHERE troop_num='" . $_SESSION['troop_num'] . "' ORDER BY scout_last ASC";
$mysql_result = mysql_query($query);
while($row = mysql_fetch_row($mysql_result))

{
$scout_id = $row[0];
$scout_first = $row[1];
$scout_last = $row[2];
$scoutcount = $scoutcount +1;

if($scoutcount%2): $color = "#FFFFFF"; else: $color = "#C1FFC1"; endif; 

print("  <table width='750' border='0' cellspacing='0' cellpadding='2' align='left'>
    <tr bgcolor=$color>	
      <td width='150' align='left'><b>$scout_first $scout_last</b></td>
      <td width='200' align='left'> 
	  	  <select name='merit[1]'>
	  		** List of Badges **
	      </select>
	  </td><td width='200' align='left'> 
		  <select name='merit[2]'>
		    ** List of Badges **
		  </select>
	  </td><td width='200' align='left'>
		  <select name='merit[3]'>
		    ** List of Badges **
		  </select>
	  </td>
	  </td>
    </tr>
	<tr bgcolor=$color>
	  <td width='150' align='left'></td>
      <td width='200' align='left'>
	  	  <select name='merit[4]'>
	  		** List of Badges **
	      </select>
	  </td><td width='200' align='left'>
		  <select name='merit[5]'>
		    ** List of Badges **
		  </select>
	  </td><td width='200' align='left'>
		  <select name='merit[6]'>
		    ** List of Badges **
		  </select>
	  </td>
	  </td>
    </tr>
  </table><br /><br /><br /><br />
");  

}
echo "<br /><center><input type='submit' name='submit' value='Submit Choices'>
      <input type='hidden' name='submitted' value='TRUE'></center>";
echo '</form>';
}
mysql_close();
include ('./includes/footer.html');
?>

When I run the code, I can get it to list the scouts names and the merit badge options, but the way my code is designed, I'm having trouble inserting it all into a new table. I want the new table to contain the scouts' first and last name, the merit badge selections, and their troop number (the troop number isn't very hard since it's also the SESSION_ID).
I have to present a finished copy of this on Thursday...any recommendations on code or structure would be great! Thanks!

[bastien]

Your design is not what I would choose, as previously pointed out in your other post and my reply to it. My design would give you N number of merits to play with and you avoid the duplication of data, which is one of the tenets of good database design.

You say you are having trouble with the insert, but don't mention what it is? A sql error? A coding error? If you change your query to this

$result = mysql_query($query) or die("Can't complete query<br>$query<br> because ".mysql_error());

Then you will get meaningful error messages out of the db that you can use to tune the code.

[NFClimber]

Right. I'm not opposed to changing the design, although I am quickly running out of time. Because of my insert troubles I hadn't included your afformentioned error structure. The issue is not so much with the INSERT statement, but how to do it with my code. I think I've almost perfected it, by changing my variables to ones such as "meritnine . $i". Thus creating a number individual variable with each loop generating a scout name. The problem I'm having now is that after creating all my variables and code, the page does not even load. All I get is a white screen. I don't even get a SQL error. I'll be reviewing my code, most likely making changes.

If you'd like to clarify your coding suggestions, I'd be interested in seeing them.

Thanks again.

[bastien]

just a follow-up...how did things go?

[NFClimber]

Awesome, actually. I had the whole program fixed the night before I had to present it.

I basically used a counter variable to generate individual variables for each merit badge selection, per scout (i.e. merit1_1, merit2_1, merit3_1 ... merit1_4, merit2_4, merit3_4, etc., etc.) As I stated before, in the form, I also listed the pre-entered scout names. When the form was submitted, I called another mySQL query, identical to the one I originally used in the form (so basically, as the names are inserted, they will be put in the same order, and thus, with the same merit badge selections.) Using a while loop, I posted the merit badge selection values into variables and inserted them into a table. It's actually pretty simple. I'll post a code sample below:

$scoutcount = 0;
	$rowcount = 0;
	$query = "SELECT scout_id, scout_first, scout_last, troop_num FROM roster WHERE troop_num='" . $_SESSION['troop_num'] . "' ORDER BY scout_last ASC";
	$mysql_result = mysql_query($query);

while($row = mysql_fetch_row($mysql_result)) {

	$scoutcount = $scoutcount + 1;

	$scout_id = $row[0];
	$scout_first = $row[1];
	$scout_last = $row[2];
	$troopnum = $row[3];

	$mnine = escape_data($_POST['meritnine' . $scoutcount]);
	$mten = escape_data($_POST['meritten' . $scoutcount]);
	$meleven = escape_data($_POST['meriteleven' . $scoutcount]);
	$mtwo = escape_data($_POST['merittwo' . $scoutcount]);
	$mthree = escape_data($_POST['meritthree' . $scoutcount]);
	$malt = escape_data($_POST['meritalt' . $scoutcount]);

	$query = "INSERT INTO merit_reg (scout_id, scout_first, scout_last, troop_num, merit9, merit10, merit11, merit2, merit3, meritalt, regdate) VALUES ('$scout_id', '$scout_first', '$scout_last', '$troopnum', '$mnine', '$mten', '$meleven', '$mtwo', '$mthree', '$malt', NOW() )";
	$result = mysql_query($query);

	if (mysql_affected_rows() == 1) {
		$rowcount = $rowcount +1;

Like I said, pretty simple. If you'd like more info on how I did it, you can just drop me an email. Thanks again for all your help.