Quick SQL help!

RevillWebDesign's picture

He has: 16 posts

Joined: Sep 2010

Hi All,

Need some quick SQL help. Details are as follows:

I have 4 tables:

businesses [Holds business info]
loc_link [links businesses to a location]
cat_link [links businesses to a category]
reviews [holds reviews for businesses]

I have a query:

SELECT * FROM businesses, loc_link, cat_link WHERE loc_link.loc_id = '$loc_id' AND loc_link.business_id = businesses.id AND cat_link.cat_id = '$cat_id' AND cat_link.business_id = businesses.id AND businesses.active = 'true'

What I need to do on the fly is check how many reviews the businesses have and then ORDER BY amount of reviews.

Is it possible to do something like mysql_num_rows() within an SQL query?

Any help would be greatly appreciated.

Thanks.

They have: 1 posts

Joined: Jun 2011

did the table show the reviews of the business and order by amount? because, if the question want to check the reviews, it can be to see on the table..and the answer is on the table..

RevillWebDesign's picture

He has: 16 posts

Joined: Sep 2010

I don't really understand what you are asking? I need to check how many reviews each business has within that SQL query and then order them by the amount of reviews they have so,

if business 1 has 3 reviews and business 2 has 1, then business 1 should be displayed 1st.

I know this can be done easily with mysql_num_rows() but I need to do it on the fly within the SQL query if possible, unless someone can come up with another idea as to how it can be done.

Cheers,

Leon.

Greg K's picture

He has: 2,145 posts

Joined: Nov 2003

Just woke up so mind is still waking up, but give this a try:

SELECT *, (SELECT COUNT(review_id) FROM reviews AS r WHERE r.business_id = b.businesses.id) AS review_count
FROM businesses AS b
JOIN loc_link AS ll ON ll.business_id = b.businesses.id
JOIN cat_link AS cl ON cl.business_id = b.businesses.id
WHERE ll.loc_id = '$loc_id' AND cat_link.cat_id = '$cat_id' AND businesses.active = 'true'
ORDER BY review_count

RevillWebDesign's picture

He has: 16 posts

Joined: Sep 2010

Thanks for your help! It nearly works, it doesn't understand the first b.businesses column, this is a little out of my league so if you could help that would be highly appreciated!

Thank you!

lrevillhendley.

Want to join the discussion? Create an account or log in if you already have one. Joining is fast, free and painless! We’ll even whisk you back here when you’ve finished.