need help with updating mysql records

spowel4 · July 11, 2008

I've got a table which displays records from a mysql database; the idea is to allow editing of the information and then clicking on a submit button to save the changes back to the database. It's not working and since I'm new at this I'd appreciate some pointers. I'm not asking for someone to write my code for me but if someone could give me some clues as to what's wrong/how to change it that would be great.

My code:

<html>
<head><title>Edit Southware Customer Information</title></head>

<body>
<?php
$cust=$_POST['list1'];
//echo "cust: $cust";

// connect to db
$conn = mysql_connect('localhost','root') or trigger_error("SQL", E_USER_ERROR); 
$db = mysql_select_db('sw_customers',$conn) or trigger_error("SQL", E_USER_ERROR);

$sql = "select custName, contact, address1, address2, address3, city, state, zipcode, Phone, Fax, contractNum, rev, userCount,
productCode, productKey, serialNum, custAuth, prodAuth, licenseNum, installPath from customer_info where custName like '$cust'";
$result = mysql_query($sql, $conn) or trigger_error("SQL", E_USER_ERROR);
$row = mysql_fetch_assoc($result);
echo "<form action= '{$_SERVER['PHP_SELF']}' method=post>";
echo "<table border=1, cellpadding=1, cellspacing=1>";
//	echo "row: $row[custName]<br/>";
echo "<tr><td width=200 font size=7><font face=Tahoma>Customer Name</td><td width=400><input type=text style='width:400px; font-family:Tahoma; font-size:16' name=custName value='$row[custName]'></td></tr>";
echo "<tr><td font size=7><font face=Tahoma>Contact</td><td width=400><input type=text style='width:400px; font-family:Tahoma; font-size:16' name=contact value='$row[contact]'></td></tr>";
echo "<tr><td font size=7><font face=Tahoma>Address Line 1</td><td width=400><input type=text style='width:400px; font-family:Tahoma; font-size:16' name=address1 value='$row[address1]'></td></tr>";
echo "<tr><td font size=7><font face=Tahoma>Address Line 2</td><td width=400><input type=text style='width:400px; font-family:Tahoma; font-size:16' name=address2 value='$row[address2]'></td></tr>";
echo "<tr><td font size=7><font face=Tahoma>Address Line 3</td><td width=400><input type=text style='width:400px; font-family:Tahoma; font-size:16' name=address3 value='$row[address3]'></td></tr>";
echo "<tr><td font size=7><font face=Tahoma>City</td><td width=400><input type=text style='width:400px; font-family:Tahoma; font-size:16' name=city value='$row[city]'></td></tr>";
echo "<tr><td font size=7><font face=Tahoma>State</td><td width=400><input type=text style='width:400px; font-family:Tahoma; font-size:16' name=state value='$row[state]'></td></tr>";
echo "<tr><td font size=7><font face=Tahoma>Zipcode</td><td width=400><input type=text style='width:400px; font-family:Tahoma; font-size:16' name=zipcode value='$row[zipcode]'></td></tr>";
echo "<tr><td font size=7><font face=Tahoma>Phone Number</td><td width=400><input type=text style='width:400px; font-family:Tahoma; font-size:16' name=phone value='$row[phone]'></td></tr>";
echo "<tr><td font size=7><font face=Tahoma>Fax Number</td><td width=400><input type=text style='width:400px; font-family:Tahoma; font-size:16' name=fax value='$row[fax]'></td></tr>";
echo "<tr><td font size=7><font face=Tahoma>Contract Number</td><td width=400><input type=text style='width:400px; font-family:Tahoma; font-size:16' name=contractNum value='$row[contractNum]'></td></tr>";
echo "<tr><td font size=7><font face=Tahoma>Revision</td><td width=400><input type=text style='width:400px; font-family:Tahoma; font-size:16' name=rev value='$row[rev]'></td></tr>";
echo "<tr><td font size=7><font face=Tahoma>User Count</td><td width=400><input type=text style='width:400px; font-family:Tahoma; font-size:16' name=userCount value='$row[userCount]'></td></tr>";
echo "<tr><td font size=7><font face=Tahoma>Product Code</td><td width=400><input type=text style='width:400px; font-family:Tahoma; font-size:16' name=productCode value='$row[productCode]'></td></tr>";	
echo "<tr><td font size=7><font face=Tahoma>Product Key</td><td width=400><input type=text style='width:400px; font-family:Tahoma; font-size:16' name=productKey value='$row[productKey]'></td></tr>";
echo "<tr><td font size=7><font face=Tahoma>Serial Number</td><td width=400><input type=text style='width:400px; font-family:Tahoma; font-size:16' name=serialNum value='$row[serialNum]'></td></tr>";
echo "<tr><td font size=7><font face=Tahoma>Customer Authorization</td><td width=400><input type=text style='width:400px; font-family:Tahoma; font-size:16' name=custAuth value='$row[custAuth]'></td></tr>";
echo "<tr><td font size=7><font face=Tahoma>Product Authorization</td><td width=400><input type=text style='width:400px; font-family:Tahoma; font-size:16' name=prodAuth value='$row[prodAuth]'></td></tr>";
echo "<tr><td font size=7><font face=Tahoma>License Number</td><td width=400><input type=text style='width:400px; font-family:Tahoma; font-size:16' name=licenseNum value='$row[licenseNum]'></td></tr>";
echo "<tr><td font size=7><font face=Tahoma>Install Path</td><td width=400><input type=text style='width:400px; font-family:Tahoma; font-size:16' name=installPath value='$row[installPath]'></td></tr>";
echo "</table>";
echo "</br>";
echo "<input type=submit style='height:40px; width:150px' name=editCust value='Submit Changes'>";
?>

