Barry wrote:
Can someone plaese help? I have three tables 1st is a collection of
propertys, 2nd is a list of facilities(98 in total) and the third is a
list of property id's corresponding to the facilities id's offered at
each property,
The query I am running:
'SELECT'
'`'.$type.'`.`id`,'
'`'.$type.'`.`name`,'
'`'.$type.'`.`addr_1`,'
'`'.$type.'`.`addr_2`,'
'`'.$type.'`.`addr_3`,'
'`'.$type.'`.`addr_4`,'
'`'.$type.'`.`post_code`,'
'`'.$type.'`.`short_desc`,'
'`'.$type.'`.`phone_1`,'
'`'.$type.'`.`email`,'
'`'.$type.'`.`website`,'
'`'.$type.'`.`cost_single`,'
'`'.$type.'`.`cost_double`,'
'`'.$link.'`.`f_id`, `facilities`.`path`'
'FROM `'.$type.'`'
'inner JOIN `'.$link.'` ON `'.$type.'`.`id` = `'.$link.'`.`id`'
'inner JOIN `facilities` ON `'.$link.'`.`f_id` = `facilities`.`f_id`'
'where `name` like '. $name.''
works after a fashion, except that for each facility I get a
duplicate property entry in my results row, (if there are ten
facilities I get ten listing for the same property).
What I am trying to achieve is one result row for each property along
with a list of facilites offered.
Can this be done or do I need to redisgn the DB.
Thanks
Barry
Your query has a ridiculous amount of quoting and variable use. Here it is,
reformatted so I can read it:
"SELECT t.id,
t.name,
t.addr_1,
t.addr_2,
t.addr_3,
t.addr_4,
t.post_code,
t.short_desc,
t.phone_1,
t.email,
t.website,
t.cost_single,
t.cost_double,
l.f_id, facilities.path
FROM $type t
JOIN $link l ON t.id = l.id
JOIN facilities ON l.f_id = facilities.f_id
WHERE name like '$name'"
As you say, for each property, this query returns one row for each facility.
That's the correct behavior, since you do want all the facilities. In other
words, your problem is with display format, not the results of your query.
Usually, display format is handled in your application code, not by the database
back end. It should be relatively easy to modify your display code to only
start a new output line and print the properties info when the property info
changes. Just compare the id of this row to the id of the previous row stored
in a $last_id variable. If they match, just print f_id and facilities.path,
else start a new line.
If you are determined to handle this in your query, and have at least mysql 4.1,
you may be able to get what you want using GROUP_CONCAT(). Then your query
would be something like
"SELECT t.id,
t.name,
t.addr_1,
t.addr_2,
t.addr_3,
t.addr_4,
t.post_code,
t.short_desc,
t.phone_1,
t.email,
t.website,
t.cost_single,
t.cost_double,
GROUP_CONCAT(l.f_id, '-', facilities.path)
FROM $type t
JOIN $link l ON t.id = l.id
JOIN facilities ON l.f_id = facilities.f_id
WHERE name like '$name'
GROUP BY t.id"
See the manual for more on GROUP_CONCAT()
<http://dev.mysql.com/doc/refman/4.1/en/group-by-functions.html>.
Michael
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
