?
Solved

simple mysql insert with a subselect not behaving as expected

Posted on 2012-03-28
4
Medium Priority
?
370 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 2000 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

Enroll in August's Course of the Month

August's CompTIA IT Fundamentals course includes 19 hours of basic computer principle modules and prepares you for the certification exam. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

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

Originally, this post was published on Monitis Blog, you can check it here . In business circles, we sometimes hear that today is the “age of the customer.” And so it is. Thanks to the enormous advances over the past few years in consumer techno…
In this article, we’ll look at how to deploy ProxySQL.
The viewer will learn how to count occurrences of each item in an array.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses

800 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