Jump to content

populating new table from an existing table


Recommended Posts

hi there,the previous leads table contained all the consumer info. it is still working and populating, but i managed to get this page written, slightly modified now to get unique emails in this table - and updating the existing record when a duplicate key is encountered:

<?php// include database connectioninclude("includes/content/dwtphovu_f3rr37y.php");$sql = "SELECT 		6_serviceleads.bigint_LeadID, 		6_serviceleads.text_Consumer, 		6_serviceleads.bigint_RegionID, 		6_serviceleads.timestamp_LeadCreated, 		1_regions.text_RegionDescription 	FROM 6_serviceleads 	LEFT JOIN 1_regions ON (		6_serviceleads.bigint_RegionID = 		  1_regions.bigint_RegionID) 	GROUP BY 6_serviceleads.text_Consumer 	ORDER BY 6_serviceleads.timestamp_LeadCreated 		ASC;";echo $sql."<br />\n";$result = mysql_query_errors($sql, $conn , __FILE__ , __LINE__ , false);if ($result) {	while ($row = mysql_fetch_array($result)) {		// set_time_limit(25);		$id = $row["bigint_LeadID"];		$consumer = explode(";",$row["text_Consumer"]);		$consumeremail = $consumer[1];		$consumerfullname = $consumer[0];		$consumercell = $consumer[2];		$city_town = $consumer[3];		$region = $row["bigint_RegionID"];		$regionname = $row["text_RegionDescription"];		$ts = $row["timestamp_LeadCreated"];		$status = 0;		if ($name != "" && !is_numeric($name)) {			$tsql = "INSERT INTO 					19_consumers (					  `text_ConsumerE-Mail`, 					  text_ConsumerName, 					  text_ConsumerPhone, 					  text_ConsumerCity, 					  bigint_ConsumerRegion, 					  tinyint_ConsumerStatus, 					  timestamp_Populated					) VALUES (					  \"".strtolower($consumeremail)."\", 					  \"".ucwords(strtolower($consumerfullname))."\", 					  \"".$consumercell."\", 					  \"".ucwords(strtolower($city_town))."\", 					  ".$region.", 					  0, 					  \"".$ts."\"				) ON DUPLICATE KEY UPDATE 					`text_ConsumerE-Mail` = 					  \"".strtolower($consumeremail)."\", 					text_ConsumerName = 					  \"".ucwords(strtolower($consumerfullname))."\", 					text_ConsumerPhone = 					  \"".$consumercell."\", 					text_ConsumerCity = 					  \"".ucwords(strtolower($city_town))."\", 					bigint_ConsumerRegion = 					  ".$region.", 					tinyint_ConsumerStatus = 					  0;";			echo $tsql."<br />\n";			$tresult = mysql_query_errors($tsql, $conn , __FILE__ , 				__LINE__ , false);			echo $id."): ".$ts."<br />\n".$consumerfullname." - ".				$consumeremail." (".$consumercell.")<br />\n".				$city_town." - ".$regionname." (".$region.				")<br /><br />\n\n";		}	}	mysql_free_result($result);} else {	echo "Parent query failed: #".mysql_errno($conn)."<br />\n".mysql_error($conn);	}?>

the table has the following coloumn specs:

Table 19_consumers created: bigint_ConsumerID - bigint(128), Not Null, Auto Increment, Primary Key text_ConsumerE-Mail - text, utf8_bin collation, Not Null, Unique Key (64) text_ConsumerName - text, utf8_bin collation, Not Null, FullText Key text_ConsumerPhone - text, utf8_bin collation, Not Null, FullText Key text_ConsumerCity - text, utf8_bin collation, Not Null, FullText Key tinyint_ConsumerStatus - smallint(1), Not Null, Default 0, No Key timestamp_Populated - timestamp, Null, Default 0000-00-00 00:00:00, No Key
it used to populate previously, but doesn't anymore - since i tried adding uniqueness by email address... perhaps i should have posted this on the sql forum, but as most of the code was php - it seemed more relevant here.why when i load the page, does it not update this table anymore? :) i've tried commenting the set_time_limit(25); line incase the isp doesnt allow it anymore. i've also added the error trapping for the outer select, but to no avail. no errors display, despite all errors being marked to display on apache. the inner queries don't even echo Edited by Pierre 'Greywacke' du Toit
Link to comment
Share on other sites

ahhhh... the issue was resolved. due to inserting into the handlers of the site, so that leads would create consumers when entering the database if they do not exist, otherwise update them - the $name variable was not used anymore. the if statement still did with that variable and not $consumerfullname. :)

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

  • Create New...