Help Error updating submitted agent

[txapache]

Can someone please help me and look at these codes and tell me what I am not seeing.  I have similar controllers and forms and for some reason this one will not update when I submit the form.  I do not see my error and would appreciate a second set of eyes.  When I post I get a blank white screen with error message : "Error updating submitted agent!".

 

form:

<h1><?php htmlout($pagetitle); ?></h1>
<form action='?<?php htmlout($action); ?>' method="post">
<fieldset>
<legend>Agent Profile</legend>
	<div>
	<label for="group">Agents Group:</label><input type="text" name="group" id="group" value="<?php htmlout($group); ?>"/>
</div><div>
	<label for="company">Company:</label><input type="text" name="company" id="company" value="<?php htmlout($company); ?>"/>
</div><div>
	<label for="address">Address:</label><input type="text" name="address" id="address" value="<?php htmlout($address); ?>"/>
</div><div>
	<label for="city">City:</label><input type="text" name="city" id="city" value="<?php htmlout($city); ?>"/>
</div><div>
	<label for="county">County:</label><input type="text" name="county" id="county" value="<?php htmlout($county); ?>"/>
</div><div>
	<label for="state">State:</label><input type="text" name="state" id="state" value="<?php htmlout($state); ?>"/>
</div><div>
	<label for="zipcode">Zip Code:</label><input type="text" name="zipcode" id="zipcode" value="<?php htmlout($zipcode); ?>"/>
</div><div>
	<label for="phone">Contact Number:</label><input type="text" name="phone" id="phone" value="<?php htmlout($phone); ?>"/>
</div><div>
	<label for="poc">Point of Contact:</label><input type="text" name="poc" id="poc" value="<?php htmlout($poc); ?>"/>
</div><div>
	<label for="email">Email Address:</label><input type="text" name="email" id="email" value="<?php htmlout($email); ?>"/>
</div><div>
	<label for="password">Set Password:</label><input type="text" name="password" id="password" value="<?php htmlout($password); ?>"/>
</div><div>
	<fieldset>
		<legend>Roles:</legend>
		<?php for ($i = 0; $i < count($roles); $i++): ?>
			<div>
			<label for="role<?php echo $i; ?>">
			<input type="checkbox" name="roles[]" id="role<?php echo $i; ?>" value="<?php htmlout($roles[$i]['id']); ?>"<?php
			if ($roles[$i]['selected'])
			{ 
			echo ' checked="checked"';
			}
			?>/><?php htmlout($roles[$i]['id']); ?></label>:
			<?php htmlout($roles[$i]['description']); ?>
			</div>
			<?php endfor; ?>
			</fieldset>

 

Controller for Updating/Editing:

<?php
if (isset($_POST['action']) and $_POST['action'] == 'Edit')
{ 
include 'db_inc.php';

$id = mysqli_real_escape_string($link, $_POST['id']);// Fetch records to update
$sql = "SELECT * FROM agent WHERE id='$id'";
$result = mysqli_query($link, $sql);
if (!$result)
{
	$error = 'Error fetching agent details.';
	include 'error_html.php';
	exit();
}
$row = mysqli_fetch_array($result);
$pagetitle = 'Edit Agent';
$action = 'editform';
$group = $row['group'];
$company = $row['company'];
$address = $row['address'];
$city = $row['city'];
$county = $row['county'];
$state = $row['state'];
$zipcode = $row['zipcode'];
$phone = $row['phone'];
$poc = $row['poc'];
$email = $row['email'];
$password = $row['password'];
$id = $row['id'];
$button = 'Update Agent';

//Get list of roles assigned to this agent
$sql = "SELECT roleid FROM agentrole WHERE agentid = '$id'";
$result = mysqli_query($link, $sql);
if (!$result)
{
$error = 'Error fetching list of assigned roles.';
include 'error_html.php';
exit();
}

$selectedRoles[] = array();
while ($row = mysqli_fetch_array($result))
{
$selectedRoles[] = $row['roleid'];
}

//Build the list of all roles
$sql = "SELECT id, description FROM role";
$result = mysqli_query($link, $sql);
if (!$result)
{
$error = 'Error fetching list of roles.';
include 'error_html.php';
exit();
}

while ($row = mysqli_fetch_array($result))
{
$roles[] = array(
	'id' => $row['id'],
	'description' => $row['description'],
	'selected' => in_array($row['id'], $selectedRoles));
}
include 'agent_form.php';
exit();
}

