Doubt how to query several tables with a word

Asked

Viewed 101 times

2

I am setting up a search system, where the user type in a search box any word, Example "Programmer", mysql will have to search that word in various fields in various table, if by chance he finds that word somewhere return to me who is the professional who gets that word.

Below are the tables and the query I’m using at the moment, but it’s not very functional.

Tables/columns

Professional:

id

Pri_name

surname

address_professional:

id

id_professional

state

city

school history:

id

id_professional

schooling

experience:

id

id_professional

office

Query I’m trying to hit!

SELECT a.id FROM profissional a, endereco_profissional b, historico_escola c, experiencia d WHERE a.pri_nome LIKE '%programador%' OR a.sobre_nome LIKE '%programador%' OR b.estado LIKE '%programador%' OR b.cidade LIKE '%programador%' OR c.escolaridade LIKE '%programador%' OR d.cargo LIKE '%programador%' OR a.id = b.id_profissional OR a.id = c.id_profissional OR a.id=d.id_profissional OR a.id=e.id_profissional OR a.id=f.id_profissional GROUP BY a.id
php mysql query

  • I believe making relationship with the tables. After you make a select Join with all the tables at once

  • Your tables contain only these columns?

  • No, they all contain id, the main table and the professional table, and the others contain their own id and the professional id_id.

  • They’re all related now.

  • If they are related then just make a select with Join.

1 answer

1


Class, I think I got it. It was just to use a little more head logic with the doors or and.

SELECT a.id FROM profissional a, endereco_profissional b, historico_escola c, experiencia d, area_interesse_profissional e, curso_extra f WHERE a.pri_nome LIKE '%programador%' OR a.sobre_nome LIKE '%programador%' OR (b.estado LIKE '%programador%' AND a.id = b.id_profissional) OR (b.cidade LIKE '%programador%' and a.id = b.id_profissional) OR (c.escolaridade LIKE '%programador%' AND  a.id = c.id_profissional) OR (d.cargo LIKE '%programador%' AND a.id=d.id_profissional)  GROUP BY a.id

  • "SELECT * FROM professional prof Join address_professional end on pro.id = end.id Where pri_name LIKE '%programmer%' " This is a way to join the tables.

  • I’ve always used this method and never had a problem, but what’s the difference between the two?

  • In Join vc makes a table from other or be you merge the tables. See this content for a clear http://vivenciandoti.blogspot.com.br/2009/05/sql-uso-do-join-clausulas-select.html?m=1

  • Make a select Join with your tables and play in phpmyadmin so you will visualize what I’m talking about

Browser other questions tagged php mysql query

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