SELECT with 2 tables and same field in both

Question

SELECT with 2 tables and same field in both

Navigation

#1 by (2 votes)
#2 by (1 votes)
#3 by (0 votes)

1

Good afternoon!

In my database, I have two tables: imoveisvenda and imoveislocacao

Both have the "dorm" field, which says how many dorms the house or apartment has, either for sale or lease.

I want to create a SELECT to make it easier for anyone browsing the site.

I have tried several combinations in PHPmyAdmin, but without success.

How can I make this query to deploy to my PHP page?

The last one I tried was: SELECT * imoveislocacao INNER JOIN imoveisvenda WHERE dormitorios = 3 . Once again, to no avail!

I have already looked at some posts here from StackOverflow, but I could not deploy it.

Can anyone help me?

UPDATING:

My friend @Ricardo, I want to thank you for the support. And, as requested, it follows the error that was generated below:

Notice in ./libraries/sqlparser.lib.php#2477
Undefined offset: -2

Backtrace

./libraries/sql.lib.php#1456: PMA_SQP_format(
array,
string 'query_only',
integer 0,
integer 2,
)
./libraries/sql.lib.php#1574: PMA_countQueryResults(
integer 3,
boolean false,
boolean false,
string 'adimovel_imoveis',
string '',
array,
array,
)
./libraries/sql.lib.php#2411: PMA_executeTheQuery(
array,
string '(SELECT iv.id, iv.dormitorio FROM imoveisvenda iv WHERE iv.dormitorio = 3) UNION (SELECT il.id, il.dormitorio FROM imoveislocacao il WHERE il.dormitorio = 3) LIMIT 0, 25 ',
boolean false,
string 'adimovel_imoveis',
string '',
NULL,
string '(SELECT iv.id, iv.dormitorio FROM imoveisvenda iv WHERE iv.dormitorio = 3) UNION (SELECT il.id, il.dormitorio FROM imoveislocacao il WHERE il.dormitorio = 3)',
NULL,
)
./import.php#708: PMA_executeQueryAndSendQueryResponse(
array,
boolean false,
string 'adimovel_imoveis',
string '',
NULL,
string '(SELECT iv.id, iv.dormitorio FROM imoveisvenda iv WHERE iv.dormitorio = 3) UNION (SELECT il.id, il.dormitorio FROM imoveislocacao il WHERE il.dormitorio = 3)',
NULL,
boolean false,
NULL,
NULL,
NULL,
array,
string 'db_structure.php',
string './themes/pmahomme/img/',
NULL,
NULL,
NULL,
string '(SELECT iv.id, iv.dormitorio FROM imoveisvenda iv WHERE iv.dormitorio = 3) UNION (SELECT il.id, il.dormitorio FROM imoveislocacao il WHERE il.dormitorio = 3)',
NULL,
NULL,
)
Notice in ./libraries/sqlparser.lib.php#2482
Undefined offset: -1

Backtrace

./libraries/sql.lib.php#1456: PMA_SQP_format(
array,
string 'query_only',
integer 0,
integer 2,
)
./libraries/sql.lib.php#1574: PMA_countQueryResults(
integer 3,
boolean false,
boolean false,
string 'adimovel_imoveis',
string '',
array,
array,
)
./libraries/sql.lib.php#2411: PMA_executeTheQuery(
array,
string '(SELECT iv.id, iv.dormitorio FROM imoveisvenda iv WHERE iv.dormitorio = 3) UNION (SELECT il.id, il.dormitorio FROM imoveislocacao il WHERE il.dormitorio = 3) LIMIT 0, 25 ',
boolean false,
string 'adimovel_imoveis',
string '',
NULL,
string '(SELECT iv.id, iv.dormitorio FROM imoveisvenda iv WHERE iv.dormitorio = 3) UNION (SELECT il.id, il.dormitorio FROM imoveislocacao il WHERE il.dormitorio = 3)',
NULL,
)
./import.php#708: PMA_executeQueryAndSendQueryResponse(
array,
boolean false,
string 'adimovel_imoveis',
string '',
NULL,
string '(SELECT iv.id, iv.dormitorio FROM imoveisvenda iv WHERE iv.dormitorio = 3) UNION (SELECT il.id, il.dormitorio FROM imoveislocacao il WHERE il.dormitorio = 3)',
NULL,
boolean false,
NULL,
NULL,
NULL,
array,
string 'db_structure.php',
string './themes/pmahomme/img/',
NULL,
NULL,
NULL,
string '(SELECT iv.id, iv.dormitorio FROM imoveisvenda iv WHERE iv.dormitorio = 3) UNION (SELECT il.id, il.dormitorio FROM imoveislocacao il WHERE il.dormitorio = 3)',
NULL,
NULL,
)

Finally, my complete code looks like this:

<?php

                            $hostdb = "**********";
                            $userdb = "**********";
                            $passdb = "**********";
                            $tabledb = "**********";

                            $conecta = mysql_connect($hostdb, $userdb, $passdb) or die (mysql_error());
                            @mysql_select_db($tabledb, $conecta) or die ("Erro ao conectar com o banco de dados");

                            $busca_query = mysql_query("SELECT * FROM imoveisvenda WHERE imoveisvenda.dormitorio = 3 UNION ALL SELECT * FROM imoveislocacao WHERE imoveislocacao.dormitorio = 3")or die(mysql_error());

                            if (empty($busca_query)) {
                                echo "Nenhum registro encontrado.";
                            }

                            while ($dados = mysql_fetch_array($busca_query)) { ?>
                                <img src="<?php echo "$dados[bancoimgthumb]";?>" class="img-responsive"> <?php
                                echo "Imóvel: $dados[imovel]<br />"; 
                                echo "Localização: $dados[localizacao]<br />"; ?>
                                Mais detalhes: <a href="<?php echo "$dados[file]";?>?id=<?php echo "$dados[id]";?>">Clique aqui!</a><br />
                                <?php echo "<hr>";
                            }
                            ?>

EVERYTHING WORKING PERFECTLY! Thanks to all who gave up their time to help me! Hugs!

php mysql

asked by anonymous 18.12.2015 / 20:39

3 answers

1

I would use a Union in two selects since I did not understand how to do join of the tables:

(SELECT iv.campo1, iv.campo2 FROM imoveisvenda iv WHERE iv.dormitorios = 3)
UNION
(SELECT il.campo1, il.campo2 FROM imoveislocacao il WHERE il.dormitorios = 3)

Read about the UNION here

Here's a Fiddle with Union running.

I'll put the fiddle code also to keep the log:

Create table imovelVendido (
  id varchar(10),
  nome varchar(10));

Create table imovelAlugado (
  id varchar(10),
  nome varchar(10));

  insert into imovelVendido values ("1","venda");
  insert into imovelAlugado values ("1","alugado");

Once this created data executes the SQL below that returns the two records:

(SELECT iv.id, iv.nome FROM imovelVendido iv WHERE iv.id = "1")
UNION
(SELECT il.id, il.nome FROM imovelAlugado il WHERE il.id = "1")

18.12.2015 / 20:48

0

In the future, you can improve your application by leaving this data in only one table, because from what I understand the structure of the tables is the same, you can create a field to indicate the type of contract (sale / lease). So your tables will become better normalized.

21.12.2015 / 17:58

Delete in multiple POSTS with checkbox [closed] Session in PHP using class

score 2 · Accepted Answer

2

You only need to use UNION ALL

select * from imoveisvenda where imoveisvenda.dormitorio=3 union all select * from imoveislocacao where imoveislocacao.dormitorio = 3

18.12.2015 / 23:23