if (isset($_GET['editform']))
{
include 'db_inc.php';

$id = mysqli_real_escape_string($link, $_POST['id']);
$group = mysqli_real_escape_string($link, $_POST['group']);
$company = mysqli_real_escape_string($link, $_POST['company']);
$address = mysqli_real_escape_string($link, $_POST['address']);
$city = mysqli_real_escape_string($link, $_POST['city']);
$county = mysqli_real_escape_string($link, $_POST['county']);
$state = mysqli_real_escape_string($link, $_POST['state']);
$zipcode = mysqli_real_escape_string($link, $_POST['zipcode']);
$phone = mysqli_real_escape_string($link, $_POST['phone']);
$poc = mysqli_real_escape_string($link, $_POST['poc']);
$email = mysqli_real_escape_string($link, $_POST['email']);
$password = mysqli_real_escape_string($link, $_POST['password']);
$sql = "UPDATE agent SET
	group = '$group',
	company = '$company',
	address = '$address',
	city = '$city',
	county = '$county',
	state = '$state',
	zipcode = '$zipcode',
	phone = '$phone',
	poc = '$poc',
	email = '$email',
	password = '$password'
	WHERE id = '$id'";
if (!mysqli_query($link, $sql))
{ 
	$error = 'Error updating submitted agent.';
	include 'error_html.php';
	exit();
}

if ($_POST['password'] != '')
{
$password = md5($_POST['password']);
$password = mysqli_real_escape_string($link, $password);
$sql = "Update agent SET
	password = '$password'
	WHERE id = '$id'";
if (!mysqli_query($link, $sql))
{
	$error = 'Error setting agent password.';
	include 'error_html.php';
	exit();
}
}

$sql = "DELETE FROM agentrole WHERE agentid = '$id'";
if (!mysqli_query($link, $sql))
{
$error = 'Error removing obsolete agent role entries.';
include 'error_html.php';
exit();
}

if (isset($_POST['role']))
{
foreach ($_POST['roles'] as $role)
{
	$roleid = mysqli_real_escape_string($link, $role);
	$sql = "INSERT INTO agentrole SET
		agentid = '$id',
		roleid = '$roleid'";
	if (!mysqli_query($link, $sql))
	{
		$error = 'Error assigning selected role to agent.';
		include 'error_html.php';
		exit();
	}
}
}

header('Location: admin.html');//redirect browser to admin page
}
?>

[PFMaBiSmAd]

Your code has error messages for the user (your $error = "..."; statements), but you need to also (always) have application error handling, where you display/log the actual information about errors that occur when your code runs.

 

If you use a trigger_error; statement in addition to your $error = "..."; statements, you can get php to automatically display and/or log information about the problem. Use something like -

 

trigger_error("Query failed: $sql, Reason: " . mysqli_error($link));

 

If you set error_reporting to E_ALL (it should always be set to this value or to a -1) and set display_errors to ON for your development system, the trigger_error statement will display the information you pass it along with the filename and line number (of the trigger_error statement) where the error is occurring at. On a live site, you would have display_errors set to OFF and log_errors set to ON, so that the application error information will be logged but not displayed.

 

Once you do this, you should be getting an sql syntax error at the group column name in the query because group is a reserved mysql keyword. You need to either rename your column to something else or you will need to enclose it in back-ticks `` every time you use it in a query. There may be other errors in the query, which is why you should always have application level error reporting/logging logic in your code.

[txapache]

Where would I place the trigger statement at the beginning of the code or where the current error statement is?  And if I understand correctly I have to change table column "group" to somethng like "agentgroup".

[txapache]

Chaanged "group" to "agentgroup" and it worked no error message, BUT it did not update the "agentrole" SET nor give me the error message 'Error assigning selected role to agent.'

[PFMaBiSmAd]

if (isset($_POST['role']))

 

^^^ Do you have a form field named 'role', so that if(){} statement will be true?

[txapache]

Do have a form fiel role.  Think I figured it out, I needed to add 1st before I can edit.  I had manually added agent and did not add role manually so therefore I could not edit.  Deleted manual add and use online form to add and it added agent to "agent" SET and "role" SET.  Thanks you have been mighty helpful.  Just don't understand why all was working on home "localhost" and when I uploaded to server it all went to %^$&.  Next, email controller.