SitePoint Sponsor

User Tag List

Results 1 to 10 of 10
  1. #1
    SitePoint Member sjfwhitby's Avatar
    Join Date
    Jan 2005
    Location
    austria
    Posts
    9
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Red face Help in creating a complex query (using left joins or multiple recordsets?)

    Hi All,

    I'm fairly new to developing a "dynamic" website, and I have hit what seems to be (to me!) a huge blockage!

    My plan is to have one "detailed" search form on my site, which users can use to search basic info like location, as well as to specify certain criteria that they want from their result-set - for example, I want to allow them to select multiple values from a list of facilities, and the same with categories etc.

    I believe that I have to use an array to pass the values, but I think I can figure that out. My problem is in the recordset in the results.php page - in order to receive all the values and filter the recordset accordingly, I need to create a query that is, I think, a bit too complex (see below) and it just comes back with no data.

    Is there a way of using multiple recordsets to generate the results, or is there a way in which I can alter the query in order to generate the results that I need?

    Any advice would be much appreciated!

    Kind regards,

    Suzanne

    [FONT=Arial]My Existing Query:[/FONT]

    Select BUSINESS.ID as 'businessid', BUSINESS.NAME,BUSINESS.FK_NACATEGORY, BUSINESS.FK_COUNTRY, PROVINCE.PROVINCE AS FK_PROVINCE, REGION.REGION AS FK_REGION, TOWN.TOWNORSUBURB AS FK_TOWN, BUSINESS.TEL, BUSINESS.NAWEBSITE,BUSINESS.LIVE, ESTABLISHMENT.FK_BUSINESS, ESTABLISHMENT.DESCRIPTION, ESTABLISHMENT.COST, ESTABLISHMENT_CATEGORY.CATEGORY_NAME, ESTABLISHMENT_CATEGORY_M2M.FK_ESTABLISHMENT_CATEGORY, ESTABLISHMENT_CATEGORY_M2M.FK_ESTABLISHMENT, LINKS.THUMB_IMAGE1, METHODOFCHARGING.METHODOFCHARGING, PRICE_HIGH.HIGHPRICE, PRICE_LOW.LOWPRICE, ESTABLISHMENT.COST

    FROM (((((BUSINESS LEFT JOIN ESTABLISHMENT ON BUSINESS.ID = ESTABLISHMENT.FK_BUSINESS) LEFT JOIN LINKS ON BUSINESS.ID = LINKS.FK_BUSINESS) LEFT JOIN PROVINCE ON BUSINESS.FK_PROVINCE = PROVINCE.ID) LEFT JOIN REGION ON BUSINESS.FK_REGION = REGION.ID) LEFT JOIN TOWN ON BUSINESS.FK_TOWN = TOWN.ID), ((ESTABLISHMENT_CATEGORY_M2M LEFT JOIN ESTABLISHMENT ON ESTABLISHMENT.ID = ESTABLISHMENT_CATEGORY_M2M.FK_ESTABLISHMENT) LEFT ESTABLISHMENT_CATEGORY ON ESTABLISHMENT_CATEGORY.ID = ESTABLISHMENT_CATEGORY_M2M.FK_ESTABLISHMENT_CATEGORY), ((ESTABLISHMENT_FACILITIES_M2M LEFT JOIN ESTABLISHMENT ON ESTABLISHMENT.ID = ESTABLISHMENT_FACILITIES_M2M.FK_ESTABLISHMENT) LEFT JOIN ESTABLISHMENT_FACILITIES ON ESTABLISHMENT_FACILITIES.ID = ESTABLISHMENT_FACILITIES_M2M.FK_ESTABLISHMENT), (((ESTABLISHMENT LEFT JOIN METHODOFCHARGING ON ESTABLISHMENT.STAY_FK_METHODOFCHARGING = METHODOFCHARGING.ID) LEFT JOIN PRICECATEGORYPERNIGHT ON ESTABLISHMENT.STAY_FK_HIGHESTINSEASONRATE = PRICE_HIGH.ID) LEFT JOIN PRICECATEGORYPERNIGHT as 'LOWPRICE' ON ESTABLISHMENT.STAY_FK_LOWESTOUTSEASONRATE = PRICE_LOW.ID)

    WHERE (BUSINESS.NAME LIKE '%%$name%%' OR ESTABLISHMENT.PLAY_DURATION LIKE '%%$duration%%' OR ESTABLISHMENT_FACILITIES.ID LIKE '%%$facilities_id%%' OR ESTABLISHMENT_CATEGORY.ID LIKE '%%$category2_id%%' OR BUSINESS.FK_PROVINCE LIKE '%%$province1_id%%' OR BUSINESS.FK_REGION LIKE '%%$region_id%% OR PRICE_LOW.ID LIKE '%%$lowprice_id%%' OR PRICE_HIGH.ID LIKE '%%$highprice_id%%') AND PRICE_HIGH.FK_NA_CATEGORY = BUSINESS.NAME,BUSINESS.FK_NACATEGORY AND PRICE_LOW.FK_NA_CATEGORY = BUSINESS.NAME,BUSINESS.FK_NACATEGORY
    ESTABLISHMENT.FK_BUSINESS = BUSINESS.ID

  2. #2
    dooby dooby doo silver trophybronze trophy
    spikeZ's Avatar
    Join Date
    Aug 2004
    Location
    Manchester UK
    Posts
    13,804
    Mentioned
    157 Post(s)
    Tagged
    3 Thread(s)
    HOLY POO!, now that's a query!
    Mike Swiffin - Community Team Advisor
    Only a woman can read between the lines of a one word answer.....

  3. #3
    SitePoint Wizard stereofrog's Avatar
    Join Date
    Apr 2004
    Location
    germany
    Posts
    4,324
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi Suzanne

    nice one indeed

    Can you tell us which database are you using? And what NAME LIKE '%%$name%%' etc is supposed to do?

  4. #4
    SitePoint Zealot saurab's Avatar
    Join Date
    Sep 2004
    Posts
    127
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by sjfwhitby
    Hi All,

    I'm fairly new to developing a "dynamic" website, and I have hit what seems to be (to me!) a huge blockage!

    My plan is to have one "detailed" search form on my site, which users can use to search basic info like location, as well as to specify certain criteria that they want from their result-set - for example, I want to allow them to select multiple values from a list of facilities, and the same with categories etc.

    I believe that I have to use an array to pass the values, but I think I can figure that out. My problem is in the recordset in the results.php page - in order to receive all the values and filter the recordset accordingly, I need to create a query that is, I think, a bit too complex (see below) and it just comes back with no data.

    Is there a way of using multiple recordsets to generate the results, or is there a way in which I can alter the query in order to generate the results that I need?

    Any advice would be much appreciated!

    Kind regards,

    Suzanne

    [FONT=Arial]My Existing Query:[/FONT]

    Select BUSINESS.ID as 'businessid', BUSINESS.NAME,BUSINESS.FK_NACATEGORY, BUSINESS.FK_COUNTRY, PROVINCE.PROVINCE AS FK_PROVINCE, REGION.REGION AS FK_REGION, TOWN.TOWNORSUBURB AS FK_TOWN, BUSINESS.TEL, BUSINESS.NAWEBSITE,BUSINESS.LIVE, ESTABLISHMENT.FK_BUSINESS, ESTABLISHMENT.DESCRIPTION, ESTABLISHMENT.COST, ESTABLISHMENT_CATEGORY.CATEGORY_NAME, ESTABLISHMENT_CATEGORY_M2M.FK_ESTABLISHMENT_CATEGORY, ESTABLISHMENT_CATEGORY_M2M.FK_ESTABLISHMENT, LINKS.THUMB_IMAGE1, METHODOFCHARGING.METHODOFCHARGING, PRICE_HIGH.HIGHPRICE, PRICE_LOW.LOWPRICE, ESTABLISHMENT.COST

    FROM (((((BUSINESS LEFT JOIN ESTABLISHMENT ON BUSINESS.ID = ESTABLISHMENT.FK_BUSINESS) LEFT JOIN LINKS ON BUSINESS.ID = LINKS.FK_BUSINESS) LEFT JOIN PROVINCE ON BUSINESS.FK_PROVINCE = PROVINCE.ID) LEFT JOIN REGION ON BUSINESS.FK_REGION = REGION.ID) LEFT JOIN TOWN ON BUSINESS.FK_TOWN = TOWN.ID), ((ESTABLISHMENT_CATEGORY_M2M LEFT JOIN ESTABLISHMENT ON ESTABLISHMENT.ID = ESTABLISHMENT_CATEGORY_M2M.FK_ESTABLISHMENT) LEFT ESTABLISHMENT_CATEGORY ON ESTABLISHMENT_CATEGORY.ID = ESTABLISHMENT_CATEGORY_M2M.FK_ESTABLISHMENT_CATEGORY), ((ESTABLISHMENT_FACILITIES_M2M LEFT JOIN ESTABLISHMENT ON ESTABLISHMENT.ID = ESTABLISHMENT_FACILITIES_M2M.FK_ESTABLISHMENT) LEFT JOIN ESTABLISHMENT_FACILITIES ON ESTABLISHMENT_FACILITIES.ID = ESTABLISHMENT_FACILITIES_M2M.FK_ESTABLISHMENT), (((ESTABLISHMENT LEFT JOIN METHODOFCHARGING ON ESTABLISHMENT.STAY_FK_METHODOFCHARGING = METHODOFCHARGING.ID) LEFT JOIN PRICECATEGORYPERNIGHT ON ESTABLISHMENT.STAY_FK_HIGHESTINSEASONRATE = PRICE_HIGH.ID) LEFT JOIN PRICECATEGORYPERNIGHT as 'LOWPRICE' ON ESTABLISHMENT.STAY_FK_LOWESTOUTSEASONRATE = PRICE_LOW.ID)

    WHERE (BUSINESS.NAME LIKE '%%$name%%' OR ESTABLISHMENT.PLAY_DURATION LIKE '%%$duration%%' OR ESTABLISHMENT_FACILITIES.ID LIKE '%%$facilities_id%%' OR ESTABLISHMENT_CATEGORY.ID LIKE '%%$category2_id%%' OR BUSINESS.FK_PROVINCE LIKE '%%$province1_id%%' OR BUSINESS.FK_REGION LIKE '%%$region_id%% OR PRICE_LOW.ID LIKE '%%$lowprice_id%%' OR PRICE_HIGH.ID LIKE '%%$highprice_id%%') AND PRICE_HIGH.FK_NA_CATEGORY = BUSINESS.NAME,BUSINESS.FK_NACATEGORY AND PRICE_LOW.FK_NA_CATEGORY = BUSINESS.NAME,BUSINESS.FK_NACATEGORY
    ESTABLISHMENT.FK_BUSINESS = BUSINESS.ID
    hmmm... nice query . Guess without seeing the db schema (and because of the enormous size of this query) , it's difficult to say much. However, when you talk about implementing a search functionality, I think it is a better (and a faster) approach to utilize the Fulltext search capabilities of the MySQL server itself (assuming that you use MySQL version 4.x) .. look into this ...

    on the other hand if you need to search by category or by region, then IMO dont have so many parameters in one query. I assume that you are trying to use an advanced search form where folks are going to select parameters like region, category etc .....
    The reason to avoid so many parameters is that the queries may get slowere and slower as the size of your DB increases. On the other hand, if you are able to somehow make use of MYSQL's fulltext feature, this will not be a problem.. with this you can still use where clauses and filter out stuff like region or category .. etc

    and ohh by the way, you are currently joining something like 7 or 8 tables which will consume the entire memory available to your hosting account as soon as the tables that you are joining start getting filled up with several rows ..... think about it ...
    Accepting Offers: NicheLabs.com - ExploringVOIP.com
    StableJob.com - AboutUniversities.com - Gemstone.co.in
    Assets.co.in - PropertyDealers.org - MortgageFirms.org
    eLearner.org - TravelResorts.info - TourstoAsia.com



  5. #5
    SitePoint Member
    Join Date
    Jan 2005
    Location
    Sausalito
    Posts
    1
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Suzanne,

    Your query is too complex to work well in practice (see previous poster's comments).

    Have you considered building your query based on only the fields the user chooses?
    I.e. if the user chooses only 1 field as their search parameter, then build the query for only that field instead of a massive query including every field on the form.
    Also, avoid "LIKE" when you don't need to use it. For a single choice list use "=" and for multiple choice lists use "IN".

  6. #6
    SitePoint Zealot saurab's Avatar
    Join Date
    Sep 2004
    Posts
    127
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by ccb700
    Suzanne,
    Have you considered building your query based on only the fields the user chooses?
    Yes thats a good point ..... as an example of how you "build queries" based on user input I'll copy and paste a piece of code that I wrote :

    PHP Code:
     
    $sql 
    "SELECT dq.sectionId, dq.documentId, dq.documentTitle, dq.publishDate, u1.userName, dq.numPages";
                     if(
    $reviewStatusFilter != 'UNREVIEWED') {
                       
    $flag true;
                       
    $sql .= ", u2.userName";
                     }
                     
    $sql .= " FROM document_queue AS dq ";
                     
    $sql .= " INNER JOIN users AS u1 ON dq.authorId = u1.userId ";
                     if(
    $flag) {
                       
    $sql .= " INNER JOIN users AS u2 ON dq.reviewerId = u2.userId ";
                     }
                     
    $sql .= " WHERE dq.documentType = '" $typeFilter "'";
                     if(
    $sectionFilter)) {
                 
    $sql .= " and dq.sectionId = '" $sectionFilter "'";
                     }
                     if(!empty(
    $authorFilter) && is_int($authorFilter)) {
                 
    $sql .= " and dq.authorId = '" $authorFilter "'";
                     }
                     
    $sql .= " and dq.submitStatus = '" $submitStatusFilter "'";
                     
    $sql .= " and dq.reviewStatus = '" $reviewStatusFilter "'";
                     
    $sql .= " and dq.flag = '" $flagFilter "'"
                     
    $sql .= " ORDER BY dq.submitDate DESC";
                     
    $sql .= " LIMIT " $offset ", " $limit;
                     
    $this->db->setQuery($sql);
                     
    $this->prs = & $this->db->pExecute($this->lim_per_page$page_num); 
    see how based on user input certain parts of the query statemnt is being constructed...... you could try something similar. Be aware that joining multiple tables (more than 3) is resource intensive and will slow down the search functionality so much that it would become unusable.

    Also based on user input you could control which tables to join . carefuly see the code above especially this part...

    PHP Code:
    if($flag) {
                       
    $sql .= " INNER JOIN users AS u2 ON dq.reviewerId = u2.userId ";
                } 
    based on the boolean value of $flag this inner join segment is appended to the query statement being constructed ..... you could build queries in this manner and join tables based on user input ....

    --saurab
    Accepting Offers: NicheLabs.com - ExploringVOIP.com
    StableJob.com - AboutUniversities.com - Gemstone.co.in
    Assets.co.in - PropertyDealers.org - MortgageFirms.org
    eLearner.org - TravelResorts.info - TourstoAsia.com



  7. #7
    SitePoint Member sjfwhitby's Avatar
    Join Date
    Jan 2005
    Location
    austria
    Posts
    9
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi all,

    Thanks so much for all the replies - I'm not sure whether to feel like a fool for having attempted such a query, or to laugh at the fact that I was such a fool to do so

    The database I am using is MySQL 4.0.1, and I am indeed trying to put together an advanced search for that will allow users to "combine" several smaller searches.

    saurab and ccb700, thank you for your ideas. I shall investigate the Fulltext search capabilities you recommended, and the idea of letting the user choose which query to run.

    Thanks so much again, and have a wonderful evening/ morning, whereever you are!

    S.

  8. #8
    SitePoint Zealot saurab's Avatar
    Join Date
    Sep 2004
    Posts
    127
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by sjfwhitby
    Hi all,

    Thanks so much for all the replies - I'm not sure whether to feel like a fool for having attempted such a query, or to laugh at the fact that I was such a fool to do so
    Oh it was not so trivial .. so good attempt !

    Quote Originally Posted by sjfwhitby
    I shall investigate the Fulltext search capabilities you recommended, and the idea of letting the user choose which query to run.
    just a small correction ... it shouldnt be which query to run... but which part of the query to run .. i mean a single query is being built according to user input ....
    Accepting Offers: NicheLabs.com - ExploringVOIP.com
    StableJob.com - AboutUniversities.com - Gemstone.co.in
    Assets.co.in - PropertyDealers.org - MortgageFirms.org
    eLearner.org - TravelResorts.info - TourstoAsia.com



  9. #9
    SitePoint Member sjfwhitby's Avatar
    Join Date
    Jan 2005
    Location
    austria
    Posts
    9
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Okay, got it. I have been playing with your code and trying to fit it to what I want to do, and think that I might delay the inclusion of my advanced form into my site.

    I really need all those tables, and I might need to go back to the drawing-board design-wise to see whether I can make them work together in a query, otherwise a user selecting an item from each option (i.e. town, region, country, category, facilties, prices etc) will have HUGE problems.

    Thanks again for the help!

    Very best,

    S.

  10. #10
    SitePoint Zealot saurab's Avatar
    Join Date
    Sep 2004
    Posts
    127
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by sjfwhitby
    Okay, got it. I have been playing with your code and trying to fit it to what I want to do, and think that I might delay the inclusion of my advanced form into my site.

    I really need all those tables, and I might need to go back to the drawing-board design-wise to see whether I can make them work together in a query, otherwise a user selecting an item from each option (i.e. town, region, country, category, facilties, prices etc) will have HUGE problems.

    Thanks again for the help!

    Very best,

    S.
    hmm... could you copy and paste your db schema and the search form fields .... that way i could suggest something ...
    Accepting Offers: NicheLabs.com - ExploringVOIP.com
    StableJob.com - AboutUniversities.com - Gemstone.co.in
    Assets.co.in - PropertyDealers.org - MortgageFirms.org
    eLearner.org - TravelResorts.info - TourstoAsia.com




Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •