Member Avatar for MDGM

Hi all,

I need to do a query like this:

SELECT * FROM table1 WHERE ( SELECT COUNT(*) FROM table2 WHERE fbid = *current primaryKey*) > 150

How do I get the *current primaryKey* in order to do the second section of the query?

Max.

  • 3 Contributors
  • 10 Replies
  • 3K Views
  • 3 Days Discussion Span
  • Latest Post Latest Post by nav33n

Dani AI

Generated

You can push the whole check into SQL without looping in PHP. was on the right track with aggregation; the bit that tripped you earlier was the order of clauses (GROUP BY must precede HAVING). Also double-check your table name; your PHP uses ratings while one reply queried rating, which would 1064 if it does not exist.

A performant pattern is to pre-aggregate the ratings, then join back to the images that qualify:

SELECT i.*
FROM images AS i
JOIN (
  SELECT fbid
  FROM ratings
  GROUP BY fbid
  HAVING COUNT(*) > 150
) AS r ON r.fbid = i.fbid;

That returns each image whose fbid has more than 150 rows in ratings. It avoids scanning all photos in PHP and lets the optimizer use indexes. Make sure you have an index on ratings(fbid) (and images.fbid as the PK):

ALTER TABLE ratings ADD INDEX idx_ratings_fbid (fbid);

If your business rule is "150 distinct users rated this photo" (not just 150 rows), switch to a distinct count:

SELECT i.*
FROM images AS i
JOIN (
  SELECT fbid
  FROM ratings
  GROUP BY fbid
  HAVING COUNT(DISTINCT rater_id) > 150
) AS r ON r.fbid = i.fbid;

Notes:

  • Avoid SELECT * in production; project only the columns you need from images.
  • A correlated COUNT(*) filter also works (WHERE (SELECT COUNT(*) ...) > 150), but the derived-table join above is explicit about the aggregation and is easy to index-tune.
  • In PHP, use prepared statements (mysqli/PDO) rather than concatenating SQL in loops.

Recommended Answers

All 10 Replies

Member Avatar for darkagn

Assuming the primary key is stored in a PHP variable, $key, then you can build your SQL query using double quotes:

$sql = "SELECT * FROM table1 WHERE ( SELECT COUNT(*) FROM table2 WHERE fbid = $key) > 150";
// run the query by passing $sql to your query handler
Member Avatar for MDGM

I dont think I can explain it easily.

I basically want to encapsulate this:

$get_photos = mysql_query('SELECT * FROM images');//get all photos
while($photos = mysql_fetch_array($get_photos) )
{
     $get_ratings = mysql_query('SELECT COUNT(fbid) FROM ratings WHERE fbid = "'.$photos['fbid'].'" ');
     $ratings = mysql_fetch_array($get_ratings);
     if($ratings['COUNT(fbid)'] > 150){//if they have more than 150 ratings
          echo 'We can use this photo';//we want to use it
     }
     else{
          continue;//otherwise ignore it
     }
}

into a single mysql query...

Member Avatar for MDGM

or is it not possible?

Member Avatar for nav33n

What does your query do ? Query the table 'photos' and for every photo, You are again querying the table 'ratings' if that photo's fbid has a count > 150 ?
I didn't get the question or what you actually want. Do these 2 tables have any common 'linking' field ? What is fbid ?

Member Avatar for MDGM

each photo is saved in the table images with FBID as the primary key.

Then ratings are saved in the ratings table and FBID refers to the photo the rating is about.

I want to only use photos that have at least 150 ratings about them saved in the ratings table.

hope thats clearer

Member Avatar for nav33n 
select * from images where fbid IN (select fbid from ratings HAVING count(fbid) > 150 group by fbid)

The subquery returns fbid of all the records which has a count of 150+ . The main query returns all the records which matches these fbid.
Read more about having clause here.. http://www.techonthenet.com/sql/having.php

Not sure if this will work. If it doesn't, we ll try some other way :)
Cheers!

Member Avatar for MDGM

Heyy,

Thanks for the reply, your answer makes sense and seems very logical. I entered the following code in the phpmyadmin code tester:

SELECT * FROM images WHERE fbid IN (SELECT fbid FROM ratings HAVING count(fbid) > 150 GROUP BY fbid)

and got the following error:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'GROUP BY fbid) LIMIT 0, 30' at line 1

Thanks again

Member Avatar for nav33n

Hmm.. There was a little mistake in that query. Here, this works fine.

select * from images where fbid IN (
select fbid
from rating
group by fbid
HAVING count( fbid ) > 150
)

Cheers!

Member Avatar for MDGM

perfect, thanks a lot :D

Member Avatar for nav33n

:) You are welcome!

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.