Mass updating db records

richei · March 2, 2008

ok, i have a script that kinda works. I can enter information and display it properly, the lies with me trying to edit those records

<?php include('db.php'); ?>

<form action='<?php echo $_SERVER['PHP_SELF']; ?>' method='post'>
<div align='center'><table width='75%' border='1' cellspacing='1' cellpadding='1'>
  <tr>
    <td>Name:</td>
    <td><input name='name' type='text' /></td>
    <td>Captain:</td>
    <td>Yes <input type='radio' name='captain' value='1' id='captain_0' /></td>    
    <td>No <input type='radio' name='captain' value='0' id='captain_1' /></td>
  </tr>
  <tr>
    <td>Team:</td>
    <td><input name='team' type='text' /></td>
    <td>Clan:</td>
    <td><input name='clan' type='text' /></td>
    <td>Active <input type='radio' name='status' value='active' id='status_0' /></td>
    <td>Inactive <input type='radio' name='status' value='inactive' id='status_1' /></td>
    <td>Suspended <input type='radio' name='status' value='suspended' id='status_2' /></td>
    <td>Banned <input type='radio' name='status' value='banned' id='status_3' /></td>
  </tr>
  <tr>
    <td>Steamid(s):</td>
    <td><textarea name='steamid' cols='20' rows='2' wrap='off'></textarea></td>
  </tr>
  <tr>
    <td colspan='8'><div align='center'>
      <input type='submit' name='submit' id='submit' value='Submit'>
    </div></td>
  </tr>
</table>
</div>
</form>

<?php
if($_POST['submit']) {

$name = $_POST['name'];
$status = $_POST['status'];
$team = $_POST['team'];
$clan = $_POST['clan'];
$captain = $_POST['captain'];
$steamid = $_POST['steamid'];

mysql_query("INSERT INTO `teams` VALUES('', '$name', '$status', '$team', '$clan', $captain, '$steamid', NOW(), '')") or die(mysql_error());
}
?>
  
<?php
$query = mysql_query("SELECT * FROM `teams`");

echo "<form action='".$_SERVER['PHP_SELF']."' method='post'>\n";
echo "<div align='center'><table width='75%' border='1' cellspacing='1' cellpadding='1'>\n";
echo "	<tr>\n";
echo "		<td width='10%'><div align='center'>Clan</div></td>\n";
echo "		<td width='25%'><div align='center'>Name</div></td>\n";
echo "		<td width='25%'><div align='center'>Steamid(s)</div></td>\n";
echo "		<td width='10%'><div align='center'>Status</div></td>\n";
echo "		<td width='10%'><div align='center'>Update</div></td>\n";
echo "	</tr>\n";

while($r = mysql_fetch_array($query)) {

if($r['captain'] == 1) {
echo "	<tr>\n";
echo "		<td colspan='5'><div align='left'>\nTeam ".$r['team']."</div>\n</td>\n";
echo "	</tr>\n<tr>\n";
echo "		<td width='10%'>".$r['clan']."</td>\n";
echo "		<td width='25%'>Captain: ".$r['name']."</td>\n";
echo "		<td width='25%'>".$r['steamid']."</td>\n";
echo "		<td width='10%'>".$r['status']."</td>\n";
echo "		<td><input type='checkbox' name='alter[]' value='".$r['id']."' /></td>\n";
echo "	</tr>\n";
} else {
	echo "	<tr>\n";
	echo "		<td width='10%'> </td>\n";
	echo "		<td width='25%'>".$r['name']."</td>\n";
	echo "		<td width='25%'>".$r['steamid']."</td>\n";
	echo "		<td width='10%'>".$r['status']."</td>\n";
	echo "		<td><input type='checkbox' name='alter[]' value='".$r['id']."' /></td>\n";
	echo "	</tr>\n";
}
}
echo "	<tr>\n<td colspan='5'>\n";
echo "		<div align='center'><input type='submit' name='update' id='update' value='update'></div>";
echo "	</td>\n</tr>\n";
echo "</table>\n</div>\n</form>";
?>
<?php

