0
I have a posting system where the user makes a post with the following fields: title, description, tags (separated by comma) as I could make a query in the table through the tags and display the posts reported?
2 answers
2
first case: You can do something like this:
SELECT *.noticias,
noticias.tags
FROM noticias
WHERE noticias.tags IN('mysql', 'php', 'jquery')
ORDER BY data_noticias DESC, noticias.tags;
Of course:
Select everything from the news table and the value of the news table tags field where the value of the tags field is in the list (value 1, value 2, value 3) and sort by descending date and by ascending tags.
second case: You can do this in a relational way too, with three tables, you register the tags within a table and the relationship between the tables in another table, relating everything by the foreign key. Let’s say you have three tables:
1st table - news:
+----------------------------------------------+
| id_noticia | titulo | descricao |
+----------------------------------------------+
| 1 | exemplo 1 | lorem inpsun dolor |
+----------------------------------------------+
| 2 | exemplo 2 | lorem inpsun dolor |
+----------------------------------------------+
2nd table - table_tags: Create a table to register all your tags
+----------------+
| id_tag | tag |
+----------------+
| 1 | tag1 |
+----------------+
| 2 | tag2 |
+----------------+
| 3 | tag3 |
+----------------+
3rd table - table_rel_tags: "id_fk_noticia" would be the "id_noticia" (from the news table) and "id_fk_tag" would be the id_tag of the table table_tags.
+--------------------------------------+
| id_rel | id_fk_tag | id_fk_noticia |
+--------------------------------------+
| 1 | 1 | 1 |
+--------------------------------------+
| 2 | 2 | 2 |
+--------------------------------------+
| 3 | 3 | 2 |
+--------------------------------------+
So, you collect the tags from the relational table, Example:
//declara o tipo: array
$select= array();
//traz do banco as tags consultadas
$tags = array(
1 => 'tag1',
2 => 'tag2',
3 => 'tag3'
);
//inicia o array de seleções
$select[] = "SELECT *.noticias";
//faz o loop nas tags consultadas
foreach ($tags as $id_tag => $tag) {
//na query você verifica se a id da tabela tag consta na sua tabela relacional, se existir você cria um alias com o nome da tag e traz a id no campo
$select[] = "IF(tabela_rel_tags.tags = '$id_tag', tabela_rel_tags.tags, null) as '$tag'";
}
//monta a string da sua query separando-a por vírgulas
$sql = implode(", ", $select);
//concatena o resto da sua query que irá relacionar as tabelas
$sql .= "
FROM noticias
INNER JOIN tabela_rel_tags ON(tabela_rel_tags.id_fk_noticias = noticias.id_noticia)
INNER JOIN tabela_tags ON (tabela_tags.id_tag = tabela_rel_tags.id_tag)
WHERE tabela_tags.tags IN (".implode(",", $tags).")
ORDER BY data_noticias DESC, noticias.tags; ";
-
the posts would be what you can explain me better the code?
-
1
just one more question as I’ll know the values of the tags field to make the query?
-
In the first case, you would have to tag only for each news item. In the second case, you may have more than one related tag.
0
SELECT *.posts,
posts.tags
FROM posts
WHERE posts.tags IN('mysql', 'php', 'jquery')
ORDER BY date_post DESC, posts.tags;
The select*.posts: would be the table of your posts and when appears posts.tags would relate the video and tags.
Browser other questions tagged php database
You are not signed in. Login or sign up in order to post.
Put the database schema and PHP code
– Ricardo
news table
– Gabriel José
Post the table creation sql in the question body and related php code
– Ricardo
Maybe it’ll help, What syntax to search with array as parameter in Mysql?
– rray
SELECT *, MATCH(title, description) AGAINST('$itulo') AS score FROM posts WHERE MATCH(title, description) AGAINST('$title') ORDER BY score DESC LIMIT 5 more or less that?
– Gabriel José