SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Zealot
    Join Date
    Nov 2004
    Location
    San Diego, CA (USA)
    Posts
    126
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    My (comprehensive) query is returning no rows. Please help!

    Hi. I am currently developing a design community site for a client to which anybody can submit t-shirt designs.

    As of now, I am developing the part where a user can view a submitted design and all of its details. This requires accessing many tables and sorting the data in one big query. Specifically, the query grabs all of the design's information, then goes to another table to gather and sort the design's rating (out of 5), then goes to another table to count how many views it has received, then goes to another table to get information about what theme/category the design is in, and finally goes to another table to get information about the user who submitted it.

    Here it is:

    Code:
    SELECT d.id AS id, d.title AS title, d.description AS description, d.userid AS creatorid, m.username AS creator, d.submitted AS submitted, COUNT(v.id) AS views, COUNT(r.id) AS ratingcount, SUM(r.rating) AS ratingsum, SUM(r.rating)/COUNT(r.id) AS rawrating, ROUND(SUM(r.rating)/COUNT(r.id), 1) AS rating, t.id AS themeid, t.title AS themename
    FROM designs AS d, design_views AS v, design_ratings AS r, members AS m, designthemes AS t 
    WHERE d.state = '1' AND d.id = '".$id."' AND v.designid = d.id AND r.designid = d.id AND d.userid = m.id AND t.id = d.themeid 
    GROUP BY v.designid, r.designid 
    LIMIT 1
    There aren't any syntax errors, but unfortunately, this query always fails to return a specific row of data, as indicated by the $id variable (this query is being run in a PHP 5 environment).

    Can any of you mysql gurus help me fix my query and possibly even clean it up a bit?

    Thanks in advance.

  2. #2
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    first off, if id and/or state are numeric, then you shouldn't be using quotes on them.

    are you saying it's not returning any rows, or it's returning the wrong rows? your query will only return rows if there are corresponding rows in ALL of the tables you have specified.

    here is your query rewritten in join syntax:
    Code:
    SELECT d.id AS id
         , d.title AS title
         , d.description AS description
         , d.userid AS creatorid
         , m.username AS creator
         , d.submitted AS submitted
         , COUNT(v.id) AS views
         , COUNT(r.id) AS ratingcount
         , SUM(r.rating) AS ratingsum
         , SUM(r.rating)/COUNT(r.id) AS rawrating
         , ROUND(SUM(r.rating)/COUNT(r.id), 1) AS rating
         , t.id AS themeid
         , t.title AS themename
      FROM designs AS d
      JOIN design_views AS v
        ON v.designid = d.id
      JOIN design_ratings AS r
        ON r.designid = d.id
      JOIN members AS m
        ON d.userid = m.id
      JOIN designthemes AS t
        ON t.id = d.themeid
     WHERE d.state = '1' AND d.id = $id
    GROUP
        BY v.designid, r.designid 
     LIMIT 1

  3. #3
    SitePoint Zealot
    Join Date
    Nov 2004
    Location
    San Diego, CA (USA)
    Posts
    126
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by longneck
    first off, if id and/or state are numeric, then you shouldn't be using quotes on them.

    are you saying it's not returning any rows, or it's returning the wrong rows? your query will only return rows if there are corresponding rows in ALL of the tables you have specified.

    here is your query rewritten in join syntax:
    Ah, thanks. It wasn't returning anything because some of the corresponding rows didn't exist. For example, there were no "view" or "rating" entries because the submission hadn't been viewed or rated yet.

    Is there any way to get around this?

    Thanks.

  4. #4
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,708
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yes change the joins for tables containing those columns, to LEFT JOIN instead of JOIN. Everything else stays the same.

  5. #5
    SitePoint Zealot
    Join Date
    Nov 2004
    Location
    San Diego, CA (USA)
    Posts
    126
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Works perfectly.

    Thanks!

  6. #6
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,708
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    just remember when you are trying to find unmatched data that you need an OUTER join. You can use LEFT or RIGHT but stick with one through all your joins, the tables just join in different orders is all. Good advice is since we read left to right stick to left joins.

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    be very careful, if you use all LEFT OUTER JOINs, your GROUP BY is going to cause the query to go south
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •