Form Post to DATABASE from MySQL Query output

Question

ceeandcee 0 Junior Poster in Training

14 Years Ago

I am learning mySQL and PHP and am very appreciative of the help I have received here. As some of you know, I am working on a Hockey Pool site.

One of my first experiments was to build a static form on a php page that would take the input of the user and enter it into the database. I had success with that project.

Now, I want to build to run a query that will yield a table based on the query limitations. In this particular example I want players from a specific NHL team to show from my database. In my form, I want to show what the current value of the field is in the database and leave a input box to update that number. You can see my progess here:

http://www.cornwallfantasyhockey.com/data/mtl/Index.php

I can build the table and the input boxes, yet when I submit new data, it doesnt update the existing values. I think it is because it is not drawing an association from the PRIMARY KEY or in my case [playerID]. Can anybody please provide some guidance as to how to proceed?

Here is my code

<form method="post" action="<? echo $_SERVER['PHP_SELF']; ?>"> 

<center>
<img src="/images/data_entry_mtl.png" width="625" height="100" border="0" alt="Data Entry Title">


<!--Team Roster table starts here (TMI) -->
 

 <?php
// Make a MySQL Connection
mysql_connect("localhost", "user", "password") or die(mysql_error());
mysql_select_db("playerdb") or die(mysql_error());

// Get all the data from the "player" table
error_reporting (E_ERROR);

$result = mysql_query("SELECT * FROM stats1011 where Team = 'MTL' order by field(pos,'LW','C','RW','D','G'), last")
or die(mysql_error());  

echo "<table width='870' border='1' cellspacing='0' cellpadding='2' bgcolor='ffffff'>";
echo "<tr> 
<td colspan='3' align='center' bgcolor='000000'><font face='arial' size='2' color='ffffff'><b>Player Info</b></td>
<td colspan='4' align='center' bgcolor='CC0000'><font face='arial' size='2' color='ffffff'><b>Week #1</b></td>
<td colspan='4' align='center' bgcolor='FFFF00'><font face='arial' size='2' color='000000'><b>Week #2</b></td>
<td colspan='4' align='center' bgcolor='CC0000'><font face='arial' size='2' color='ffffff'><b>Week #3</b></td>
<td colspan='4' align='center' bgcolor='FFFF00'><font face='arial' size='2' color='000000'><b>Week #4</b></td>
<td colspan='3' align='center' bgcolor='000000'><font face='arial' size='2' color='ffffff'><b>TOTALS</b></td>
</tr>";
echo "<tr> 
<td width='5' bgcolor='000000' align='left'><font face='arial' size='1' color='FFFFFF'><b>Pos</b></td>
<td width='10' bgcolor='000000' align='center'><font face='arial' size='1' color='FFFFFF'><b>#</b></td>
<td width='170' bgcolor='000000' align='center'><font face='arial' size='1' color='FFFFFF'><b>PLAYER</b></td>
<td width='20' bgcolor='000000' align='center'><font face='arial' size='1' color='FFFFFF'><b>GP</b></td>
<td width='2' bgcolor='000000' align='center'><font face='arial' size='1' color='FFFFFF'><b>GP</b></td>
<td width='20' bgcolor='000000' align='center'><font face='arial' size='1' color='FFFFFF'><b>PTS</b></td>
<td width='2' bgcolor='000000' align='center'><font face='arial' size='1' color='FFFFFF'><b>PTS</b></td>
<td width='20' bgcolor='000000' align='center'><font face='arial' size='1' color='FFFFFF'><b>GP</b></td>
<td width='2' bgcolor='000000' align='center'><font face='arial' size='1' color='FFFFFF'><b>GP</b></td>
<td width='20' bgcolor='000000' align='center'><font face='arial' size='1' color='FFFFFF'><b>PTS</b></td>
<td width='2' bgcolor='000000' align='center'><font face='arial' size='1' color='FFFFFF'><b>PTS</b></td>
<td width='20' bgcolor='000000' align='center'><font face='arial' size='1' color='FFFFFF'><b>GP</b></td>
<td width='2' bgcolor='000000' align='center'><font face='arial' size='1' color='FFFFFF'><b>GP</b></td>
<td width='20' bgcolor='000000' align='center'><font face='arial' size='1' color='FFFFFF'><b>PTS</b></td>
<td width='2' bgcolor='000000' align='center'><font face='arial' size='1' color='FFFFFF'><b>PTS</b></td>
<td width='20' bgcolor='000000' align='center'><font face='arial' size='1' color='FFFFFF'><b>GP</b></td>
<td width='2' bgcolor='000000' align='center'><font face='arial' size='1' color='FFFFFF'><b>GP</b></td>
<td width='20' bgcolor='000000' align='center'><font face='arial' size='1' color='FFFFFF'><b>PTS</b></td>
<td width='2' bgcolor='000000' align='center'><font face='arial' size='1' color='FFFFFF'><b>PTS</b></td>
<td width='30' bgcolor='000000' align='center'><font face='arial' size='1' color='FFFFFF'><b>GP</b></td>
<td width='30' bgcolor='000000' align='center'><font face='arial' size='1' color='FFFFFF'><b>PTS</b></td>
<td width='30' bgcolor='000000' align='center'><font face='arial' size='1' color='FFFFFF'><b>PPG</b></td>
</tr>";


