Help with mysql curdate () - 1

Question

Help with mysql curdate () - 1

Navigation

#1 by (3 votes)
#2 by (3 votes)

2

I'm getting this error:

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

I have tried it in many ways, but it always gives this error.

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

try again 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 previous day of event using curdate .

My table EVENT has the columns id , name , description , date_at , time_at and category_id . The CATEGORY table 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

php mysql sql

asked by anonymous 23.11.2016 / 16:05

2 answers

3

I think what you want is this from here:

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 DATE_SUB and also DATE_ADD .

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

In addition, your SQLs have / had other problems. For example, there can only be a single WHERE by SELECT , but your first SQL has two.

In SQL, as is used to give aliases to fields or tables used in SELECT or to do type conversions. However, your second SQL command attempts to use as for something else (which obviously does not work).

23.11.2016 / 16:45

How to shuffle this array inside itself? [duplicate] compare and remove repeated item

score 3 · Accepted Answer

The function that subtracts a range from a date is SUBTIME .

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

As above 1 day is subtracted from the date.

According to Tech On The Net - SUBTIME :

The MySQL SUBTIME function returns the date / time value after a certain amount of time 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