[RESOLVED] Can't update tables

[tams]

Hi,
I am trying to update mysql tables and the code works fine without giving me any error but when I go and check my tables to see if they have been update and there is no change in them. Help please.

$query= "UPDATE personalinfo SET firstname='$_POST[firstname]',lastname='$_POST[lastname]',birthdate='$_POST[birthdate]',
sex='$_POST[sex]',contract='$_POST[contract]',jobtitle='$_POST[jobtitle]',jobcategory='$_POST[jobcategory]',
startdate='$_POST[startdate]',probationdate='$_POST[probationdate]',enddate='$_POST[enddate]',
contractlocation='$_POST[contractlocation]',managingsection='$_POST[managingsection]',
branch='$_POST[branch]',salary='$_POST[salary]',address='$_POST[address]'_POST[phone]' WHERE firstname='$_POST[firstname]' AND lastname='$_POST[lastname]'";
	$result=mysqli_query($cxn,$query) or die ("Couldn't execute query.".mysqli_error($cxn));
	echo "Personal Info Updated";

I get the message that personal info is updated but nothing seems to change in mysql tables.

Thank you

[NogDog]

The fact that the mysqli_query() command does not fail does not mean that any records were updated, just that the SQL was valid. You might try the following to help debug:

$result=mysqli_query($cxn,$query) or die ("Couldn't execute query.".mysqli_error($cxn));
if(!mysqli_affected_rows($cxn))
{
   user_error("No rows updated. Query: $query");
}

[tams]

OK, so that code does tell me that nothing was updated but I am still wondering why the code is not updating anything. Is it something to do with $_POST?

[NogDog]

If you used my suggested pattern, look carefully at the query string that the error message outputs. Do all the values that you expect to be filled in by the various $_POST values look correct, having the expected values?

If you copy-and-paste the WHERE clause of that query and do a SELECT with it, does it actually match any rows?

Lastly, by any chance are you using a transactional storage engine for the table in question (e.g.: InnoD😎 configured with auto_commit off, requiring you to do a [man]mysqli_commit()/man?

[tams]

Well, I think I figured what the problem is but I don't know how to fix it. I am posting more code so it will be easier to understand what is going on.

if($_POST['findpersonalinfo'] == "Find Personal Info")

{
$query="SELECT * FROM personalinfo WHERE firstname= '$_POST[ffirstname13]' AND lastname='$_POST[flastname13]'";
$result=mysqli_query($cxn,$query)
	or die ("Couldn't execute query.".mysqli_error($cxn));

 while($row=mysqli_fetch_assoc($result))
  {
   extract($row);
	echo " <form action='retrieve.php' method='POST'><table width='100%' border='0'><tr><td class='bold_centre'>First Name</td>

           			<td><input type='text' name='firstname' value='$row[firstname]'size='20' maxsize='20'></td>";
	echo "<td class='bold_centre'>Last Name</td>

           			<td><input type='text' name='lastname' value='$row[lastname]'size='20' maxsize='20'></td>";
	echo "<td class='bold_centre'>Date of Birth</td><td><input type='text' name='BirthDate'value='$row[birthdate]'

                  		size='20' maxsize='20'></td>";
	echo "<td class='bold_centre'>Sex</td><td><input type='text' name='Sex' value='$row[sex]'

                  		size='20' maxsize='20'></td></tr>";
	echo "<tr><td class='bold_centre'>Contract Status</td><td><input type='text' name='contract' value='$row[contract]'

                  		size='20' maxsize='20'></td>";
            echo "<td style='text-align: center' colspan='2'><br /><input type='submit' name='update1' value='Update Personal Info'></td>";
	echo "<td style='text-align: center' colspan='2'><br /><input type='submit' name='delete1' value='Delete Personal Info'></td></tr></table>";

}
echo "</form>";

}

elseif($_POST['update1']== "Update Personal Info")
{
	$query= "UPDATE personalinfo SET firstname='$_POST[firstname]',
lastname='$_POST[lastname]',birthdate='$_POST[birthdate]',sex='$_POST[sex]',
contract='$_POST[contract]' WHERE firstname='$_POST[firstname]' AND lastname='$_POST[lastname]'";
	$result=mysqli_query($cxn,$query) or die ("Couldn't execute query.".mysqli_error($cxn));
	echo "Personal Info Updated";
	if(!mysqli_affected_rows($cxn))
	{
   	user_error("No rows updated. Query: $query");
	} 
}

elseif($_POST['delete1']== "Delete Personal Info")
{
	$query= "Delete FROM personalinfo WHERE firstname='$_POST[firstname]' AND lastname='$_POST[lastname]'";
	$result=mysqli_query($cxn,$query) or die ("Couldn't execute query.".mysqli_error($cxn));
	echo "Personal Info Deleted";
}	

So the select statement selects the required info from two textboxes and displays the retrieved information in newly created textboxes.
The problem is that the other if statement for updating doesn't see the newly created textboxes and nothing is thus updated. So how do I fix this?

Thank you