// keeps getting the next row until there are no more to get
while($row = mysql_fetch_array( $result )) {

$GP1011=$row['GP1011_wk1']+$row['GP1011_wk2']+$row['GP1011_wk3']+$row['GP1011_wk4']+$row['GP1011_wk5']+$row['GP1011_wk6']+$row['GP1011_wk7']+$row['GP1011_wk8']+$row['GP1011_wk9']+$row['GP1011_wk10']+$row['GP1011_wk11']+$row['GP1011_wk12']+$row['GP1011_wk13']+$row['GP1011_wk14']+$row['GP1011_wk15']+$row['GP1011_wk16']+$row['GP1011_wk17']+$row['GP1011_wk18']+$row['GP1011_wk19']+$row['GP1011_wk20']+$row['GP1011_wk21']+$row['GP1011_wk22']+$row['GP1011_wk23']+$row['GP1011_wk24']+$row['GP1011_wk25']+$row['GP1011_wk26'];
	$PTS1011=$row['PTS1011_wk1']+$row['PTS1011_wk2']+$row['PTS1011_wk3']+$row['PTS1011_wk4']+$row['PTS1011_wk5']+$row['PTS1011_wk6']+$row['PTS1011_wk7']+$row['PTS1011_wk8']+$row['PTS1011_wk9']+$row['PTS1011_wk10']+$row['PTS1011_wk11']+$row['PTS1011_wk12']+$row['PTS1011_wk13']+$row['PTS1011_wk14']+$row['PTS1011_wk15']+$row['PTS1011_wk16']+$row['PTS1011_wk17']+$row['PTS1011_wk18']+$row['PTS1011_wk19']+$row['PTS1011_wk20']+$row['PTS1011_wk21']+$row['PTS1011_wk22']+$row['PTS1011_wk23']+$row['PTS1011_wk24']+$row['PTS1011_wk25']+$row['PTS1011_wk26'];
	
	
	// Print out the contents of each row into a table
	echo "<tr><td width='5' bgcolor='FFFFFF' align='center'><font face='arial' size='1' color='000000'>"; 
	echo $row['Pos'];
	
	echo "</td><td width='10' bgcolor='FFFFFF' align='center'><font face='arial' size='1' color='000000'>";
	echo $row['No'];
	
	echo "</td><td width='170' bgcolor='FFFFFF' align='left'><font face='arial' size='2' color='000000'>"; 
	echo $row['Last'];
	echo ", <font face='arial' size='1' color='000000'>";
	echo $row['First'];
	
	echo "</td><td width='20' bgcolor='CCCCCC' align='center'><font face='arial' size='2' color='000000'>"; 
	echo $row['GP1011_wk1'];
	
	echo "</td><td width='2' bgcolor='CCCCCC' align='center'><font face='arial' size='2' color='000000'>"; 
	echo "<input type='text' size='2' name='PTS1011_wk1'>";
	
	echo "</td><td width='20' bgcolor='CCCCCC' align='center'><font face='arial' size='2' color='000000'>"; 
	echo $row['PTS1011_wk1'];
	
	echo "</td><td width='2' bgcolor='CCCCCC' align='center'><font face='arial' size='2' color='000000'>"; 
	echo "<input type='text' size='2' name='PTS1011_wk1'>";
	
	echo "</td><td width='20' bgcolor='FFFF99' align='center'><font face='arial' size='2' color='000000'>"; 
	echo $row['GP1011_wk2'];
	
	echo "</td><td width='2' bgcolor='FFFF99' align='center'><font face='arial' size='2' color='000000'>"; 
	echo "<input type='text' size='2' name='GP1011_wk2'>";
	
	echo "</td><td width='20' bgcolor='FFFF99' align='center'><font face='arial' size='2' color='000000'>"; 
	echo $row['PTS1011_wk2'];
	
	echo "</td><td width='2' bgcolor='FFFF99' align='center'><font face='arial' size='2' color='000000'>"; 
	echo "<input type='text' size='2' name='PTS1011_wk2'>";
		
	echo "</td><td width='20' bgcolor='CCCCCC' align='center'><font face='arial' size='2' color='000000'>"; 
	echo $row['GP1011_wk3'];
	
	echo "</td><td width='2' bgcolor='CCCCCC' align='center'><font face='arial' size='2' color='000000'>"; 
	echo "<input type='text' size='2' name='GP1011_wk3'>";
	
	echo "</td><td width='20' bgcolor='CCCCCC' align='center'><font face='arial' size='2' color='000000'>"; 
	echo $row['PTS1011_wk3'];
	
	echo "</td><td width='2' bgcolor='CCCCCC' align='center'><font face='arial' size='2' color='000000'>"; 
	echo "<input type='text' size='2' name='PTS1011_wk3'>";
	
	echo "</td><td width='20' bgcolor='FFFF99' align='center'><font face='arial' size='2' color='000000'>"; 
	echo $row['GP1011_wk4'];
	
	echo "</td><td width='2' bgcolor='FFFF99' align='center'><font face='arial' size='2' color='000000'>"; 
	echo "<input type='text' size='2' name='GP1011_wk4'>";
	
	echo "</td><td width='20' bgcolor='FFFF99' align='center'><font face='arial' size='2' color='000000'>"; 
	echo $row['PTS1011_wk4'];
	
	echo "</td><td width='2' bgcolor='FFFF99' align='center'><font face='arial' size='2' color='000000'>"; 
	echo "<input type='text' size='2' name='PTS1011_wk4'>";
		
	echo "</td><td width='30' bgcolor='666666' align='center'><font face='arial' size='2' color='ffffff'><b>";  
	echo $GP1011;
	
	echo "</td><td width='30' bgcolor='666666' align='center'><font face='arial' size='2' color='ffffff'><b>"; 
	echo $PTS1011;
	
	echo "</td><td width='30' bgcolor='666666' align='center'><font face='arial' size='2' color='ffffff'><b>"; 
	echo number_format($PTS1011/$GP1011,2); 
	echo "</td></tr>";
} 

