PHP Forum

**flforlife** · 05-02-2008, 03:13 PM

Ok what I want to do is update rows in MySQL using the same form that I posted. I'm a php mysql noob but I can't read code and figure out what it does.
This is my input form......

Code:

<form method='post' enctype='multipart/form-data' action="majoradd.php">
<table>
<tr>
<td>Do not enter in this field.</td>
<td>teamone</td>
<td>teamtwo</td>
<td>teamthree</td>
<td>teamfour</td>
<td>teamfive</td>
<td>teamsix</td>
<td>teamseven</td>
<td>Do not enter in this field.</td>
</tr>
<tr>
<td><input name="entrytwo[7][teamb]" type="text" /></td>
<td><input name="entrytwo[7][team]" type="text" /></td>
<td><input name="entrytwo[7][wl]" type="text" /></td>
<td><input name="entrytwo[7][gb]" type="text" /></td>
<td><input name="entrytwo[7][pcage]" type="text" /></td>
<td><input name="entrytwo[7][rs]" type="text" /></td>
<td><input name="entrytwo[7][ra]" type="text" /></td>
<td><input name="entrytwo[7][ags]" type="text" /></td>
<td><input name="entrytwo[7][records]" type="text" /></td>
</tr>
<!--row one-->
<tr></tr>
<tr></tr>
<tr>
<td>teamone</td>
<td>Win-Lose</td>
<td>Win-Lose</td>
<td>Win-Lose</td>
<td>Win-Lose</td>
<td>Win-Lose</td>
<td>Win-Lose</td>
<td>Win-Lose</td>
<td>record</td>
</tr>
<!--row one-->
<tr>
<td><input name="entrytwo[8][teamb]" type="text" /></td>
<td><input name="entrytwo[8][team]" type="text" /></td>
<td><input name="entrytwo[8][wl]" type="text" /></td>
<td><input name="entrytwo[8][gb]" type="text" /></td>
<td><input name="entrytwo[8][pcage]" type="text" /></td>
<td><input name="entrytwo[8][rs]" type="text" /></td>
<td><input name="entrytwo[8][ra]" type="text" /></td>
<td><input name="entrytwo[8][ags]" type="text" /></td>
<td><input name="entrytwo[8][records]" type="text" /></td>
</tr>
<tr>
<td><input name="entrytwo[9][teamb]" type="text" /></td>
<td><input name="entrytwo[9][team]" type="text" /></td>
<td><input name="entrytwo[9][wl]" type="text" /></td>
<td><input name="entrytwo[9][gb]" type="text" /></td>
<td><input name="entrytwo[9][pcage]" type="text" /></td>
<td><input name="entrytwo[9][rs]" type="text" /></td>
<td><input name="entrytwo[9][ra]" type="text" /></td>
<td><input name="entrytwo[9][ags]" type="text" /></td>
<td><input name="entrytwo[9][records]" type="text" /></td>
</tr>
<tr>
<td><input name="entrytwo[10][teamb]" type="text" /></td>
<td><input name="entrytwo[10][team]" type="text" /></td>
<td><input name="entrytwo[10][wl]" type="text" /></td>
<td><input name="entrytwo[10][gb]" type="text" /></td>
<td><input name="entrytwo[10][pcage]" type="text" /></td>
<td><input name="entrytwo[10][rs]" type="text" /></td>
<td><input name="entrytwo[10][ra]" type="text" /></td>
<td><input name="entrytwo[10][ags]" type="text" /></td>
<td><input name="entrytwo[10][records]" type="text" /></td>
</tr>
<tr>
<td><input name="entrytwo[11][teamb]" type="text" /></td>
<td><input name="entrytwo[11][team]" type="text" /></td>
<td><input name="entrytwo[11][wl]" type="text" /></td>
<td><input name="entrytwo[11][gb]" type="text" /></td>
<td><input name="entrytwo[11][pcage]" type="text" /></td>
<td><input name="entrytwo[11][rs]" type="text" /></td>
<td><input name="entrytwo[11][ra]" type="text" /></td>
<td><input name="entrytwo[11][ags]" type="text" /></td>
<td><input name="entrytwo[11][records]" type="text" /></td>
</tr>
<tr>
<td><input name="entrytwo[12][teamb]" type="text" /></td>
<td><input name="entrytwo[12][team]" type="text" /></td>
<td><input name="entrytwo[12][wl]" type="text" /></td>
<td><input name="entrytwo[12][gb]" type="text" /></td>
<td><input name="entrytwo[12][pcage]" type="text" /></td>
<td><input name="entrytwo[12][rs]" type="text" /></td>
<td><input name="entrytwo[12][ra]" type="text" /></td>
<td><input name="entrytwo[12][ags]" type="text" /></td>
<td><input name="entrytwo[12][records]" type="text" /></td>
</tr>
<tr>
<td><input name="entrytwo[13][teamb]" type="text" /></td>
<td><input name="entrytwo[13][team]" type="text" /></td>
<td><input name="entrytwo[13][wl]" type="text" /></td>
<td><input name="entrytwo[13][gb]" type="text" /></td>
<td><input name="entrytwo[13][pcage]" type="text" /></td>
<td><input name="entrytwo[13][rs]" type="text" /></td>
<td><input name="entrytwo[13][ra]" type="text" /></td>
<td><input name="entrytwo[13][ags]" type="text" /></td>
<td><input name="entrytwo[13][records]" type="text" /></td>
</tr>
<tr>
<td><input name="entrytwo[14][teamb]" type="text" /></td>
<td><input name="entrytwo[14][team]" type="text" /></td>
<td><input name="entrytwo[14][wl]" type="text" /></td>
<td><input name="entrytwo[14][gb]" type="text" /></td>
<td><input name="entrytwo[14][pcage]" type="text" /></td>
<td><input name="entrytwo[14][rs]" type="text" /></td>
<td><input name="entrytwo[14][ra]" type="text" /></td>
<td><input name="entrytwo[14][ags]" type="text" /></td>
<td><input name="entrytwo[14][records]" type="text" /></td>
</tr>
<tr>
<td><input name="entrytwo[15][teamb]" type="text" /></td>
<td><input name="entrytwo[15][team]" type="text" /></td>
<td><input name="entrytwo[15][wl]" type="text" /></td>
<td><input name="entrytwo[15][gb]" type="text" /></td>
<td><input name="entrytwo[15][pcage]" type="text" /></td>
<td><input name="entrytwo[15][rs]" type="text" /></td>
<td><input name="entrytwo[15][ra]" type="text" /></td>
<td><input name="entrytwo[15][ags]" type="text" /></td>
<td><input name="entrytwo[15][records]" type="text" /></td>
</tr>
</table>
<input type='submit' name='btngub' value='Submit'>
</form>

