Trying to update record but getting error?

[cmaclennan]

I have a page that I'm trying to use to edit a ticket from a mysql database entry but for some reason I keep getting my generated error that i cannot update it due to the ticket number already being registered? Can anyone tell me what i'm missing here?

 

Thanks

 

$page_title = 'Edit a Ticket';
include ('style.html');

// Check for a valid Ticket ID, through GET or POST.
if ( (isset($_GET['id'])) ) { // Accessed through parts_search.php
$id = $_GET['id'];
} elseif ( (isset($_POST['id'])) ) { // Form has been submitted.
$id = $_POST['id'];
} else { // No valid ID, kill the script.
echo '<div id="title">Page Error</div>
<p class="error">This page has been accessed in error.</p><p><br /><br /></p>'; 
exit();
}

require_once ('mysql_connect.php'); // Connect to the db.

// Check if the form has been submitted.
if (isset($_POST['submitted'])) {

$errors = array(); // Initialize error array.

// Trim Name
{
	$nm = trim($_POST['name']);
}

// Trim Email
{
	$em = trim($_POST['email']);
}

// Trim Contact
{
	$cn = trim($_POST['contact']);
}

// Trim Contact Email
{
	$ce = trim($_POST['c_email']);
}

// Trim Phone
{
	$ph = trim($_POST['phone']);
}

// Trim Ext
{
	$ex = trim($_POST['ext']);
}

// Trim Store ID
{
	$si = trim($_POST['store_id']);
}

// Trim Address
{
	$ad = trim($_POST['address']);
}

// Trim Status
{
	$st = trim($_POST['status']);
}

// Trim Priority
{
	$pi = trim($_POST['priority']);
}

// Trim Serial
{
	$se = trim($_POST['serial']);
}

// Trim Issue
{
	$is = trim($_POST['issue']);
}

// Trim Original Date
{
	$od = trim($_POST['o_date']);
}

// Trim Notes
{
	$nt = trim($_POST['notes']);
}

if (empty($errors)) { // If everything's OK.

	//  Test for unique cart serial.
	$query = "SELECT ticket_id FROM tickets WHERE ticket_id='$id'";
	$result = mysql_query($query);
	if (mysql_num_rows($result) == 0) {

		// Make the query.
		$query = "UPDATE tickets SET name='$nm', email='$em', contact='$cn', c_email='$ce', phone='$ph', ext='$ex', store_id='$si', address='$ad', status='$st', priority='$pi', serial='$se', issue='$is', o_date='$od', notes='$nt' WHERE ticket_id='$id'";
		$result = @mysql_query ($query); // Run the query.
		if (mysql_affected_rows() == 1) { // If it ran OK.

			// Print a message.
			echo '<div id="title">Success!</div>
			<p>The Ticket has been edited.</p><p><br /><br /></p>';	

		} else { // If it did not run OK.
			echo '<div id="title">System Error</div>
			<p class="error">The ticket could not be edited due to a system error. We apologize for any inconvenience.</p>'; // Public message.
			echo '<p>' . mysql_error() . '<br /><br />Query: ' . $query . '</p>'; // Debugging message. 
			exit();
		}

	} else { // Already registered.
		echo '<div id="title">Error!</div>
		<p class="error">The ticket has already been registered.</p>';
	}

} else { // Report the errors.

	echo '<div id="title">Error!</div>
	<p class="error">The following error(s) occurred:<br />';
	foreach ($errors as $msg) { // Print each error.
		echo " - $msg<br />\n";
	}
	echo '</p><p>Please try again.</p><p><br /></p>';

} // End of if (empty($errors)) IF.

} // End of submit conditional.

// Always show the form.

// Retrieve the user's information.
$query = "SELECT name, email, contact, c_email, phone, ext, address, store_id, status, priority, serial, issue, o_date, notes FROM tickets WHERE ticket_id='$id'";		
$result = @mysql_query ($query); // Run the query.

