Update Doesn't Work

**Rabbit** · Oct 3 '11, 06:02 PM

If you're trying to qualify a table/field/schema name, I think you have to use ` and not '.

**Antonio Bacasno** · Oct 4 '11, 07:34 AM

I tried revising the code of my PROCESSING page as per your suggestion, and still the same problem occurred. Please kindly check my code. Also, I've attached an image showing the error.

Code:

<?php include("includes/db_connect.php"); ?>
<?php require_once("includes/functions.php"); ?>

<?php

	$equipid = mysqli_real_escape_string($db_cxn, trim(strip_tags($_POST['equipid'])));
	$equipname = mysqli_real_escape_string($db_cxn, trim(htmlspecialchars($_POST['equipname'])));
	$manufacturer = mysqli_real_escape_string($db_cxn, trim(htmlspecialchars($_POST['manufacturer'])));
	$serno = mysqli_real_escape_string($db_cxn, trim(htmlspecialchars($_POST['serno'])));
	$modelno = mysqli_real_escape_string($db_cxn, trim(htmlspecialchars($_POST['modelno'])));
	$capacity = mysqli_real_escape_string($db_cxn, trim(htmlspecialchars($_POST['capacity'])));
	$curloc = mysqli_real_escape_string($db_cxn, trim(strip_tags($_POST['curlocation'])));
	$equipcode = mysqli_real_escape_string($db_cxn, trim(strip_tags($_POST['equipcode'])));
	$seqno = mysqli_real_escape_string($db_cxn, trim(strip_tags($_POST['seqno'])));	
	$addDate = mysqli_real_escape_string($db_cxn, trim(strip_tags($_POST['adddate'])));
	
	
	$query_update = "UPDATE `tblequipmentmasterlist` SET ";
	$query_update .= "`equipname` = '{$equipname}', ";
	$query_update .= "`manufacturer` = '{$manufacturer}', ";
	$query_update .= "`serno` = '{$serno}', ";
	$query_update .= "`modelno` = '{$modelno}', "; 
	$query_update .= "`capacity` = '{$capacity}', "; 
	$query_update .= "`curlocation` = '{$curloc}', "; 
	$query_update .= "`equipcode` = '{$equipcode}', "; 
	$query_update .= "`seqno` = '{$seqno}', "; 
	$query_update .= "`adddate` = '{$addDate}' ";
	$query_update .= "WHERE `tblequipmentmasterlist`.`equipid` = {$equipid} LIMIT 1";
					
					
	if (mysqli_affected_rows() == 1) {
		header ("LOCATION : updatedata.php");
	} else {
		print_r($_POST);
		var_dump($_POST);
		echo "<p>Update failed : <br />". $query_update.  " <br/>Please contact the site administrator.</p>";
		echo "<p>" . mysqli_error($db_cxn) . "</p>";
	}
		
?>

<?php 
	if (isset($db_cxn)) {
		mysqli_close($db_cxn); 
	}
?>

Attached Files

update_error.jpg (53.1 KB, 333 views)

**Rabbit** · Oct 4 '11, 03:18 PM

I can't really see what the error says, it's too small. Have you tried outputting the SQL and running it manually through the DBMS, which I'm assuming is MySQL?

**Antonio Bacasno** · Oct 4 '11, 05:15 PM

If you mean if I tried updating via the MySQL console, YES, I tried that and it works fine. I was able to update the database manually. I'm sorry about the image, I just can't have a larger image due to the forum restrictions (500 x 600) so I have to resize it before posting it here. Please find again the image on this link:

http://i729.photobucket.com/albums/ww293/AN2N10/UpdateError.jpg

Also, I made some revision with the mysqli_num_rows function. Please find the code here:

Code:

<?php include("includes/db_connect.php"); ?>
<?php require_once("includes/functions.php"); ?>

<?php

	$equipid = mysqli_real_escape_string($db_cxn, trim(strip_tags($_POST['equipid'])));
	$equipname = mysqli_real_escape_string($db_cxn, trim(htmlspecialchars($_POST['equipname'])));
	$manufacturer = mysqli_real_escape_string($db_cxn, trim(htmlspecialchars($_POST['manufacturer'])));
	$serno = mysqli_real_escape_string($db_cxn, trim(htmlspecialchars($_POST['serno'])));
	$modelno = mysqli_real_escape_string($db_cxn, trim(htmlspecialchars($_POST['modelno'])));
	$capacity = mysqli_real_escape_string($db_cxn, trim(htmlspecialchars($_POST['capacity'])));
	$curloc = mysqli_real_escape_string($db_cxn, trim(strip_tags($_POST['curlocation'])));
	$equipcode = mysqli_real_escape_string($db_cxn, trim(strip_tags($_POST['equipcode'])));
	$seqno = mysqli_real_escape_string($db_cxn, trim(strip_tags($_POST['seqno'])));	
	$addDate = mysqli_real_escape_string($db_cxn, trim(strip_tags($_POST['adddate'])));
	
	
	$query_update = "UPDATE `tblequipmentmasterlist` SET ";
	$query_update .= "`equipname` = '{$equipname}', ";
	$query_update .= "`manufacturer` = '{$manufacturer}', ";
	$query_update .= "`serno` = '{$serno}', ";
	$query_update .= "`modelno` = '{$modelno}', "; 
	$query_update .= "`capacity` = '{$capacity}', "; 
	$query_update .= "`curlocation` = '{$curloc}', "; 
	$query_update .= "`equipcode` = '{$equipcode}', "; 
	$query_update .= "`seqno` = '{$seqno}', "; 
	$query_update .= "`adddate` = '{$addDate}' ";
	$query_update .= "WHERE `tblequipmentmasterlist`.`equipid` = {$equipid} LIMIT 1";
					
					
	if (mysqli_affected_rows($db_cxn) == 1) {
		header ("LOCATION : updatedata.php"); //Record was successfully updated
	} else {
		print_r($_POST);
		var_dump($_POST);
		echo "<p>Update failed : <br />". $query_update.  " <br/>Please contact the site administrator.</p>";
		echo "<p>" . mysqli_error($db_cxn) . "</p>";
	}
		
