Ad:

PHP Discussion Thread
Marked Solved
Views: 16703

Page 1
2
Next

You are currently viewing page 1 of this multi-page discussion thread

Feb 28th, 2009

WHERE ( SELECT COUNT() FROM table2 WHERE fbid = current_row_primary_key*) > 150

Expand Post »

Hi all,

I need to do a query like this:

PHP Syntax (Toggle Plain Text)
SELECT * FROM table1 WHERE ( SELECT COUNT(*) FROM table2 WHERE fbid = *current primaryKey*) > 150
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.

MaxMumford

Reputation Points: 32

Solved Threads: 3

Posting Whiz in Training

Offline

228 posts
since Oct 2006

Permalink

Mar 1st, 2009

Re: WHERE ( SELECT COUNT() FROM table2 WHERE fbid = current_row_primary_key*) > 150

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

php Syntax (Toggle Plain Text)
$sql = "SELECT * FROM table1 WHERE ( SELECT COUNT(*) FROM table2 WHERE fbid = $key) > 150";
// run the query by passing $sql to your query handler
$sql = "SELECT * FROM table1 WHERE ( SELECT COUNT(*) FROM table2 WHERE fbid = $key) > 150";
// run the query by passing $sql to your query handler

darkagn

Reputation Points: 395

Solved Threads: 192

Veteran Poster

Offline

1,136 posts
since Aug 2007

Permalink

Mar 1st, 2009

Re: WHERE ( SELECT COUNT() FROM table2 WHERE fbid = current_row_primary_key*) > 150

I dont think I can explain it easily.

I basically want to encapsulate this:

PHP Syntax (Toggle Plain Text)
$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
     }
}
$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...

Last edited by MaxMumford; Mar 1st, 2009 at 7:59 am.

MaxMumford

Reputation Points: 32

Solved Threads: 3

Posting Whiz in Training

Offline

228 posts
since Oct 2006

Permalink

Mar 2nd, 2009

Re: WHERE ( SELECT COUNT() FROM table2 WHERE fbid = current_row_primary_key*) > 150

or is it not possible?

MaxMumford

Reputation Points: 32

Solved Threads: 3

Posting Whiz in Training

Offline

228 posts
since Oct 2006

Permalink

Mar 2nd, 2009

Re: WHERE ( SELECT COUNT() FROM table2 WHERE fbid = current_row_primary_key*) > 150

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 ?

nav33n

Moderator

Featured Poster

Reputation Points: 524

Solved Threads: 356

Purple hazed!

Offline

3,878 posts
since Nov 2007

Permalink

Mar 2nd, 2009

Re: WHERE ( SELECT COUNT() FROM table2 WHERE fbid = current_row_primary_key*) > 150

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

MaxMumford

Reputation Points: 32

Solved Threads: 3

Posting Whiz in Training

Offline

228 posts
since Oct 2006

Permalink

Mar 2nd, 2009

Re: WHERE ( SELECT COUNT() FROM table2 WHERE fbid = current_row_primary_key*) > 150

mysql Syntax (Toggle Plain Text)
SELECT * FROM images WHERE fbid IN (SELECT fbid FROM ratings HAVING count(fbid) > 150 GROUP BY fbid)
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!

nav33n

Moderator

Featured Poster

Reputation Points: 524

Solved Threads: 356

Purple hazed!

Offline

3,878 posts
since Nov 2007

Permalink

Mar 3rd, 2009

Re: WHERE ( SELECT COUNT() FROM table2 WHERE fbid = current_row_primary_key*) > 150

Heyy,

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

PHP Syntax (Toggle Plain Text)
SELECT * FROM images WHERE fbid IN (SELECT fbid FROM ratings HAVING count(fbid) > 150 GROUP BY fbid)
SELECT * FROM images WHERE fbid IN (SELECT fbid FROM ratings HAVING count(fbid) > 150 GROUP BY fbid)

and got the following error:

PHP Syntax (Toggle Plain Text)

#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

Last edited by MaxMumford; Mar 3rd, 2009 at 6:18 am.

