Search system with related tables mysql

Question

Search system with related tables mysql

Navigation

#1 by (0 votes)

1

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

WhatIneedisthefollowing:

Youhaveatextfieldtoperformasimplesearchandanythingcanbetyped.Assumeausertype"São Bernardo do Campo", the system will look in the Type, Neighborhoods and Cities tables until in the "name" column the word is entered and if it finds, it picks up the ID and it makes a loop comparing with its ID in the table Real Estate and returns all occurrences found.

I'd like to do with INNER JOIN, but I'm having trouble understanding.

Thanks for any help.

mysql

asked by anonymous 02.04.2016 / 18:08

1 answer

Error compiling project How to create a txt file?

score 0 · Answer 1

First you will have to link all the tables with the inner join. Then you have to see where you will look for 'fields' ... It would look something like this:

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 connections of the tables by their foreign keys, after that you will have to do what to look for ...

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

Now, you have your query string, of course it will return all the fields of the table, before you must do the correct SELECT of which fields you want to have the return.

Note: Before posting a question try reading about it, study more, this is a simple thing to do, it would achieve in a few hours searching the internet.