Multiple rows added to mysql with php

[patsman77]

Hello All,

I am having a frustrating time trying to figure this out.

I have a form, in which I want to have fields WEEK, ID, FAVORITE, UNDERDOG, SPREAD, FSCORE, USCORE, and TIME.

The form is to have 16 rows to be entered at one time, instead of entering 1 at a time. I can get it to 1 record at a time easily enough. How do you get all 20 to update or insert? Do you have to use an array in this situation? The FAVORITE and UNDERDOG fields are from a drop down menu where you make a selection. Here is a sample of code:

<form action="database77.php" method="POST">
Enter current week: 
<Select size="1" name="Week" >
<option value="None" <? if($pass[Week]== "None"){ echo "selected"; }  ?>>Week</option>
<option <? if($pass[Week]== "01" ) { echo "selected"; } ?>>01</option>
<option <? if($pass[Week]== "02" ) { echo "selected"; } ?>>02</option>
<option <? if($pass[Week]== "03" ) { echo "selected"; } ?>>03</option>
<option <? if($pass[Week]== "04" ) { echo "selected"; } ?>>04</option>
<option <? if($pass[Week]== "05" ) { echo "selected"; } ?>>05</option>
<option <? if($pass[Week]== "06" ) { echo "selected"; } ?>>06</option>
<option <? if($pass[Week]== "07" ) { echo "selected"; } ?>>07</option>
<option <? if($pass[Week]== "08" ) { echo "selected"; } ?>>08</option>
<option <? if($pass[Week]== "09" ) { echo "selected"; } ?>>09</option>
<option <? if($pass[Week]== "10" ) { echo "selected"; } ?>>10</option>
<option <? if($pass[Week]== "11" ) { echo "selected"; } ?>>11</option>
<option <? if($pass[Week]== "12" ) { echo "selected"; } ?>>12</option>
<option <? if($pass[Week]== "13" ) { echo "selected"; } ?>>13</option>
<option <? if($pass[Week]== "14" ) { echo "selected"; } ?>>14</option>
<option <? if($pass[Week]== "15" ) { echo "selected"; } ?>>15</option>
<option <? if($pass[Week]== "16" ) { echo "selected"; } ?>>16</option>
<option <? if($pass[Week]== "17" ) { echo "selected"; } ?>>17</option>
</select>
<br>
<p>
<li>
<input type="Number" name="ID" size="5" MAXLENGTH="4" value="<? if($pass[ID]!=""){ echo "$pass[ID]"; }  ?>" >
    <select size="1" name="Favorite">
        <option value="None"<? if($pass[Favorite]== "None"){ echo "selected"; } ?>>Favorite</option>
        <option <? if($pass[Favorite]== "Atlanta"){ echo "selected"; } ?>>Atlanta</option>
        <option <? if($pass[Favorite]== "Arizona"){ echo "selected"; } ?>>Arizona</option>
        <option <? if($pass[Favorite]== "Baltimore"){ echo "selected"; } ?>>Baltimore</option>
        <option <? if($pass[Favorite]== "Buffalo"){ echo "selected"; } ?>>Buffalo</option>
        <option <? if($pass[Favorite]== "Carolina"){ echo "selected"; } ?>>Carolina</option>
        <option <? if($pass[Favorite]== "Chicago"){ echo "selected"; } ?>>Chicago</option>
        <option <? if($pass[Favorite]== "Cincinnati"){ echo "selected"; } ?>>Cincinnati</option>
        <option <? if($pass[Favorite]== "Cleveland"){ echo "selected"; } ?>>Cleveland</option>
        <option <? if($pass[Favorite]== "Dallas"){ echo "selected"; } ?>>Dallas</option>
        <option <? if($pass[Favorite]== "Denver"){ echo "selected"; } ?>>Denver</option>
        <option <? if($pass[Favorite]== "Detroit"){ echo "selected"; } ?>>Detroit</option>
        <option <? if($pass[Favorite]== "Green Bay"){ echo "selected"; } ?>>Green Bay</option>
        <option <? if($pass[Favorite]== "Houston"){ echo "selected"; } ?>>Houston</option>
        <option <? if($pass[Favorite]== "Indianapolis"){ echo "selected"; } ?>>Indianapolis</option>
        <option <? if($pass[Favorite]== "Jacksonville"){ echo "selected"; } ?>>Jacksonville</option>
        <option <? if($pass[Favorite]== "Kansas City"){ echo "selected"; } ?>>Kansas City</option>
        <option <? if($pass[Favorite]== "Miami"){ echo "selected"; } ?>>Miami</option>
        <option <? if($pass[Favorite]== "Minnesota"){ echo "selected"; } ?>>Minnesota</option>
        <option <? if($pass[Favorite]== "New England"){ echo "selected"; } ?>>New England</option>
        <option <? if($pass[Favorite]== "New Orleans"){ echo "selected"; } ?>>New Orleans</option>
        <option <? if($pass[Favorite]== "NY Giants"){ echo "selected"; } ?>>NY Giants</option>
        <option <? if($pass[Favorite]== "NY Jets"){ echo "selected"; } ?>>NY Jets</option>
        <option <? if($pass[Favorite]== "Oakland"){ echo "selected"; } ?>>Oakland</option>
        <option <? if($pass[Favorite]== "Philadelphia"){ echo "selected"; } ?>>Philadelphia</option>
        <option <? if($pass[Favorite]== "Pittsburgh"){ echo "selected"; } ?>>Pittsburgh</option>
        <option <? if($pass[Favorite]== "San Diego"){ echo "selected"; } ?>>San Diego</option>
        <option <? if($pass[Favorite]== "San Francisco"){ echo "selected"; } ?>>San Francisco</option>
        <option <? if($pass[Favorite]== "Seattle"){ echo "selected"; } ?>>Seattle</option>
        <option <? if($pass[Favorite]== "St.Louis"){ echo "selected"; } ?>>St.Louis</option>
        <option <? if($pass[Favorite]== "Tampa Bay"){ echo "selected"; } ?>>Tampa Bay</option>
        <option <? if($pass[Favorite]== "Tennessee"){ echo "selected"; } ?>>Tennessee</option>
        <option <? if($pass[Favorite]== "Washington"){ echo "selected"; } ?>>Washington</option>