?>

<?php 
	if (isset($db_cxn)) {
		mysqli_close($db_cxn); 
	}
?>

**Rabbit** · Oct 6 '11, 10:55 PM

When you say that you tried updating via the MySQL console, what does that mean? What SQL string did you use? Did you output the SQL string from PHP and then use that? Or did you manually type it in?

**Antonio Bacasno** · Oct 7 '11, 04:47 PM

I only tried a manual update of my database using the MySQL console to check if the commands are right and working if encoded directly in MySQL. My MySQL version has GUI (I'm using WAMP), so I can also directly check if my webpage did changed my database. Right now, I don't really know what's the problem with my code, the data variables from GETDATA page is being transferred to my PROCESSING page, as the print_r and var_dump shows from the imaged linked, but the data stops there, it doesn't get into my database, whatsoever.

**Rabbit** · Oct 7 '11, 05:41 PM

Instead of the manual update, try outputting the string from PHP and then pasting that into MySQL to see if it works.

**Antonio Bacasno** · Oct 7 '11, 06:02 PM

You mean the whole UPDATE string from my PHP and then pasting it to the MySQL sql query? Ok, I'll try it, thanks for the advice, hope this helps...

**Rabbit** · Oct 7 '11, 06:32 PM

Yep, I'm thinking there might be an issue with the string creation. Outputting it and using what the PHP engine uses will either confirm or deny it. And then we can proceed from there.

**Antonio Bacasno** · Oct 8 '11, 07:29 AM

Please find the linked images. As per your suggestion, I copied the PHP string and then pasted it on the MySQL sql query, and it successfully updated my dbase.

***The data to be be sent as an update***

***Update failed, copied the query string***

***Database doesn't show any update activity at all***

***Pasted the copied string***

***Query was a success!***

***Update was reflected on the main dbase***

**Rabbit** · Oct 9 '11, 03:28 AM

I can't believe I missed this but you didn't actually run the update query in your code. You create the string but then you don't execute it.

**Antonio Bacasno** · Oct 9 '11, 04:26 AM

Could you please elaborate?

**Rabbit** · Oct 9 '11, 05:06 AM

After you build the string, you need to execute it using the mysql_query($qu ery_update) function.

**Antonio Bacasno** · Oct 9 '11, 09:20 AM

Wow, thanks dude, that surely solves the UPDATE problem. But, I can't make my redirect to work. When a successful update was performed, it should redirect to UPDATEDATA.PHP. What happens is it only opens a blank PROCESSFORM_UPD ATE.PHP page.

Code:

<?php include("includes/db_connect.php"); ?>
<?php require_once("includes/functions.php"); ?>

<?php

	$equipid = mysqli_real_escape_string($db_cxn, trim(strip_tags($_POST['equipid'])));
	$equipname = mysqli_real_escape_string($db_cxn, trim(htmlspecialchars($_POST['equipname'])));
	$manufacturer = mysqli_real_escape_string($db_cxn, trim(htmlspecialchars($_POST['manufacturer'])));
	$serno = mysqli_real_escape_string($db_cxn, trim(htmlspecialchars($_POST['serno'])));
	$modelno = mysqli_real_escape_string($db_cxn, trim(htmlspecialchars($_POST['modelno'])));
	$capacity = mysqli_real_escape_string($db_cxn, trim(htmlspecialchars($_POST['capacity'])));
	$curloc = mysqli_real_escape_string($db_cxn, trim(strip_tags($_POST['curlocation'])));
	$equipcode = mysqli_real_escape_string($db_cxn, trim(strip_tags($_POST['equipcode'])));
	$seqno = mysqli_real_escape_string($db_cxn, trim(strip_tags($_POST['seqno'])));	
	$addDate = mysqli_real_escape_string($db_cxn, trim(strip_tags($_POST['adddate'])));
	
	
	$query_update = "UPDATE `tblequipmentmasterlist` SET ";
	$query_update .= "`equipname` = '{$equipname}', ";
	$query_update .= "`manufacturer` = '{$manufacturer}', ";
	$query_update .= "`serno` = '{$serno}', ";
	$query_update .= "`modelno` = '{$modelno}', "; 
	$query_update .= "`capacity` = '{$capacity}', "; 
	$query_update .= "`curlocation` = '{$curloc}', "; 
	$query_update .= "`equipcode` = '{$equipcode}', "; 
	$query_update .= "`seqno` = '{$seqno}', "; 
	$query_update .= "`adddate` = '{$addDate}' ";
	$query_update .= "WHERE `tblequipmentmasterlist`.`equipid` = {$equipid} LIMIT 1";
	
	$result = mysqli_query($db_cxn, $query_update);
	
	if (mysqli_affected_rows($db_cxn) == 1) {
		header ("LOCATION : updatedata.php"); //Record was successfully updated
	} else {
		print_r($_POST);
		var_dump($_POST);
		echo "<p>Update failed : <br />". $query_update.  " <br/>Please contact the site administrator.</p>";
		echo "<p>" . mysqli_error($db_cxn) . "</p>";
	}
		
?>

<?php 
	if (isset($db_cxn)) {
		mysqli_close($db_cxn); 
	}
?>

Another thing, how do I make a text field uneditable? I tried DISABLING my EQUIPID text field, but then I always encounter an UNDEFINED INDEX error. I would like my EQUIPID to be uneditable to prevent errors.

Update Doesn't Work

Update Doesn't Work

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment