Complex SELECT with five+ tables

Asked

Viewed 128 times

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.

php sql

1 answer

1

Opa!

A possible solution would be to use the UNION. The query would look something like this (untested):

SELECT "article" as categoria, articles_id as "id", articles_description as "description", articles_text as "article_text", NULL as "pontos_letter"
    FROM tuc_articles
    WHERE (`articles_title` LIKE '%".$a."%') OR (`articles_text` LIKE '%".$a."%')
UNION
SELECT "ponto", pontos_id, pontos_description, NULL, pontos_letter
    FROM tuc_pontos
    WHERE (`pontos_title` LIKE '%".$a."%') OR (`pontos_letter` LIKE '%".$a."%')
ORDER BY categoria ASC;

Return a result set like this:

categoria | id | description | article_text | pontos_letter
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ 
article   | 1  | desc A...   | text A...    | NULL
article   | 2  | desc B...   | text B...    | NULL
ponto     | 1  | desc C...   | NULL         | letter A...
ponto     | 3  | desc D...   | NULL         | letter B...

  • 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!

  • 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.

  • 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?

  • 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.

  • 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 that pontos_letter and articles_text are not of the same type and will return in different columns.

  • 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.

  • Let’s go continue this discussion in chat.

Show 2 more comments

Browser other questions tagged php sql

You are not signed in. Login or sign up in order to post.