echo "</table>";
?>
<br>
<input type="submit" name="Submit" value="Submit">
</form>


<?php 
mysql_connect ("localhost","user","password") or die ('Error: ' . mysql_error());
mysql_select_db ("playerdb")or die("cannot select DB");


$playerID=$_POST['playerID'];
$Pos=$_POST['Pos'];
$No=$_POST['No'];
$Last=$_POST['Last'];
$First=$_POST['First'];
$GP1011_wk1=$_POST['GP1011_wk1'];
$PTS1011_wk1=$_POST['PTS1011_wk1'];
$GP1011_wk2=$_POST['GP1011_wk2'];
$PTS1011_wk2=$_POST['PTS1011_wk2'];
$GP1011_wk3=$_POST['GP1011_wk3'];
$PTS1011_wk3=$_POST['PTS1011_wk3'];
$GP1011_wk4=$_POST['GP1011_wk4'];
$PTS1011_wk4=$_POST['PTS1011_wk4'];




if($_SERVER['REQUEST_METHOD']=='POST') {
$sql="INSERT INTO stats1010 (playerID, Pos, No, Last, First, GP1011_wk1, PTS1011_wk1, GP1011_wk2, PTS1011_wk2, GP1011_wk3, PTS1011_wk3, GP1011_wk4, PTS1011_wk4) VALUES ('".$playerID."','".$Pos."','".$No."','".$Last."','".$First."','".$GP1011_wk1."','".$PTS1011_wk1."','".$GP1011_wk2."','".$PTS1011_wk2."','".$GP1011_wk3."','".$PTS1011_wk3."','".$GP1011_wk4."','".$PTS1011_wk4."')";
$result=mysql_query($sql);
}
// if successfully insert data into database, displays message "Successful". 
if($result){
echo "<font face='arial' size='2' color='ffffff'>Successful"; 
echo "<BR>";
echo "<font face='arial' size='2' color='ffffff'>Database updated with: ".$playerID ;	
}

else {
echo "<font face='arial' size='2' color='ffffff'>ERROR";
}

// close connection 
mysql_close();
?>

Hoping someone can help!
Cheers,
Chris

mysql php

Edited 14 Years Ago by ceeandcee because: n/a

4 Contributors
15 Replies
158 Views
1 Month Discussion Span
Latest Post 14 Years Ago Latest Post by ceeandcee

hielo 65 Veteran Poster

14 Years Ago

If there is already Data in the field should I be using UPDATE instead of INSERT?

UPDATE

How do I specify which record/primary key is to update?

currently you are generating:

...
echo "<input type='text' size='2' name='PTS1011_wk1'>";
...
echo "<input type='text' size='2' name='GP1011_wk1'>";
...

for each player. I am assuming that playerID IS the unique key for every record. If that is the case then what you need to do is put that as an array index in the name of the field:

...
echo "<input type='text' size='2' name='PTS1011_wk1[" . $row['playerID'] . "]'>";
...
echo "<input type='text' size='2' name='GP1011_wk1[" . $row['playerID'] . "]'>";
...

which should generate something like:

...
<input type='text' size='2' name='PTS1011_wk1[7]'>
<input type='text' size='2'  name='GP1011_wk1[7]'>
<input type='text' size='2' name='PTS1011_wk2[7]'>
<input type='text' size='2'  name='GP1011_wk2[7]'>
<input type='text' size='2' name='PTS1011_wk3[7]'>
<input type='text' size='2'  name='GP1011_wk3[7]'>
<input type='text' size='2' name='PTS1011_wk4[7]'>
<input type='text' size='2'  name='GP1011_wk4[7]'>
...
<input type='text' size='2' name='PTS1011_wk1[21]'>
<input type='text' size='2'  name='GP1011_wk1[21]'>
<input type='text' size='2' name='PTS1011_wk2[21]'>
<input type='text' size='2'  name='GP1011_wk2[21]'>
<input type='text' size='2' name='PTS1011_wk3[21]'>
<input type='text' size='2'  name='GP1011_wk3[21]'>
<input type='text' size='2' name='PTS1011_wk4[21]'>
<input type='text' size='2'  name='GP1011_wk4[21]'>
...

where the 7 and 21 represent the unique id for each player per row. PHP will see an array SIMILAR to:

$_POST['PTS1011_wk1'] => array(7=>"a")
$_POST['PTS1011_wk2'] => array(7=>"b")
$_POST['PTS1011_wk3'] => array(7=>"c")
$_POST['PTS1011_wk4'] => array(7=>"d")

$_POST['PTS1011_wk1'] => array(21=>"e")
$_POST['PTS1011_wk2'] => array(21=>"f")
$_POST['PTS1011_wk3'] => array(21=>"g")
$_POST['PTS1011_wk4'] => array(21=>"h")

the same goes for the GP1011_wk# group. So if you want to get the value "a", you would need to get it from $_POST[7].

So, your "challenge" it so get that playerId (the 7) dynamically, but because of the array structure that php creates for you, that is easily accomplished with a foreach:

foreach($_POST['PTS1011_wk1'] as $playerId=>$v)
{
  $GP1011_wk1=mysql_real_escape_string($_POST['GP1011_wk1'][$playerId]);
  $PTS1011_wk1=mysql_real_escape_string($_POST['PTS1011_wk1'][$playerId]);
  $GP1011_wk2=mysql_real_escape_string($_POST['GP1011_wk2'][$playerId]);
  $PTS1011_wk2=mysql_real_escape_string($_POST['PTS1011_wk2'][$playerId]);
  $GP1011_wk3=mysql_real_escape_string($_POST['GP1011_wk3'][$playerId]);
  $PTS1011_wk3=mysql_real_escape_string($_POST['PTS1011_wk3'][$playerId]);
  $GP1011_wk4=mysql_real_escape_string($_POST['GP1011_wk4'][$playerId]);
  $PTS1011_wk4=mysql_real_escape_string($_POST['PTS1011_wk4'][$playerId]);

  //complete your update statement below
  mysql_query("UPDATE ... WHERE `playerID`=" . $playerId) or die( mysql_error() );
  echo "Update record for " . $playerId;
}

Stefano Mtangoo 455 Senior Poster

14 Years Ago

How do I specify which record/primary key is to update?

One note, when updating primary key, it MUST be Unique. I hope you know that but just reinforcing in case you don't know

hielo 65 Veteran Poster

14 Years Ago

try:

if( isset($_POST['Submit']) && $_POST['Submit']=='Submit' )
{
    foreach($_POST['GP1011_wk1'] as $playerID=>$v)
    {
        //initialize to empty string and add field=value only if something was submitted
        $update="";

        $GP1011_wk1=mysql_real_escape_string(  trim($_POST['GP1011_wk1'][$playerID]) );
        $PTS1011_wk1=mysql_real_escape_string( trim($_POST['PTS1011_wk1'][$playerID]) );
  

        if( !empty($GP1011_wk1) ) {
            $update.="`GP1011_wk1`='" . $GP1011_wk1 . "'";
        }

        if( !empty($PTS1011_wk1) )
        {
            $update.=",`PTS1011_wk1`='".$PTS1011_wk1."'";
        }

        if(!empty($update))
        {
            $update = 'UPDATE `stats1011` SET '. $update . " WHERE `playerID` ='".$playerID."'";
            mysql_query($update) or die( sprintf('Error @Line %d while trying to execute<br/>%s<br/>%s',__LINE__,$update, mysql_error() ) );
        }
    }

}
// Get all the data from the "player" table
error_reporting (E_ERROR);
$GETQ="SELECT
Sum(stats1011.GP1011_wk1+stats1011.GP1011_wk2+stats1011.GP1011_wk3+stats1011.GP1011_wk4+stats1011.GP1011_wk5+stats1011.GP1011_wk6+stats1011.GP1011_wk7+stats1011.GP1011_wk8+stats1011.GP1011_wk9+stats1011.GP1011_wk10+stats1011.GP1011_wk11+stats1011.GP1011_wk12+stats1011.GP1011_wk13+stats1011.GP1011_wk14+stats1011.GP1011_wk15+stats1011.GP1011_wk16+stats1011.GP1011_wk17+stats1011.GP1011_wk18+stats1011.GP1011_wk19+stats1011.GP1011_wk20+stats1011.GP1011_wk21+stats1011.GP1011_wk22+stats1011.GP1011_wk23+stats1011.GP1011_wk24+stats1011.GP1011_wk25+stats1011.GP1011_wk26) AS `GP_TTL_1011`,

Sum(stats1011.PTS1011_wk1+stats1011.PTS1011_wk2+stats1011.PTS1011_wk3+stats1011.PTS1011_wk4+stats1011.PTS1011_wk5+stats1011.PTS1011_wk6+stats1011.PTS1011_wk7+stats1011.PTS1011_wk8+stats1011.PTS1011_wk9+stats1011.PTS1011_wk10+stats1011.PTS1011_wk11+stats1011.PTS1011_wk12+stats1011.PTS1011_wk13+stats1011.PTS1011_wk14+stats1011.PTS1011_wk15+stats1011.PTS1011_wk16+stats1011.PTS1011_wk17+stats1011.PTS1011_wk18+stats1011.PTS1011_wk19+stats1011.PTS1011_wk20+stats1011.PTS1011_wk21+stats1011.PTS1011_wk22+stats1011.PTS1011_wk23+stats1011.PTS1011_wk24+stats1011.PTS1011_wk25+stats1011.PTS1011_wk26) AS `PTS_TTL_1011`,

`stats1011`.`Last`,
`stats1011`.`First`,
`stats1011`.`playerID`,
`stats1011`.`Pos`,
`stats1011`.`No`,
`stats1011`.`GP1011_wk1`,
`stats1011`.`PTS1011_wk1`,
`stats1011`.`Team`

FROM
`stats1011`

WHERE
`stats1011`.`Team` = 'tor'

GROUP BY
`stats1011`.`playerID`

ORDER BY
field(pos,'LW','C','RW','D','G'), last";


$result = mysql_query($GETQ)
or die(mysql_error());

ceeandcee commented: Thank you! +1

Stefano Mtangoo 455 Senior Poster

14 Years Ago

