Bookmarks

Yahoo Gmail Google Facebook Delicious Twitter Reddit Stumpleupon Myspace Digg

Search queries

dbf2mysql parameter, WWWXXXAPC, wwwxxxAPC, How to unsubscrube from dategen spam, docmd.close 2585, WWWXXXDOCO, nu vot, dhcpd lease file "binding state", WWWXXXDOCO, how to setup procmail to process html2text

Links

XODOX
Impressum

#1: Strange error with multiple left joins in query

Posted on 2007-10-28 22:43:22 by pieter.thoma

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

Report this message

#2: Re: Strange error with multiple left joins in query

Posted on 2007-10-28 23:20:55 by Paul Lautman

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())?

Report this message

#3: Re: Strange error with multiple left joins in query

Posted on 2007-11-01 17:02:52 by pieter.thoma

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" <paul.laut...@btinternet.com>
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())?

Report this message