Mathias Nervik Mathias Nervik - 2 months ago 5
PHP Question

Fetching data from two queries as one

This is a picture-type webpage



What i want to be returned is a string like this:
nameOfTag (numberOfUnratedElements)

I am using a system where the user only knows the filename of the image, and the rest is stored in two tables.

Table info



table_name = images




  • id (int) primaryKey

  • filename (varchar255) unique

  • pick (tinyint)

  • reject (tinyint)



table_name = image_tags




  • id (int) primaryKey

  • filename (varchar255)

  • tag_name (varchar255)



Functionality



The program should get the filename of the image from the user, and get all tags associated with that filename. Afterwards the program should return the number of unrated (
pick = FALSE AND reject = FALSE
) images in each of the tags returned.

Code



As of now I am using a loop to achieve this. ("$fname" is the filename of the image in the format {filename}.{extension})

$query = $pdo->prepare("SELECT image_tags.tag_name, images.filename FROM image_tags JOIN images ON image_tags.filename = images.filename WHERE images.filename = ? ORDER BY image_tags.tag_name");
$query->bindValue(1, $fname);
$query->execute();
foreach($query->fetchAll() as $result){
$query = $pdo->prepare("SELECT count(*) as total, image_tags.tag_name, images.filename, images.pick, images.reject FROM image_tags JOIN images ON image_tags.filename = images.filename WHERE image_tags.tag_name = ? AND images.pick = FALSE AND images.reject = FALSE");
$query->bindValue(1, $tag['tag_name']);
$query->execute();
print '<span style="display: block"><a style="display: inline-block" href=tag.php?tag=' . $tag['tag_name'] . '>' . $tag['tag_name'] . ' (' . $query->fetch()['total'] . ')</a></span>';
}


Question



My question is if there is a way to achieve this without using the loop, or alternatively with just one query?

Answer

You can do this with more joins:

SELECT it.tag_name, i.filename, COUNT(i2.filename) as total,
       GROUP_CONCAT(i2.filename) as filenames
FROM image_tags it JOIN
     images i
     ON it.filename = i.filename LEFT JOIN
     image_tags it2
     ON it2.tag_name = it.tag_name LEFT JOIN
     images i2
     ON it2.filename = i2.filename AND
        i2.pick = FALSE AND i2.reject = FALSE
WHERE i.filename = ?
GROUP BY it.tag_name, i.filename
ORDER BY it.tag_name;

Note that filenames is a concatenated list of filenames. I'm not sure if that is needed, and it might exceed the default length for group_concat() (1024 bytes).

Hmmm, I think you can simplify your first query and hence this query to:

SELECT it.tag_name, it.filename, COUNT(i2.filename) as total,
       GROUP_CONCAT(i2.filename) as filenames
FROM image_tags it LEFT JOIN
     image_tags it2
     ON it2.tag_name = it.tag_name LEFT JOIN
     images i2
     ON it2.filename = i2.filename AND
        i2.pick = FALSE AND i2.reject = FALSE
WHERE it.filename = ?
GROUP BY it.tag_name, i.filename
ORDER BY it.tag_name;

The first images reference isn't needed because all the needed data is in image_tags.

Comments