Thanks evsteve...this current code doesn't seem to work. It wont update the database. I am looking for help to get this to work and I think I am close.

I will advice you the best way to do these stuffs. Get your copy of MySQL workbench and connect to your local copy (remote site?) and test queries. Tweak them until they work. Transfer query to PHP. MWB have got syntax highlighting. You can also create view that acts as a table with all sums you need!

Edited 14 Years Ago by Stefano Mtangoo because: n/a

ceeandcee commented: Thank You! +1

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

ceeandcee 0 Junior Poster in Training · Answer 1 · 2010-08-28T23:33:14+00:00

I think I am close...to simplify my request let me ask these two questions?

If there is already Data in the field should I be using UPDATE instead of INSERT?

How do I specify which record/primary key is to update?

Huakalero 0 Newbie Poster · Answer 2 · 2010-08-29T23:03:37+00:00

I think all this needs some loops to simplify the code, otherwise people will scare and wont help much, like me...

ceeandcee 0 Junior Poster in Training · Answer 3 · 2010-09-04T18:03:10+00:00

Thanks Hielo....I think i have followed your directions carefully and it is still not working. My code is below and you can see the page at http://www.cornwallfantasyhockey.com/data/mtl/Index.php

<?php
// Make a MySQL Connection


// Get all the data from the "player" table
error_reporting (E_ERROR);

$result = mysql_query("SELECT * FROM stats1011 where Team = 'MTL' order by field(pos,'LW','C','RW','D','G'), last")
or die(mysql_error());  

echo "<table width='870' border='1' cellspacing='0' cellpadding='2' bgcolor='ffffff'>";
echo "<tr> 
<td colspan='3' align='center' bgcolor='000000'><font face='arial' size='2' color='ffffff'><b>Player Info</b></td>
<td colspan='4' align='center' bgcolor='CC0000'><font face='arial' size='2' color='ffffff'><b>Week #1</b></td>
<td colspan='4' align='center' bgcolor='FFFF00'><font face='arial' size='2' color='000000'><b>Week #2</b></td>
<td colspan='4' align='center' bgcolor='CC0000'><font face='arial' size='2' color='ffffff'><b>Week #3</b></td>
<td colspan='4' align='center' bgcolor='FFFF00'><font face='arial' size='2' color='000000'><b>Week #4</b></td>
<td colspan='3' align='center' bgcolor='000000'><font face='arial' size='2' color='ffffff'><b>TOTALS</b></td>
</tr>";
echo "<tr> 
<td width='5' bgcolor='000000' align='left'><font face='arial' size='1' color='FFFFFF'><b>Pos</b></td>
<td width='10' bgcolor='000000' align='center'><font face='arial' size='1' color='FFFFFF'><b>#</b></td>
<td width='170' bgcolor='000000' align='center'><font face='arial' size='1' color='FFFFFF'><b>PLAYER</b></td>
<td width='20' bgcolor='000000' align='center'><font face='arial' size='1' color='FFFFFF'><b>GP</b></td>
<td width='2' bgcolor='000000' align='center'><font face='arial' size='1' color='FFFFFF'><b>GP</b></td>
<td width='20' bgcolor='000000' align='center'><font face='arial' size='1' color='FFFFFF'><b>PTS</b></td>
<td width='2' bgcolor='000000' align='center'><font face='arial' size='1' color='FFFFFF'><b>PTS</b></td>
<td width='20' bgcolor='000000' align='center'><font face='arial' size='1' color='FFFFFF'><b>GP</b></td>
<td width='2' bgcolor='000000' align='center'><font face='arial' size='1' color='FFFFFF'><b>GP</b></td>
<td width='20' bgcolor='000000' align='center'><font face='arial' size='1' color='FFFFFF'><b>PTS</b></td>
<td width='2' bgcolor='000000' align='center'><font face='arial' size='1' color='FFFFFF'><b>PTS</b></td>
<td width='20' bgcolor='000000' align='center'><font face='arial' size='1' color='FFFFFF'><b>GP</b></td>
<td width='2' bgcolor='000000' align='center'><font face='arial' size='1' color='FFFFFF'><b>GP</b></td>
<td width='20' bgcolor='000000' align='center'><font face='arial' size='1' color='FFFFFF'><b>PTS</b></td>
<td width='2' bgcolor='000000' align='center'><font face='arial' size='1' color='FFFFFF'><b>PTS</b></td>
<td width='20' bgcolor='000000' align='center'><font face='arial' size='1' color='FFFFFF'><b>GP</b></td>
<td width='2' bgcolor='000000' align='center'><font face='arial' size='1' color='FFFFFF'><b>GP</b></td>
<td width='20' bgcolor='000000' align='center'><font face='arial' size='1' color='FFFFFF'><b>PTS</b></td>
<td width='2' bgcolor='000000' align='center'><font face='arial' size='1' color='FFFFFF'><b>PTS</b></td>
<td width='30' bgcolor='000000' align='center'><font face='arial' size='1' color='FFFFFF'><b>GP</b></td>
<td width='30' bgcolor='000000' align='center'><font face='arial' size='1' color='FFFFFF'><b>PTS</b></td>
<td width='30' bgcolor='000000' align='center'><font face='arial' size='1' color='FFFFFF'><b>PPG</b></td>
</tr>";