<?php
if(isset($_POST['submit'])) {
echo $row[custName];
echo $row[city];
update();
}
?>

<?php
function update() {
$custName=$_POST['custName'];
$contact=$_POST['contact'];
$address1=$_POST['address1'];
$address2=$_POST['address2'];
$address3=$_POST['address3'];
$city=$_POST['city'];
$state=$_POST['state'];
$zipcode=$_POST['zipcode'];
$phone=$_POST['phone'];
$fax=$_POST['fax'];
$contractNum=$_POST['contractNum'];
$rev=$_POST['rev'];
$userCount=$_POST['userCount'];
$productCode=$_POST['productCode'];
$productKey=$_POST['productKey'];
$serialNum=$_POST['serialNum'];
$custAuth=$_POST['custAuth'];
$prodAuth=$_POST['prodAuth'];
$licenseNum=$_POST['licenseNum'];
$installpath=$_POST['installPath'];



$sql = "UPDATE customer_info SET custName='$custName', contact='$contact',
address1='$address1', address2='$address2', address3='$address3', city='$city', state='$state', zipcode='$zipcode',
phone='$phone', fax='$fax', contractNum='$contractNum', rev='$rev',  userCount='$userCount', productCode='$productCode',
productKey='$productKey', serialNum='$serialNum', custAuth='$custAuth', prodAuth='$prodAuth', licenseNum='$licenseNum',
installPath='$installPath' WHERE custNum = 1011 LIMIT 1";
mysql_query($sql, $conn);
}
?>	
</form>
</body>
</html>

waynew · July 11, 2008

Add this to the end of the update query in order to troubleshoot:

mysql_query($sql, $conn) or die(mysql_error());

chronister · July 11, 2008

Aside from the previous suggestion of or die()..... when I have an issue with a query I will echo the sql and copy & paste into phpmyadmin and see what I get.

Typically the results from or die(mysql_error()) and the error produced in phpmyadmin should be the same

You can also do this...

$result = mysql_query($sql, $conn);

if(mysql_affected_rows($result) > 0)
{
     echo 'Query sucessful';
}
else
{
     echo mysql_error();
}

Good luck

spowel4 · July 11, 2008

Thanks for the suggestions. I tried both of your suggestions and what I'm seeing is that if I run the query through the php script I get no error messages but the records don't get updated either. If I run the query through phpmyadmin the query runs successfully and the records do get updated.

Where do I look next?

chronister · July 11, 2008

echo echo echo....

echo things during each piece of your script to see where it is bombing out...

$conn = mysql_connect('localhost','root') or trigger_error("SQL", E_USER_ERROR);

Shouldn't there be a password here??

Sign In

need help with updating mysql records

Recommended Posts

spowel4

Link to comment

Share on other sites

waynew

Link to comment

Share on other sites

chronister

Link to comment

Share on other sites

spowel4

Link to comment

Share on other sites

chronister

Link to comment

Share on other sites

Archived

Browse

Activity

Important Information