select certain columns to update in an mysql using a form.

Beginners - Learning PHP
#1

Hello,

I have a problem. I would like to update certain columns in an mysql database by clicking on one submit button. I know there are many different ways on doing this but I would like to us a checkbox to select the columns I would like to edit or update. This is what I have so far:

[php]

<?php include 'acinclude/config.php'; include 'acinclude/function.php'; include("login/include/session.php"); if(!$session->logged_in){ header("Location: login/main.php"); } else { ?> 
	<style type="text/css">
		td.thick {font-weight:bold;}
	</style>
</head>
<body>
	<center>
	[<a href = "index.php">Aircraft Status</a>]
	[<a href = "index.php?status=up">UP</a>]
	[<a href = "index.php?status=down">Down</a>]
	[<a href = "index.php?status=FCF">FCF</a>]
	[<a href = "index.php?status=down_and_FCF">Down and FCF</a>]
	[<a href = "report.php">Status Report</a>]
	[<a href = "update.php">Change Status</a>]
	[<a href = "login/main.php">Account Controls</a>]
	[<a href = "login/process.php">Logout</a>]
	</center>
		</br>
	<center>
	<form action="update1.php" method="post">
	<table border="1">
		<thead>
			<tr>
				<th>BUNO</th>
				<th>MODEX</th>
				<th>Status</th>
				<th>Updated</th>
				<th>Updated By</th>
				<th>Reason</th>
				<th>RTS</th>
				<th>Check</th>
			</tr>
		</thead>
<?php while ($row = mysql_fetch_array($all)){ $buno = $row[0]; $modex = $row[1]; $originalDate = $row[2]; $color = $row[4]; $fname = $row[5]; $lname = $row[6]; $reason = $row[7]; $rts = $row[8]; $date = date("m/d/y H:i",strtotime($originalDate)); print ' '. $buno . ' '. $modex . ' '; print statuslist($a); print ' '. $date . ' '. $fname .' '. $lname .' '; } } ?> 
	</table>
		<input type="submit" name="submit" value="Update" /></td>
	</form>	
	</center>
</body>
[/php]

update1.php

[php]

<?php include 'acinclude/config.php'; include 'acinclude/function.php'; include 'login/include/session.php'; if(isset($_REQUEST['submit'])){ if (isset($_REQUEST['checked'])) { $BUNO = $_POST['buno']; $MODEX = $_POST['modex']; $acstatus = $_POST['acstatus']; $reason = $_POST['reason']; $rts = $_POST['rts']; $uname = $_POST['user']; $i = -1; foreach ($BUNO as $BUNOS){ $row_modex = $MODEX[++$i]; $row_reason = $reason[$i]; $row_rts = $rts[$i]; $row_uname = $uname[$i]; $row_acstatus = $acstatus[$i]; $update_qr = 'UPDATE status SET STATUS ="'.$acstatus.'", username ="'.$uname.'", reason="'.$reason.'", rts="'.$rts.'" WHERE BUNO ="'.$BUNOS.'"'; $update = mysql_query($update_qr) or die('Invalid query: ' . mysql_error()); } header('Location: update.php'); } } else { echo "The Update button has not been clicked"; } ?>

[/php]

I hope this explains everything. I will be more then happy to answer any question. Thank you in advance.

#2

The code you provided doesn’t support what you’re trying to do. You can’t do a generalized update when you’re selecting which fields to update in the query. You need to actually build the query in peices, then either impode them or use a loop to move through the array.

Unless some code wasn’t posted, $a has no value at all, there’s no for loop. You can also take the [] away from each name. You only need those if you’re updating multiple records or on checkboxes

on update1, you’re confusing a foreach loop with a for loop, i’m not sure why you’re starting $1 out with a negative value though since every array index starts at 0. anyways, this is what i’d do
[php]
if(!empty($_POST[‘acstatus’];)) {
$up[] = “STATUS = ‘$_POST[acstatus]’”;
}
if(!empty($_POST[‘user’];)) {
$up[] = “username = ‘$_POST[user]’”;
}
if(!empty($_POST[‘reason’];)) {
$up[] = “reason = ‘$_POST[reason]’”;
}
if(!empty($_POST[‘rts’];)) {
$up[] = “rts = ‘$_POST[rts]’”;
}
if(!empty($_POST[‘buno’])) {
$up[] = " WHERE BUNO = ‘$_POST[buno]’";
}

$MODEX = $_POST[‘modex’]++;