// keeps getting the next row until there are no more to get
while($row = mysql_fetch_array( $result )) {

$GP1011=$row['GP1011_wk1']+$row['GP1011_wk2']+$row['GP1011_wk3']+$row['GP1011_wk4']+$row['GP1011_wk5']+$row['GP1011_wk6']+$row['GP1011_wk7']+$row['GP1011_wk8']+$row['GP1011_wk9']+$row['GP1011_wk10']+$row['GP1011_wk11']+$row['GP1011_wk12']+$row['GP1011_wk13']+$row['GP1011_wk14']+$row['GP1011_wk15']+$row['GP1011_wk16']+$row['GP1011_wk17']+$row['GP1011_wk18']+$row['GP1011_wk19']+$row['GP1011_wk20']+$row['GP1011_wk21']+$row['GP1011_wk22']+$row['GP1011_wk23']+$row['GP1011_wk24']+$row['GP1011_wk25']+$row['GP1011_wk26'];
	$PTS1011=$row['PTS1011_wk1']+$row['PTS1011_wk2']+$row['PTS1011_wk3']+$row['PTS1011_wk4']+$row['PTS1011_wk5']+$row['PTS1011_wk6']+$row['PTS1011_wk7']+$row['PTS1011_wk8']+$row['PTS1011_wk9']+$row['PTS1011_wk10']+$row['PTS1011_wk11']+$row['PTS1011_wk12']+$row['PTS1011_wk13']+$row['PTS1011_wk14']+$row['PTS1011_wk15']+$row['PTS1011_wk16']+$row['PTS1011_wk17']+$row['PTS1011_wk18']+$row['PTS1011_wk19']+$row['PTS1011_wk20']+$row['PTS1011_wk21']+$row['PTS1011_wk22']+$row['PTS1011_wk23']+$row['PTS1011_wk24']+$row['PTS1011_wk25']+$row['PTS1011_wk26'];
	
	
	// Print out the contents of each row into a table
	echo "<tr><td width='5' bgcolor='FFFFFF' align='center'><font face='arial' size='1' color='000000'>"; 
	echo $row['Pos'];
	
	echo "</td><td width='10' bgcolor='FFFFFF' align='center'><font face='arial' size='1' color='000000'>";
	echo $row['No'];
	
	echo "</td><td width='170' bgcolor='FFFFFF' align='left'><font face='arial' size='2' color='000000'>"; 
	echo $row['Last'];
	echo ", <font face='arial' size='1' color='000000'>";
	echo $row['First'];
	
	echo "</td><td width='20' bgcolor='CCCCCC' align='center'><font face='arial' size='2' color='000000'>"; 
	echo $row['GP1011_wk1'];
	
	echo "</td><td width='2' bgcolor='CCCCCC' align='center'><font face='arial' size='2' color='000000'>"; 
	echo "<input type='text' size='2' name='GP1011_wk1[" . $row['playerID'] . "]'>";
	
	echo "</td><td width='20' bgcolor='CCCCCC' align='center'><font face='arial' size='2' color='000000'>"; 
	echo $row['PTS1011_wk1'];
	
	echo "</td><td width='2' bgcolor='CCCCCC' align='center'><font face='arial' size='2' color='000000'>"; 
	echo "<input type='text' size='2' name='PTS1011_wk1[" . $row['playerID'] . "]'>";
	
	echo "</td><td width='20' bgcolor='FFFF99' align='center'><font face='arial' size='2' color='000000'>"; 
	echo $row['GP1011_wk2'];
	
	echo "</td><td width='2' bgcolor='FFFF99' align='center'><font face='arial' size='2' color='000000'>"; 
	echo "<input type='text' size='2' name='GP1011_wk2[" . $row['playerID'] . "]'>";
	
	echo "</td><td width='20' bgcolor='FFFF99' align='center'><font face='arial' size='2' color='000000'>"; 
	echo $row['PTS1011_wk2'];
	
	echo "</td><td width='2' bgcolor='FFFF99' align='center'><font face='arial' size='2' color='000000'>"; 
	echo "<input type='text' size='2' name='PTS1011_wk2[" . $row['playerID'] . "]'>";
		
	echo "</td><td width='20' bgcolor='CCCCCC' align='center'><font face='arial' size='2' color='000000'>"; 
	echo $row['GP1011_wk3'];
	
	echo "</td><td width='2' bgcolor='CCCCCC' align='center'><font face='arial' size='2' color='000000'>"; 
	echo "<input type='text' size='2' name='GP1011_wk3[" . $row['playerID'] . "]'>";
	
	echo "</td><td width='20' bgcolor='CCCCCC' align='center'><font face='arial' size='2' color='000000'>"; 
	echo $row['PTS1011_wk3'];
	
	echo "</td><td width='2' bgcolor='CCCCCC' align='center'><font face='arial' size='2' color='000000'>"; 
	echo "<input type='text' size='2' name='PTS1011_wk3[" . $row['playerID'] . "]'>";
	
	echo "</td><td width='20' bgcolor='FFFF99' align='center'><font face='arial' size='2' color='000000'>"; 
	echo $row['GP1011_wk4'];
	
	echo "</td><td width='2' bgcolor='FFFF99' align='center'><font face='arial' size='2' color='000000'>"; 
	echo "<input type='text' size='2' name='GP1011_wk4[" . $row['playerID'] . "]'>";
	
	echo "</td><td width='20' bgcolor='FFFF99' align='center'><font face='arial' size='2' color='000000'>"; 
	echo $row['PTS1011_wk4'];
	
	echo "</td><td width='2' bgcolor='FFFF99' align='center'><font face='arial' size='2' color='000000'>"; 
	echo "<input type='text' size='2' name='PTS1011_wk4[" . $row['playerID'] . "]'>";
		
	echo "</td><td width='30' bgcolor='666666' align='center'><font face='arial' size='2' color='ffffff'><b>";  
	echo $GP1011;
	
	echo "</td><td width='30' bgcolor='666666' align='center'><font face='arial' size='2' color='ffffff'><b>"; 
	echo $PTS1011;
	
	echo "</td><td width='30' bgcolor='666666' align='center'><font face='arial' size='2' color='ffffff'><b>"; 
	echo number_format($PTS1011/$GP1011,2); 
	echo "</td></tr>";
} 