if($_POST['update']) {
$alter = $_POST['alter'];
for($z = 0; $z < count($alter); $z++) {
	$id = $alter[$z];
	$row = mysql_fetch_array(mysql_query("SELECT * FROM `teams` WHERE `id`='".$id."'"));

echo "<form action='".$_SERVER['PHP_SELF']."'?id='".$id[$z]."' method='post'>\n";
echo "<table width='75%' border='1' cellspacing='1' cellpadding='1' align='center' >\n";
echo "  <tr>\n";
echo "   <td>Name, ID</td>\n";
echo "    <td>".$row['name'].",".$row['id']."</td>\n";
echo "    <td>Name</td>\n";
echo "    <td colspan='5'><input name='nname' type='text' /></td>\n";
echo "  </tr>\n";
echo "  <tr>\n";
echo "    <td>Team</td>\n";
echo "    <td>". $row['team']. "</td>\n";
echo "    <td>Team</td>\n";
echo "    <td><input name='nteam' type='text' /></td>\n";
echo "  </tr>\n";
echo "  <tr>\n";
echo "    <td>Steamid(s)</td>\n";
echo "    <td>".$row['steamid']."</td>\n";
echo "    <td>SteamId(s)</td>\n";
echo "    <td ><textarea name='nsteamid' cols='30' rows='2' wrap='off'></textarea></td>\n";
echo "  </tr>\n";
echo "  <tr>\n";
echo "    <td>Captain</td>\n";
echo "    <td>".$row['captain']."</td>\n";
echo "    <td>Captain</td>\n";
echo "    <td>Yes <input type='radio' name='ncaptain' value='1' id='captain_2' /> No <input type='radio' name='ncaptain' value='0' id='captain_3' /></td>\n";
echo "  </tr>\n";
echo "  <tr>\n";
echo "    <td>Clan</td>\n";
echo "    <td>".$row['clan']."</td>\n";
echo "    <td>Clan</td>\n";
echo "    <td><input name='nclan' type='text' /></td>\n";
echo "  </tr>\n";
echo "  <tr>\n";
echo "    <td>Status</td>\n";
echo "    <td>".$row['status']."</td>\n";
echo "    <td>Status</td>\n";
echo "    <td>Active <input type='radio' name='nstatus' value='active' id='status_4' />\n";
echo "		Inactive <input type='radio' name='nstatus' value='inactive' id='status_5' />\n";
echo "		Suspended <input type='radio' name='nstatus' value='suspended' id='status_6' />\n";
echo "		Banned <input type='radio' name='nstatus' value='banned' id='status_7' />\n";
echo "    </td>\n";
echo "  </tr>\n";
echo "  <tr>\n";
echo "    <td>Enty On</td>\n";
echo "    <td>".$row['entered']."</td>\n";
echo "  </tr>\n";
echo "  <tr>\n";
echo "    <td>Last Updated On</td>\n";
echo "    <td>".$row['updated']."</td>\n";
}
echo "    <td colspan='6'><div align='center'><div align='center'><input type='submit' name='sub_update' id='sub_update' value='sub_update'></div></div></td>\n";
echo "  </tr>\n";
echo "</table>\n";
echo "	</form>\n";
}
?>
<?php // seems to be broken here - everything above here seems to work
if($_POST['sub_update']) {

$nname = $_POST['nname'];
$nclan = $_POST['nclan'];
$nteam = $_POST['nteam'];
$nsteamid = $_POST['nsteamid'];
$nstatus = $_POST['nstatus'];

while ($n = mysql_fetch_array($query)) {	

$oteam = $n['team'];
$oclan = $n['clan'];
$oname = $n['name'];
$osteamid = $n['steamid'];
$ostatus = $n['status'];
$oentered = $n['stamp'];
$oupdated = $n['updatedon'];

if($nname == "") {
	$nname = $oname;
}
if($nteam == "") {
	$nname = $oteam;
}
if($nsteamid == "") {
	$nsteamid = $osteamid;
}
if($ncaptain == "") {
	$ncaptain = $ocaptain;
}
if($nclan == "") {
	$nclan = $oclan;
}
if(nstatus == "") {
	$nstatus = $ostatus;
}

$nupdatedon = DATE_FORMAT(updatedon, "%m, %d, %y, %h:%i.%s");
}
mysql_query("UPDATE `teams` SET name = '".$nname."', status = '".$nstatus."', team = '".$nteam."', clan = '".$nclan."', captain = $ncaptain, steamid = '".$nsteamid."', updatedon = '".$nupdatedon."' WHERE `id` = '".$_POST['id']."'") or die(mysql_error());

//echo $id."<br />";
//echo $nname."<br />";
//echo $nstatus."<br />";
//echo $nteam."<br />";
//echo $nclan."<br />";
//echo $ncaptain."<br />";
//echo $nsteamid."<br />";
//echo $nupdatedon."<br />";
//echo "<br />";
//echo $_POST['id']."<br />";

}
?>

