Nested select with embedded quotes

[giants_fan]

MySQL client version: 4.1.7

 

Hi,

I cannot figure out how to set up this query. I've tried all of the variations I can think of, but no joy. I need the correct Country Id inserted based on the user input.

 

$shopName and $countryCode are being passed in from a form that the user fills out. In this test, the Country Code entered is 'UK' (without the quotes). The nested  select statement should return COUNTRY_ID=2, but it bombs with confused quotes and acts as if it is truncating the query. Here are two of the ways I have tried it...

 

$query = "insert into xxxxx.TD_SHOP ( SHOP_ID, SHOP_NAME, SHOP_COUNTRY_ID, CRT_DATE, UPD_DATE ) values ( LAST_INSERT_ID(), '$shopName', select COUNTRY_ID from xxxxx.TS_COUNTRY where COUNTRY_CODE = '$countryCode', NOW(), NULL )";

mysql_query( $query ) or die ( 'Could not create Shop. shopName = ' . $shopName . '. countryCode = ' . $countryCode . ' : ' . mysql_error() );

The above code results in this error message:

Could not create Shop. shopName = c. countryCode = UK : 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 COUNTRY_ID from xxxxx.TS_COUNTRY where COUNTRY_CODE = 'UK', NO' at line 1

 

$query = "insert into stevens_mcfrugal.TD_SHOP ( SHOP_ID, SHOP_NAME, SHOP_COUNTRY_ID, CRT_DATE, UPD_DATE ) values ( LAST_INSERT_ID(), '$shopName', select COUNTRY_ID from stevens_mcfrugal.TS_COUNTRY where COUNTRY_CODE = $countryCode, NOW(), NULL )";

mysql_query( $query ) or die ( 'Could not create Shop. shopName = ' . $shopName . '. countryCode = ' . $countryCode . ' : ' . mysql_error() );

The above code results in this error:

Could not create Shop. shopName = d. countryCode = UK : 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 COUNTRY_ID from stevens_mcfrugal.TS_COUNTRY where COUNTRY_CODE = UK, NOW(' at line 1

 

TD_SHOP

CREATE TABLE `TD_SHOP` (\n  `SHOP_ID` int(9) NOT NULL auto_increment,\n  `SHOP_NAME` varchar(30) NOT NULL default '',\n  `SHOP_COUNTRY_ID` char(3) default NULL,\n  `CRT_DATE` datetime NOT NULL default '0000-00-00 00:00:00',\n  `UPD_DATE` datetime default NULL,\n  PRIMARY KEY  (`SHOP_ID`),\n  UNIQUE KEY `SHOP_NAME` (`SHOP_NAME`),\n  FULLTEXT KEY `SHOP_NAME_2` (`SHOP_NAME`)\n) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=latin1

 

TS_COUNTRY

CREATE TABLE `TS_COUNTRY` (\n  `COUNTRY_ID` int(9) NOT NULL auto_increment,\n  `COUNTRY_NAME` varchar(25) NOT NULL default '',\n  `COUNTRY_CODE` char(3) NOT NULL default '',\n  `CRT_DATE` datetime NOT NULL default '0000-00-00 00:00:00',\n  `UPD_DATE` datetime default NULL,\n  PRIMARY KEY  (`COUNTRY_ID`),\n  KEY `COUNTRY_NAME` (`COUNTRY_NAME`),\n  KEY `COUNTRY_CODE` (`COUNTRY_CODE`)\n) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=latin1

 

COUNTRY_ID  COUNTRY_NAME  COUNTRY_CODE  CRT_DATE  UPD_DATE 

      1 Ireland IE 2010-01-24 20:34:16 NULL

      2 United Kingdom UK 2010-01-24 20:34:16 NULL

      3 Germany DE 2010-01-24 20:34:59 NULL

 

 

Any help will be appreciated.

[kickstart]

Hi

 

Think a subselect needs to surrounded with brackets.

 

All the best

 

Keith