echo "</table>";
?>
<br>
<input type="submit" name="Submit" value="Submit">
</form>


<?php 
mysql_connect ("localhost","chris","tmi1234") or die ('Error: ' . mysql_error());
mysql_select_db ("playerdb")or die("cannot select DB");


foreach($_POST['PTS1011_wk1'] as $playerId=>$v)
{
  $GP1011_wk1=mysql_real_escape_string($_POST['GP1011_wk1'][$playerId]);
  $PTS1011_wk1=mysql_real_escape_string($_POST['PTS1011_wk1'][$playerId]);
  $GP1011_wk2=mysql_real_escape_string($_POST['GP1011_wk2'][$playerId]);
  $PTS1011_wk2=mysql_real_escape_string($_POST['PTS1011_wk2'][$playerId]);
  $GP1011_wk3=mysql_real_escape_string($_POST['GP1011_wk3'][$playerId]);
  $PTS1011_wk3=mysql_real_escape_string($_POST['PTS1011_wk3'][$playerId]);
  $GP1011_wk4=mysql_real_escape_string($_POST['GP1011_wk4'][$playerId]);
  $PTS1011_wk4=mysql_real_escape_string($_POST['PTS1011_wk4'][$playerId]);

  //complete your update statement below
  mysql_query("UPDATE ... WHERE `playerID`=" . $playerId) or die( mysql_error() );
  echo "Update record for " . $playerId;
}

// close connection 
mysql_close();
?>

Stefano Mtangoo 455 Senior Poster · Answer 4 · 2010-09-04T19:53:17+00:00

It is hard to help with such massive codes.my advice is, make small code based on your above code, and poke with it until it works then incorporate the idea in your big project.

I will help if you reduce to something readable :)

ceeandcee 0 Junior Poster in Training · Answer 5 · 2010-09-04T21:04:49+00:00

It is hard to help with such massive codes.my advice is, make small code based on your above code, and poke with it until it works then incorporate the idea in your big project.
I will help if you reduce to something readable :)

Fair comment evsteve.

As I re-read my code, I think my problem might be here:

foreach($_POST['PTS1011_wk1'] as $playerId=>$v)
{
  $GP1011_wk1=mysql_real_escape_string($_POST['GP1011_wk1'][$playerId]);
  $PTS1011_wk1=mysql_real_escape_string($_POST['PTS1011_wk1'][$playerId]);
  $GP1011_wk2=mysql_real_escape_string($_POST['GP1011_wk2'][$playerId]);
  $PTS1011_wk2=mysql_real_escape_string($_POST['PTS1011_wk2'][$playerId]);
  $GP1011_wk3=mysql_real_escape_string($_POST['GP1011_wk3'][$playerId]);
  $PTS1011_wk3=mysql_real_escape_string($_POST['PTS1011_wk3'][$playerId]);
  $GP1011_wk4=mysql_real_escape_string($_POST['GP1011_wk4'][$playerId]);
  $PTS1011_wk4=mysql_real_escape_string($_POST['PTS1011_wk4'][$playerId]);

  //complete your update statement below
  mysql_query("UPDATE ... WHERE `playerID`=" . $playerId) or die( mysql_error() );
  echo "Update record for " . $playerId;
}

Does my UPDATE statement need to specify all the fields that it will update or does the " ... " take care of that?

hielo 65 Veteran Poster · Answer 6 · 2010-09-04T22:17:02+00:00

Does my UPDATE statement need to specify all the fields that it will update or does the " ... " take care of that?

When you execute an UPDATE you need to EXPLICITLY state which fields should be updated and provide the values for them.

So in the above, instead of the "..." I was expecting you to replace it with something like: UPDATE TableName SET `field1`='value1', `field2`='value2' WHERE condition Please, don't take this the wrong way, but based on the question above, I STRONGLY suggest you read an introductory sql tutorial:
http://w3schools.com/sql/default.asp

The little amount of time you spend on the tutorial will more than make up for it in the long run since you will spend less time waiting for answers to beginner questions/mistakes.

