inserting multiple entrys into a db

[koolkrasher]

I finaly figured out how to add single entry to a db now i need to add multiple entrys to a db. here is the form im using and the post page.

figured u dont need to see the whole page


<form method="post" action="input.php">
<input type="text" name="player_name" id="name" />
<select name="position" id="position">
<option value="1" selected="selected">Manager</option>
<option value="2">Pitcher</option>
<option value="3">Catcher</option>
<option value="4">1st Base</option>
<option value="5">2nd Base</option>
<option value="6">Short Stop</option>
<option value="7">3rd Base</option>
<option value="8">left Field</option>
<option value="9">Center Field</option>
<option value="10">Rite Field</option>
<option value="11">DH</option>
</select>
</form>
thats pretty much it but i want to repeat the same form 10x on a page and have all the info from the form put into a table.(i have it set up in my htm page 10x already shortened it to save some reading time)

and now for the input.php


<?php include_once("resources/includes/connection.php");?>
<?php
$sql="INSERT INTO players (player_name, position_id, $team_id)
VALUES
('$_POST[player_name]','$_POST[position_id]','$_POST[$team_id]')";

if (!mysql_query($sql))
{
die('Error: ' . mysql_error());
}
echo "1 record added";

mysql_close()
?>

i was thinking of sending the 10 input fields and position to an array but when i went to do it i was alos thinkin of repeating the $sql 10x but im not sure if that will over write the previous var's sent.

any one have any ideas or suggestions?

[Charleh]

No repeating the $sql won't do anything like that - $sql is just a string which you pass as an argument to the mysql_query() function - changing it after mysql_query() has been run will have no effect.

You can make an array of input fields if you give them all the same name - can't remember the exact method but have a google for arrays in HTTP POST

[koolkrasher]

I did some hunting and i came across this
http://www.evolt.org/article/Storing_form_array_data_to_MySQL_using_PHP/18/60222/

I then went and implemented the changes like so


<form method="post" action="input.php">

<input type="text" name="player_name[]" id="name" />
<select name="position_id[]" id="position">
<option value="1">Manager</option>
<option value="2">Pitcher</option>
<option value="3">Catcher</option>
<option value="4">1st Base</option>
<option value="5">2nd Base</option>
<option value="6">Short Stop</option>
<option value="7">3rd Base</option>
<option value="8">left Field</option>
<option value="9">Center Field</option>
<option value="10">Rite Field</option>
<option value="11" selected="selected">DH</option>
</select>
</form>

then made changes to my input php like so



<?php include_once("resources/includes/connection.php");?>
<?php

$player_name=serialize($_POST['player_name']);
$position_id=serialize($_POST['position_id']);


$sql="INSERT INTO players ($player_name, $position_id, team_id)
VALUES ('$_POST[$player_name]','$_POST[$position_id]','$_POST[team_id]')";

if (!mysql_query($sql))
{
die('Error: ' . mysql_error());
}
echo "1 record added";

mysql_close()
?>

and when i go to test it out i get an error

Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ':11:{i:0;s:3:"joe";i:1;s:4:"mike";i:2;s:4:"juan";i:3;s:2:"ev";i:4;s:4:"pete";i:5' at line 1

I took a look at line 1 and what exist there is my include for the server info.
Anyone know why im getting this error?

[Charleh]

Couple of things - the INSER INTO players () statement should have column names not PHP variables.

PHP replaces any $varname in a string with the variables content

So take off the $s on that first bit as it should be the column names

Also change the VALUES bit for each one to say
$_POST["player_name"] not $_POST[$player_name]

So it should be

$sql="INSERT INTO players (player_name, position_id, team_id)
VALUES ('$_POST["player_name"]','$_POST["position_id"]','$_POST["team_id"]')";

Also I'd probably use concatenation to be safe :) but either should work

$sql="INSERT INTO players (player_name, position_id, team_id)
VALUES ('".$_POST["player_name"]."','".$_POST["position_id"]".','".$_POST["team_id"]".')";

[koolkrasher]

Couple of things - the INSER INTO players () statement should have column names not PHP variables.

PHP replaces any $varname in a string with the variables content

So take off the $s on that first bit as it should be the column names

Also change the VALUES bit for each one to say
$_POST["player_name"] not $_POST[$player_name]

So it should be

$sql="INSERT INTO players (player_name, position_id, team_id)
VALUES ('$_POST["player_name"]','$_POST["position_id"]','$_POST["team_id"]')";

Also I'd probably use concatenation to be safe :) but either should work

$sql="INSERT INTO players (player_name, position_id, team_id)
VALUES ('".$_POST["player_name"]."','".$_POST["position_id"]".','".$_POST["team_id"]".')";


thanks for the help Charleh. when i introduced the new changes it only took 1 of the form fields and put it into the db not the entire form.

[JPhilS]

Hi to all webmasters!

i'm totally stuck in my project. I am making a school project about on how to save records of the students in a certain subject.

I'm almost there, but when I am now ready to save the records, I got this problem:

first, I have to view all students under the certain subject and at the last field, I putted a form to put the scores of each student.

say '<input type=text name='score' size=10>' and number of 'input' it is equal to the number of students coz it is a loop.
I use the primary key of the students(IdNo) to be inserted at in the first field of the table(studScore).
this is the problem, i want to insert the score of each student in the second field of the said table.
I am thinking that maybe it deals with array, but i really don't know how to formulate it.

The solution to this problem would be my key in finishing this project.
Hope you will reply. Plz.

Thanks in advance.:)

God Speed!

heres my sample code:

////ADDING SCORE
$addscore=$_GET['score'];
if($addscore){
?>
<table border=1 bgcolor=#E7EBED width=829>
<form action='<? PHP_SELF; ?>' method=post>
<tr align=center>
<td colspan=8><font face="Tahoma" size="2" color=00000><b> Result/s</b></font> </td>
</tr>
<tr align=center>
<td colspan=8>&nbsp;</td>
</tr>
<?
}
$control=$_GET['subControl'];
$result = mysql_query( "SELECT students.IDNO,students.FIRSTNAME,students.LASTNAME ,students.MI,students.GENDER,students.COURSE,stude nts.YEAR FROM students,subjects,subjecttrans WHERE subjects.SUB_CONTROL=subjecttrans.SUBCONTROL AND subjecttrans.SUBCONTROL='$control' AND subjecttrans.IDNO=students.IDNO ORDER BY students.LASTNAME" ) or die ("SELECT Error: ".mysql_error());
$num_rows=mysql_num_rows($result);
//start if
if($num_rows > 0){
?>

<tr>
<th><font face="Tahoma" size="2" color=CC3300><b>Id No.</b></font> </th>
<th align=center><font face="Tahoma" size="2" color=CC3300>Students' Name</b></font> </th>
<th><font face="Tahoma" size="2" color=CC3300>Gender</b></font> </th>
<th><font face="Tahoma" size="2" color=CC3300>Course</b></font> </th>
<th><font face="Tahoma" size="2" color=CC3300>Year</b></font> </th>
<th><font face="Tahoma" size="2" color=CC3300>Add Score</b></font> </th>
</tr>
<tr align=center>
<td colspan=8>&nbsp;</td>
</tr>
<form action="<? PHP_SELF; ?>" method=post>

<? $i=0;
$total=0;
while ($i < $num_rows) {

?>
<tr height=15 class=off onmouseover="this.className='on'" onmouseout="this.className='off'">

<?

$id=mysql_result($result,$i,"IDNO");
$fn=mysql_result($result,$i,"FIRSTNAME");
$ln=mysql_result($result,$i,"LASTNAME");
$mi=mysql_result($result,$i,"MI");
$gen=mysql_result($result,$i,"GENDER");
$cors=mysql_result($result,$i,"COURSE");
$yer=mysql_result($result,$i,"YEAR");
?>
<td>&nbsp;<font face="Tahoma" size="2" color=666622><? echo $id; ?></font></td>
<td align=left>&nbsp;<font face="Tahoma" size="2" color=666622><? echo strtoupper($ln.","." ".$fn." ".$mi); ?> </font> </td>
<td>&nbsp;<font face="Tahoma" size="2" color=666622><? echo substr($gen,0,1); ?></font></td>
<td>&nbsp;<font face="Tahoma" size="2" color=666622><? echo strtoupper($cors); ?></font></td>
<td>&nbsp;<font face="Tahoma" size="2" color=666622><? echo strtoupper($yer); ?></font></td>
<td>&nbsp;<font face="Tahoma" size="2" color=666622><input name="score" type="text" id="score[]"></td>


<?

$i++;


}

?>
</tr>
<tr height=20>
<td colspan=8 align=center>&nbsp;<font face="Tahoma" color=666666 size="2">****Nothing Follows****</font></td>

</tr>
<tr>
<td align=right>&nbsp;</td>
<td align=right>&nbsp;</td>
<td align=right>&nbsp;</td>
<td align=right>&nbsp;</td>
<td align=right>&nbsp;</td>
<td align=center><input size=40 type="submit" value="Save Checking" name="add" class="submit" /></td>
</tr>
</form>

</table>

[mbeards]

koolkrasher,

There are a number of ways to accomplish what you're after. Here's one solution:

<?php
// You would probably get the position data from a database
$positions = array(1 => "Manager",
2 => "Pitcher",
3 => "Catcher",
4 => "1st Base",
5 => "2nd Base",
6 => "Short Stop",
7 => "3rd Base",
8 => "Left Field",
9 => "Center Field",
10 => "Right Field",
11 => "Designated Hitter");

if (!empty($_POST))
{
// Assuming team id is a positive number and is required
if (!empty($_POST['team_id']) && is_numeric($_POST['team_id']))
{
$sql = "INSERT INTO players (player_name, position_id, team_id) VALUES ('%s', %d, %d)";
foreach ($_POST['player'] as $player)
{
//echo "Player Name: '" . $player['name']. "'<br />";
//echo "Position: '" . $player['position'] . "'<br>";
if (!empty($player['name']) && !empty($player['position']))
{
mysql_query(sprintf($sql, mysql_real_escape_string($player['name']), mysql_real_escape_string($player['position']), $_POST['team_id']));
}
}
}
}
?>
<html>
<head></head>
<body>
<form method="post" action="<?php echo $_SERVER['PHP_SELF']; ?>">
Team ID <input type="text" name="team_id" value="" /><br />
<?php
// How many form inputs do we show?
$total_players = 11;
for ($n = 0; $n < $total_players; $n++) { ?>

<p>Player <?php echo $n; ?> <input type="text" name="player[<?php echo $n; ?>][name]" value="" />
<select name="player[<?php echo $n; ?>][position]">
<option value="">Position</option>
<?php foreach ($positions as $pos_id => $position) { ?>
<option value="<?php echo $pos_id; ?>"><?php echo $position; ?></option>
<?php } ?>

</select></p>

<?php } ?>

<input type="submit" name="submit" value="Submit" />
</form>
</body>
</html>