Everything above the sub_edit area works fine. Its not tossing out any error messages. when i hit sub_edit (the button), it just refreshes the page and nothing has been done to the records.

The date thing is to show when the record was last updated, for whatever reason, NOW(), didn't want to work. Seems to work fine in the insert part though, weird

does anyone have any suggestions for what's causing the problem. I apoligize for the long script, but i'm still rather new to putting it all on one page.

deansatch · March 2, 2008

This line is a bit of a mess:

mysql_query("UPDATE `teams` SET name = '".$nname."', status = '".$nstatus."', team = '".$nteam."', clan = '".$nclan."', captain = $ncaptain, steamid = '".$nsteamid."', updatedon = '".$nupdatedon."' WHERE `id` = '".$_POST['id']."'") or die(mysql_error());

try adding this to make id a variable first:

 $id=$_POST['id'];

then change your mysql query to this:

mysql_query("UPDATE `teams` SET name = '$nname', status = '$nstatus', team = '$nteam', clan = '$nclan', captain = '$ncaptain', steamid = '$nsteamid', updatedon = '$nupdatedon' WHERE `id` = '$id'") or die(mysql_error());

deansatch · March 2, 2008

And what's this?

$nupdatedon = DATE_FORMAT(updatedon, "%m, %d, %y, %h:%i.%s");

Should updatedon be a variable? Is it set anywhere?

richei · March 2, 2008