This is the code that adds the data to the database....

Code:

<?php
if (isset($_POST['btngub']))
{
//check for records later i
$sql = "INSERT INTO majortwo (teamb, team, wl, gb, pcage, rs, ra, ags, records) VALUES\n ";

foreach ($_POST['entrytwo'] as $data)
{
$dataArray[] = "('" . join ("','", $data) . "')";
}
$sql .= join (",\n", $dataArray);

/*echo '<pre>', $sql, '</pre>'; */ // view query
mysql_query($sql) or die(mysql_error());
header("location:setup.html"); 

}
mysql_close($mysql) 
?>

This is the database.sql ...

Code:

CREATE TABLE majortwo (
id INT NOT NULL AUTO_INCREMENT,
teamb VARCHAR(65) NOT NULL DEFAULT '', 
team VARCHAR(65) NOT NULL DEFAULT '',
wl VARCHAR(65) NOT NULL DEFAULT '',
gb VARCHAR(65) NOT NULL DEFAULT '',
pcage VARCHAR(65) NOT NULL DEFAULT '',
rs VARCHAR(65) NOT NULL DEFAULT '',
ra VARCHAR(65) NOT NULL DEFAULT '',
ags VARCHAR(65) NOT NULL DEFAULT '',
agstwo VARCHAR(65) NOT NULL DEFAULT '',
records VARCHAR(65) NOT NULL DEFAULT '',
PRIMARY KEY(id)
);

How can I update the rows in the database using the same form?

**JeremyMiller** · 05-02-2008, 05:42 PM

Try this.

PHP Code:


			
<?php
if (is_array($_POST['entrytwo']) && !empty($_POST['entrytwo']))
{
  foreach ($_POST['entrytwo'] as $primary_key=>$data) {
    //Sanitize data
    $teamb = quoteSmart($data['teamb']);
    $team = quoteSmart($data['team']);
    $wl = quoteSmart($data['wl']);
    $gb = quoteSmart($data['gb']);
    $pcage = quoteSmart($data['pcage']);
    $rs = quoteSmart($data['rs']);
    $ra = quoteSmart($data['ra']);
    $ags = quoteSmart($data['ags']);
    $records = quoteSmart($data['records']);
    
    //Create SQL query
    $sql = "UPDATE majortwo SET teamb=".$teamb.", team=".$team.", wl=".$wl.", gb=".$gb.", pcage=".$pcage.", rs=".$rs.", ra=".$ra.", ags=".$ags.", records=".$records." WHERE id=".((int)$primary_key)." LIMIT 1";
    
    if (!mysql_query($sql)) {
       echo 'Error Updating '.$teamb.' vs '.$team.'.<br /> The database reported: '.mysql_error().'<br />';
     }
  }

  header("location:setup.html"); 
}

mysql_close($mysql);

//Function for protecting against SQL injection hacks
function quoteSmart($value, $add_quotes=true, $allow_wildcards=true, $require_quotes=false) {
  // Taken from the PHP site, with modifications for quotes and wildcards.
  
  // Stripslashes
  if (get_magic_quotes_gpc()) {
  $value = stripslashes($value);
  }
  
  // Quote if not a number or a numeric string
  if (is_numeric($value)) {
    if ($require_quotes) {
      $value = "'" . $value . "'";
    }
  } else {
    if ($add_quotes){
      $value = "'" . mysql_real_escape_string($value) . "'";
    } else {
      $value = $database->mysql_real_escape_string($value);
    }
  }
  //Use for SELECT queries where % and _ have a wildcard meaning.
  if (!$allow_wildcards) {
    $value = str_replace('%','\%',$value);
    $value = str_replace('_','\_',$value);
  }
  return $value;
}
?>

You probably don't want all your fields as VARCHAR(65) either -- they should be what's appropriate for the type of information being entered in. Check the MySQL site for documentation on types. PHPMyAdmin is great for creating tables.

Also, you had not protected against SQL injection, so I added that -- it's the quoteSmart() function.

Finally, you were detecting whether the button itself had been pressed, but forms can be submitted without pressing the button (sometimes by hitting ENTER), so I changed it to look for whether the array needed is present.

Good luck and if your project gets bigger, you may want to consider hiring a pro to ensure your site runs efficiently and as securely as is reasonably possible.