Re: [PHP] Help wtih a query?

On Jan 30, 2007, at 4:33 PM, Skip Evans wrote:

Hey all,

I have the following query:

$sql="SELECT count(*) AS count,votes.storyID,stories.title,stories.storyID as sID,stories.approved, stories.story,stories.userID, fname, lname
FROM `bsp_story_votes` as votes, bsp_story_stories AS stories, users AS usr
WHERE votes.storyID=stories.storyID AND stories.userID=usr.id AND stories.contestID=$contestID
GROUP BY votes.storyID
ORDER BY stories.approved,count DESC, sID ASC LIMIT $b_recno,$recs";

How would this need to be changed so that it would return rows for the members of the bsp_story_stories table that do not have records in the bsp_story_votes table?

Is that what the left/right joins do???

Thanks!
Skip


This may be closer to what you're wanting...

<query>
SELECT count(stories.*) AS count, votes.storyID, stories.title, stories.storyID AS sID, stories.approved, stories.story, stories.userID, fname, lname

FROM bsp_story_votes AS votes LEFT JOIN (bsp_story_stories AS stories ON votes.storyID = stories.storyID) INNER JOIN users AS usr ON stories.userID = usr.id

WHERE stories.contestID=$contestID

GROUP BY votes.storyID

ORDER BY stories.approved, count DESC, sID ASC LIMIT $b_recno, $recs
</query>

If that doesn't work, you may try changing the LEFT to RIGHT. I probably screwed it up, but that's closer to how JOINs work. I'm not sure if the GROUP BY will work with this query either??? I thought you had to use HAVING with a GROUP BY? Refer to the M*SQL manual.

Now that think about it... I don't know if you can have a 'count()' in with that query. If it doesn't work, you may try pulling count() out and just using m*sql_num_rows() afterwards to get the count. Hope that helps!

~Philip
.

Relevant Pages

  • Re: Modified Access query does not respect ORDER!
    ... Close the query. ... But the SQL says it uses fname. ... Creating a report with the wizard uses the "invisible" order by lname. ...
    (microsoft.public.access.queries)
  • Re: Query in AS 400
    ... I am trying to accomplish something in a query on the AS400. ... is LNAME is SMITH and FNAME is BOB ... I would like to create it as SMITH, BOB ...
    (comp.sys.ibm.as400.misc)
  • Re: Query in AS 400
    ... I am trying to accomplish something in a query on the AS400. ... is LNAME is SMITH and FNAME is BOB ... I would like to create it as SMITH, BOB ...
    (comp.sys.ibm.as400.misc)
  • strtolower() for query results
    ... I need to take the output from a query and convert it to lower case. ... Here is the relevant code: ... I am just looking for a way to access the FNAME and the ... LNAME in the $result variable. ...
    (comp.lang.php)
  • Re: Help w/ writing a If Statement
    ... On your form add a combobox with the rowsource the above query. ... You can now select the SSN ... Since the social and Fname and> last name are always connected. ...
    (microsoft.public.access.formscoding)