Strange error with multiple left joins in query

Hi,

I'm having a query doing some left joining on some tables, and for
some strange reason, when adding another join, the query is not
executed anymore by mysql_query(). If I logon to the mysql server, I
can still execute & see the query, so the query syntax is fine. Is
there a limitation on the maximum length of query in mysql_query()?

This works:

SELECT t1.client_id, t1.client_name, t2.project_id,
CONCAT(t3.definition_name,'_',t2.project_number) AS `project_name`,
t2.project_title, t4.exp_id, CONCAT('EXP_',t4.exp_number) AS
`experiment_name`, t4.exp_title, t5.separation_id,
CONCAT(t6.separation_technology_name,'_',t5.separation_diffe rential,t5.separation_number)
AS `separation_name` FROM `dir_clients` AS t1 LEFT JOIN `dir_projects`
AS t2 ON t1.client_id = t2.client_id LEFT JOIN
`dir_project_definition` AS t3 ON t2.project_definition_id =
t3.definition_id LEFT JOIN `dir_project_experiments` AS t4 ON
t2.project_id = t4.project_id LEFT JOIN `dir_separations` AS t5 ON
t4.exp_id = t5.experiment_id LEFT JOIN `dir_separation_technology` AS
t6 ON t5.separation_technology = t6.separation_technology_id GROUP BY
t1.client_id, t2.project_id, t4.exp_id, separation_name ORDER BY
t1.client_name, project_name, experiment_name, separation_name

This doesn't anymore:

SELECT t1.client_id, t1.client_name, t2.project_id,
CONCAT(t3.definition_name,'_',t2.project_number) AS `project_name`,
t2.project_title, t4.exp_id, CONCAT('EXP_',t4.exp_number) AS
`experiment_name`, t4.exp_title, t5.separation_id,
CONCAT(t6.separation_technology_name,'_',t7.differential_let ter,t5.separation_number)
AS `separation_name` FROM `dir_clients` AS t1 LEFT JOIN `dir_projects`
AS t2 ON t1.client_id = t2.client_id LEFT JOIN
`dir_project_definition` AS t3 ON t2.project_definition_id =
t3.definition_id LEFT JOIN `dir_project_experiments` AS t4 ON
t2.project_id = t4.project_id LEFT JOIN `dir_separations` AS t5 ON
t4.exp_id = t5.experiment_id LEFT JOIN `dir_separation_technology` AS
t6 ON t5.separation_technology = t6.separation_technology_id LEFT JOIN
`dir_separation_differential` AS t7 ON t5.separation_differential =
t7.differential_id GROUP BY t1.client_id, t2.project_id, t4.exp_id,
separation_name ORDER BY t1.client_name, project_name,
experiment_name, separation_name

Re: Strange error with multiple left joins in query

