MySQL query logic

Question

I have a DB full of deals, and a website which will present just one of these deals if the deal is a featured deal, however I am struggling to get the right logic... the deal that is shown on the site must a. be within a valid date range b. be the most recently added deal to the database

by using the following query, I am able to accomplish this:

"SELECT * FROM deals WHERE datestart < now() AND dateend > now() ORDER BY deals.deal_id DESC"

Great. however... on rare occasions a whole bunch of deals are added at once, so I need some kind of override to specify which one should be 'featured'. I added a boolean value [featured] and tested the following query:

"SELECT * FROM deals WHERE datestart < now() AND dateend > now() ORDER BY deals.featured DESC"

It worked, but now I need to specify the featured deal, or else the featured deal will be randomly selected? whereas I only want to have it as an override.

SO I need to combine the above 2 scripts somehow.

any ideas?

thanks guys.

why not have featured = 1 in WHERE clause?

Michał Szajbe
– Michał Szajbe

2012-07-24 09:19:30 +00:00
Commented Jul 24, 2012 at 9:19 — Michał Szajbe
– Michał Szajbe, Commented Jul 24, 2012 at 9:19

dstrachan · Accepted Answer · 2012-07-24 09:54:15Z

0

Add the boolean comparison to the WHERE clause and limit the number of results to 1. When ordering by the deal_id this will always return the same result.

SELECT *
FROM deals
WHERE datestart < now() AND dateend > now() AND deals.featured IS TRUE
ORDER BY deals.deal_id DESC
LIMIT 1

EDIT:

Here is an alternative so you don't have to add more to the WHERE clause.

SELECT *
FROM deals
WHERE datestart < now() AND dateend > now()
ORDER BY deals.featured DESC, deals.deal_id DESC
LIMIT 1

The order of the ORDER clause is important, if deal_id's are unique, as i presumed they were, the featured boolean would not be used if they were ordered the opposite way round.

edited Jul 24, 2012 at 9:54

answered Jul 24, 2012 at 9:20

dstrachan

2061 silver badge7 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Pete Norris Over a year ago

Thing is, I don't want them to have to specify the deal as true normally (all deals will have a bool = 0 ideally).

dstrachan Over a year ago

You could move the deals.featured into the ORDER clause, still limiting the results to 1, allowing you to retrieve the most recent featured deal.

Bridge · Accepted Answer · 2012-07-24 09:15:51Z

0

If you just want to combine the two queries, you can just combine the where clauses - order by deal_id first, then if there are multiples with the same deal_id, it'll then sub order by whether it's featured or not:

SELECT *
FROM   deals
WHERE  datestart < Now()
       AND dateend > Now()
ORDER  BY deals.deal_id DESC,
          deals.featured DESC

answered Jul 24, 2012 at 9:15

Bridge

30.9k10 gold badges64 silver badges85 bronze badges

2 Comments

Pete Norris Over a year ago

I liked the look of tho, but on testing it seems to ignore the second order by statement

Bridge Over a year ago

@PeteNorris Do you actually have more than one row per deal_id? That's what I thought your question inferred - I'm guessing that isn't actually the case then.

Omesh · Accepted Answer · 2012-07-24 09:24:03Z

0

To get featured deals you need to check for featured IS TRUE and use UNION ALL operation to override it:

SELECT *
FROM   deals
WHERE  datestart < Now()
       AND dateend > Now()
       AND featured IS TRUE
ORDER  BY deal_id DESC

UNION ALL

SELECT *
FROM   deals
WHERE  datestart < Now()
       AND dateend > Now()
ORDER  BY deal_id DESC;

answered Jul 24, 2012 at 9:24

Omesh

29.4k6 gold badges44 silver badges51 bronze badges

Comments

mike56 · Accepted Answer · 2012-07-24 10:03:03Z

0

I think what you 're trying to do is the following:

UPDATE deals 
SET featured=1 
WHERE datestart < now() AND dateend > now()
ORDER BY deal_id DESC
LIMIT 1;

Ok, so now you are trying to update the most recent valid featured deal and mark it as featured. If the rows affected count is 0 then you already have the most recent valid featured deal or there is no valid deal at all in the table (it's your choice how to react to this).

If the rows affected count is 1 then you just found a new one and you need to select it (perhaps to fill it in your site). The select statement:

SELECT *
FROM deals
WHERE datestart < now() AND dateend > now() AND featured=1
ORDER BY deal_id DESC
LIMIT 1;

Ideally you would combine these two queries into only one update-select but no way to do just that at the time.

edited Jul 24, 2012 at 10:03

answered Jul 24, 2012 at 9:27

mike56

31 bronze badge

9 Comments

Pete Norris Over a year ago

But if all deals have featured set to '0' will this not break?

mike56 Over a year ago

Yes, you are right. I thought that the featured column was already set at the respective value. If you want to select the most recent featured deal and at the same time mark it as featured=1 you would actually need two queries, an update and a select statement.

Pete Norris Over a year ago

I don't suppose you could give me the syntax? I'm quite new to this stuff.

Pete Norris Over a year ago

scrap that, I see what you are saying. the site has no permission to update by design.

mike56 Over a year ago

It seems I'm missing the logic behind the use of the featured column... If you just need it as an override as you said, all you need to do is an update statement that sets featured=1 of the latest (by deal_id) valid deal. Then you select only the featured=1 ordered by deal_id. Ideally you could update and select a featured deal at the same time, in only one query, but not in mysql (at least not yet).

|

Collectives™ on Stack Overflow

MySQL query logic

4 Answers 4

2 Comments

2 Comments

Comments

9 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

2 Comments

Comments

9 Comments

Your Answer

Sign up or log in

Post as a guest

Related