no luck, does the samething. the updateon variable is set right there($nupdatedon = DATE_FORMAT(updatedon, "%m, %d, %y, %h:%i.%s"). I set it as a variable because any attempt i made to use it in the update script threw out error messages. all its for is allow me to know when the record was last updated.

crap, can't modify my first post, so here's the updated section

<?php
if($_POST['sub_update']) {

$id=$_POST['id'];
$nname = $_POST['nname'];
$nclan = $_POST['nclan'];
$nteam = $_POST['nteam'];
$nsteamid = $_POST['nsteamid'];
$nstatus = $_POST['nstatus'];

while ($n = mysql_fetch_array($query)) {	
	$oteam = $n['team'];
	$oclan = $n['clan'];
	$oname = $n['name'];
	$osteamid = $n['steamid'];
	$ostatus = $n['status'];
	$oentered = $n['stamp'];
	$oupdated = $n['updatedon'];

if($nname == "") {
	$nname = $oname;
}
if($nteam == "") {
	$nname = $oteam;
}
if($nsteamid == "") {
	$nsteamid = $osteamid;
}
if($ncaptain == "") {
	$ncaptain = $ocaptain;
}
if($nclan == "") {
	$nclan = $oclan;
}
if(nstatus == "") {
	$nstatus = $ostatus;
}

$nupdatedon = DATE_FORMAT(updatedon, "%m, %d, %y, %h:%i.%s");
}
mysql_query("UPDATE `teams` SET name = '$nname', status = '$nstatus', team = '$nteam', clan = '$nclan', captain = '$ncaptain', steamid = '$nsteamid', updatedon = '$nupdatedon' WHERE `id` = '$id'") or die(mysql_error());

//echo $id."<br />";
//echo $nname."<br />";
//echo $nstatus."<br />";
//echo $nteam."<br />";
//echo $nclan."<br />";
//echo $ncaptain."<br />";
//echo $nsteamid."<br />";
//echo $nupdatedon."<br />";
//echo "<br />";
//echo $_POST['id']."<br />";

}
?>

richei · March 2, 2008

sorry for the double post, but got some new info for this.

I think its got something to do with the multiple records, still doesn't work, but i purposly put an error condition in there and echoed the variables. It only gave me information on 1 record instead of the 2 that i had selected.

deansatch · March 2, 2008

This line still does not appear right to me:

$nupdatedon = DATE_FORMAT(updatedon, "%m, %d, %y, %h:%i.%s");

I know you are setting a variable: $nupdatedon but what is updatedon? It is not a variable:

Should it be? $updatedon is not set anywhere in your script.

$nupdatedon = DATE_FORMAT($oupdated, "%m, %d, %y, %h:%i.%s");

Also, I assume you have php5 since you need it for that function

richei · March 2, 2008

The problem lies with the id, for some reason, when i hit sub_edit, its not passing the id of the record being edited.

deansatch · March 2, 2008

I may just have missed it but to me it seems as though you aren't actually posting an id yet you are referencing one. i.e. $_POST['id']

richei · March 2, 2008

the id problem was fixed, but i'm getting a problem with the date.

this is the error: Warning: date_format() expects parameter 1 to be DateTime, string given in C:\wamp\www\fa2_league\person_input.php on line 195

195 is $nupdatedon = DATE_FORMAT($oupdated, "%m, %d, %y %h:%i.%s");

richei · March 3, 2008

update - i got it fixed. i didn't end up putting the date in, i can do that at a later date.

but this is the code i ended up with

<?php include('db.php');

if($_POST['update']) {
$alter = $_POST['alter'];
for($z = 0; $z < count($alter); $z++) {
	$id = $alter[$z];
	$q = mysql_query("SELECT * FROM `teams` WHERE `id`='".$id."'") or die(mysql_error());
	$row = mysql_fetch_array( $q );
?>
<form action='' method='post'>
<table width='75%' border='1' cellspacing='1' cellpadding='1' align='center'>
<tr>
	<td>Name, ID</td>
	<td><?php echo $row['name'].",".$row['id']; ?></td>
	<input type='hidden' name='id' id='id' value='<?php echo $row['id']; ?>'>
	<td>Name</td>
	<td colspan='5'><input name='nname' type='text' /></td>
</tr>
<tr>
	<td>Team</td>
	<td><?php echo $row['team']; ?></td>
	<td>Team</td>
	<td><input name='nteam' type='text' /></td>
</tr>
<tr>
	<td>Steamid(s)</td>
	<td><?php echo $row['steamid']; ?></td>
	<td>SteamId(s)</td>
	<td ><textarea name='nsteamid' cols='30' rows='2' wrap='off'></textarea></td>
</tr>
<tr>
	<td>Captain</td>
	<td><?php echo $row['captain']; ?></td>
	<td>Captain</td>
	<td>Yes <input type='radio' name='ncaptain' value='1' id='ncaptain' /> No <input type='radio' name='ncaptain' value='0' id='ncaptain' /></td>
</tr>
<tr>
	<td>Clan</td>
	<td><?php echo $row['clan']; ?></td>
	<td>Clan</td>
	<td><input name='nclan' type='text' /></td>
</tr>
<tr>
	<td>Status</td>
	<td><?php echo $row['status']; ?></td>
	<td>Status</td>
	<td>Active <input type='radio' name='nstatus' value='active' id='status_4' />
		Inactive <input type='radio' name='nstatus' value='inactive' id='status_5' />
		Suspended <input type='radio' name='nstatus' value='suspended' id='status_6' />
		Banned <input type='radio' name='nstatus' value='banned' id='status_7' />
</td>
</tr>
	<tr>
	<td>Entered On</td>
	<td><?php echo $row['stamp']; ?></td>
</tr>
<tr>
	<td>Last Updated On</td>
	<td><?php echo $row['updated']; ?></td>
<?php } ?>
	<td colspan='6'>
		<div align='center'>
			<input type='submit' name='subupdate' id='subupdate' value='subupdate'>
		</div>
	</td>
</tr>
</table>
</form>
<?php
}
?>

<?php
if($_POST['subupdate']) {

$id = $_POST['id'];
$query = mysql_query("SELECT * FROM `teams` WHERE id = '$id'");

while ($n = mysql_fetch_array( $query )) {	

	//Information to Update
	$id = $_POST['id'];
	$nname = $_POST['nname'];
	$nclan = $_POST['nclan'];
	$nteam = $_POST['nteam'];
	$ncaptain = $_POST['captain'];
	$nsteamid = $_POST['nsteamid'];
	$nstatus = $_POST['nstatus'];

	//Old Information
	$oname = $n['name'];	
	$ostatus = $n['status'];
	$oteam = $n['team'];
	$oclan = $n['clan'];
	$ocaptain = $n['captain'];
	$osteamid = $n['steamid'];
	$oupdated = $n['updatedon'];

if($nname == "") {
	$nname = $oname;
}
if($nstatus == "") {
	$nstatus = $ostatus;
}
if($nteam == "") {
	$nteam = $oteam;
}
if($nclan == "") {
	$nclan = $oclan;
}
if($ncaptain == "") {
	$ncaptain = $ocaptain;
}
if($nsteamid == "") {
	$nsteamid = $osteamid;
}

//$nupdatedon = NOW();
//$nupdatedon = DATE_FORMAT($oupdated, "%m, %d, %y %h:%i.%s");
}
mysql_query("UPDATE `teams` SET name='$nname', status='$nstatus', team='$nteam', clan='$nclan', captain=$ncaptain, steamid='$nsteamid' WHERE id=$id") or die(mysql_error());
}
?>

<form action='<?php echo $_SERVER['PHP_SELF']; ?>' method='post'>
<div align='center'><table width='75%' border='1' cellspacing='1' cellpadding='1'>
  <tr>
    <td>Name:</td>
    <td><input name='name' type='text' /></td>
    <td>Captain:</td>
    <td>Yes <input type='radio' name='captain' value='1' id='captain_0' /></td>    
    <td>No <input type='radio' name='captain' value='0' id='captain_1' /></td>
  </tr>
  <tr>
    <td>Team:</td>
    <td><input name='team' type='text' /></td>
    <td>Clan:</td>
    <td><input name='clan' type='text' /></td>
    <td>Active <input type='radio' name='status' value='active' id='status_0' /></td>
    <td>Inactive <input type='radio' name='status' value='inactive' id='status_1' /></td>
    <td>Suspended <input type='radio' name='status' value='suspended' id='status_2' /></td>
    <td>Banned <input type='radio' name='status' value='banned' id='status_3' /></td>
  </tr>
  <tr>
    <td>Steamid(s):</td>
    <td><textarea name='steamid' cols='20' rows='2' wrap='off'></textarea></td>
  </tr>
  <tr>
    <td colspan='8'><div align='center'>
      <input type='submit' name='submit' id='submit' value='Submit'>
    </div></td>
  </tr>
</table>
</div>
</form>

<?php
if($_POST['submit']) {

$name = $_POST['name'];
$status = $_POST['status'];
$team = $_POST['team'];
$clan = $_POST['clan'];
$captain = $_POST['captain'];
$steamid = $_POST['steamid'];

mysql_query("INSERT INTO `teams` VALUES('', '$name', '$status', '$team', '$clan', $captain, '$steamid', NOW(), '')") or die(mysql_error());
}
?>
  
<?php
$query = mysql_query("SELECT * FROM `teams`");
?>
<form action=<?php echo $_SERVER['PHP_SELF']; ?> method='post'>
<div align='center'><table width='75%' border='1' cellspacing='1' cellpadding='1'>
<tr>
	<td width='10%'><div align='center'>Clan</div></td>
	<td width='25%'><div align='center'>Name</div></td>
	<td width='25%'><div align='center'>Steamid(s)</div></td>
	<td width='10%'><div align='center'>Status</div></td>
	<td width='10%'><div align='center'>Update</div></td>
</tr>
<?php
while($r = mysql_fetch_array($query)) {

if($r['captain'] == 1) {
echo "	<tr>\n";
echo "		<td colspan='5'><div align='left'>\nTeam ".$r['team']."</div>\n</td>\n";
echo "	</tr>\n<tr>\n";
echo "		<td width='10%'>".$r['clan']."</td>\n";
echo "		<td width='25%'>Captain: ".$r['name']."</td>\n";
echo "		<td width='25%'>".$r['steamid']."</td>\n";
echo "		<td width='10%'>".$r['status']."</td>\n";
echo "		<td wdith='5%'><input type='checkbox' name='alter[]' value='".$r['id']."' /></td>\n";
echo "	</tr>\n";
} else {
	echo "	<tr>\n";
	echo "		<td width='10%'> </td>\n";
	echo "		<td width='25%'>".$r['name']."</td>\n";
	echo "		<td width='25%'>".$r['steamid']."</td>\n";
	echo "		<td width='10%'>".$r['status']."</td>\n";
	echo "		<td><input type='checkbox' name='alter[]' value='".$r['id']."' /></td>\n";
	echo "	</tr>\n";
}
}
echo "	<tr>\n<td colspan='5'>\n";
echo "		<div align='center'><input type='submit' name='update' id='update' value='update'></div>";
echo "	</td>\n</tr>\n";
echo "</table>\n</div>\n</form>";
?>

I'm sure there's probably a cleaner way of doing this, but this works for what i need it for.

Sign In

Mass updating db records

Recommended Posts

richei

Link to comment

Share on other sites

deansatch

Link to comment

Share on other sites

deansatch

Link to comment

Share on other sites

richei

Link to comment

Share on other sites

richei

Link to comment

Share on other sites

deansatch

Link to comment

Share on other sites

richei

Link to comment

Share on other sites

deansatch

Link to comment

Share on other sites

richei

Link to comment

Share on other sites

richei

Link to comment

Share on other sites

Archived

Browse

Activity

Important Information