pieter.thoma@gmail.com wrote:
> Hi,
>
> I'm having a query doing some left joining on some tables, and for
> some strange reason, when adding another join, the query is not
> executed anymore by mysql_query(). If I logon to the mysql server, I
> can still execute & see the query, so the query syntax is fine. Is
> there a limitation on the maximum length of query in mysql_query()?
>
> This works:
>
> SELECT t1.client_id, t1.client_name, t2.project_id,
> CONCAT(t3.definition_name,'_',t2.project_number) AS `project_name`,
> t2.project_title, t4.exp_id, CONCAT('EXP_',t4.exp_number) AS
> `experiment_name`, t4.exp_title, t5.separation_id,
> CONCAT(t6.separation_technology_name,'_',t5.separation_diffe rential,t5.separation_number)
> AS `separation_name` FROM `dir_clients` AS t1 LEFT JOIN `dir_projects`
> AS t2 ON t1.client_id = t2.client_id LEFT JOIN
> `dir_project_definition` AS t3 ON t2.project_definition_id =
> t3.definition_id LEFT JOIN `dir_project_experiments` AS t4 ON
> t2.project_id = t4.project_id LEFT JOIN `dir_separations` AS t5 ON
> t4.exp_id = t5.experiment_id LEFT JOIN `dir_separation_technology` AS
> t6 ON t5.separation_technology = t6.separation_technology_id GROUP BY
> t1.client_id, t2.project_id, t4.exp_id, separation_name ORDER BY
> t1.client_name, project_name, experiment_name, separation_name
>
> This doesn't anymore:
>
> SELECT t1.client_id, t1.client_name, t2.project_id,
> CONCAT(t3.definition_name,'_',t2.project_number) AS `project_name`,
> t2.project_title, t4.exp_id, CONCAT('EXP_',t4.exp_number) AS
> `experiment_name`, t4.exp_title, t5.separation_id,
> CONCAT(t6.separation_technology_name,'_',t7.differential_let ter,t5.separation_number)
> AS `separation_name` FROM `dir_clients` AS t1 LEFT JOIN `dir_projects`
> AS t2 ON t1.client_id = t2.client_id LEFT JOIN
> `dir_project_definition` AS t3 ON t2.project_definition_id =
> t3.definition_id LEFT JOIN `dir_project_experiments` AS t4 ON
> t2.project_id = t4.project_id LEFT JOIN `dir_separations` AS t5 ON
> t4.exp_id = t5.experiment_id LEFT JOIN `dir_separation_technology` AS
> t6 ON t5.separation_technology = t6.separation_technology_id LEFT JOIN
> `dir_separation_differential` AS t7 ON t5.separation_differential =
> t7.differential_id GROUP BY t1.client_id, t2.project_id, t4.exp_id,
> separation_name ORDER BY t1.client_name, project_name,
> experiment_name, separation_name

How can you work with such a mess?
Please try laying the queries out tidily so that we can see what is joining
to what with what criteria.
Also, what do you get when you try the latter query? Is there anything
returned by mysql_errno() or mysql_errno())?

Re: Strange error with multiple left joins in query

Sorry for the mess. :)

This is what phpMyAdmin produces:

So this works (in my script & phpMyAdmin):

SELECT t1.client_id, t1.client_name, t2.project_id,
CONCAT( t3.definition_name, '_', t2.project_number ) AS
`project_name` , t2.project_title, t4.exp_id, CONCAT( 'EXP_',
t4.exp_number ) AS `experiment_name` , t4.exp_title, t5.separation_id,
CONCAT( t6.separation_technology_name, '_',
t5.separation_differential, t5.separation_number ) AS
`separation_name`
FROM `dir_clients` AS t1
LEFT JOIN `dir_projects` AS t2 ON t1.client_id = t2.client_id
LEFT JOIN `dir_project_definition` AS t3 ON t2.project_definition_id =
t3.definition_id
LEFT JOIN `dir_project_experiments` AS t4 ON t2.project_id =
t4.project_id
LEFT JOIN `dir_separations` AS t5 ON t4.exp_id = t5.experiment_id
LEFT JOIN `dir_separation_technology` AS t6 ON
t5.separation_technology = t6.separation_technology_id
GROUP BY t1.client_id, t2.project_id, t4.exp_id, separation_name
ORDER BY t1.client_name, project_name, experiment_name,
separation_name
LIMIT 0 , 30

This doesn't work anymore (in my php script), but it does work in
phpMyAdmin:

SELECT t1.client_id, t1.client_name, t2.project_id,
CONCAT( t3.definition_name, '_', t2.project_number ) AS
`project_name` , t2.project_title, t4.exp_id, CONCAT( 'EXP_',
t4.exp_number ) AS `experiment_name` , t4.exp_title, t5.separation_id,
CONCAT( t6.separation_technology_name, '_', t7.differential_letter,
t5.separation_number ) AS `separation_name`
FROM `dir_clients` AS t1
LEFT JOIN `dir_projects` AS t2 ON t1.client_id = t2.client_id
LEFT JOIN `dir_project_definition` AS t3 ON t2.project_definition_id =
t3.definition_id
LEFT JOIN `dir_project_experiments` AS t4 ON t2.project_id =
t4.project_id
LEFT JOIN `dir_separations` AS t5 ON t4.exp_id = t5.experiment_id
LEFT JOIN `dir_separation_technology` AS t6 ON
t5.separation_technology = t6.separation_technology_id
LEFT JOIN `dir_separation_differential` AS t7 ON
t5.separation_differential = t7.differential_id
GROUP BY t1.client_id, t2.project_id, t4.exp_id, separation_name
ORDER BY t1.client_name, project_name, experiment_name,
separation_name
LIMIT 0 , 30

