Radicore Forum
Fast Uncompromising Discussions. FUDforum will get your users talking.

Home » RADICORE » How To » CONCAT ((SELECT ...),...) Error
CONCAT ((SELECT ...),...) Error [message #1089] Sat, 08 September 2007 10:56 Go to next message
luca is currently offline  luca
Messages: 3
Registered: September 2007
Junior Member
Hi Tony,

I have 3 tables:

instruments:
- instrument_id -----------------> unsigned int(10) (PK)
- instrument_model_id --------> unsigned int(10) (FK)
.....

instrument_models:
- instrument_model_id ----------------> unsigned int(10) (PK)
- instrument_type_id ------------------> unsigned int(10) (FK)
- instrument_model_name -----------> varchar(32)
- instrument_model_manufacturer --> varchar(32)
.....

instrument_types:
- instrument_type_id --------------> unsigned int(10) (PK)
- instrument_type_name ---------> varchar(32)
.....

I use a LIST1 Transaction Pattern to show "instruments" data.
I'd like to show a new field "instrument_model" as the concatenation of:

"instrument_type_name - instrument_model_manufacturer instrument_model_name"

First of all, I write in instruments.list1.screen.inc:

$structure['main']['fields'][] = array('instrument_model' => 'Instrument Model');

Then, I create a relationship between instrument_models(snr) and instruments(jnr):

Relationship Type: CASCADE
Order By: instrument_model_id
Parent Field: CALCULATED
Calculated Field: CONCAT((SELECT instrument_type_name FROM instrument_types WHERE instrument_models.instrument_type_id = instrument_types.instrument_type_id),' - ',instrument_model_manufacturer,' ',instrument_model_name) AS instrument_model
Column Id(snr): instrument_model_id
Column Id(jnr): instrument_model_id


I "Export To PHP" the tables and this produces the lines in instruments.dict.inc:

// parent relationship details
$this->parent_relations[] = array('parent' => 'instrument_models',
'parent_field' => 'CONCAT((SELECT instrument_type_name FROM instrument_types WHERE instrument_models.instrument_type_id = instrument_types.instrument_type_id),\' - \',instrument_model_manufacturer,\' \',instrument_model_name) AS instrument_model',
'fields' => array('instrument_model_id' => 'instrument_model_id'));


BUT, when I go to enter instruments menu, I read the error windows:

Fatal Error: MySQL 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 '), AS instrument_model FROM instruments LEFT JOIN instrument_models ON (instrume' at line 1 (# 1064).

SQL query: SELECT SQL_CALC_FOUND_ROWS instruments.*, CONCAT((SELECT instrument_type_name FROM instrument_types WHERE instrument_models.instrument_type_id = instrument_types.instrument_type_id), ' - ', instrument_models.instrument_model_manufacturer, ' ', instrument_models.instrument_model_name, ), AS instrument_model FROM instruments LEFT JOIN instrument_models ON (instrument_models.instrument_model_id=instruments.instrumen t_model_id) LIMIT 10 OFFSET 0

Error in line 407 of file 'C:\Programmi\wamp\www\wis\includes\dml.mysqli.class.inc'.


What is wrong of my procedure?
Is there another way to do it?

Thanks for your support,
Luca
Re: CONCAT ((SELECT ...),...) Error [message #1090 is a reply to message #1089] Sat, 08 September 2007 13:34 Go to previous messageGo to next message
AJM is currently offline  AJM
Messages: 2350
Registered: April 2006
Location: Surrey, UK
Senior Member
Trying to use a SELECT statement inside a CONCAT(..) is causing the problem. The framework has routines which unpacks the SELECT statement to see if any of its component parts need to be qualified, then it repacks then back together again. Unfortunately it appears that your mixture of SELECT and CONCAT is causing the unpacking and repacking to become confused.

You should try using a JOIN to get the columns from the related tables, then use those column names inside the CONCAT(..).


Re: CONCAT ((SELECT ...),...) Error [message #1091 is a reply to message #1090] Sun, 09 September 2007 10:26 Go to previous messageGo to next message
luca is currently offline  luca
Messages: 3
Registered: September 2007
Junior Member
My framework input line is:

Calculated Field: CONCAT((SELECT instrument_type_name FROM instrument_types WHERE instrument_models.instrument_type_id = instrument_types.instrument_type_id),' - ',instrument_model_manufacturer,' ',instrument_model_name) AS instrument_model

in the relationship between instrument_models(snr) and instruments(jnr).

How should I have to modify this line to do what you suggest?

Thanks,
Luca
Re: CONCAT ((SELECT ...),...) Error [message #1092 is a reply to message #1091] Sun, 09 September 2007 11:18 Go to previous messageGo to next message
AJM is currently offline  AJM
Messages: 2350
Registered: April 2006
Location: Surrey, UK
Senior Member
The default SELECT statement created by Radicore should be as follows:
SELECT instruments.*
FROM instruments
LEFT JOIN instrument_models ON (instrument_models.instrument_model_id = instruments.instrument_model_id)

You need to extend this in your _cm_pre_getdata() method (as shown in http://www.tonymarston.net/php-mysql/infrastructure-faq.html #faq84) so that it becomes as follows:
SELECT instruments.*, CONCAT (instrument_type_name, ' - ',
                              instrument_model_manufacturer, ' ',
                              instrument_model_name) AS instrument_model
FROM instruments
LEFT JOIN instrument_models ON (instrument_models.instrument_model_id = instruments.instrument_model_id)
LEFT JOIN instrument_types ON (instrument_type.instrument_type_id = instrument_models.instrument_type_id)



Re: CONCAT ((SELECT ...),...) Error [message #1093 is a reply to message #1092] Tue, 11 September 2007 09:53 Go to previous message
luca is currently offline  luca
Messages: 3
Registered: September 2007
Junior Member
I moved everything to _cm_pre_getdata() method and it seems to work prouperly.

Thank you very much,
Luca
Previous Topic: passwords
Next Topic: Restricting access to user's own record
Goto Forum:
  


Current Time: Mon Jun 24 08:49:53 EDT 2024

Total time taken to generate the page: 0.01176 seconds