</select>
 
 
    <select size="1" name="Underdog">
        <option value="None"<? if($pass[Underdog]== "None"){ echo "selected"; } ?>>Underdog</option>
        <option <? if($pass[Underdog]== "Atlanta"){ echo "selected"; } ?>>Atlanta</option>
        <option <? if($pass[Underdog]== "Arizona"){ echo "selected"; } ?>>Arizona</option>
        <option <? if($pass[Underdog]== "Baltimore"){ echo "selected"; } ?>>Baltimore</option>
        <option <? if($pass[Underdog]== "Buffalo"){ echo "selected"; } ?>>Buffalo</option>
        <option <? if($pass[Underdog]== "Carolina"){ echo "selected"; } ?>>Carolina</option>
        <option <? if($pass[Underdog]== "Chicago"){ echo "selected"; } ?>>Chicago</option>
        <option <? if($pass[Underdog]== "Cincinnati"){ echo "selected"; } ?>>Cincinnati</option>
        <option <? if($pass[Underdog]== "Cleveland"){ echo "selected"; } ?>>Cleveland</option>
        <option <? if($pass[Underdog]== "Dallas"){ echo "selected"; } ?>>Dallas</option>
        <option <? if($pass[Underdog]== "Denver"){ echo "selected"; } ?>>Denver</option>
        <option <? if($pass[Underdog]== "Detroit"){ echo "selected"; } ?>>Detroit</option>
        <option <? if($pass[Underdog]== "Green Bay"){ echo "selected"; } ?>>Green Bay</option>
        <option <? if($pass[Underdog]== "Houston"){ echo "selected"; } ?>>Houston</option>
        <option <? if($pass[Underdog]== "Indianapolis"){ echo "selected"; } ?>>Indianapolis</option>
        <option <? if($pass[Underdog]== "Jacksonville"){ echo "selected"; } ?>>Jacksonville</option>
        <option <? if($pass[Underdog]== "Kansas City"){ echo "selected"; } ?>>Kansas City</option>
        <option <? if($pass[Underdog]== "Miami"){ echo "selected"; } ?>>Miami</option>
        <option <? if($pass[Underdog]== "Minnesota"){ echo "selected"; } ?>>Minnesota</option>
        <option <? if($pass[Underdog]== "New England"){ echo "selected"; } ?>>New England</option>
        <option <? if($pass[Underdog]== "New Orleans"){ echo "selected"; } ?>>New Orleans</option>
        <option <? if($pass[Underdog]== "NY Giants"){ echo "selected"; } ?>>NY Giants</option>
        <option <? if($pass[Underdog]== "NY Jets"){ echo "selected"; } ?>>NY Jets</option>
        <option <? if($pass[Underdog]== "Oakland"){ echo "selected"; } ?>>Oakland</option>
        <option <? if($pass[Underdog]== "Philadelphia"){ echo "selected"; } ?>>Philadelphia</option>
        <option <? if($pass[Underdog]== "Pittsburgh"){ echo "selected"; } ?>>Pittsburgh</option>
        <option <? if($pass[Underdog]== "San Diego"){ echo "selected"; } ?>>San Diego</option>
        <option <? if($pass[Underdog]== "San Francisco"){ echo "selected"; } ?>>San Francisco</option>
        <option <? if($pass[Underdog]== "Seattle"){ echo "selected"; } ?>>Seattle</option>
        <option <? if($pass[Underdog]== "St.Louis"){ echo "selected"; } ?>>St.Louis</option>
        <option <? if($pass[Underdog]== "Tampa Bay"){ echo "selected"; } ?>>Tampa Bay</option>
        <option <? if($pass[Underdog]== "Tennessee"){ echo "selected"; } ?>>Tennessee</option>
        <option <? if($pass[Underdog]== "Washington"){ echo "selected"; } ?>>Washington</option>
