CODEWITHSUNDEEP
phpmysql
user1048676
user1048676

Reputation: 10066

Count in mySQL based on a certain select

All, Say I have the following Select statement in mySQL:

$qry = "Select filename from upload where file_id='$file_id'";
$result = mysql_query($qry);
$resultset = mysql_fetch_array($result);

That query works fine but what I would also like to do is include a count of how many times that same filename appears somewhere else in that table in the same statement so I only have to run the mySQL statement once instead of getting that filename from this result and then executing another query like this one:

$qrycheck = "Select COUNT(filename) from upload where filename='$resultset[filename]'";

Is there anyway to do this check in a single mySQL statement?

Thanks!

Upvotes: 0

Views: 184

Answers (5)

TaeL
TaeL

Reputation: 1066

it could be help you

SELECT COUNT( * ) AS  `Cnt` ,  `filename` 
FROM  `upload` 
GROUP BY  `filename`
limit 10;

and you wanna it maybe

SELECT filename, count(*) as cnt 
from upload 
where file_id='###my_file_id###' 
group by filename;

you can search with google 'mysql group by'

Upvotes: 0

LSerni
LSerni

Reputation: 57388

If I understand correctly, you have several instances of 'filename' with different file_ids (I suppose that file_id is a unique ID, so it wouldn't make sense to count occurrences of file_id - you'd either get 1 or 0):

  file_id  filename
  1        filename1.jpg
  2        filename2.jpg
  3        filename1.jpg
  4        filename1.jpg

If that's the case, you have to JOIN upload with itself:

  SELECT uploads.filename, count(names.instance) AS instances FROM upload AS uploads
       JOIN upload AS names ON (uploads.filename = names.filename)
       WHERE uploads.file_id = '$file_id' GROUP BY uploads.filename;

This will return the name in $resultset['filename'] and the number of instances in $resultset['instances'].

Upvotes: 0

Palladium
Palladium

Reputation: 3763

Self-join is your friend.

$qry = "SELECT DISTINCT filename, a
    FROM (SELECT count(filename) AS a, file_id FROM upload GROUP BY file_id) AS x
    JOIN upload ON upload.file_id = x.file_id
    WHERE upload.file_id = '$file_id'";

The DISTINCThelps to prevent the same filename showing up multiple times (although if you're only grabbing the first row, it really doesn't matter).

Upvotes: 0

lc.
lc.

Reputation: 116458

SELECT u.filename, c.cnt
FROM upload AS u
INNER JOIN 
(
    SELECT COUNT(*) AS cnt, uu.filename 
    FROM upload AS uu
    GROUP BY uu.filename
) AS c ON u.filename = c.filename
WHERE u.file_id = '$file_id'

Upvotes: 0

Mathieu Dumoulin
Mathieu Dumoulin

Reputation: 12244

SELECT COUNT(filename) AS filenameOccurences, filename FROM upload WHERE filename = '$resultset[filename]' GROUP BY filename;

Upvotes: 2

Related Questions