A very complex query help pls

[MarioPro]

Hi,

My two questions, and thanks in advance for any help, are:

1. Is the follwoing query the best or correct way to do it?
2. Should I use the LEFT JOIN or search in each separate tables within the results found from the earlier query (not the case of thee query below)?

Here's my query comming from multiple checkboxes:


$query="SELECT DISTINCT(location.locationID), location.* FROM location ";
$query.="LEFT JOIN location_categories_rel ON location.locationID=location_categories_rel.location_ID ";
$query.="LEFT JOIN location_subcategories_rel ON location.locationID=location_subcategories_rel.location_ID ";
$query.="LEFT JOIN location_keywords_rel ON location.locationID=location_keywords_rel.location_ID ";
$query.="LEFT JOIN location_concepts_rel ON location.locationID=location_concepts_rel.location_ID ";
$query.="LEFT JOIN location_styles_rel ON location.locationID=location_styles_rel.location_ID ";
$query.="WHERE location.location_active='Y' ";
$query.="AND location_categories_rel.cat_ID='13' AND location_categories_rel.cat_ID='13' ";
$query.="AND location_subcategories_rel.sub_cat_ID='14' ";
$query.="AND location_keywords_rel.keyword_ID='29' ";
$query.="AND location_concepts_rel.concept_ID='145' ";
$query.="AND location_styles_rel.style_ID='14' ";
$query.="ORDER BY location.locationID DESC";
$result=mysql_query($query) or die("Can't Search: ".mysql_error());
while($fields=mysql_fetch_array($result)){
print "ID: ".$fields["locationID"]." NAME: ".$fields["location_title_en"]."<br>";
}


Well, hope it's understandable...

[GJay]

I'd abbreviate table-names myself, less chance of spelling a table name wrongly
i.e. ...FROM location l JOIN location_categories_rel lcr ON (l.id=lcd.llocationid)...

and the DISTINCT is probably uneccessary
SELECT DISTINCT(location.locationID), location.*
as a) the brackets are ignored, so it won't do what I'm assuming you think it will
and b) if location.locationID isn't unique anyway, you need a better table-structure :)

[MarioPro]

Hi, thanks for the reply and tips.

I'm not so concerned about the table names but more with the way the query should be made in order to get the results from selected checkboxes and get also a good query time. I'm talking of a locations table (where the locationID is in fact unique) wich handles about 800.000 records (id's).

Now you can imagine the categories, sub categories, keywords, concepts and styles tables which handle the location_ID and each of it's ID associated with the location itself.

I'm updating this post with the tables on which I want to perform the searches.

location (
locationID bigint(20) NOT NULL auto_increment,
location_title_en varchar(255) default NULL,
location_caption_en text,
location_country_ID int(11) default NULL,
PRIMARY KEY (locationID),
KEY location_title_en (location_title_en),
FULLTEXT KEY location_caption_en (location_caption_en),
KEY location_country_ID (location_country_ID),
)

categories (
catID int(11) NOT NULL auto_increment,
cat_name_en varchar(255) NOT NULL default '',
cat_searched int(11) NOT NULL default '0',
cat_active enum('Y','N') NOT NULL default 'Y',
PRIMARY KEY (catID),
UNIQUE KEY cat_name_en (cat_name_en),
KEY cat_searched (cat_searched)
)

categories_sub (
subcatID int(11) NOT NULL auto_increment,
subcat_name_en varchar(255) NOT NULL default '',
subcat_searched int(11) NOT NULL default '0',
subcat_active enum('Y','N') NOT NULL default 'Y',
PRIMARY KEY (subcatID),
KEY subcat_searched (subcat_searched),
KEY subcat_name_en (subcat_name_en)
)

concepts (
conceptID int(11) NOT NULL auto_increment,
concept_name_en varchar(255) NOT NULL default '',
concept_searched int(11) NOT NULL default '0',
concept_active enum('Y','N') NOT NULL default 'Y',
PRIMARY KEY (conceptID),
UNIQUE KEY concept_name_en (concept_name_en),
KEY concept_searched (concept_searched)
)

keywords (
keywordID int(11) NOT NULL auto_increment,
keyword_name_en varchar(255) NOT NULL default '',
keyword_searched int(11) NOT NULL default '0',
keyword_active enum('Y','N') NOT NULL default 'Y',
PRIMARY KEY (keywordID),
UNIQUE KEY keyword_name_en (keyword_name_en),
KEY keyword_searched (keyword_searched)
)

styles (
styleID int(11) NOT NULL auto_increment,
style_name_en varchar(255) NOT NULL default '',
style_searched int(11) NOT NULL default '0',
style_active enum('Y','N') NOT NULL default 'Y',
PRIMARY KEY (styleID),
UNIQUE KEY style_name_en (style_name_en),
KEY style_searched (style_searched)
)

location_categories_rel (
location_ID int(11) NOT NULL default '0',
cat_ID int(11) NOT NULL default '0',
KEY location_ID (location_ID),
KEY cat_ID (cat_ID)
)

location_concepts_rel (
location_ID int(11) NOT NULL default '0',
concept_ID int(11) NOT NULL default '0',
KEY location_ID (location_ID),
KEY concept_ID (concept_ID)
)

location_keywords_rel (
location_ID int(11) NOT NULL default '0',
keyword_ID int(11) NOT NULL default '0',
KEY location_ID (location_ID),
KEY keyword_ID (keyword_ID)
)

location_styles_rel (
location_ID int(11) NOT NULL default '0',
style_ID int(11) NOT NULL default '0',
KEY location_ID (location_ID),
KEY style_ID (style_ID)
)

location_subcategories_rel (
location_ID int(11) NOT NULL default '0',
sub_cat_ID int(11) NOT NULL default '0',
KEY location_ID (location_ID),
KEY sub_cat_ID (sub_cat_ID)
)


--> Now, what I emnt was that based on checkboxes selection on the page, the user may serach for o characteristic or many, from location style, concept, category, etc. and give the user the possibility to search for example a location that is in category 1 and 3 and also with style 5 or 14 and that the location may be associated with keywords 7, 15, 45, 185, 186 etc.

Well, I told this was a complex query! :)

If you have something to add, please do, like table structure critique, etc.

Thanks!

[MarioPro]

What I'm trying to do is a search based on user inputs like in the image below.

http://www.spsuicidologia.pt/search.gif

[MarioPro]

I think I'm really stucked now, but reaching the solution.

I found that I must search each relational table in sequence, this is, search first in table_categories then table_styles within the result from previous search and so on until I get the final filtered results. I just can't get this running :confused:



foreach($catID as $k=>$v){
$query="SELECT * FROM photo_categories_rel WHERE cat_ID='$v' GROUP BY photo_ID ";
$result=mysql_query($query) or die("Can't select cats: ".mysql_error());
while($fields=mysql_fetch_array($result)){
$found[]=$fields["photo_ID"];
}

}



Now I need to do the search for each of the other tables on the results from each preceding table. In this case for example from photo_ID's in $found[], then for photo_ID's in $found2[] etc.

Can anyone give me a little help here please. Thanks!