|
|
Posted by pieter.thoma on 11/01/07 16:02
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_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
>
> > 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_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
>
> 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())?
Navigation:
[Reply to this message]
|