phpMyAdmin is outputting the column separation_name as [BLOB - 8 B]

I'm fetching the results by mysql_fetch_array(). I've tried adding
options like force associate array etc, but none of them work.

The problem isn't MySQL or my query, apparently mysql_query() can not
handle this query for some reason.

Any suggestion?

On Oct 28, 11:20 pm, "Paul Lautman"
wrote:
> pieter.th...@gmail.com wrote:
> > Hi,
>
> > I'm having a query doing some left joining on some tables, and for
> > some strange reason, when adding another join, the query is not
> > executed anymore by mysql_query(). If I logon to the mysql server, I
> > can still execute & see the query, so the query syntax is fine. Is
> > there a limitation on the maximum length of query in mysql_query()?
>
> > This works:
>
> > SELECT t1.client_id, t1.client_name, t2.project_id,
> > CONCAT(t3.definition_name,'_',t2.project_number) AS `project_name`,
> > t2.project_title, t4.exp_id, CONCAT('EXP_',t4.exp_number) AS
> > `experiment_name`, t4.exp_title, t5.separation_id,
> > CONCAT(t6.separation_technology_name,'_',t5.separation_diffe rential,t5.separation_number)
> > AS `separation_name` FROM `dir_clients` AS t1 LEFT JOIN `dir_projects`
> > AS t2 ON t1.client_id = t2.client_id LEFT JOIN
> > `dir_project_definition` AS t3 ON t2.project_definition_id =
> > t3.definition_id LEFT JOIN `dir_project_experiments` AS t4 ON
> > t2.project_id = t4.project_id LEFT JOIN `dir_separations` AS t5 ON
> > t4.exp_id = t5.experiment_id LEFT JOIN `dir_separation_technology` AS
> > t6 ON t5.separation_technology = t6.separation_technology_id GROUP BY
> > t1.client_id, t2.project_id, t4.exp_id, separation_name ORDER BY
> > t1.client_name, project_name, experiment_name, separation_name
>
> > This doesn't anymore:
>
> > SELECT t1.client_id, t1.client_name, t2.project_id,
> > CONCAT(t3.definition_name,'_',t2.project_number) AS `project_name`,
> > t2.project_title, t4.exp_id, CONCAT('EXP_',t4.exp_number) AS
> > `experiment_name`, t4.exp_title, t5.separation_id,
> > CONCAT(t6.separation_technology_name,'_',t7.differential_let ter,t5.separation_number)
> > AS `separation_name` FROM `dir_clients` AS t1 LEFT JOIN `dir_projects`
> > AS t2 ON t1.client_id = t2.client_id LEFT JOIN
> > `dir_project_definition` AS t3 ON t2.project_definition_id =
> > t3.definition_id LEFT JOIN `dir_project_experiments` AS t4 ON
> > t2.project_id = t4.project_id LEFT JOIN `dir_separations` AS t5 ON
> > t4.exp_id = t5.experiment_id LEFT JOIN `dir_separation_technology` AS
> > t6 ON t5.separation_technology = t6.separation_technology_id LEFT JOIN
> > `dir_separation_differential` AS t7 ON t5.separation_differential =
> > t7.differential_id GROUP BY t1.client_id, t2.project_id, t4.exp_id,
> > separation_name ORDER BY t1.client_name, project_name,
> > experiment_name, separation_name
>
> How can you work with such a mess?
> Please try laying the queries out tidily so that we can see what is joining
> to what with what criteria.
> Also, what do you get when you try the latter query? Is there anything
> returned by mysql_errno() or mysql_errno())?