[RESOLVED] mysql_query with nested select & embedded quotes?

giants_fan

Hi,
I cannot figure out how to set up this query. I've tried all of the variations I can think of, but the closest I can get is an insert that creates a value of SHOP_COUNTRY_ID='sel'. I can then use phpMyAdmin database browser to click its hyperlink to select the actual Country Id, but 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 embedded select statement should return COUNTRY_ID=1, but it either bombs with confused quotes, or it inserts with the 'sel' value I mentioned above. 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 )";

$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 . '. ' . mysql_error() );

Any help will be appreciated.

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=InnoDB 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=InnoDB 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.

halojoy

I have never put one SELECT inside another query.
I do not like to complicate things.

I would first run a SELECT to get the COUNTRY_ID value
then put this value in INSERT.

$query = "select COUNTRY_ID from xxxxx.TS_COUNTRY where COUNTRY_CODE = '$countryCode'";
$res = mysql_query($query);
$row = mysql_fetch_array($res);
$countryID = $row['COUNTRY_ID'];

$query = "insert into xxxxx.TD_SHOP 
( SHOP_ID, SHOP_NAME, SHOP_COUNTRY_ID, CRT_DATE, UPD_DATE ) values 
( LAST_INSERT_ID(), '$shopName', $countryID, NOW(), NULL )";

johanafm

The syntax is

INSERT INTO tblname (columns) SELECT ...

So drop values and rearrange your query to select the values you want to insert, rather than select one value and dump it in the middle.

giants_fan

Thank you, both, for your suggestions.
Very much appreciated. 🙂