www.webdeveloper.com
Results 1 to 6 of 6

Thread: Help w/ Adding Search Field in Complex MySQL Query

Hybrid View

  1. #1
    Join Date
    Jan 2003
    Posts
    226

    Question Help w/ Adding Search Field in Complex MySQL Query

    I've got a pretty complex MySQL query running on a live site. And I need to add an additional search field -- but I keep coming with an unkown column in field list error.

    Here's the existing query:
    Code:
    SELECT f.fieldID, f.fieldNew, f.fieldFeatured, f.fieldSale, f.fieldSaleAmount, f.fieldTitle, f.fieldDrillGrade, TRUNCATE(ROUND(AVG(i.itemGrade) * 2) / 2,1) AS fieldMusicGrade, g.genreName, (f.fieldSets1 + f.fieldSets2 + f.fieldSets3 + f.fieldSets4 + f.fieldSets5) AS fieldTotalSets, SUM(i.itemTime) AS fieldTotalTime, (IF(f.fieldMusicOnly != '', f.fieldMusicOnly, SUM(i.itemPrice)) + IF(f.fieldDrillOnly != '', f.fieldDrillOnly, o.optionPrice)) AS fieldTotalPrice
    FROM field AS f
    INNER JOIN items AS i ON i.itemID IN (f.fieldMusic1, f.fieldMusic2, f.fieldMusic3, f.fieldMusic4, f.fieldMusic5)
    LEFT OUTER JOIN options AS o ON o.optionPerDrillGrade = f.fieldDrillGrade
    LEFT OUTER JOIN genres AS g ON g.genreID = f.fieldGenre
    WHERE f.fieldEnabled = 'Yes' AND (o.optionID = 662 OR o.optionID = 663 OR o.optionID = 664 OR o.optionID = 665) AND (f.fieldTitle LIKE '%test%' OR f.fieldSynopsis LIKE '%test%' OR f.fieldNotes LIKE '%test%' OR f.fieldKeywords LIKE '%test%')
    AND NOT (COALESCE(fieldMusicTitle1,'') > '' OR COALESCE(fieldMusicTitle2,'') > '' OR COALESCE(fieldMusicTitle3,'') > '' OR COALESCE(fieldMusicTitle4,'') > '' OR COALESCE(fieldMusicTitle5,'') > '')
    GROUP BY f.fieldID, f.fieldNew, f.fieldFeatured, f.fieldSale, f.fieldTitle, f.fieldDrillGrade, g.genreName
    UNION ALL
    SELECT f.fieldID, f.fieldNew, f.fieldFeatured, f.fieldSale, f.fieldSaleAmount, f.fieldTitle, f.fieldDrillGrade, fieldMusicGrade AS fieldMusicGradeAvg, g.genreName, (f.fieldSets1 + f.fieldSets2 + f.fieldSets3 + f.fieldSets4 + f.fieldSets5) AS fieldTotalSets, (f.fieldTime1 + f.fieldTime2 + f.fieldTime3 + f.fieldTime4 + f.fieldTime5) AS fieldTotalTime, (f.fieldMusicOnly + IF(f.fieldDrillOnly != '', f.fieldDrillOnly, o.optionPrice)) AS fieldTotalPrice
    FROM field AS f
    LEFT OUTER JOIN options AS o ON o.optionPerDrillGrade = f.fieldDrillGrade
    LEFT OUTER JOIN genres AS g ON g.genreID = f.fieldGenre
    WHERE f.fieldEnabled = 'Yes' AND (o.optionID = 662 OR o.optionID = 663 OR o.optionID = 664 OR o.optionID = 665) AND (f.fieldTitle LIKE '%test%' OR f.fieldSynopsis LIKE '%test%' OR f.fieldNotes LIKE '%test%' OR f.fieldKeywords LIKE '%test%')
    AND (COALESCE(fieldMusicTitle1,'') > '' OR COALESCE(fieldMusicTitle2,'') > '' OR COALESCE(fieldMusicTitle3,'') > '' OR COALESCE(fieldMusicTitle4,'') > '' OR COALESCE(fieldMusicTitle5,'') > '')
    ORDER BY fieldTitle
    The new field I need to also search now is called i.itemComposer from the items table. When I add:
    Code:
     OR i.itemComposer LIKE '%$searchText%'
    To the WHERE clause -- I get an unknown column error.

    How do I need to go about adding this? Thank you for any help you can provide.

  2. #2
    Join Date
    Dec 2013
    Posts
    63
    There are two WHERE clauses, in which clause did you put your additional field?

  3. #3
    Join Date
    Jan 2003
    Posts
    226
    Okay. I just added the i.itemComposer search field to the first WHERE clause. And it now returns results. Here's the revised query:
    Code:
    SELECT f.fieldID, f.fieldNew, f.fieldFeatured, f.fieldSale, f.fieldSaleAmount, f.fieldTitle, f.fieldDrillGrade, TRUNCATE(ROUND(AVG(i.itemGrade) * 2) / 2,1) AS fieldMusicGrade, g.genreName, (f.fieldSets1 + f.fieldSets2 + f.fieldSets3 + f.fieldSets4 + f.fieldSets5) AS fieldTotalSets, SUM(i.itemTime) AS fieldTotalTime, (IF(f.fieldMusicOnly != '', f.fieldMusicOnly, SUM(i.itemPrice)) + IF(f.fieldDrillOnly != '', f.fieldDrillOnly, o.optionPrice)) AS fieldTotalPrice 
    FROM field AS f 
    INNER JOIN items AS i ON i.itemID IN (f.fieldMusic1, f.fieldMusic2, f.fieldMusic3, f.fieldMusic4, f.fieldMusic5) 
    LEFT OUTER JOIN options AS o ON o.optionPerDrillGrade = f.fieldDrillGrade 
    LEFT OUTER JOIN genres AS g ON g.genreID = f.fieldGenre 
    WHERE f.fieldEnabled = 'Yes' 
    AND (o.optionID = 662 OR o.optionID = 663 OR o.optionID = 664 OR o.optionID = 665) 
    AND (f.fieldTitle LIKE '%$searchText%' OR f.fieldSynopsis LIKE '%$searchText%' OR f.fieldNotes LIKE '%$searchText%' OR f.fieldKeywords LIKE '%$searchText%' OR i.itemComposer LIKE '%$searchText%') 
    AND NOT (COALESCE(fieldMusicTitle1,'') > '' OR COALESCE(fieldMusicTitle2,'') > '' OR COALESCE(fieldMusicTitle3,'') > '' OR COALESCE(fieldMusicTitle4,'') > '' OR COALESCE(fieldMusicTitle5,'') > '') 
    GROUP BY f.fieldID, f.fieldNew, f.fieldFeatured, f.fieldSale, f.fieldTitle, f.fieldDrillGrade, g.genreName 
    UNION ALL 
    SELECT f.fieldID, f.fieldNew, f.fieldFeatured, f.fieldSale, f.fieldSaleAmount, f.fieldTitle, f.fieldDrillGrade, fieldMusicGrade AS fieldMusicGradeAvg, g.genreName, (f.fieldSets1 + f.fieldSets2 + f.fieldSets3 + f.fieldSets4 + f.fieldSets5) AS fieldTotalSets, (f.fieldTime1 + f.fieldTime2 + f.fieldTime3 + f.fieldTime4 + f.fieldTime5) AS fieldTotalTime, (f.fieldMusicOnly + IF(f.fieldDrillOnly != '', f.fieldDrillOnly, o.optionPrice)) AS fieldTotalPrice 
    FROM field AS f 
    LEFT OUTER JOIN options AS o ON o.optionPerDrillGrade = f.fieldDrillGrade 
    LEFT OUTER JOIN genres AS g ON g.genreID = f.fieldGenre 
    WHERE f.fieldEnabled = 'Yes' 
    AND (o.optionID = 662 OR o.optionID = 663 OR o.optionID = 664 OR o.optionID = 665) 
    AND (f.fieldTitle LIKE '%$searchText%' OR f.fieldSynopsis LIKE '%$searchText%' OR f.fieldNotes LIKE '%$searchText%' OR f.fieldKeywords LIKE '%$searchText%') 
    AND (COALESCE(fieldMusicTitle1,'') > '' OR COALESCE(fieldMusicTitle2,'') > '' OR COALESCE(fieldMusicTitle3,'') > '' OR COALESCE(fieldMusicTitle4,'') > '' OR COALESCE(fieldMusicTitle5,'') > '') 
    ORDER BY fieldTitle
    The new issue is that the fieldTotalPrice is wrong when you find matches with the i.itemComposer field. If you find a match with any of the other search fields -- it's fine. I'm assuming it has to do with the group/union and the fact that the items table isn't used in the second half of the query?

    Any suggestions on getting around this? Thank you again for all your help.
    Last edited by msmith29063; 01-02-2014 at 10:23 PM.

  4. #4
    Join Date
    Dec 2013
    Posts
    63
    I'm assuming it has to do with the group/union and the fact that the items table isn't used in the second half of the query?
    Not necessesarily. The problem may be with the data that you have in your tables. But it will be hard to resolve because we can't see these data My imagination and my english aren't enough good to recognize what are these tables (and their structure) used for, and what type of application are you creating.

  5. #5
    Join Date
    Jan 2003
    Posts
    226
    The price (fieldTotalPrice) is generated by adding the prices of the entries from the items table (linked via f.fieldMusic1, etc.) as long as there is no "override" price (f.fieldMusicOnly). If there is an "override" -- the price is calculated differently. That's why there is a group/union. The price for each record to be displayed is calculated one of these two ways.

    * When I run the query with no search text ($searchText) -- all the records display with the correct price.

    * If I use search text that is found in one of the i.itemComposer fields (items table) -- and there is a price "override" (f.fieldMusicOnly) -- the price displays properly.

    * If I use search text that is found in one of the i.itemComposer fields (items table) -- and there is NO price "override" (f.fieldMusicOnly) -- the price displays wrong. It appears to only be using the price (i.itemPrice) of the items record that has the i.itemComposer match -- and not the other linked items. It should still add all the prices of the linked items.

    So it appears that the i.itemComposer LIKE '%$searchText%' is incorrect or in the wrong place. Anyone know how to address this?

    Sorry -- I've had help with the query up to this point and it's a little over my comfortability level with SQL queries.

    Thank you for your help.

  6. #6
    Join Date
    Jan 2014
    Posts
    4
    Any suggestions on getting around this? Thank you again for all your help.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

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



Recent Articles