[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 373
  • Last Modified:

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.
0
intellisource
Asked:
intellisource
  • 2
1 Solution
 
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
 
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

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now