PDA

View Full Version : Update script not working


helpme
04-17-2006, 02:29 AM
Hi. Using PHP, MySQL & DWMX, I'm trying to run a php script that will update the status field in a record, but when I try to update a selected record (by app_id) , the selected record will not update. When I use the update script without the where clause, the script will update all of the records in the database. Here's my code for update.php:

<?php
$db_name="applications"; // Database name
$tbl_name="application"; // Table name
$app_status = $_POST['app_status'];
$app_id = $_POST['app_id'];

mysql_select_db($database_applications, $applications);
$sql="SELECT * FROM $tbl_name WHERE app_id='$app_id'";
$result=mysql_query($sql);

$rows=mysql_fetch_array($result);
mysql_select_db($database_applications, $applications);
$sql="UPDATE $tbl_name SET app_status='$app_status' where app_id='$app_id'";
$result=mysql_query($sql);
?>

new_application.php shows the details that a customer submitted on an application. The admin user gets to this page and there is an app_status dropdown field. The user should be able to select a value, and when the Update button is selected, update.php is supposed to update the selected record.

Your help is greatly appreciated!!!!

davidj
04-19-2006, 07:40 AM
you need an SPRINTF

try this...

<?php
$db_name="applications"; // Database name
$tbl_name="application"; // Table name
$app_status = $_POST['app_status'];
$app_id = $_POST['app_id'];

mysql_select_db($database_applications, $applications);
$sql=sprintf("SELECT * FROM '%s' WHERE app_id='%s'",$tbl_name, $app_id); ///// <<< sprintf
$result=mysql_query($sql);

$rows=mysql_fetch_array($result);
mysql_select_db($database_applications, $applications);
$sql=sprintf("UPDATE '%s' SET app_status='%s' where app_id='%s'", $tbl_name, $app_status', $app_id);
$result=mysql_query($sql);
?>

if this doesnt work then you need to echo each statemement to see what it contains

helpme
04-20-2006, 01:53 AM
thanks for the help david. i tried your code, and it's still not working, so i echoed the statements. this is what i get:

1SELECT * FROM 'application' WHERE app_id=''1UPDATE 'application' SET app_status='Approved' where app_id=''

looks like it's not seeing the app_id in either of the sql statements

davidj
04-20-2006, 07:35 AM
ok
you are getting this var from > $app_id = $_POST['app_id'];

make sure that app_id is the name of your formfield and that it contains data

How does this formfield get data??? Is it user input???

helpme
04-21-2006, 12:08 AM
the way i've got it working, is there is a form where users submit their application. then the admin user goes to the admin section and a page displays all of the applications that have been submitted. they select one of the records and the details page is displayed, showing all of the information for the selected application. this is the code from that page:

code tags added - dj
<?php
$db_name="apps"; // Database name
$tbl_name="application"; // Table name

//select the database
mysql_select_db($database_applications, $applications);

$sql="SELECT * FROM $tbl_name WHERE app_id='$app_id'";
$result=mysql_query($sql);

$rows=mysql_fetch_array($result);
?>
<style type="text/css">
<!--
body {
background-image: url(Graphics/background.jpg);
}
.style3 {font-weight: bold}
.style5 {font-size: 12px}
.style6 {
font-size: 14px;
font-weight: bold;
}
.style2 {font-size: 14px}
-->
</style>
<title>Applications</title>





</p>
<table width="400" border="0" align="center" cellpadding="0" cellspacing="1" bgcolor="#CCCCCC">
<tr>
<td><table width="100%" border="0" cellpadding="3" cellspacing="1" bordercolor="1" bgcolor="#FFFFFF">
<tr>
<td width="38%" bgcolor="#F8F7F1"><span class="style2 style1">Application ID</span></td>
<td width="62%" bgcolor="#F8F7F1"><span class="style2 style1"><? echo $rows['app_id']; ?></span></td>
</tr>

<tr>
<td colspan="2" bgcolor="#F8F7F1"><div align="center"><span class="style6">Applicant Information</span></div></td>
</tr>
<tr>
<td bgcolor="#F8F7F1"><span class="style5">First Name</span></td>
<td bgcolor="#F8F7F1"><span class="style3"><? echo $rows['first_name']; ?></span></td>
</tr>

<tr>
<td bgcolor="#F8F7F1"><span class="style5">Middle Initial</span></td>
<td bgcolor="#F8F7F1"><span class="style3"><? echo $rows['middle_initial']; ?></span></td>
</tr>

<tr>
<td bgcolor="#F8F7F1"><span class="style5">Last Name</span></td>
<td bgcolor="#F8F7F1"><span class="style3"><? echo $rows['last_name']; ?></span></td>
</tr>
<tr>
<td bgcolor="#F8F7F1"><span class="style5">Suffix</span></td>
<td bgcolor="#F8F7F1"><span class="style3"><? echo $rows['suffix']; ?></span></td>
</tr>
<tr>
<td bgcolor="#F8F7F1"><span class="style5">Street</span></td>
<td bgcolor="#F8F7F1"><span class="style3"><? echo $rows['street']; ?></span></td>
</tr>
<tr>
<td bgcolor="#F8F7F1"><span class="style5">City</span></td>
<td bgcolor="#F8F7F1"><span class="style3"><? echo $rows['city']; ?></span></td>
</tr>
<tr>
<td bgcolor="#F8F7F1"><span class="style5">State</span></td>
<td bgcolor="#F8F7F1"><span class="style3"><? echo $rows['state']; ?></span></td>
</tr>
<tr>
<td bgcolor="#F8F7F1"><span class="style5">Zip Code</span></td>
<td bgcolor="#F8F7F1"><span class="style3"><? echo $rows['zip']; ?></span></td>
</tr>
<tr>
<td bgcolor="#F8F7F1"><span class="style5">Daytime Phone </span></td>
<td bgcolor="#F8F7F1"><span class="style3"><? echo $rows['day_phone']; ?></span></td>
</tr>
<tr>
<td bgcolor="#F8F7F1"><span class="style5">Evening Phone </span></td>
<td bgcolor="#F8F7F1"><span class="style3"><? echo $rows['eve_phone']; ?></span></td>
</tr>
<tr>
<td bgcolor="#F8F7F1"><span class="style5">Email Address </span></td>
<td bgcolor="#F8F7F1"><span class="style3"><? echo $rows['email']; ?></span></td>
</tr>
<tr>
<td bgcolor="#F8F7F1"><span class="style5">Date</span></td>
<td bgcolor="#F8F7F1"><span class="style3"><? echo $rows['date']; ?></span></td>
</tr>
</table></td>
</tr>
</table>


<form name="form1" method="post" action="update.php">



<select name="app_status">
<option>Select One</option>
<option>Approved</option>
<option>Rejected</option>
<option>Pending</option>
</select>
</p>



<input type="submit" name="Submit" value="Update">
</p>
</form>at the bottom of the page is a dropdown field where the admin user can select a status and when they select the Update button, the status for that application needs to be changed to whatever they selected in the dropdown.

davidj
04-21-2006, 07:47 AM
you are not setting a var for app_id

example..

<?php
$db_name="apps"; // Database name
$tbl_name="application"; // Table name

//select the database
mysql_select_db($database_applications, $applications);

$sql="SELECT * FROM $tbl_name WHERE app_id='$app_id'"; //<<<< not set
$result=mysql_query($sql);

where are you getting this value from ($app_id')???