if (mysql_num_rows($result) == 1) { // Valid Ticket ID, show the form.

// Get the user's information.
$row = mysql_fetch_array ($result, MYSQL_NUM);

// Create the form.
echo '<div id="title">Edit Ticket: ' . $id . '</div>
<form action="ticket_edit.php" method="post">
<table width="100%" border="0" cellspacing="0" cellpadding="0">
<tr>
<td>Name:</td><td><input type="text" name="name" size="15" maxlength="30" value="' . $row[0] . '" /></td>
</tr>
<tr>
<td>Email:</td><td><input type="text" name="email" size="20" maxlength="100" value="' . $row[1] . '"  /> </td>
</tr>
<tr>
<td>Contact:</td><td><input type="text" name="contact" size="20" maxlength="40" value="' . $row[2] . '"  /> </td>
</tr>
<tr>
<td>Contact Email:</td><td><input type="text" name="c_email" size="20" maxlength="40" value="' . $row[3] . '"  /> </td>
</tr>
<tr>
<td>Phone:</td><td><input type="text" name="phone" size="20" maxlength="40" value="' . $row[4] . '"  /> </td>
</tr>
<tr>
<td>Ext:</td><td><input type="state_prov" name="ext" size="15" maxlength="15" value="' . $row[5] . '" /></td>
</tr>
<tr>
<td>Address:</td><td><input type="text" name="address" size="15" maxlength="15" value="' . $row[6] . '" /></td>
</tr>
<tr>
<td>Store ID:</td><td><input type="text" name="store_id" size="15" maxlength="15" value="' . $row[7] . '" /></td>
</tr>
<tr>
<td>Status:</td><td><label>
      <select name="status" id="status">
        <option selected="selected">Open</option>
        <option>Closed</option>
      </select>
    </label></td>
</tr>
<tr>
<td>Priority:</td><td><label>
      <select name="priority" id="priority">
        <option>1. Critical</option>
        <option>2. High</option>
        <option selected="selected">3. Normal</option>
        <option>4. Low</option>
      </select>
    </label></td>
</tr>
<tr>
<td>Issues:</td><td><label>
      <select name="issue" id="issue">
        <option selected="selected">General Support</option>
        <option>Spare Parts</option>
        <option>ABC Issues</option>
        <option>Charger Issues</option>
        <option>Inverter Issues</option>
        <option>Battery Issues</option>
        <option>Other</option>
      </select>
    </label></td>
</tr>
<tr>
<td>Serial:</td><td><input type="text" name="serial" size="15" maxlength="15" value="' . $row[10] . '" /></td>
</tr>
<tr>
<td>Original Date:</td><td><input type="text" name="o_date" size="15" maxlength="15" value="' . $row[12] . '" /></td>
</tr>
<tr>
<td>Notes:</td><td><label>
      <textarea name="notes" id="notes" cols="60" rows="5">' . $row[13] . '</textarea>
    </label></td>
</tr>
<tr>
<td><input type="submit" name="submit" value="Submit" /><input type="hidden" name="submitted" value="TRUE" /></td>
<td><input type="hidden" name="id" value="' . $id . '" /></td></tr>
</form>';

} else { // Not a valid Customer ID.
echo '<div id="title">Page Error</div>
<p class="error">This page has been accessed in error.</p><p><br /><br /></p>';
}

mysql_close(); // Close the database connection.

?>

[Maq]

      $query = "SELECT ticket_id FROM tickets WHERE ticket_id='$id'";
      $result = mysql_query($query);
      if (mysql_num_rows($result) == 0) {

 

This evaluates to false.  Meaning, it's finding a record with a matching id of $id.  Echo the query and see what it's looking for to make sure it's correct.

 

(Please don't post all of the code, only the relevant part.)

[radi8]

Also, in addition to what Mag said, if the select statement returns 0 rows, you cannot update a record that does not exist. I believe you need to do the insert rather than the update.

[Ken2k7]

It's Maq not Mag.

[radi8]

It's Maq not Mag.

 

Sorry, my bad, no offense meant.

[Maq]

It's Maq not Mag.

 

Sorry, my bad, no offense meant.

 

No biggie 

[cmaclennan]

Hi Guys,

 

Sorry for posting all of the code right off the bat wasnt to sure what part was relevant or may have been causing the issue, and the select statement is pulling directly from the ticket_id selected on the search page, essentially the search page gives a list of entries and next to each is an edit link which then comes to the page i posted all of the data populates as it should i am just unable to submit it as it says the id is already registered.

 

Thanks again.

[Maq]

So what did it output when you echoed the query statement?

[cmaclennan]

sorry having some difficulty getting it to show the query

[cmaclennan]

Ok so when I hit submit to update the record it seems to be running the SELECT query?

[radi8]

well, which one? you have 2.

 

Put a crap load of echo statements in your code to see where it is breaking, and report findings.

 

I am still unsure what you expect this to do:

if (mysql_num_rows($result) == 0) {

         // Make the query.
         $query = "UPDATE tickets SET name='$nm', email='$em', contact='$cn', c_email='$ce', phone='$ph', ext='$ex', store_id='$si', address='$ad', status='$st', priority='$pi', serial='$se', issue='$is', o_date='$od', notes='$nt' WHERE ticket_id='$id'";
         $result = @mysql_query ($query); // Run the query.
         if (mysql_affected_rows() == 1) { // If it ran OK.

[Maq]

Please re-read my very first post.