0
You guys, good night.
I am developing a search system, and the query should be performed IN ALL existing tables. It turns out that for each type of result there is a presentation, for example:
if($cat == 'a'):
echo 'mostra isso';
elseif($cat == 'b'):
echo 'mostra assim';
else:
echo 'mostra outra coisa';
endif;
Before I had a single table for all information, and many of the fields were empty, and at the time of selection, uses the CATEGORY field to display accordingly, as the example previously passed.
I would like to know how select should be, and how to use it in PHP.
Table structure (is a religious site)
TUC_ARTICLES
articles_id
articles_title
articles_description
articles_text
articles_image
articles_visits
articles_slug
articles_date
articles_dateModified
TUC_PONTOS
dot_id
points_title
point_description
pontos_letter
pontos_visits
pontos_slug
date
pontos_dateModified
point_url
point_orixa
TUC_ALBUNS
albuns_id
albuns_url
albuns_image
albuns_date
albuns_dateModified
albuns_slug
How would I create an SQL query for all tables, and how would I differentiate, to be able to display accordingly? If the result is from the Articles table, it shows so, if it is from the tuc_points, it shows otherwise, and so on...
What I thought was to create a category field in all tables, but it would be too redundant, because if I had 1000 records in the tuc_articles table, all records would have the same value..
So the extra question is this: has how to know from which table comes the result, to be able to manipulate the data according to its respective table?
What I’ve tried so far:
$search->Read("SELECT * FROM tuc_articles, tuc_pontos WHERE ((`articles_title` LIKE '%".$a."%') OR (`articles_text` LIKE '%".$a."%') OR (`pontos_title` LIKE '%".$a."%') OR (`pontos_letter` LIKE '%".$a."%')) ORDER BY articles_id DESC");
PS: The above test is generic, I used only two tables and two fields each.
The answer is partially correct. However, I had a question: When I give the var_dump, it appears exactly as I said, an array with category and id, and then I can do the IF. It turns out that select limited me to only use category and ID, and now I don’t know how to rescue the other columns from the table.. How to proceed? I want to show it one way if it’s article, another way if it’s point.. Anyway, I thank you until then, and I apologize for the delay in returning. Hug!
– Cobra
If you want different columns, it seems to me that the only option is to do more than one query, one select for each category.
– mrlew
I thought about it from the beginning, and until then it would not be a problem to create the query.. It turns out that in my view, such practice would be wrong.. What do you think? Besides, how would you proceed with the situation?
– Cobra
It’s all a matter of necessity. The ideal is always to minimize the number of queries, but depending on your need before your data structure (in your case it seems, since you want different results extracted from the same table), there is no alternative if you do not do more than one query. And if this will impact performance, only knowing its structure, number of users, server structure, structure of its application, or even stress tests etc.
– mrlew
But looking at your answer above, if you just want to add columns, just add them to the select (I edited the answer) of both Union’s select, as long as they are the same type (in the answer, it’s the column
description
). If it’s not of the same type, just return null, as I did above (I don’t know its structure, but assumed thatpontos_letter
andarticles_text
are not of the same type and will return in different columns.– mrlew
I understood. The way you gave the option, I believe that the "correct" or "less worse" would be the creation of several queries.. It turns out that there is not much relationship between the tables, except the ID and TITLE.
– Cobra
Let’s go continue this discussion in chat.
– Cobra