Update query failed but successful message is retured

Hello.
First of all, I am actually new in PHP and HTML coding. So, please go easy on me.

Here is my problem,
My update query is not working which actually make me confused. My code seem to be fine (Well, for me) and I have checked the error log and nothing is wrong with the code (I even deleted it so it will generate a new one which it does not). The attribute and column names is match… The ‘UPDATE DATA’ message is returned which means the query should be successful, right? But, in this case, the message is triggered but the table hasn’t been updated.

Is there any issue while using an UPDATE command in any PHP code? If there is any, please let me know.
I stuck on this for nearly two weeks…

Thank you for your time.

[php]<?php
$sqlshowid = “SELECT conid FROM registerlogin”;
$insertsql = mysqli_query( $sqltable, $sqlshowid );

echo "<form method=post>";
echo "<table>";
	
echo "<select name=updatedata onchange=this.form.submit()>";
echo "<option style=display:none>Pick an ID</option>";
	
while( $showid = mysqli_fetch_array( $insertsql ) )
{
	echo "<option>".$showid['conid']."</option>";
} echo "</select>";
	
if( isset( $_POST['updatedata'] ) )
{
	$id = $_POST['updatedata'];
	$sqlshowall = "SELECT * FROM registerlogin WHERE conid='$id'";
	$displaydata = mysqli_query( $sqltable, $sqlshowall );
		
	$rowdata = mysqli_fetch_array( $displaydata );
		
	echo "<tr><td>ID</td><td>".$id."</td></tr>";
	echo "<tr><td>Fullname</td><td><input type=text name=usernameupdate value='".$rowdata['name']."'></td></tr>";
	echo "<tr><td>Password</td><td><input type=text name=passwordupdate value='".$rowdata['passwd']."'></td></tr>";
	echo "<tr><td>Email</td><td><input type=email name=emailupdate value='".$rowdata['email']."'></td></tr>";
			
	echo "<tr><td><td><button type=submit name=buttonupdate>Update</td></td></tr>";
	echo "</table>";
	echo "</form>";
}
	
if( isset( $_POST['buttonupdate'] ) )
{
	$updatename = $_POST['usernameupdate'];
	$updatepassword = $_POST['passwordupdate'];
	$updateemail = $_POST['emailupdate'];
		
	$sqlchange = "UPDATE registerlogin SET name='$updatename',passwd='$updatepassword',email='$updateemail' WHERE conid='$id'";
		
	$checkquery = mysqli_query( $sqltable, $sqlchange );
		
	if( $checkquery )
		echo "UPDATED DATA";
	else
		echo "Failed to update data";
}

?>[/php]

  1. Parameterized queries.
  2. DON’T STORE PLAIN TEXT PASSWORDS, EVER!

$id doesnt look like it is ever set for this use case. There isnt anything wrong with anything, there is just nothing to update.

Hello.

#1

Does that mean I have to escape all the string before storing into the database or use PDO instead?

#2