MaxMumford

Reputation Points: 32

Solved Threads: 3

Posting Whiz in Training

Offline

228 posts
since Oct 2006

Permalink

Mar 3rd, 2009

Re: WHERE ( SELECT COUNT() FROM table2 WHERE fbid = current_row_primary_key*) > 150

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

mysql Syntax (Toggle Plain Text)
SELECT * FROM images WHERE fbid IN (
SELECT fbid
FROM rating
GROUP BY fbid
HAVING count( fbid ) > 150
)
select * from images where fbid IN (
select fbid
from rating
group by fbid
HAVING count( fbid ) > 150
)

Cheers!

nav33n

Moderator

Featured Poster

Reputation Points: 524

Solved Threads: 356

Purple hazed!

Offline

3,878 posts
since Nov 2007

Permalink

Mar 3rd, 2009

Re: WHERE ( SELECT COUNT() FROM table2 WHERE fbid = current_row_primary_key*) > 150

perfect, thanks a lot

MaxMumford

Reputation Points: 32

Solved Threads: 3

Posting Whiz in Training

Offline

228 posts
since Oct 2006

Page 1
2
Next

This thread is solved

Either the thread starter or a moderator has marked this thread as solved. You can most likely trust the responses and answers given. There is most likely no reason for any further responses to be posted here. If you have a related question, please start a new thread in this forum instead.

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.

Previous Thread in PHP Forum Timeline: Show and Hide form from user

Next Thread in PHP Forum Timeline: HEX colours from mySQL to PHP (in colour)

DaniWeb Message

Cancel Changes

User Name
Password

WHERE ( SELECT COUNT(*) FROM table2 WHERE fbid = *current_row_primary_key*) > 150

Re: WHERE ( SELECT COUNT(*) FROM table2 WHERE fbid = *current_row_primary_key*) > 150

Re: WHERE ( SELECT COUNT(*) FROM table2 WHERE fbid = *current_row_primary_key*) > 150

Re: WHERE ( SELECT COUNT(*) FROM table2 WHERE fbid = *current_row_primary_key*) > 150

Re: WHERE ( SELECT COUNT(*) FROM table2 WHERE fbid = *current_row_primary_key*) > 150

Re: WHERE ( SELECT COUNT(*) FROM table2 WHERE fbid = *current_row_primary_key*) > 150

Re: WHERE ( SELECT COUNT(*) FROM table2 WHERE fbid = *current_row_primary_key*) > 150

Re: WHERE ( SELECT COUNT(*) FROM table2 WHERE fbid = *current_row_primary_key*) > 150

Re: WHERE ( SELECT COUNT(*) FROM table2 WHERE fbid = *current_row_primary_key*) > 150

Re: WHERE ( SELECT COUNT(*) FROM table2 WHERE fbid = *current_row_primary_key*) > 150

This thread is solved

This thread is more than three months old

WHERE ( SELECT COUNT() FROM table2 WHERE fbid = current_row_primary_key*) > 150

Re: WHERE ( SELECT COUNT() FROM table2 WHERE fbid = current_row_primary_key*) > 150

Re: WHERE ( SELECT COUNT() FROM table2 WHERE fbid = current_row_primary_key*) > 150

Re: WHERE ( SELECT COUNT() FROM table2 WHERE fbid = current_row_primary_key*) > 150

Re: WHERE ( SELECT COUNT() FROM table2 WHERE fbid = current_row_primary_key*) > 150

Re: WHERE ( SELECT COUNT() FROM table2 WHERE fbid = current_row_primary_key*) > 150

Re: WHERE ( SELECT COUNT() FROM table2 WHERE fbid = current_row_primary_key*) > 150

Re: WHERE ( SELECT COUNT() FROM table2 WHERE fbid = current_row_primary_key*) > 150

Re: WHERE ( SELECT COUNT() FROM table2 WHERE fbid = current_row_primary_key*) > 150

Re: WHERE ( SELECT COUNT() FROM table2 WHERE fbid = current_row_primary_key*) > 150