Search More Then 1 Table

zzz

Trying to write a search to query to table.

One is simple:

$sql  = "SELECT * FROM table1 WHERE 1 = 1"; 
$sql .= empty($_POST['VAL1']) ? NULL : " AND VAL1 LIKE '%".$_POST['VAL1']."%'"; 
if($search1  = $db->get_results($sql)) 
{
			    foreach ( $search1 as $s1 )
	            {

How would I do it for two?

SumitGupta

Hi,

do you have any relation between tables. I mean do you have any forgein key in the table I don't thing so you have any.

zzz

Not any foreign keys, but there are certain columns that refer to the same item IDS of the third table... if that helps...

zzz

OK, here's what I'm doing:

<form method=post action='page.php'>
	<input type=text name=searchfield>
	<input type=radio name=searchoptions value=val_1> Val 1
	<input type=radio name=searchoptions value=val_2> Val 2
	<input type=radio name=searchoptions value=val_3 checked> Both Vals
	<input type=submit value=Search>
</form>

if ($_POST['searchfield']) {

$sql  = "SELECT * FROM table1 WHERE 1 = 1"; 
$sql .= empty($_POST['searchfield']) ? NULL : " AND val_1 LIKE '%".$_POST['searchfield']."%'";
$sql .= empty($_POST['searchfield']) ? NULL : " AND val_2 LIKE '%".$_POST['searchfield']."%'";
$sql .= empty($_POST['searchfield']) ? NULL : " AND val_3 LIKE '%".$_POST['searchfield']."%'"; 

if($search = $db->get_results($sql)) {
	    foreach ( $search as $s )
        {
			...OUTPUT...
		}
}

Above code works for one table. I'd like to be able to do the following:

if ($searchoptions == val_1 ) - search table 1
if ($searchoptions == val_2 ) - search table 2
if ($searchoptions == val_3 ) - search both tables

Afterthought...

Couldn't I just do something like this for running a query on both tables:

$sql  = "
		(SELECT * FROM table1)
		 UNION 
		(SELECT * FROM table2)"; 
$sql .= empty($_POST['searchfield']) ? NULL : " AND val_1 LIKE '%".$_POST['searchfield']."%'";
$sql .= empty($_POST['searchfield']) ? NULL : " AND val_2 LIKE '%".$_POST['searchfield']."%'";
$sql .= empty($_POST['searchfield']) ? NULL : " AND val_3 LIKE '%".$_POST['searchfield']."%'";

SumitGupta

Hi,

The Union that way cannot work for your as it search all result from table one and than search the where condition from table two. Also If you use the Union I think it has a restriction to have the same no of column in you union output.....(Not sure on this though)

Well you can write something like this for your code

if ($_POST['searchfield']) { 

$sql  = "SELECT * FROM table1, table2, table3 WHERE 1 = 1";  

$sql .= empty($_POST['searchfield']) ? NULL : " AND table1.val_1 LIKE '%".$_POST['searchfield']."%'"; 
$sql .= empty($_POST['searchfield']) ? NULL : " AND table2.val_2 LIKE '%".$_POST['searchfield']."%'"; 
$sql .= empty($_POST['searchfield']) ? NULL : " AND table3.val_3 LIKE '%".$_POST['searchfield']."%'";  

//If you have any Forgein key relation do join with it 
//  and  table3.id=table2.foreign_id and table3.id=table1.forgein_id
//Second thing is that If possible pick only the required data
if($search = $db->get_results($sql)) { 
        foreach ( $search as $s ) 
        { 
            ...OUTPUT... 
        } 
}

But this query will take lot of time to execute in compare to that you do the search on three query and and than copythe result of them in one array for the final output before your foreach...

Hope That Help