Help with mysql Kurdate()-1

Question

I’m getting this mistake:

#1064 - You have an error in your SQL syntax;

I’ve tried so many ways, but there’s always this mistake.

Here are two of my attempts:

SELECT `category`. * , `event`. *
FROM category, event 
WHERE `category_id` = 7,
WHERE date_at = CURDATE()-1
ORDER BY `event`.`category_id` ASC
LIMIT 0 , 30 

SELECT `category`. * , `event`. *
FROM category, event 
WHERE `category_id` = 7 as  date_at = CURDATE()-1
ORDER BY `event`.`category_id` ASC
LIMIT 0 , 30

another attempt after some response worked and returns 0

  SELECT `event`. * , `category`. *
  FROM event, category
  WHERE date( date_at ) = CURDATE( ) -1
  AND category_id =7
  LIMIT 0 , 30 

I need to return the day before the event using the curdate.

My table EVENT has the columns id, name, description, date_at, time_at and category_id. The table CATEGORY has the columns id, name, color, description.

query = pegar os nomes ok 
        pegar as datas ok
        pegar as categorias ok
        pegar as categorias por id = ok
        quando insiro CURDATE()-1 = You have an error in your SQL syntax; 
        pegar a data por curdate -1 ERRO

Answer 1

The function that subtracts an interval from a date is a SUBTIME.

SELECT SUBTIME(CURRDATE(), '1 0:0:0.0')

As above 1 day is subtracted from date.

According to the Tech On The Net - SUBTIME:

Mysql’s SUBTIME function returns the date/time value after a certain time interval is subtracted.

Correcting and replacing in your example:

SELECT c.*,
       e. *
  FROM category c, event e
 WHERE c.category_id = e.id
   AND c.category_id = 7
   AND e.date_at = SUBTIME(CURRDATE(), '1 0:0:0.0')
 ORDER BY e.category_id ASC
 LIMIT 0, 30

Answer 2

I think what you want is this:

SELECT category.*, event.*
FROM category
INNER JOIN event ON event.category_id = category.id
WHERE category_id = 7
AND event.date_at = DATE_SUB(CURDATE(), INTERVAL 1 DAY)
ORDER BY event.category_id ASC
LIMIT 0, 30

See here about the DATE_SUB and also on the DATE_ADD.

The INNER JOIN is important because it tells how events and categories relate. Without it, the result would be any and all database events related to category 7.

Also, your Sqls have/had other problems. For example, there can only be one WHERE for SELECT, but your first SQL has two.

In SQL, the as is used to give nicknames to fields or tables used in SELECT or to make type conversions. However, your second SQL command tries to use as to something else (that doesn’t work, obviously).