Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2009
    Posts
    124

    Unanswered: Painted into a corner?

    I the following query, which works quite well. I either select everything, when colname='rand' or the results of filtering drop down boxes. You can see it in action at: https://www.artcardsforcauses.com/gallery.php?thm=rand . What I would like it to have this initial view, when no filtering is selected, to be a random selection of art work from the last 90 days.

    SELECT *
    , country_art
    FROM image_img
    LEFT JOIN label
    ON id_lbl=(theme_img
    OR style_img
    OR medium_img
    OR season_img)
    LEFT JOIN artist_art On artist_img=user_art
    WHERE approvedIMG=1
    AND private_img <> 1
    OR `private_img`IS NULL
    AND (colname LIKE 'rand')
    OR( artist_img = colname2
    AND theme_img LIKE %colname3%
    AND medium_img LIKE %colname4%
    AND style_img LIKE %colname5%
    AND season_img LIKE %colname6%
    AND country_art LIKE %colname7%)
    ORDER BY cdates_img DESC

    (I hope this formatting works for Rudy!)

    Nick

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by oldnickj View Post
    (I hope this formatting works for Rudy!)
    nope, it didn't

    before i look at a query, i have to understand where the columns are coming from

    could you please revise it, and supply the missing table qualifiers
    Code:
    SELECT _____.something
         , _____.anything
         , _____.just_not_the_dreaded_evil_select_star 
         , _____.country_art
      FROM image_img 
    LEFT OUTER
      JOIN label 
        ON _____.id_lbl = ( _____.theme_img 
                         OR _____.style_img 
                         OR _____.medium_img 
                         OR _____.season_img ) 
    LEFT OUTER
      JOIN artist_art 
        On _____.artist_img = _____.user_art
     WHERE _____.approvedIMG = 1 
       AND _____.private_img <> 1 
        OR _____.`private_img`IS NULL 
       AND ( _____.colname LIKE 'rand' ) 
        OR ( _____.artist_img = _____.colname2 
         AND _____.theme_img LIKE %colname3% 
         AND _____.medium_img LIKE %colname4% 
         AND _____.style_img LIKE %colname5% 
         AND _____.season_img LIKE %colname6% 
         AND _____.country_art LIKE %colname7% )
    ORDER 
        BY _____.cdates_img DESC
    also, just looking at your mix of ANDs and ORs in the WHERE clause, i'm pretty sure i know what you wanted it to mean, but i can assure you that it doesn't actually work the way you wanted

    also, looking at your first ON clause, it is for sure not executing the way you think

    i dispute your claim that this query "works quite well"

    no offence

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jan 2009
    Posts
    124

    right!

    The variables are record ids from a lookup table. (attached)

    SELECT image_img.id_img, artist_art.country
    FROM image_img
    LEFT OUTER
    JOIN label
    ON label.id_lbl = ( $theme_img
    OR $style_img
    OR $medium_img
    OR $season_img )
    LEFT OUTER
    JOIN artist_art
    On image_img.artist_img = artist_art.user_art
    WHERE image_img.approvedIMG = 1
    AND image_img.private_img <> 1
    OR image_img.`private_img`IS NULL
    AND ( $URLvar LIKE 'rand' )
    OR ( image_img.artist_img = $artist
    AND image_img.theme_img LIKE %$theme%
    AND image_img.medium_img LIKE %$medium%
    AND image_img.style_img LIKE %$style%
    AND image_img.season_img LIKE %$season%
    AND image_img.country_art LIKE %$country% )
    ORDER
    BY imageimg.cdates_img DESC
    Attached Files Attached Files

  4. #4
    Join Date
    Jan 2009
    Posts
    124
    Your right it is not working perfectly, the more testing I do the more problems I have. Time is short today but I will follow up on your pointers.

    Thank you

  5. #5
    Join Date
    Jan 2009
    Posts
    124
    Ultimately I convinced my wife that even when this works the result is not so desirable. Until she have images in every combination the viewer will see empty results most of the time.

    So back to square one

    Nick

Posting Permissions

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