SELECT complex with five + tables

0

Well, good evening.

I am developing a search system, and the query must be performed on 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, use the CATEGORY field to display accordingly, as in the example above.

I would like to know how select should be, and how to use it in PHP.

Structure of tables (it's 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 pontos_id
pontos_title
puntos_description
puntos_letter
pontos_visits
dot_slug
pontos_date
dot_dateModified
pontos_url

TUC_ALBUNS

albuns_id
albuns_url
albuns_image
albuns_date
albuns_dateModified
albuns_slug

How would I create a SQL query for all tables, and how would I differentiate, so I could display accordingly? If the result is from the articles table, it shows so, if it is from tuc_pontos, it shows in another way, and so on ...

What I thought was to create a category field in all tables, but would be redundant too, because if there were 1000 records in the table tuc_articles, all records would have the same value.

So the extra doubt is this: do you know what 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 of each.

    
asked by anonymous 07.07.2016 / 03:20

1 answer

1

Oops!

One possible solution would be to use UNION . The query would look something like this (not tested):

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;

I would 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...
    
07.07.2016 / 10:36