Search system with mysql related tables

Question

As you can see in the image below, I have the table Real Estate (mysql) which relates to the tables Type, Neighborhood and City.

What I need is this:

Has a text field to perform a simple search and can be typed anything. Suppose a user type "São Bernardo do Campo", the system will search in the tables Type, Neighborhoods and Cities until you find in the column "name" the word typed and if you find, takes the ID and loops it against the respective ID in the Properties table and returns all occurrences found.

I would like to do with INNER JOIN, but I’m having trouble understanding.

I appreciate any help.

Answer 1

First you will have to link all tables with the Inner Join. Then you have to see where you will get 'fields'... It would be something like that:

SELECT * FROM `imoveis` AS `i`
INNER JOIN `tipo` AS `t` ON `i`.`tipo_id` = `t`.`tipo_id`
INNER JOIN `cidades` AS `c` ON `i`.`cidade_id` = `c`.`cidade_id`
INNER JOIN `bairros` AS `b` ON `i`.`bairro_id` = `b`.`bairro_id`

This makes the links of the tables by the respective foreign keys, after that will have to do where to search...

WHERE `b`.`bairro_nome` = ? OR `c`.`cidade_nome` = ? OR `t`.`tipo_nome` = ?

Ready, now you have your query string, of course it will come back all fields of the table, before you must make the correct SELECT of which fields will want to have the return.

Note: before posting a question try to read about it, study plus, this is a simple thing to do, would achieve in a few hours searching on the internet.