Solved

simple mysql insert with a subselect not behaving as expected

Posted on 2012-03-28
4
363 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
  • 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
Since pre-biblical times, humans have sought ways to keep secrets, and share the secrets selectively.  This article explores the ways PHP can be used to hide and encrypt information.
The viewer will learn how to count occurrences of each item in an array.
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

708 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now