Check for existing data

kconley

Hi,

I need some advice and maybe a little help with a project I'm working on. Here's the background:

I have a database that consists of data that's imported and over written about once an hour.

In this data is customer contact info, but the info is not always current. Because this data is coming from a third party, we don't have control over updating it. What I've done is create a new table to hold updated contact info. This table does not get over written.
I've got all the orignal data loading fine. All data that is to be edited goes into a form field. When the data is edited and submitted, what I want to have done when the page loads is:

check if there is contact data that has been manually entered.
if the manually entered contact data exists, compair it with the original data.
2a. if the original data does not match, use the manually entered data.
2b. if no manual data exists, display the original data in a form field to allow update.
2c. allow original data to be edited and inserted into the manual data table.
If the manual data does not exist, display origial data in a form to allow updating.

Here's part of what I have at the moment. I'm still working on it, trying to figure the best way to do this:
(This is after clicking the submit button (name='sub1') after editing some data)

 
if (isset($_POST['sub1'])) {  //This checks to see if submit button 1 has been clicked on.
		include('header.php'); //display's page header.
	$cname = $_POST["cname"]; //customer name
	$pnum = $_POST["pnum"]; //phone number
	$workstarted = $_POST["workstarted"]; //work started date
	$creditstat = $_POST["creditstat"]; //credit status
	$sono = $_POST["sono"]; //sales order #
	$custno = $_POST["custno"]; //customer number
	$manual_check = mysql_query("select * from manual where manual.sono = '$sono';", $db) or die(mysql_error()); //selects only manually entered data from database that matches the sales order #.
	$first_line = mysql_query("select * from jobs where srono in (select srono from sosro where sono in (select sono from salesorder where sono = '$sono'));",$db) or die(mysql_error());  //selects first line from jobs table that matches the sales order #

//gets customer info into array
	while ($first_line_row = mysql_fetch_array($first_line)) {  	
//display data in form
printf("<form action='$_SERVER[PHP_SELF]' method='post'>
		<div id='customer'>
		<fieldset>
			<legend>Customer Detail</legend>
			Customer Name: %s<br/>
		Customer Number: %s<br />
		<input type=hidden name='supporttype' />
		<input type='hidden' name='custno' value='%s' />
		<input type='hidden' name='sono' value='%s' />",
			$first_line_row["custdesc"], 
			$first_line_row["custno"],
			$first_line_row["custno"],
			$sono);
	}
	//gets manually entered data into array
	while ($manual_check_row = mysql_fetch_array($manual_check)) { 
		//checking to see if the manually entered data matches what has been posted
		if ($manual_check_row['cname'] = $_POST["cname"]) { 
			//if data matches, print it.
			printf("Contact Name:&nbsp;&nbsp;<input type='text' name='cname' value='%s' style='width: 165px;' /><input type='submit' name='sub1' class='submitLink' value='&rarr;'/><br />",
				$_POST["cname"]);
		} else {
			//if data does not match, do this:
			//check to see if there's a data entry in manual table.  If it's null, insert data into database
			if (is_null($manual_check_row['cname'])) {
				$insert_sql = "INSERT INTO manual values ('$cname','$pnum','$workstarted','$creditstat','$sono','$custno')";
				$insert_result = mysql_query($insert_sql, $db) or die(mysql_error());
				//print data to page, process should be transparent to end user
				printf("Contact Name:&nbsp;&nbsp;<input type='text' name='cname' value='%s' style='width: 165px;' /><input type='submit' name='sub1' class='submitLink' value='&rarr;'/><br />",
					$_POST["cname"]);
				} else { 
					//data was not null, update database with data from form
					$update_sql = "UPDATE manual SET cname='$cname' where custno = '$custno'";
					$update_result = mysql_query($update_sql, $db) or die(mysql_error());
					//display data to end user, process should be transparent to end user.
					printf("Contact Name:&nbsp;&nbsp;<input type='text' name='cname' value='%s' style='width: 165px;' /><input type='submit' name='sub1' class='submitLink' value='&rarr;'/><br />",
						$manual_check_row['cname']);
						}
				}
		}
	//adding the rest of the user info. 
	while ($first_line_row = mysql_fetch_array($first_line)) {
		printf("Phone Number: <input type='text' name='pnum' value='%s' style='width: 80px;' />
			<input type='submit' name='sub2' class='submitLink' value='&rarr;' /></fieldset></div>",
			$first_line_row['phone']);
	}

}

I'm sure there's probably a better way to do this... that's why I'm asking for advice.

Thanks in advance

dalecosp

Well, it doesn't look too bad; probably, there's about as many ways to go about this as there are personality types multiplied by the number of logical operations in the flow.

You might consider that using equalities inside the while loop could be a bit of a drag if the app has to scale up. Could you write the comparisons simply based on a boolean return from a db operation?

<?php

if ($duplicated=mysql_query("select $foo from mytable where $foo='".mysqlescapestring($_POST['foo'])."'";))

?>

Or something like that....

kconley

Thanks dalecosp, I'll give that a try.