CODEWITHSUNDEEP
phpmysqlsql
Alien
Alien

Reputation: 29

Searching all tables

I'm having a little problem. I have 2 tables in my database:

servico, with id, nome, profissao (plumber, electrician,e tc), data and local.

canalizador, with id, nome, profissao (only plumber), data and local.

I have this code so that I can get to the "canalizador" table:

mysql_connect($host,$user,$passwd) or die (mysql_error());
mysql_select_db($database);

$query = "SELECT * FROM canalizador WHERE profissao LIKE '$profissao'";
$result = mysql_query($query) or die(mysql_error());
$casaArray = array();

But I need a code where I can search all the tables like the "canalizador" table by just pressing the button of each career(profissao) in the menu. If I do this:

mysql_connect($host,$user,$passwd) or die (mysql_error());
mysql_select_db($database);

$query = "SELECT * FROM canalizador, carpinteiro WHERE profissao LIKE '$profissao'";
$result = mysql_query($query) or die(mysql_error());
$casaArray = array();

I have this error:

Column 'profissao' in where clause is ambiguous

Upvotes: 0

Views: 61

Answers (4)

jarlh
jarlh

Reputation: 44766

Do a UNION ALL with the two tables, use LIKE in the result (as a derived table):

select id, nome, profissao, data, local 
from
(
select id, nome, profissao, data, local from servico
UNION ALL
select id, nome, profissao, data, local from canalizador
) dt
where dt.profissao LIKE '$profissao'

Alternatively, as suggested by AsConfused, just a UNION ALL:

select id, nome, profissao, data, local from servico
where profissao LIKE '$profissao'
UNION ALL
select id, nome, profissao, data, local from canalizador
where profissao LIKE '$profissao'

Upvotes: 2

Stanislovas Kalašnikovas
Stanislovas Kalašnikovas

Reputation: 9053

First of all is bad practice to use comma separated tables like this:

SELECT * FROM canalizador, carpinteiro, etc...

You need to use JOINS and list column names in SELECT clause like this:

SELECT col1, col2, col3 
FROM canalizador can 
JOIN carpinteiro car ON can.Id = car.Id

Then specify from which table you want to use profissao column

WHERE can.profissao LIKE '$profissao

Upvotes: 0

Bud Damyanov
Bud Damyanov

Reputation: 31839

As first option you can give your column name an alias with AS keyword, i.e:

SELECT canalizador.column AS col1, carpinteiro.column AS col2 
FROM canalizador, carpinteiro WHERE profissao LIKE '$profissao'

As second option you can rework your SQL query and use MySQL JOIN to combine multiple table results, read more about how to join tables here and more general information here.

Upvotes: 0

Sanjay Kumar N S
Sanjay Kumar N S

Reputation: 4739

Try this:

mysql_connect($host,$user,$passwd) or die (mysql_error());
mysql_select_db($database);

$query = "SELECT * FROM canalizador c1, carpinteiro c2 WHERE c1.profissao LIKE '$profissao'";
                                                          // ^ c1 or c2 
$result = mysql_query($query) or die(mysql_error());
$casaArray = array();

Upvotes: 0

Related Questions