</select>
 
 
 
 <input type="Number" name="Spread" size="5" MAXLENGTH="4" value="<? if($pass[Spread]!=""){ echo "$pass[Spread]"; }  ?>" >
 <input type="Number" name="FScore" size="5" MAXLENGTH="2" value="<? if($pass[FScore]!=""){ echo "$pass[FScore]"; }  ?>" >
 <input type="Number" name="UScore" size="5" MAXLENGTH="2" value="<? if($pass[UScore]!=""){ echo "$pass[UScore]"; }  ?>" >
 <Select size="1" name="Time" >
<option value="None" <? if($pass[Time]== "None"){ echo "selected"; }  ?>>Time</option>
<option <? if($pass[Time]== "13:00:00" ) { echo "selected"; } ?>>13:00:00</option>
<option <? if($pass[Time]== "16:00:00" ) { echo "selected"; } ?>>16:00:00</option>
<option <? if($pass[Time]== "20:00:00" ) { echo "selected"; } ?>>20:00:00</option>
<option <? if($pass[Time]== "20:30:00" ) { echo "selected"; } ?>>20:30:00</option>
</select>
 

From here, what would be the best way to go? I am fairly new to PHP/MYSQL, and am learning as I go.....

Thanx for any input,

Patsman77

[pbmods]

Heya, Patsman. Welcome to TSDN!

I'm not sure I follow. Are you trying to output a form with controls to update multiple rows, or do you already have the form set up and are trying to process the input?

[patsman77]

Thanx for the welcome!! I am honored! I have done so much reading on this, I felt I needed to see if my case was unique....

I created the form shown in example, which is only 1 record submitted at a time. I want to be able to have 16 record entries at 1 time... where I can put 1 weeks worth of games into the form and submit it. The only method I could do so far is to have different name values, like Favorite, Favorite2, Favorite3..... but it gets to point that I would have over 100 fields in my database, where I just want to have the listed fileds in example, where each record would have a different ID. So my DB table would look like:

GameID | Week | Favorite | Underdog | Spread | Time
01 01 Seattle Miami 7 13:00:00
02 01 Buffalo Detroit 4.5 16:00:00


How it would look in DB table is how I would want my form to look...where I would enter the information, then update it to DB.

Patsman77

[code green]

