Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 3 of 3
  1. #1
    New Coder
    Join Date
    Aug 2006
    Posts
    28
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Crazy Query. Need Some Expert Advice!

    I have a master search box on my site that I want to do a bunch of things when submitted. Basically, I want customers to be able to enter anything in there..like keywords and colors and have the query look in a bunch of different places for matches. I already have these 2 queries running, that search for keywords and colors, but I'd like them to both be run when a customer inputs the search box. I'd also like it to query the item name and the notes field. The third query is displayed below but I'm not sure of the syntax on it...

    At any rate... how can these three be combined to one varible that I can run in a while loop to display products on my page...I also only need one entry per item (shirt_id)...in case numerous fields match the search string...

    I think it may look something like this:

    mysql_query("SELECT * FROM shirts, shirtcolors, shirtkeywords WHERE color = '$query' OR keyword = '$query' OR keywords like '%$query%' or name like '%$query%' AND active='true'") or die(mysql_error());

    but it's not working...it's giving me this error..

    Unknown column 'color' in 'where clause'

    Anyway, heres the 3 queries I need to combine

    mysql_query("SELECT * FROM shirtcolors
    LEFT JOIN colors ON shirtcolors.color_id = colors.color_id
    LEFT JOIN shirts ON shirtcolors.shirt_id = shirts.shirt_id
    WHERE color = '$colorsearch' ORDER BY shirts.$field $direction") or die(mysql_error());

    mysql_query("SELECT * FROM shirtkeywords
    LEFT JOIN keywords ON shirtkeywords.keyword_id = keywords.keyword_id
    LEFT JOIN shirts ON shirtkeywords.shirt_id = shirts.shirt_id
    WHERE keyword = '$keywordsearch' ORDER BY $field $direction") or die(mysql_error());

    mysql_query("SELECT * FROM shirts WHERE notes like'%$search%' OR name like'%$search%'") or die(mysql_error());

  • #2
    Regular Coder
    Join Date
    Jan 2003
    Posts
    867
    Thanks
    4
    Thanked 8 Times in 8 Posts
    If they are returning the same amount of columns you can use a UNION. Presumably they would be returning the same number of columns so you can create a consistent display.

  • #3
    New Coder
    Join Date
    Aug 2006
    Posts
    28
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I thought of that, but I've never used one...

    I'm not sure if they are returning the same # of columns. The last query, the 3rd one, selects all the columns from the shirts table.. which has 10 columns.... but the Queries with the JOIN's in them probably have more columns because they return those 10 columns plus at least 1 more column from the joined table?? Right???


  •  

    Posting Permissions

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