On another note, lines 131-154 of your follow-up post to mine need to be at the beginning of the file (not at the end, and they need to be within and if clause:

<?php
if( isset($_POST['Submit']) && $_POST['Submit']=='Submit' )
{
  //code to update your table goes here
  ...
}

//rest of the code to generate your form fields goes here.
...
?>

Lastly, there is no need to execute a mysql_connect() for SELECT and one for UPDATE.
You just need to connect to the db once (at the beginning of the file) and do not close the connection until the very end of the file. In between you can execute any sql statements you want/need

ceeandcee 0 Junior Poster in Training · Answer 7 · 2010-09-04T22:41:41+00:00

Thanks Hielo. I appreciate your help and I do not take your suggestion the wrong way. In fact, before reading your last post I had done some more reading and had realized that I had the UPDATE command wrong and was experimenting with some options. I had also learned that I needed to move those lines into the above part of the file.

I will do more reading and will continue to try to get this to work. As you can see, I am still learning. With your help and the help of others, I have made great progress on my project.

I will keep trying and will come back if I get jammed.

ceeandcee 0 Junior Poster in Training · Answer 8 · 2010-09-27T00:54:45+00:00

Thanks Heilo...I have taken your advice, done some reading and worked with a friend. I think I am close. What I want to do is to pull a query from the database...show the two existing values for each player (GP and PTS) and be able to enter new numbers in the column beside it. I had it working but it would reset a value to "0" if I left it blank. Here is my page:

http://www.cornwallfantasyhockey.com/data/tor/

Here is the relevant sections of my coding

if( isset($_POST['Submit']) && $_POST['Submit']=='Submit' )
 foreach($_POST['GP1011_wk1'] as $playerID=>$v)
{
  $GP1011_wk1=mysql_real_escape_string($_POST['GP1011_wk1'][$playerID]);
  $PTS1011_wk1=mysql_real_escape_string($_POST['PTS1011_wk1'][$playerID]);
  
  //complete your update statement below
 	$update="UPDATE stats1011 SET ";
	if($_POST['GP1011_wk1'][$playerID]!="") {
		$update.="GP1011_wk1='".$_POST['GP1011_wk1'][$playerID]."'";
	} else {}
	if(($_POST['GP1011_wk1'][$playerID]!="")AND($_POST['PTS1011_wk1'][$playerID]!=""))
	{
		$update.=",";
	} else {}
	if($_POST['PTS1011_wk1'][$playerID]!="") {
		$update.="PTS1011_wk1='".$_POST['PTS1011_wk1'][$playerID]."'";
	} else {}
	$update.=" WHERE playerID ='".$playerID."'";


}
// Get all the data from the "player" table
error_reporting (E_ERROR);
$GETQ="SELECT
Sum(stats1011.GP1011_wk1+stats1011.GP1011_wk2+stats1011.GP1011_wk3+stats1011.GP1011_wk4+stats1011.GP1011_wk5+stats1011.GP1011_wk6+stats1011.GP1011_wk7+stats1011.GP1011_wk8+stats1011.GP1011_wk9+stats1011.GP1011_wk10+stats1011.GP1011_wk11+stats1011.GP1011_wk12+stats1011.GP1011_wk13+stats1011.GP1011_wk14+stats1011.GP1011_wk15+stats1011.GP1011_wk16+stats1011.GP1011_wk17+stats1011.GP1011_wk18+stats1011.GP1011_wk19+stats1011.GP1011_wk20+stats1011.GP1011_wk21+stats1011.GP1011_wk22+stats1011.GP1011_wk23+stats1011.GP1011_wk24+stats1011.GP1011_wk25+stats1011.GP1011_wk26) AS `GP_TTL_1011`,

Sum(stats1011.PTS1011_wk1+stats1011.PTS1011_wk2+stats1011.PTS1011_wk3+stats1011.PTS1011_wk4+stats1011.PTS1011_wk5+stats1011.PTS1011_wk6+stats1011.PTS1011_wk7+stats1011.PTS1011_wk8+stats1011.PTS1011_wk9+stats1011.PTS1011_wk10+stats1011.PTS1011_wk11+stats1011.PTS1011_wk12+stats1011.PTS1011_wk13+stats1011.PTS1011_wk14+stats1011.PTS1011_wk15+stats1011.PTS1011_wk16+stats1011.PTS1011_wk17+stats1011.PTS1011_wk18+stats1011.PTS1011_wk19+stats1011.PTS1011_wk20+stats1011.PTS1011_wk21+stats1011.PTS1011_wk22+stats1011.PTS1011_wk23+stats1011.PTS1011_wk24+stats1011.PTS1011_wk25+stats1011.PTS1011_wk26) AS `PTS_TTL_1011`,

`stats1011`.`Last`,
`stats1011`.`First`,
`stats1011`.`playerID`,
`stats1011`.`Pos`,
`stats1011`.`No`,
`stats1011`.`GP1011_wk1`,
`stats1011`.`PTS1011_wk1`,
`stats1011`.`Team`

FROM
`stats1011`

WHERE
`stats1011`.`Team` = 'tor'

GROUP BY
`stats1011`.`playerID`

ORDER BY
field(pos,'LW','C','RW','D','G'), last";


$result = mysql_query($GETQ)
or die(mysql_error());

Any help would be most appreciated! I am hoping to get this going by the start of the NHL Season on Oct. 7th.

Stefano Mtangoo 455 Senior Poster · Answer 9 · 2010-09-27T01:06:47+00:00

Any help would be most appreciated!

??:icon_rolleyes:??
Haven't said anything is wrong/not fixed yet

ceeandcee 0 Junior Poster in Training · Answer 10 · 2010-09-27T02:47:10+00:00

Thanks evsteve...this current code doesnt seem to work. It wont update the database. I am looking for help to get this to work and I think I am close.

ceeandcee 0 Junior Poster in Training · Answer 11 · 2010-09-27T23:45:01+00:00

ceeandcee 0 Junior Poster in Training

14 Years Ago

Thank you both! Got it working!