PHP Earth FB - https://www.facebook.com/photo.php?fbid=10159805553710072

example-query-result.md

For an array passed with values

$tags = ['tag1','tag2','foo','bar']

the resulting $query will be

SELECT * FROM (
		SELECT 
						p.id
				,   p.name
				,   p.img_
				,   CASE WHEN c.category = 'wine' THEN 'wine'
								WHEN c.type IN ('Single malt', 'Whisky') THEN 'whiskey'
								END AS `category`
		FROM ilbettija.products p
		JOIN ilbettija.productscattype c
				ON (p.type = c.type)
		WHERE p.id IN (
				SELECT DISTINCT k.prodID
				FROM ilbettija.keywords k
				WHERE ((k.keyword = :tag0) OR (k.keyword = :tag1) OR (k.keyword = :tag2) OR (k.keyword = :tag3))
		)
		AND p.inStock = 'Y'
) prk
WHERE prk.category = :category

get_wines_by_keyword.php

<?php

/**
 * @param array $tags
 * @param string $category
 * @return array|false
 */
function get_wines_by_keyword($tags, $category)
{
    require (ROOT_PATH."inc/database.php");
    
    // Ensure we have iterable tags
    if (!is_array($tags) && !($tags instanceof \Traversable)) {
        $tags = [$tags];
    }
    // Ensure tags is not empty
    $tags = array_filter(array_map('trim', $tags));
    if (empty($tags)) {
        throw new \InvalidArgumentException("Tags parameter must not be empty");
    }
    
    try {
        $query = sprintf(
            "SELECT * FROM (
                SELECT 
                        p.id
                    ,   p.name
                    ,   p.img_
                    ,   CASE WHEN c.category = 'wine' THEN 'wine'
                            WHEN c.type IN ('Single malt', 'Whisky') THEN 'whiskey'
                            END AS `category`
                FROM ilbettija.products p
                JOIN ilbettija.productscattype c
                    ON (p.type = c.type)
                WHERE p.id IN (
                    SELECT DISTINCT k.prodID
                    FROM ilbettija.keywords k
                    WHERE (%s)
                )
                AND p.inStock = 'Y'
            ) prk
            WHERE prk.category = :category",
            "(".implode(") OR (", array_map(function($k) { return sprintf("k.keyword = :tag%s", $k); }, array_keys($tags))).")"
        );
        
        $results = $db->prepare($query);
        foreach ($tags as $k => $tag) {
            $results->bindParam(sprintf(":tag%s", $k), $tag);
        }
        $results->bindParam(":category", $category);
        $results->execute();
        
        return $results->fetchAll(\PDO::FETCH_ASSOC);
    } catch (\Exception $e) {
        echo "Data could not be retrieved from the database";
        // You'll want to log the exception information here, too, so you can reference in case of error
        
        return false;
    }    
}