Yup, a guy from the other thread helped me. Using var_dump to reveal the stored variable and I just feel so dumb not to realize the importance of var_dump… :-[

Thank you.

The problem is the same as in the previous thread. The value you are using for the $id isn’t the chosen id, it’s the default ‘Pick an ID’ string (which may not even be that, depending on browser, since you are not using value attributes in the <option …> tags), since you haven’t made the id selection form field ‘sticky’ so that it remembers the previously selected id.

You are trying to mix different concerns in the code. Picking the id to edit, is a different concern from editing/updating the data. You should have two different forms and two different sets of form processing code.

The form to pick the id should use method = ‘get’, since it is determining what to display on the page when the edit form is produced. The edit form should method = ‘post’ and it should contain a hidden field with the id value that’s being edited.

The code should also be arranged with the post method form processing code near the top of the file. Any code to get/produce data needed to display the page should come after the post method form processing code. The actual html document should come at the end of the file.

If there are any validation errors in the post method form processing code, you should re-populate the form fields with the submitted form data, not the data from the database query. In fact, if the post method edit form has been submitted, you should skip running the database query code. The way to accomplish this ‘switch’ between what data to use to populate the form fields with, is to use an internal variable, $data or similar name, that you copy the submitted form data to inside the post method form processing code. At the database query code, if the $data variable is empty, you would run the query and fetch the result of the query into the $data variable. If the $data variable is not empty, you would skip running the database query code.

I recommend that you start with just the code you need to produce the form to select the id, making the correct option choice ‘sticky’ if there is already a submitted id, and the code to query for the data matching that id, storing the fetched row of data in the $data variable, then produce the post method edit form, populating the fields with the values in $data.

As has already been stated, you should store the hash of the password (see php’s password_hash() function) in the database table. You won’t be able to display the existing password, so you won’t be able to initially populate the password form field. You would be able to enter a new password in the form field and store its hashed value in the database table. You would need to write logic to make sure that if the submitted password field is empty, that you don’t alter the stored hash in the database table, by either leaving it out of the UPDATE query, or setting the passwd db column value to the same value it currently is.

Once you get the edit form so that it displays the initial data from the database query, then the submitted data from the form, write the post method form processing code to use the submitted form data.

The reason you are getting the “UPDATED DATA” message is because the query is executing without error, but because the WHERE clause is false, no row(s) are being updated. If you want to display a message only if the row is updated/changed, test the mysqli_affected_rows() value, not the value being returned by mysqli_query().

The problem is the same as in the previous thread. The value you are using for the $id isn't the chosen id, it's the default 'Pick an ID' string (which may not even be that, depending on browser, since you are not using value attributes in the tags), since you haven't made the id selection form field 'sticky' so that it remembers the previously selected id.
Thank you, phdr. I just realized that option tag can store a value attribute. I just searched and you are absolutely right. Your method would have save me the whole entire two weeks, for sure. :o

Thank you, I will bear in mind regarding your solution.

You are trying to mix different concerns in the code. Picking the id to edit, is a different concern from editing/updating the data. You should have two different forms and two different sets of form processing code.

The form to pick the id should use method = ‘get’, since it is determining what to display on the page when the edit form is produced. The edit form should method = ‘post’ and it should contain a hidden field with the id value that’s being edited.

The code should also be arranged with the post method form processing code near the top of the file. Any code to get/produce data needed to display the page should come after the post method form processing code. The actual html document should come at the end of the file.


You mean like this? It worked. This method that you provided also saves me some time and lines. Thank you, again!
[php]



<?php
while( $idrow = mysqli_fetch_array( $sqlpickquery ) )
echo “”.$idrow[‘conid’]."";
		$id = $_GET['pickselected'];
	        ?>
	</select>
</form>

<form method="post">
	<?php
	if( $id )
	{
	            $sqlshowidpicked = "SELECT * FROM registerlogin WHERE conid='$id'";
	            $sqlshowidquery = mysqli_query( $sqltable, $sqlshowidpicked );
	            $sqlshowalldata = mysqli_fetch_array( $sqlshowidquery );
				
	           echo "<tr><td>ID</td><td>".$id."</tr>";
	           echo "<tr><td>Fullname</td><td><input type=text name=updatename value='".$sqlshowalldata['name']."'></td></tr>";
	           echo "<tr><td>Password</td><td><input type=text name=updatepassword value='".$sqlshowalldata['passwd']."'></td></tr>";
	           echo "<tr><td>Email</td><td><input type=text name=updateemail value='".$sqlshowalldata['email']."'></td></tr>";
	           echo "<tr><td>Phone</td><td><input type=text name=updatephone value='".$sqlshowalldata['phone']."'></td></tr>";
	           echo "<tr><td><td><button type=submit name=submitbutton>Update</button></td></td></tr>";
	          echo "</form>";
        }
	
           if( isset( $_POST['submitbutton'] ) )		
           {
		$nameupdate = $_POST['updatename'];
		$passwordupdate = $_POST['updatepassword'];
		$emailupdate = $_POST['updateemail'];
		$phoneupdate = $_POST['updatephone'];
				
		$sqlupdated = "UPDATE registerlogin SET name='$nameupdate', passwd='$passwordupdate', email='$emailupdate', phone='$phoneupdate' WHERE conid='$id'";
		$sqlupdatedquery = mysqli_query( $sqltable, $sqlupdated );
				
		if( $sqlupdatedquery )
			echo "<script>alert('UPDATED DATA');</script>";
		else
			echo "<script>alert('FAILED TO UPDATE DATA');</script>";	
	}
	?>
[/php]
If there are any validation errors in the post method form processing code, you should re-populate the form fields with the submitted form data, not the data from the database query. In fact, if the post method edit form has been submitted, you should skip running the database query code. The way to accomplish this 'switch' between what data to use to populate the form fields with, is to use an internal variable, $data or similar name, that you copy the submitted form data to inside the post method form processing code. At the database query code, if the $data variable is empty, you would run the query and fetch the result of the query into the $data variable. If the $data variable is not empty, you would skip running the database query code.
Hey, I am not a native English speakers but I try to understand what you are trying to say that I should separate both on how to view and fetch data in a form right? (at least, make another form just to show the submitted data from the last data insertion and another form to see the data fetch from the table). I mean, like, I should see the changes that I have made in a form just to ensure that the data experience changes, right? Please rectify me if I understand it wrong.
I recommend that you start with just the code you need to produce the form to select the id, making the correct option choice 'sticky' if there is already a submitted id, and the code to query for the data matching that id, storing the fetched row of data in the $data variable, then produce the post method edit form, populating the fields with the values in $data.
Noted! I should practice this ethic. Always pass the data into a single variable (at least, a global variable) so whenever the data is needed it will always bring the same data for the whole code. Thank you. However, I did not get how you can store all the column in your '$data'? Is it by using array? Any more detail explanation would be lovely.
As has already been stated, you should store the hash of the password (see php's password_hash() function) in the database table. You won't be able to display the existing password, so you won't be able to initially populate the password form field. You would be able to enter a new password in the form field and store its hashed value in the database table. You would need to write logic to make sure that if the submitted password field is empty, that you don't alter the stored hash in the database table, by either leaving it out of the UPDATE query, or setting the passwd db column value to the same value it currently is.
This one is interesting. I do want to learn about password protection, like very bad. I went to many websites. Some recommend 'hashing','Salt and Hash' technique, and so on. Mostly, I saw codes using 'md5()' which is just a hashing technique. I would like to hear anyone's clarification on this so I can make a decision what type of method I should use in my coding style. However, I would like to apologize again, but what you mean by using logic to detect if the password field is not filled and the hashed password is not disturbed? What I understand from your writing, if the password is empty and it was updated into the table that somehow can trigger the stored hash inside the table? How is that? What the 'setting the passwd db column value to the same value it currently is' really means? I am sorry that I am pretty slow and dumb but I hate to not understanding something from the first place. Thank you.
Once you get the edit form so that it displays the initial data from the database query, then the submitted data from the form, write the post method form processing code to use the submitted form data.

The reason you are getting the “UPDATED DATA” message is because the query is executing without error, but because the WHERE clause is false, no row(s) are being updated. If you want to display a message only if the row is updated/changed, test the mysqli_affected_rows() value, not the value being returned by mysqli_query().


I do not understand this ‘post method form processing code’. Could you enlighten me?

Thank you for the precaution reminder, I will try to use that in order to notify me if there is any changes or not to the main code. Another method that should be useful for me in the future.

Overall, I am grateful for your explanation and I will try to do my best in order to keep things in order. I will try to implement some of your advice in my coding practice that I have just started (well, I just started coding PHP, HTML, and CSS last month so, yeah… haha).

Very much appreciate for your time and advice, sir. Again, Thank you very much.

Cheers,
Zaim.

Actually, let me go back one post -

A Parameterized query is also called a prepared query, where any dynamic data values in the sql query statement are replaced with place-holders, the query gets prepared as a separate step, then the actual values are supplied when the query gets executed.

The php mysqli extension supports prepared queries, but the programming interface is overly complicated and is inconstant between a non-prepared query and a prepared one. The php PDO extension is much simpler to use, is constant between a non-prepared query and a prepared one, and has the added benefit of being usable with other database types, without needing to learn a different set of php statements.

A true prepared query (PDO also has emulated prepared queries, which are open to sql injection if the character set php is using isn’t the same as your database tables) will protect against sql special characters in the data from breaking the sql syntax (which is how sql injection is accomplished) and will simplify the code, because you don’t need to have statements escaping the data.

A prepared query also simplifies the sql query syntax, because any php variables and single-quotes around the values are removed and replaced with a simple ? place-holder, one per value.

The net changes to convert a query containing dynamic data to a prepared query, at least when using the php PDO extension, is -

  1. Make the database connection using the php PDO extension.
  2. Remove any escaping logic in the code.
  3. Remove any php variables and surrounding single-quotes from the sql query statement and replace them with place-holders.
  4. Change the existing xxx_query() call to a prepare() method call.
  5. Call the execute() method with an array of the php variables that were removed from the sql query statement.
  6. Fetch data from a query using the PDO fetch() method or the fetchAll() method.
You mean like this? It worked.

Actually, the reason this works is because you changed the first form to method=‘get’ (the id is passed in the url now) and you removed the action=’…’ attribute from the edit form. By removing the action=’…’ attribute, the browser will ‘automatically’ propagate any existing get parameters in the url, so the id will now exist after the edit form is submitted. This isn’t exactly what I suggested, but it’s okay for now. It would be better if you passed the id in a hidden field in the post method edit form.

The first form still needs to be made ‘sticky’ by pre-selecting the <option choice that matches the submitted id value. The way to do this is to output the ‘selected’ attribute inside the correct <option …> tag. You should also have a value=’…’ attribute inside each <option …> tag. Most browsers will submit the option display value if there is no value=’…’ attribute, but some will not. It is best to always have a value=’…’ attribute.

If there are any validation errors in the post method form processing code, you should re-populate the form fields with the submitted form data, not the data from the database query. In fact, if the post method edit form has been submitted, you should skip running the database query code. The way to accomplish this 'switch' between what data to use to populate the form fields with, is to use an internal variable, $data or similar name, that you copy the submitted form data to inside the post method form processing code. At the database query code, if the $data variable is empty, you would run the query and fetch the result of the query into the $data variable. If the $data variable is not empty, you would skip running the database query code.
Hey, I am not a native English speakers but I try to understand what you are trying to say that I should separate both on how to view and fetch data in a form right? (at least, make another form just to show the submitted data from the last data insertion and another form to see the data fetch from the table). I mean, like, I should see the changes that I have made in a form just to ensure that the data experience changes, right? Please rectify me if I understand it wrong.

You currently don’t have any validation logic, which you need, so you currently aren’t trying to re-display the form with the submitted form data in it. You are always getting the original data from the database table when displaying the form… If there are validation errors, such as an empty username or using an incorrectly formatted email address, you would want to re-display the form with the submitted form data in it, not the original data from the database table, so that you don’t need to keep retyping the changes in the fields that don’t have any validation errors. The pseudo logic to do this would be -

[php]
$data = []; // define an array to hold a working copy of the data

…start of post method form processing code

$data = $_POST; // copy the form data to the common variable being used in the rest of the code
// actually, you should trim the data when you make this copy of it, so that you can detect if all white-space characters were entered

…end of post method form processing code

… start of code getting the original row of data from the database table
// if $data is empty
if(empty($data))
{
query to get the row from registerlogin and store it in $data
}
… end of code getting the original row of data from the database table

// at this point $data either contains the original data from the database table or the submitted form data. use the elements in $data for the form field value=’…’ attributes.

// you would display the form at this point, not as part of the code getting the original row of data from the database table.[/php]

However, I did not get how you can store all the column in your '$data'? Is it by using array?

Yes, it’s an array. From the code getting the original row of data from the database table, it’s the fetched row from the query. From the post method edit form processing code, it’s a copy of the submitted $_POST data.

Mostly, I saw codes using 'md5()' which is just a hashing technique. I would like to hear anyone's clarification on this so I can make a decision what type of method I should use in my coding style.

The php password_hash() and password_verify() functions are the current best method for hashing passwords. The hash uses a ‘stronger’ hash (the md5()/sha() hashes are easily brute force matched by today’s personal computing hardware), has a random salt value for each hashed password, and has a cost/number of times the hash is looped to slow down the process of brute force matching of input to hash value.

What I understand from your writing, if the password is empty and it was updated into the table that somehow can trigger the stored hash inside the table? How is that? What the 'setting the passwd db column value to the same value it currently is' really means?

Since the value stored in the database table is the hash of the password, there’s no point in selecting it and trying to put it into the password form field value. So, initially, all you can do is leave the password field empty. If the admin who is doing this wants to set a new password, they would enter the password in the form field.

In the form processing code, if there is a non-empty value for the password form field, you would apply password_hash() to it and set the password field in the database table with this hash. However, if the password form field is empty, it means to not change the value stored in the database table. If you are setting the password column in the database table with a new hash, the set part of the UPDATE query would need to contain passwd = ? (assuming you are using a prepared query with a place-holder for the value.) If you are not setting the password column, you would either leave the passwd = ? term out of the set part of the sql query statement, or to set the password column to the same value it currently has, you would need to make this part of the sql query be passwd = passwd. However, you cannot specify a column name via a prepared query place-holder, so you would need to dynamically build the sql query statement or select between two different UPDATE sql query statements.

I do not understand this 'post method form processing code'.

It’s the php code that is processing the post method form data. It in your current code, it starts with the if( isset( $_POST[‘submitbutton’] ) ). The reason for putting it near the top of your file is so that any changes that the form processing code makes, can be used by the rest of the code, and so that you can display any validation errors when you (re)display the form. In your current code, the post method form processing code is after the point where you are displaying the form, so, any errors would get displayed after the bottom of the form where they would be easy to miss and you currently won’t be able to re-populate the form field values with the submitted form data.

The code in your file should generally be laid out as follows -

  1. Initialization.
  2. Post method form processing.
  3. Get method ‘business logic’ - this is code that knows how to get/produce data needed to display the page.
  4. Get method ‘presentation logic’ - this is code that knows how to produce the dynamic content on the page. For simple things, you can just put this logic at the appropriate places in the html document.
  5. The actual html document.

Hopefully, this addresses all the questions you asked.

Sponsor our Newsletter | Privacy Policy | Terms of Service