Skip to content
Advertisement

Select last N rows with multiple values in a single query

I have a table:

messages:

< id – title – body – city – street – building – created on >

While I was able to select required messages in multiple queries, I need to do it in a single one:

Here are my queries at the moment:

$queryOne = "SELECT `id`, `title`, `body`, `created_on` FROM `fcm_messages` WHERE `city`='$city' AND `street`='$street' AND `building`='$building'";
$queryTwo = "SELECT `id`, `title`, `body`, `created_on` FROM `fcm_messages` WHERE `city`='$city' AND `street`='$street' AND `building`='-'";
$queryThree = "SELECT `id`, `title`, `body`, `created_on` FROM `fcm_messages` WHERE `city`='$city' AND `street`='-' AND `building`='-'";
$queryFour = "SELECT `id`, `title`, `body`, `created_on` FROM `fcm_messages` WHERE `city`='-' AND `street`='-' AND `building`='-'";

I need one query because I can then use ORDER BY id DESC which will sort all the messages perfectly suitable for my needs.

I could apply same sorting to the queries I have right now, but it would give same result because later on they’re pushed into array one after another and the order would be messed up.

Advertisement

Answer

I think the simpler approach is boolean logic:

SELECT `id`, `title`, `body`, `created_on` 
FROM `fcm_messages` 
WHERE 
    (`city` = :city AND `street` = :street AND `building` = :building)
    OR (`city` = :city AND `street` = :street AND `building` = '-')
    OR (`city` = :city AND `street` = '-' AND `building` = '-')
    OR (`city` = '-' AND `street` = '-' AND `building` = '-')

Note that this uses query parameters to pass the variables to the query rather than concatenating them into the query string: this is both safer and more efficient.

User contributions licensed under: CC BY-SA
6 People found this is helpful
Advertisement