Solved

simple mysql insert with a subselect not behaving as expected

Posted on 2012-03-28
4
368 Views
Last Modified: 2012-03-28
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
Comment
Question by:intellisource
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 19

Expert Comment

by:Bhavesh Shah
ID: 37776469
Hi,

did you check this.

text_ConsumerState = (
            "`"                                    SELECT
                                                      R6.text_RegionDescription
                                                FROM
                                                      dwtphovu_8347379386_test.1_regions R6
                                                WHERE
                                                      R6.bigint_RegionID = 25
                                     ),
0
 
LVL 24

Accepted Solution

by:
johanntagle earned 500 total points
ID: 37776542
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
 

Author Comment

by:intellisource
ID: 37776594
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
 
LVL 24

Expert Comment

by:johanntagle
ID: 37776632
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

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
PHP processing webform 25 44
Mysql query one to many 11 38
Microsoft SQL Query sum values over time periods 3 21
PHP, Curl and XML 14 11
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.‚Äč
3 proven steps to speed up Magento powered sites. The article focus is on optimizing time to first byte (TTFB), full page caching and configuring server for optimal performance.
The viewer will learn how to dynamically set the form action using jQuery.
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question