Hi everyone,
Sorry to ask this question, because this is going to get complicated...
Okay - what I need to do is be able to search for data across multiple
fields which are organized in multiple tables. Here is a basic
description of my database schema (not quite all of the fields...):
Table listings
- ListingID
- CatalogNumber*
- Title*
- ComposerID
- ArrangerID
- PublisherID
- Price
- CategoryID
Table arrangers
- ArrangerID
- ArrangerLname*
Table publishers
- PublisherID
- PublisherName*
Table composers
- ComposerID
- ComposerLname*
Table categories
- CategoryID
- Alias*
(* = field to search against)
My queries have been based on categories, so for a given category, I can
easily pull out all of the appropriate records:
<?php
$query = "select l.CatalogNumber, l.PDFLink, l.PDFName, l.Title,
p.PublisherName, c.ComposerLname,
a.ArrangerLname, l.Price, l.Description, l.DiscountID,
l.DiscountType, l.DiscountAmount, o.Alias, l.Description from
listings l, publishers p,
composers c, arrangers a, categories o
where l.CategoryID=o.CategoryID and o.Name='".$Category."'
and
l.PublisherID=p.PublisherID and
l.ComposerID=c.ComposerID and l.ArrangerID=a.ArrangerID
order by
".$OrderBy;
?>
But what I need to be able to do is search the CatalogNumber, Title,
Arranger, Composer, Publisher, and Description for a given search
string. I tried to put that into one big query and hung the database
pretty badly. So I resorted to doing five separate queries, and then
merging the result arrays into one array. This however, does not quite
do what I need it to do, because now I have to group all of the search
results according to their categories (all listings in category x
displayed together, all listings in category y displayed together, etc).
My problem is that I don't even know where to start. Can it be done
with MySQL, or does it need to be done on the PHP side?
-Erich-
PS: If you want to see all of this in action, you can go to
www.bvdpress.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
