[SOLVED] Inserting into MySQL after submitting form looped

[msaz87]

Hi all,

 

Just a quick question... I have a form that is generated according to a few variables the user defines, then loops to produce the needed fields (IE: if the user puts in six for one selection, six text inputs show up, etc.). Those results need to be inserted into a MySQL db, but I've never done an insert before that wasn't with a fixed form submitting into it. I was just hoping someone might be able to point me in the right direction of a tutorial or some sort of explanation on how to start on that process?

 

Thanks!

[PFMaBiSmAd]

You should use arrays for set(s) of form fields -

http://www.php.net/manual/en/faq.html.php#faq.html.arrays

 

You can then use a simple foreach loop to iterate over all the elements of each array to process them as needed.

[msaz87]

Thanks... that makes a lot of sense. The only question I have after that is how to run the foreach on the insert statement since I have multiple arrays of form data, along with some fixed ones.

 

So if this is the insert statement:

mysql_query("	INSERT INTO table 
	(one, two, three) 
	VALUES('$one', '$two', '$three' ) ") 
or die(mysql_error());  

 

And say $one is a fixed variable, but $two and $three are arrays, how would you affix the foreach to have both $two and $three running in it?

 

Thanks again!

[PFMaBiSmAd]

A database design where there is a variable number of same type items in a set, would have a table where a separate row would be inserted for each item in the set. In general, you don't insert a list of anything into a field in a table as that makes any finding, sorting, or any other manipulation of the data, beyond just storing/retrieving the data as is, overly complex.

 

If you have a specific example of what you are doing, someone can post a specific example how you should store it.

[msaz87]

A database design where there is a variable number of same type items in a set, would have a table where a separate row would be inserted for each item in the set. In general, you don't insert a list of anything into a field in a table as that makes any finding, sorting, or any other manipulation of the data, beyond just storing/retrieving the data as is, overly complex.

 

If you have a specific example of what you are doing, someone can post a specific example how you should store it.

 

I wanted to avoid being overly needy -- but it sounds like I'm in a little over my head... so I will do my best to describe what I'm doing:

 

The project is a scheduling tool for some sports leagues.. the first page the admin goes to they select a league, location, date, week (IE: if it's Week 1, Week 2, etc.), how many fields they need, what time games start, how many games per field, how long the games run and then if there are any notes they need to post about that week.

 

All of these variables are posted to the next page, where tables are generated to let them set up all the schedules... in other words, if they picked that they needed four fields, there were three games per field, games start at 6:30 and last 30 min, then it would output three tables showing four rows and three columns. Each table would be named a different game time (in this example, 6:30, 7, 7:30). The four rows would be named after the fields (Field 1, Field 2, Field 3, Field 4) and then <select> fields for the two teams playing eachother.

 

The admin schedules all the games and at the end, submits it. The schedule, along with those posted variables on the first page (date, notes, etc.) feed into the DB and later it'll be called so that the players can see it all (picking a league, then it displays what weeks of schedules exist and finally the schedule itself).

 

Hopefully that explanation isn't too confusing... if so, let me know what part and I can try to elaborate.

 

Thanks so much for the help!

[PFMaBiSmAd]

I would dynamically generate the select options as follows -

<select name="slot[1|6:30|a]">

 

There are three values represented, separated by the | character. The first one is the field number, the second the time, the third is either an a or b for one of the two teams selected.

 

The HTML of the form that your php code generates would look something like this -

<form method="post" action="formproc.php">
<table border="1">
<tr>
  <th>Field:</th><th>6:30 start</th><th>7:00 start</th><th>7:30 start</th>
</tr>
<tr>
  <th>1:</th>
  <td>
<select name="slot[1|6:30|a]">
	<option value="team1">team1</option>
	<option value="team2">team2</option>
	<option value="team3">team3</option>
	<option value="team4">team4</option>
	... remaining choicses ...
</select> VS
<select name="slot[1|6:30|b]">
	<option value="team1">team1</option>
	<option value="team2">team2</option>
	<option value="team3">team3</option>
	<option value="team4">team4</option>
</select> 
  </td>
  <td>
<select name="slot[1|7:00|a]">
	<option value="team1">team1</option>
	<option value="team2">team2</option>
	<option value="team3">team3</option>
	<option value="team4">team4</option>
</select> VS
<select name="slot[1|7:00|b]">
	<option value="team1">team1</option>
	<option value="team2">team2</option>
	<option value="team3">team3</option>
	<option value="team4">team4</option>
</select> 
  </td>
  <td>
<select name="slot[1|7:30|a]">
	<option value="team1">team1</option>
	<option value="team2">team2</option>
	<option value="team3">team3</option>
	<option value="team4">team4</option>
</select> VS
<select name="slot[1|7:30|b]">
	<option value="team1">team1</option>
	<option value="team2">team2</option>
	<option value="team3">team3</option>
	<option value="team4">team4</option>
</select> 
  </td>
</tr>
<tr>
  <th>2:</th>
  <td>
<select name="slot[2|6:30|a]">
	<option value="team1">team1</option>
	<option value="team2">team2</option>
	<option value="team3">team3</option>
	<option value="team4">team4</option>
</select> VS
<select name="slot[2|6:30|b]">
	<option value="team1">team1</option>
	<option value="team2">team2</option>
	<option value="team3">team3</option>
	<option value="team4">team4</option>
</select> 
  </td>
  <td>
<select name="slot[2|7:00|a]">
	<option value="team1">team1</option>
	<option value="team2">team2</option>
	<option value="team3">team3</option>
	<option value="team4">team4</option>
</select> VS
<select name="slot[2|7:00|b]">
	<option value="team1">team1</option>
	<option value="team2">team2</option>
	<option value="team3">team3</option>
	<option value="team4">team4</option>
</select> 
  </td>
  <td>
<select name="slot[2|7:30|a]">
	<option value="team1">team1</option>
	<option value="team2">team2</option>
	<option value="team3">team3</option>
	<option value="team4">team4</option>
</select> VS
<select name="slot[2|7:30|b]">
	<option value="team1">team1</option>
	<option value="team2">team2</option>
	<option value="team3">team3</option>
	<option value="team4">team4</option>
</select> 
  </td>
</tr>
<tr>
</table>
<input type="submit" name="sumbit">
</form>

 

When this is submitted, code like the following can iterate over all the values -

<?php
foreach($_POST['slot'] as $key => $value){
list($field,$time,$team) = explode('|',$key);
echo "Field: $field, Time: $time, Team: $team, Value: $value<br />";
}
?>

 

Giving a result like this (tested) -

Field: 1, Time: 6:30, Team: a, Value: team1

Field: 1, Time: 6:30, Team: b, Value: team2

Field: 1, Time: 7:00, Team: a, Value: team1

Field: 1, Time: 7:00, Team: b, Value: team3

Field: 1, Time: 7:30, Team: a, Value: team1

Field: 1, Time: 7:30, Team: b, Value: team4

Field: 2, Time: 6:30, Team: a, Value: team1

Field: 2, Time: 6:30, Team: b, Value: team1

Field: 2, Time: 7:00, Team: a, Value: team1

Field: 2, Time: 7:00, Team: b, Value: team1

Field: 2, Time: 7:30, Team: a, Value: team1

Field: 2, Time: 7:30, Team: b, Value: team1

[PFMaBiSmAd]

Code to dynamically produce the form -

<?php
// example data - four fields, three start times -
$fields = array(1,2,3,4);
$times = array('6:30','7:00','7:30');
$teams = range(1,24); // make up some data, actual teams would come from a database...

// time heading -
$time_heading = '';
foreach($times as $time){
$time_heading .= "<th>$time start</th>";
}
// team options -
$team_options = '';
foreach($teams as $team){
$team_options .= "<option value='$team'>$team</option>\n";
}

// produce form/table -
$content = "
<form method='post' action='formproc.php'>
<table border='1'>
<tr>
  <th>Field:</th>$time_heading
</tr>";

// loop for each field and time -
foreach($fields as $field){
$content .= "<tr>
		<th>$field:</th>";
foreach($times as $time){
	$content .= "<td>
			<select name='slot[$field|$time|a]'>
				$team_options
			</select> VS
			<select name='slot[$field|$time|b]'>
				$team_options
			</select> 
		</td>";
}
$content .= "</tr>";
}
$content .= "</table>
<input type='submit' name='sumbit'>
</form>";

echo $content;
?>

 

 

[msaz87]

Thanks a bunch -- that worked well after playing around with it a bit. Now I just have to figure out how to call it back to be edited or viewed by teams...

 

Thanks again!

[PFMaBiSmAd]

To pre-select choices in the drop-down select lists, I would do the following -

 

<?php
// example data - three start times, four fields (12 games)

$fields = array(1,2,3,4);
$times = array('6:30','7:00','7:30');
$teams = range(1,24); // make up some data, actual teams would come from a database...

// make up some existing picks to preselect the drop-down menus, actual values would come from a database...
$data = array(); // array of existing picks
$data['1|6:30|a'] = '2';
$data['1|6:30|b'] = '3';
$data['1|7:30|a'] = '4';
$data['1|7:30|b'] = '5';
// ... load array with remainder of data

// time heading -
$time_heading = '';
foreach($times as $time){
$time_heading .= "<th>$time start</th>";
}

// produce form/table -
$content = "
<form method='post' action='formproc.php'>
<table border='1'>
<tr>
  <th>Field:</th>$time_heading
</tr>";

// loop for each field and time -
foreach($fields as $field){
$content .= "<tr>
		<th>$field:</th>";
foreach($times as $time){
	$indexa = "$field|$time|a";
	$team_optionsa = '';
	foreach($teams as $team){
		$select = '';
		if(isset($data[$indexa]) && $data[$indexa] == $team){
			$select = " selected='selected'";
		}
		$team_optionsa .= "<option value='$team'$select>$team</option>\n";
	}
	$indexb = "$field|$time|b";		
	$team_optionsb = '';
	foreach($teams as $team){
		$select = '';
		if(isset($data[$indexb]) && $data[$indexb] == $team){
			$select = " selected='selected'";
		}
		$team_optionsb .= "<option value='$team'$select>$team</option>\n";
	}
	$content .= "<td>
			<select name='slot[$indexa]'>
				$team_optionsa
			</select> VS
			<select name='slot[$indexb]'>
				$team_optionsb
			</select> 
		</td>";
}
$content .= "</tr>";
}
$content .= "</table>
<input type='submit' name='sumbit'>
</form>";

echo $content;
?>  

[msaz87]

I started working out the output version first and ran into a bit of a quirky error where the loop is spitting out double... you can see it here:

 

http://fasports.com/test/scheduling/view_schedule.php?league=25&week=2009-11-02

 

The code for the loop is...

 

<?

$times = mysql_query("	SELECT DISTINCT gametime 
			FROM schedules 
			WHERE league = $league_selection 
			AND league_date = '$week_selection'")
			or die(mysql_error()); 

?>	

<? while($row = mysql_fetch_array($times)) foreach(($row) as $gametime){ ?>		

	....formatting, other queries related to content, etc.....	


<? } ?>	

 

Any ideas why this might double up?

 

Thanks for the help!