simple mysql insert with a subselect not behaving as expected

below is the debug info for the related mysql insert query.
[94196] => INSERT INTO 
			dwtphovu_8347379386_test.19_consumers 
		(
			`text_ConsumerE-Mail`,
			text_ConsumerName,
			text_ConsumerPhone,
			text_ConsumerCity,
			bigint_ConsumerRegion,
			tinyint_ConsumerStatus,
			timestamp_Populated,
			text_ConsumerCounty,
			text_ConsumerState,
			text_ConsumerCountry,
			text_ConsumerIM
		) 
		VALUES (
			"mariaan.melkbos@gmail.com",
			"Mariaan Belinfante",
			"0845883741",
			"Melkbosstrand",
			25,
			0,
			"2011-10-26 21:42:37",
			(
				SELECT 
					R1.text_RegionDescription 
				FROM 
					dwtphovu_8347379386_test.1_regions R1 
				WHERE 
					R1.bigint_RegionID = 25
			), 
			(
				SELECT 
					R2.text_RegionDescription 
				FROM 
					dwtphovu_8347379386_test.1_regions R2 
				WHERE 
					R2.bigint_RegionID = 25
			), 
			(
				SELECT 
					R3.text_RegionDescription 
				FROM 
					dwtphovu_8347379386_test.1_regions R3 
				WHERE 
					R3.bigint_RegionID = (
										SELECT 
											R4.bigint_ParentRegionID 
										FROM 
											dwtphovu_8347379386_test.1_regions R4 
										WHERE 
											R4.bigint_RegionID = 25
									)
			),
			""
		)
	ON DUPLICATE KEY UPDATE 
		text_ConsumerName = "Mariaan Belinfante", 
		text_ConsumerPhone = "0845883741", 
		text_ConsumerCity = "Melkbosstrand", 
		bigint_ConsumerRegion = 25, 
		tinyint_ConsumerStatus = 0, 
		timestamp_Populated = "2011-10-26 21:42:37", 
		text_ConsumerCounty = (
		`						SELECT 
									R5.text_RegionDescription 
								FROM 
									dwtphovu_8347379386_test.1_regions R5 
								WHERE 
									R5.bigint_RegionID = 25
						 ), 
		text_ConsumerState = (
		`						SELECT 
									R6.text_RegionDescription 
								FROM 
									dwtphovu_8347379386_test.1_regions R6 
								WHERE 
									R6.bigint_RegionID = 25
						 ), 
		text_ConsumerCountry = (
								SELECT 
									R7.text_RegionDescription 
								FROM 
									dwtphovu_8347379386_test.1_regions R7 
								WHERE 
									R7.bigint_RegionID = (
														SELECT 
															R8.bigint_ParentRegionID 
														FROM 
															dwtphovu_8347379386_test.1_regions R8 
														WHERE 
															R8.bigint_RegionID = 25
													)
							 ), 
		text_ConsumerIM = "";

    [94197] => ERROR #1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT 
										R6.text_RegionDescription 
									FROM 
										d' at line 65 in /home/dwtphovu/public_html/intellisource.co.za/test_8347379386/db_synchronizer.php on Line 18

Open in new window

what is wrong with this insert query around the R6 subselect? i cannot seem to see it. -_-
this query debug info was generated by the following php:
// create single consumer record by email
$consumer = explode(";",$row34["text_Consumer"]);
$sql6 =  "INSERT INTO 
			dwtphovu_8347379386_test.19_consumers 
		(
			`text_ConsumerE-Mail`,
			text_ConsumerName,
			text_ConsumerPhone,
			text_ConsumerCity,
			bigint_ConsumerRegion,
			tinyint_ConsumerStatus,
			timestamp_Populated,
			text_ConsumerCounty,
			text_ConsumerState,
			text_ConsumerCountry,
			text_ConsumerIM
		) VALUES (
			\"".$consumer[1]."\",
			\"".$consumer[0]."\",
			\"".$consumer[2]."\",
			\"".$consumer[3]."\",
			".intval($row34["bigint_RegionID"]).",
			0,
			\"".$row34["timestamp_LeadCreated"]."\",
		(
			SELECT 
				R1.text_RegionDescription 
			FROM 
				dwtphovu_8347379386_test.1_regions R1 
			WHERE 
				R1.bigint_RegionID = ".intval($row34["bigint_RegionID"])."
		), 
		(
			SELECT 
				R2.text_RegionDescription 
			FROM 
				dwtphovu_8347379386_test.1_regions R2 
			WHERE 
				R2.bigint_RegionID = ".intval($row34["bigint_RegionID"])."
		), 
		(
			SELECT 
				R3.text_RegionDescription 
			FROM 
				dwtphovu_8347379386_test.1_regions R3 
			WHERE 
				R3.bigint_RegionID = (
									SELECT 
										R4.bigint_ParentRegionID 
									FROM 
										dwtphovu_8347379386_test.1_regions R4 														WHERE 
															R4.bigint_RegionID = ".intval($row34["bigint_RegionID"])."
								)
		),
		\"\"
	)
	ON DUPLICATE KEY UPDATE 
		text_ConsumerName = \"".$consumer[0]."\", 
		text_ConsumerPhone = \"".$consumer[2]."\", 
		text_ConsumerCity = \"".$consumer[3]."\", 
		bigint_ConsumerRegion = ".intval($row34["bigint_RegionID"]).", 
		tinyint_ConsumerStatus = 0, 
		timestamp_Populated = \"".$row34["timestamp_LeadCreated"]."\", 
		text_ConsumerCounty = (
		`						SELECT 
									R5.text_RegionDescription 
								FROM 
									dwtphovu_8347379386_test.1_regions R5 
								WHERE 
									R5.bigint_RegionID = ".intval($row34["bigint_RegionID"])."
							), 
		text_ConsumerState = (
								SELECT 
									R6.text_RegionDescription 
								FROM 
									dwtphovu_8347379386_test.1_regions R6 
								WHERE 
									R6.bigint_RegionID = ".intval($row34["bigint_RegionID"])."
							), 
		text_ConsumerCountry = (
								SELECT 
									R7.text_RegionDescription 
								FROM 
									dwtphovu_8347379386_test.1_regions R7 
								WHERE 
									R7.bigint_RegionID = (
														SELECT 
															R8.bigint_ParentRegionID 
														FROM 
															dwtphovu_8347379386_test.1_regions R8 
														WHERE 
															R8.bigint_RegionID = ".intval($row34["bigint_RegionID"])."
													)
							), 
		text_ConsumerIM = \"\";";
$result6 = mysql_query_errors($sql6, $conn , __FILE__ , __LINE__ , true );

Open in new window

which is within the row loop for $sql34 - which selects leads from another database.
intellisourceAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Bhavesh ShahLead AnalysistCommented:
Hi,

did you check this.

text_ConsumerState = (
            "`"                                    SELECT
                                                      R6.text_RegionDescription
                                                FROM
                                                      dwtphovu_8347379386_test.1_regions R6
                                                WHERE
                                                      R6.bigint_RegionID = 25
                                     ),
0
johanntagleCommented:
There are floating backticks (`) at lines 65 and 73 of the first code you pasted.  When I removed them I no longer got a syntax error.  Of course I got a table not found error, which is expected since I don't have your schema.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
intellisourceAuthor Commented:
ahah! there is a ` floating around in front of that select subquery ;)
lol i mistook it for a dirty spot on my 24" led monitor! XD
0
johanntagleCommented:
Uhmm, thanks but shouldn't you have given half of the points to Brichsoft?  He actually posted first, while I was still typing my comment, though he noted only one backtick.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.