SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Enthusiast britton's Avatar
    Join Date
    Jul 2005
    Location
    Tennessee
    Posts
    83
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Query Producing Slightly Unwanted Results

    Hi guys,

    I have a query that pulls member photos. Default Y, not Default N. The problem is when the user has not set a default and they are all N... its pulling all the N photos for that user instead of just one. Somehow I've broken the query and I'm not sure how.

    Code:
    $result568=mysql_query("SELECT DISTINCT
    
         u.name, u.age, u.category, u.info1, u.info12, u.lastlogin, u.lastactivity
    
         ,COALESCE(u2i.url,u2i2.url) url
    
      FROM
    
         usertable u
    
      LEFT OUTER
    
      JOIN
    
         imagetable u2i
    
        ON
    
         u.name = u2i.name
    
       AND
    
         u2i.default = 'Y'
    
      LEFT OUTER
    
      JOIN
    
         imagetable u2i2
    
        ON
    
         u.name = u2i2.name
    
      WHERE
    
      	(u2i.priv != 1 && u2i.status = 'active' OR u2i2.priv !=1 && u2i2.status = 'active') && u.name IN (" . $picar . ") ORDER BY field(u.name, $picar) LIMIT 30;");

  2. #2
    SitePoint Enthusiast britton's Avatar
    Join Date
    Jul 2005
    Location
    Tennessee
    Posts
    83
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Anyone?

  3. #3
    SitePoint Wizard silver trophybronze trophy Cups's Avatar
    Join Date
    Oct 2006
    Location
    France, deep rural.
    Posts
    6,869
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    Try this:
    You currently have:
    PHP Code:
    $result568=mysql_query("SELECT DISTINCT

         u.name, u.age, u.category, u.info1, u.info12, u.lastlogin, u.lastactivity

         ,COALESCE(u2i.url,u2i2.url) url

      FROM
    ..... and so on 
    Change it slightly to:
    PHP Code:
    $sql "SELECT DISTINCT

         u.name, u.age, u.category, u.info1, u.info12, u.lastlogin, u.lastactivity

         ,COALESCE(u2i.url,u2i2.url) url

      FROM
    ..... and so on

    // and then do:
    echo 
    $sql ;
    $result568=mysql_query($sql); 
    Echoing out the sql statement may throw up a PHP error you have made elsewhere.

    IF the statement does look fine, the yours is ostensibly an SQL question, hit the little orange flag under your avatar on this question and ask a moderator to move it to the SQL forum, you'll likely get a better response than here.


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
  •