Skip to content

Instantly share code, notes, and snippets.

@EmanuelAzzopardi
Forked from dajve/example-query-result.md
Created January 6, 2018 17:52
Show Gist options
  • Save EmanuelAzzopardi/52177cca63499793e6595df151b68aa8 to your computer and use it in GitHub Desktop.
Save EmanuelAzzopardi/52177cca63499793e6595df151b68aa8 to your computer and use it in GitHub Desktop.

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
<?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->bindValue(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;
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment