Search over multiple tables

[kbloem]

Hello,

 

i want to do a search over multiple databases. How should my search code look? This is what i have...

 

<?php

mysql_select_db("model", $con);

 

$qry = "SELECT * FROM properties WHERE 1=1";

if (isset($_POST['brand']) && trim($_POST['brand']) != "") {

        $qry .= " AND brand='" . mysql_real_escape_string($_POST['brand']). "'";

        }

 

?>

 

So i want users to fill in a search form and when they enter a brand it has to search over multiple tables. How does that work?

 

Can i do something like this?

 

$qry = "SELECT * FROM properties (AND stores AND etc...) WHERE 1=1";

 

 

 

[kickstart]

Hi

 

Normally this would be done with a UNION (or UNION ALL).

 

Essentially several select statements bringing back one set of rows. Each select must bring back rows whose format match each other.

 

You would use something like:-

 

SELECT * 
FROM properties
WHERE 1=1
UNION
SELECT * 
FROM stores
WHERE 1=1
UNION
SELECT * 
FROM someOtherTable
WHERE 1=1

 

However most of the time those tables will not have rows of the same format, so you need to bring back specific rows that do (also using SELECT * is a bit dodgy in production situations as someone changing the table can cause chaos). So something like this might be more usual

 

SELECT 'property' AS tabSource, propertyId AS id
FROM properties
WHERE 1=1
UNION
SELECT  'stores' AS tabSource, storesId AS id
FROM stores
WHERE 1=1
UNION
SELECT 'someOtherTable' AS tabSource, someOtherId AS id
FROM someOtherTable
WHERE 1=1

 

All the best

 

Keith