Search multiple tables

Tezread

I understand from other threads that FULL TEXT search is not possible when using multiple tables. I am trying to develop a search function that enables a keyword to be entered into a textbox- the user hits enter and a list of sections with relevant titles and URL's is displayed.

My tables are as follows

qualityadmin

name
address1
address2
question1
question2
search_section
search_title
search_url

faq

question
explanation
search_section
search_title
search_url

jargon

term
explanation
search_section
search_title
search_url

As you can see the table design has a common pattern in that search_section, search_title and search_url are always in them.

Here is my code so far:

//this is my search page

<form method="post" action="search_results.php" target="_blank">
<div align="center">
<table border="0" cellpadding="0" cellspacing="0">
<tr>
<td bordercolor="#000000"><div>
<p align="center">
 <input type="text" name="searchit" size="25"> &nbsp;<br>

Search database: <input type="submit" value="Go!!" name="Go"></p>
</form

//this is the results/handler page
$searchit = $_REQUEST['searchit'];


$sql = mysql_query("SELECT search_section, search_title, search_url FROM qualityadmin, faq, jargon WHERE $searchitLIKE '%$search%')"


$result = mysql_query($res) or die(mysql_error()); 
if (mysql_num_rows($result) > 0) { 
while ($row = mysql_fetch_assoc($result)) { 

// begin output here: 
echo '<style type="text/css"> 
#myTable td { 
    font-family:arial, helvetica, sans-serif; 
    font-size: 14px; 
    } 
.heading { 
    background:#eee; 
    color: #0000cc; 
    } 
</style> 
<table cellpadding="2" cellspacing="0" border="1" id="myTable" width="700px"> 
<tr> 
<td class="heading" colspan="3">Result</td> 
</tr> 
<tr> 
<td colspan="3">' . $row[search_url] .'</td> 
</tr> 

</table>'; 

} 

} else { 
//course name not found 
//note: the query may have failed to so maybe check for that. 
} 
} else { 
//no course specified 
} 
?>

So how do I select and search the common fields search_section, search_title, search_url in each table ?

bpat1434

SELECT qa.search_section, qa.search_title, qa.search_url,
f.search_section, f.search_title, f.search_url,
j.search_section, j.search_title, j.search_url
FROM qualityadmin AS qa, faq AS f, j AS j
WHERE (
  (qa.search_title LIKE '%$search%')
  OR (f.search_title LIKE '%$search%')
  OR (j.search_title LIKE '%$search%')
)

Something like that perhaps....

But it's useless if you don't specify an input field for the $search variable 😉

Tezread

Does the suggestion you make look for all instances in the tables of the keyword typed in the input field?. i.e if the word 'quality' is typed will this solution look for all instances of quality in the entire database and output the relevant search_title field?
Also I have tried implementing the code and come up with a unexpected t_variable error:

	<?php
	include("connect.php");
	$search = $_POST['searchit'];


$sql = mysql_query("SELECT qualityadmin.search_section, qualityadmin.search_title, qualityadmin.search_url,
faq.search_section, faq.search_title, faq.search_url,
jargon.search_section, jargon.search_title, jargon.search_url
FROM qualityadmin AS qualityadmin, faq AS faq, jargon AS jargon
WHERE (
  (qualityadmin.search_title LIKE '%$search%')
  OR (faq.search_title LIKE '%$search%')
  OR (jargon.search_title LIKE '%$search%')"

$result = mysql_query($sql) or die(mysql_error()); 
if (mysql_num_rows($result) > 0) { 
while ($row = mysql_fetch_assoc($result)) { 

// begin output here: 
echo '<style type="text/css"> 
#myTable td { 
    font-family:arial, helvetica, sans-serif; 
    font-size: 14px; 
    } 
.heading { 
    background:#eee; 
    color: #0000cc; 
    } 
</style> 
<table cellpadding="2" cellspacing="0" border="1" id="myTable" width="700px"> 
<tr> 
<td class="heading" colspan="3">Result</td> 
</tr> 
<tr> 
<td colspan="3">' . $row[search_title] .'</td> 
</tr> 
<tr> 
<td colspan="3">' . $row[search_section] .'</td> 
</tr> 
<tr> 
<td colspan="3">' . $row[search_url] .'</td> 
</tr> 

</table>'; 

} 

} else { 
//course name not found 
//note: the query may have failed to so maybe check for that. 
} 
} else { 
//no course specified 
} 
?>