I'm afraid you have lost me also, but it might just be the awful American sport you are referring to. Is it baseball?

I want to be able to have 16 record entries at 1 time

Do you mean insert 16 records into the DB? Yes that is no problem

where I can put 1 weeks worth of games into the form and submit it

You have to place the data somewhere unless it is generated by code.

The only method I could do so far is to have different name values, like Favorite, Favorite2, Favorite3..... but it gets to point that I would have over 100 fields in my database,

Nope, lost me there. If this is the closest you get can you provide the code you attempted.
It may help to see what you are trying to do.

[patsman77]

Hello and thanx for replying back on my message. Hopefully I can make it a little clearer this time around....hehe

First off, this is for Football. There are 13-16 games played weekly.

With the code I posted, that gives me a form that allows me to insert/update 1 record to the database. When I mentioned the 100 fields in database... I modified the form by adding 15 more sets of entries...where I have GID, FAVORITE, UNDERDOG, SPREAD...etc...I added GID2, FAVORITE2, UNDERDOG2, SPREAD2...etc, GID3, FAVORITE3...etc, GID4, FAVORITE4....etc..

So basically I had 16 "sets" that I would insert/update to the database.

This looks horrible in the database table. I would like to just have fields WEEK, GID( Primary key that seperates each game) FAVORITE, UNDERDOG, SPREAD...etc.without resorting to adding all the "duplicate" fields with the 2, 3, 4 ,5..etc.....
Here is the insert/update file I use:

$sel="select *from picksheet where Week='$_POST[Week]'";
$result=mysql_query($sel,$con);
$num = mysql_num_rows($result);
 
 
if ($num != 0)
{
 
        $up="UPDATE picksheet set  Week='$_POST[Week]', Favorite='$_POST[Favorite]',  Underdog='$_POST[Underdog]', Spread='$_POST[Spread]', FScore='$_POST[FScore]', UScore='$_POST[UScore]', Time='$_POST[Time]',  Favorite2='$_POST[Favorite2]',  Underdog2='$_POST[Underdog2]', Spread2='$_POST[Spread2]', FScore2='$_POST[FScore2]', UScore2='$_POST[UScore2]', Time2='$_POST[Time2]' where Week='$_POST[Week]'";
 
        if (!mysql_query($up,$con))
        {
                die('Error: ' . mysql_error());
        }
 
}
else
{
$sql="INSERT INTO picksheet (  Week, Favorite, Underdog, Spread, FScore, UScore, Time,  Favorite2, Underdog2, Spread2, FScore2, UScore2, Time2 )
VALUES
('$_POST[Week]','$_POST[Favorite]', '$_POST[Underdog]','$_POST[Spread]','$_POST[FScore]','$_POST[UScore]','$_POST[Time]','$_POST[Favorite2]','$_POST[Underdog2]', '$_POST[Spread2]','$_POST[FScore2]','$_POST[UScore2]','$_POST[Time2]' )";
if (!mysql_query($sql,$con))
  {
        die('Error: ' . mysql_error());
  }
 
}
 
 
 
 
$link="";
foreach ($_REQUEST as $key => $value) {
    $link.=$key . "=" . $value . "&";
}
mysql_close($con);
 
         exit;
 

This insert/update is a test code that would do 1 record in databse with about 13 fields...the GID is not included here... I have so many test versions...ughh...

Hopefully you can direct me towards a better solution. From what I have been reading, it seems that an array is what is needed??

Thanx,
Patsman77

[pbmods]

Heya, Patsman.

Please use CODE tags when posting source code:

[CODE=php]
PHP code goes here.
[/CODE]

[code green]

First off, this is for Football

No Brazill and Italy play football. Manchester United play football.
This is a strange game we call American Football.

With the code I posted, that gives me a form that allows me to insert/update 1 record to the database. When I mentioned the 100 fields in database... I modified the form by adding 15 more sets of entries
So basically I had 16 "sets" that I would insert/update to the database.

I get the overall impression that you have designed a data entry form
followed by a database table to accept the data.
Do not do it this way. Design the database first. This is the critical component.
The data entry form is trivial

