Relation between 3 tables and find the result of last week mysql

Question

I have three tables, the tbl_recursos, tbl_categorias and the tbl_categoria_recurso. To tbl_recursos is the listing of resources posted by the user. A tbl_categorias are the categories a resource can have. And finally the tbl_categoria_recurso makes the relationship between resources and categories.

- tbl_recursos - 
id
data

- tbl_categorias -
id
nome

- tbl_categoria_recurso - 
id
id_recurso
id_categoria

What I want to do is list the names of the categories you’ve been assigned most resources in the last week. That is, the top 5 categories with more resources in the last week. I am trying to make the following query but gives error in date:

<?php
    $sql = mysql_query("SELECT * FROM tbl_categorias cat WHERE 
                (SELECT 1 FROM tbl_categoria_recurso cat_r
                        JOIN tbl_recursos re
                        ON re.id = cat_r.id_recurso
                        WHERE cat_r.id_categoria = cat.id
                        AND (re.data >= curdate() - INTERVAL DAYOFWEEK(curdate())+6 DAY

AND re.data < Kurdish() - INTERVAL DAYOFWEEK(Kurdish())-1 DAY))"); ?> ?>

Error appears in the following image:

And the image that proves that the date field exists in tbl_resources:

Answer 1

Try:

 $sql = mysql_query("SELECT * FROM tbl_categorias cat WHERE 
                    (
                        SELECT 1 FROM tbl_categoria_recurso cat_r
                        JOIN tbl_recursos r
                        ON `r`.`id` = cat_r.id_recurso
                        WHERE cat_r.id_categoria = `cat`.`id`
                        AND `r`.`data` >= (curdate() - INTERVAL DAYOFWEEK(curdate())+6 DAY)
                        AND `r`.`data` < (curdate() - INTERVAL DAYOFWEEK(curdate())-1 DAY)
                    )
                ");

I haven’t quite understood what you really want to return in the subselect, because you are making a condition in the primary select capturing a record of the secondary select, you should not return the id to be used?

namely p.e:

SELECT * FROM tbl_categorias cat WHERE (SELECT `cat_r`.`id` WHERE ...))

Answer 2

I ended up using the following solution:

<?php
    date_default_timezone_set('Europe/Lisbon');
    $data_actual = date("Y-m-d");
    $data_last_week = date('Y-m-d', strtotime($data_actual. ' - 7 days'));


    $sql1 = mysql_query("SELECT COUNT(tbl_categoria_recurso.id_categoria) as contagem, tbl_categoria_recurso.id_categoria FROM tbl_recursos INNER JOIN tbl_categoria_recurso ON tbl_recursos.id=tbl_categoria_recurso.id_recurso WHERE data > '$data_last_week' GROUP BY id_categoria order by contagem desc LIMIT 5 ");

?>