$update = mysql_query("UPDATE status SET “.implode(’, ', $up).”) or die(mysql_error());[/php]

Not really sure what that other stuff is at the bottom of the script, just add it in. but with that code above, you’re only updating fields that aren’t empty.

#3

I’m sorry I forgot to remove some code out of the update1.php. I didn’t want the $i=-1 in there. I was trying something and I forgot to remove it. On your statement " You can also take the [] away from each name. You only need those if you’re updating multiple records or on checkboxes". That is what I want to do, I would like to update multiple records or only records with the checkbox checked.

Also, here is the function statuslist($a) that you asked about.
[php]
$a = array(‘0’ =>’’,‘1’=>‘UP’ ,‘2’=>‘DOWN’ ,‘3’=>‘FCF’, ‘4’=>‘OOR’);

function statuslist($array){
foreach ($array as $key => $value){
print ‘’.$value.’’;
}
}
[/php]

Is there something I need to do different if I would like to update multiple records? I really appreciate your help. Thank you.

#4

I would like to select a value from the dropdown menu and click the submit button to ONLY update the rows that are changed in the dropdown menu. richei pointed me in the right direction and just need a little bit more help. This is what I have and it is updating all the rows. Any help is much appreciated.
[php]

<?php include 'acinclude/config.php'; include 'acinclude/function.php'; include("login/include/session.php"); ?> 
	<style type="text/css">
		td.thick {font-weight:bold;}
	</style>
</head>
<body>
	<center>
	[<a href = "index.php">Aircraft Status</a>]
	[<a href = "index.php?status=up">UP</a>]
	[<a href = "index.php?status=down">Down</a>]
	[<a href = "index.php?status=FCF">FCF</a>]
	[<a href = "index.php?status=down_and_FCF">Down and FCF</a>]
	[<a href = "report.php">Status Report</a>]
	[<a href = "update.php">Change Status</a>]
	[<a href = "login/main.php">Account Controls</a>]
	[<a href = "login/process.php">Logout</a>]
	</center>
		</br>
	<center>
	<form action="update1.php" method="post">
	<table border="1">
		<thead>
			<tr>
				<th>BUNO</th>
				<th>MODEX</th>
				<th>Status</th>
				<th>Updated</th>
				<th>Updated By</th>
				<th>Reason</th>
				<th>RTS</th>
			</tr>
		</thead>
<?php while ($row = mysql_fetch_array($all)){ $buno = $row[0]; $modex = $row[1]; $originalDate = $row[2]; $color = $row[4]; $fname = $row[5]; $lname = $row[6]; $reason = $row[7]; $rts = $row[8]; $username = 'admin'; $date = date("m/d/y H:i",strtotime($originalDate)); print ' '. $row[0] . ' '. $row[1] . ' '; print statuslist($a); print ' '. $date . ' '. $fname .' '. $lname .' '; } ?> 
	</table>
		<input type="submit" name="submit" value="Update" /></td>
	</form>	
	</center>
</body>
[/php]

update1.php
[php]

<?php include 'acinclude/config.php'; include 'acinclude/function.php'; include 'login/include/session.php'; $count = mysql_num_rows($all); $buno = $_POST['buno']; $modex = $_POST['modex']; $acstatus = $_POST['acstatus']; $reason = $_POST['reason']; $rts = $_POST['rts']; $uname = $_POST['user']; if(isset($_POST['submit'])){ if(!empty($acstatus)) { for($i=0;$i<$count;$i++){ $insert_hist = 'INSERT INTO status_hist (BUNO, MODEX, STATUS, username, reason, rts) VALUES ('.$buno[$i].', '.$modex[$i].', "'.$acstatus[$i].'", "'.$uname[$i].'", "'.$reason[$i].'", "'.$rts[$i].'")'; $insert = mysql_query($insert_hist) or die('Invalid query: ' . mysql_error()); $update_qr = 'UPDATE status SET STATUS ="'.$acstatus[$i].'", username ="'.$uname[$i].'", reason="'.$reason[$i].'", rts="'.$rts[$i].'" WHERE BUNO ="'.$buno[$i].'"'; $update = mysql_query($update_qr) or die('Invalid query: ' . mysql_error()); header('Location: update.php'); } } else { print '

Nothing was updated

'; } } else { echo "The Update button has not been clicked"; } ?>

[/php]

function.php
[php]
$a = array(‘0’ =>’’,‘1’=>‘UP’ ,‘2’=>‘DOWN’ ,‘3’=>‘FCF’, ‘4’=>‘OOR’);

function statuslist($array){
foreach ($array as $key => $value){
print ‘’.$value.’’;
}
}
[/php]

#5

I figured it out!

[php]

<?php include 'acinclude/config.php'; include 'acinclude/function.php'; include 'login/include/session.php'; if(isset($_POST['submit'])){ foreach($_POST['acstatus'] as $i=>$value){ if($value != 0) { $buno = $_POST['buno'][$i]; $modex = $_POST['modex'][$i]; $acstatus = $_POST['acstatus'][$i]; $reason = $_POST['reason'][$i]; $rts = $_POST['rts'][$i]; $uname = $_POST['user'][$i]; $insert_hist = 'INSERT INTO status_hist (BUNO, MODEX, STATUS, username, reason, rts) VALUES ('.$buno.', '.$modex.', "'.$acstatus.'", "'.$uname.'", "'.$reason.'", "'.$rts.'")'; $insert = mysql_query($insert_hist) or die('Invalid query: ' . mysql_error()); $update_qr = 'UPDATE status SET STATUS ="'.$acstatus.'", username ="'.$uname.'", reason="'.$reason.'", rts="'.$rts.'" WHERE BUNO ="'.$buno.'"'; $update = mysql_query($update_qr) or die('Invalid query: ' . mysql_error()); header('Location: update.php'); } else { print '

Nothing was updated,/p>'; } } } else { echo "The Update button has not been clicked"; } ?>

[/php]

Sponsor our Newsletter | Privacy Policy | Terms of Service