This looks horrible in the database table. I would like to just have fields WEEK, GID( Primary key that seperates each game) FAVORITE, UNDERDOG, SPREAD...etc.without resorting to adding all the "duplicate" fields with the 2, 3, 4 ,5..etc.....

Exactly, so design the database based on the data required, not on what is coming from a web page.
Can I suggest we forget about input fields and arrays for now and discuss this
Once we get that right, everything else will fall into place much easier.
Please post your DB design or your ideal DB design.

[patsman77]

You guys are great working on this with me!!!

Here is the best Database design I would Like to have:

CREATE TABLE `football` (
`GameID` INT( 4 ) NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`Week` INT( 2 ) NOT NULL ,
`Favorite` VARCHAR( 50 ) NOT NULL ,
`Underdog` VARCHAR( 50 ) NOT NULL ,
`Spread` DECIMAL( 3 ) NOT NULL ,
`FavScore` INT( 2 ) NOT NULL ,
`UndScore` INT( 2 ) NOT NULL ,
`Time` TIME NOT NULL 
) TYPE = MYISAM ;

I hope this gets us going in the right direction...

Thanx,
Patsman77

[code green]

This is known as a flat file design but looks suitable for your purpose.
There are up to 16 games with various options for each game and you wish to represent these options as fields in a web form.
I can't see a way around having 16 x options on your form,
but this would mean a laborious data input task for the user.
But to transfer these 16 blocks of data into the database
is pretty straightforward.
You would end up with every game being entered as one record in the table.
Am I on the right lines here?

[patsman77]

I think you are right on....Now that you see the database setup, here is the form setup...

There would be 1 option to choose the Week that is being played.

Then there would be 16 "sets" to be filled in by myself(admin) with the fields:

GameID, Favorite, Underdog, Spread, FavScore, UndScore, and Time.

Then the Submit button.

From there it would goto an insert.php file to either insert or update based on the Week I am entering. I will enter the Favorite, Underdog, and Spread at beginning of week, and then Update with the scores at end of week. I believe this will be something where I will have to manually update the form page so that it pulls the right week with my initial Select statement on the form page.

GameID, i am not sure if that is something I would have to fill in or just let the database do with the auto increment...isnt that what the auto increment does?

Hopefully you are understanding where this stands and can direct me in the proper way to create form... with arrays? and if arrays, the insert file would also have to be modified to that? Am I right in what I am thinking?

Thanx again for all the help!!!

Patsman77

[patsman77]

I may have misread your reply. I am not looking to add all 16 games to 1 record, but 1 game per record, with the total being upto 16 records, using the GameID as the primary that seperates them all.

Patsman77

[code green]

You are on the right lines with this query
[PHP]"INSERT INTO picksheet (Week, Favorite, Underdog, Spread, FScore, UScore, Time, Favorite2, Underdog2, Spread2, FScore2, UScore2, Time2 )
VALUES
('$_POST[Week]','$_POST[Favorite]', '$_POST[Underdog]','$_POST[Spread]','$_POST[FScore]','$_POST[UScore]','$_POST[Time]','$_POST[Favorite2]','$_POST[Underdog2]', '$_POST[Spread2]','$_POST[FScore2]','$_POST[UScore2]','$_POST[Time2]' )";[/PHP]But the query format to insert multiple records is

INSERT INTO table (list,of,field,names) VALUES (a1,b1,c1,d1),(a2,b2,c2,d2),(a3,b3,c3,d3) etc

This example will post two records in the relevant fields.

INSERT INTO picksheet (Week, Favorite, Underdog, Spread, FScore, UScore, Time)
VALUES
('$_POST[Week]','$_POST[Favorite1]', '$_POST[Underdog1]','$_POST[Spread1]','$_POST[FScore1]','$_POST[UScore1]','$_POST[Time1]'),
('$_POST[Week]','$_POST[Favorite2]','$_POST[Underdog2]', '$_POST[Spread2]','$_POST[FScore2]','$_POST[UScore2]','$_POST[Time2]')

I think this is the query you are looking for.
But there is a lot of scope to minimise the code creating the form,
thereby eliminating repititive code and minimising errors.
Think about creating an array of all the team names
then looping through the array, dynamically populating the drop down option box.