bpat1434

Also I have tried implementing the code and come up with a unexpected t_variable error:

Forgot to terminate your query line.....

Does the suggestion you make look for all instances in the tables of the keyword typed in the input field?. i.e if the word 'quality' is typed will this solution look for all instances of quality in the entire database and output the relevant search_title field?

It will search the following tables: qualityadmin, faq, jargon
It will return the following fields: "qualityadmin.search_section, qualityadmin.search_title, qualityadmin.search_url,
faq.search_section, faq.search_title, faq.search_url,
jargon.search_section, jargon.search_title, jargon.search_url"
It will match in the following fields: "(qualityadmin.search_title LIKE '%$search%')
OR (faq.search_title LIKE '%$search%')
OR (jargon.search_title LIKE '%$search%')"

Tezread

I did terminate my query line and removed brackets around the OR statements. Now when you run the qury the search keeps executing but no result occurs- it is stuck in a loop- have a look:

www.mindseyemidlands.co.uk/notts_quality/info_resource/search.php

here is my code:


<?php 
    include("connect.php"); 
    $search = $_POST['searchit']; 


$sql = "SELECT qualityadmin.search_section, qualityadmin.search_title, qualityadmin.search_url, 
faq.search_section, faq.search_title, faq.search_url, 
jargon.search_section, jargon.search_title, jargon.search_url 
FROM qualityadmin AS qualityadmin, faq AS faq, jargon AS jargon 
WHERE  qualityadmin.search_title LIKE '%$search%' 
  OR faq.search_title LIKE '%$search%'
  OR jargon.search_title LIKE '%$search%'";

$result = mysql_query($sql) or die(mysql_error()); 
if (mysql_num_rows($result) > 0) { 
while ($row = mysql_fetch_assoc($result)) { 

// begin output here: 
echo '<style type="text/css"> 
#myTable td { 
    font-family:arial, helvetica, sans-serif; 
    font-size: 14px; 
    } 
.heading { 
    background:#eee; 
    color: #0000cc; 
    } 
</style> 
<table cellpadding="2" cellspacing="0" border="1" id="myTable" width="700px"> 
<tr> 
<td class="heading" colspan="3">Result</td> 
</tr> 
<tr> 
<td colspan="3">' . $row[search_title] .'</td> 
</tr> 
<tr> 
<td colspan="3">' . $row[search_section] .'</td> 
</tr> 
<tr> 
<td colspan="3">' . $row[search_url] .'</td> 
</tr> 

</table>'; 

} 

} 

?>

on another note I have done some research on FULL TEXT searching and have reaffirmed I can most certainly not search multiple tables using that approach. I have however found a way of searching just the qualityadmin table- im posting this in case someone else finds it useful in future:

<?php
include("connect.php");
$search = $_POST['search'];

$sql= "SELECT * FROM qualityadmin WHERE MATCH (quality_name, local_agency, contact, what_is_it, who_what_apply, how_work, resource_implications, how_meet_standards, widespread, pros_cons, benefits) AGAINST ('" . stripslashes (str_replace ("&quot;", "\"", ($_POST['search']))) . "' IN BOOLEAN MODE)";

$result = mysql_query($sql, $connection) or die(mysql_error()); 
if (mysql_num_rows($result) > 0) { 
while ($row = mysql_fetch_assoc($result)) { 

// begin output here: 
echo '<style type="text/css"> 
#myTable td { 
    font-family:arial, helvetica, sans-serif; 
    font-size: 14px; 
    } 
.heading { 
    background:#eee; 
    color: #0000cc; 
    } 
</style> 
<table cellpadding="2" cellspacing="0" border="1" id="myTable" width="700px"> 
<tr> 
<td class="heading" colspan="3">Result</td> 
</tr> 
<tr> 
<td colspan="3">' . $row['search_title'] .'</td> 
</tr> 
<tr> 
<td colspan="3">' . $row['search_section'] .'</td> 
</tr> 
<tr> 
<td colspan="3">' . $row['search_url'] .'</td> 
</tr> 

</table>'; 

} 
}


?>

as it stands though im still in a position where I cannot search multiple tables

bpat1434

Notice: Undefined index: searchit in