Relation between 3 tables and search result from last week mysql

Question

Relation between 3 tables and search result from last week mysql

Navigation

#1 by (0 votes)
#2 by (0 votes)

1

I have three tables, tbl_recursos , tbl_categorias , and tbl_categoria_recurso . The tbl_recursos consists of the listing of resources posted by the user. tbl_categorias are the categories that a resource can have. And finally, 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 that have been associated with more resources in the last week. That is, the top 5 categories with more resources in the last week. I am trying to do the following query but it gives error in the 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 < curdate () - INTERVAL DAYOFWEEK (curdate ()) - 1 DAY))); ? > ? >

The error appears in the following image:

Andtheimagethatprovesthatthedatefieldexistsintbl_recursos:

php mysql sql

asked by anonymous 29.07.2014 / 16:11

2 answers

0

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 do not really understand what you really want to return in the subselect, because you're making a condition in the primary select by capturing a secondary select record, should not you return the id to be used?

ie p.e:

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

29.07.2014 / 16:41

What is the best strategy for synchronizing data offline / online? [closed] Retrieve XML TXMLDocument at runtime (Delphi XE3)

score 0 · Accepted Answer

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 ");

?>