Search Form Displaying Results Based On Multiple Checkboxes

[AdamCalvert123]

Hey,

 

new to PHPFreaks so i hope someone can help with my problem

 

Firstly what i'm trying to create... I want to have a php page that display multiple keywords (checkboxes) for example areas such as Narberth, Pembrokeshire, Carmarthenshire etc as well as other things.

 

So far i have created a way to do this and display the data separately in tables. What I'm having trouble with is showing the rows with multiple keywords associated with them first.

 

So if the user searched "Narberth" and "Pembrokeshire" the results would show any documents with both those keywords in first then anything with just "narberth" and just "pembrokshire" afterwords.

 

Hope this makes sense haha I listed my code for what I've worked out so far, hope to hear from you soon!

 

Ad

 

<form method="post">
<table border="1px solid" width="60%">
<tr>
<td><input type="checkbox" name="keywords[]" value="1">Narberth </td>
<td><input type="checkbox" name="keywords[]" value="2">James Brothers </td>
<td><input type="checkbox" name="keywords[]" value="3">Mabinogion </td>
</tr>
<tr>
<td><input type="checkbox" name="keywords[]" value="4">Pembrokeshire </td>
<td><input type="checkbox" name="keywords[]" value="5">Pubs </td>
<td><input type="checkbox" name="keywords[]" value="6">Coastal Paths </td>
</tr>
</table>
<br>
<input type="submit" name = "submit">
</form>

 

<?php
if (isset($_POST['submit'])) {

foreach( $_REQUEST['keywords'] as $keywordID )
{
$result = mysql_query("SELECT * FROM tbl_index m
inner join tbl_filekeywords r on m.file_id = r.file_id
where r.keyword_id = '".$keywordID."'");

$keywordname = mysql_query ("SELECT keyword FROM tbl_keywords WHERE keyword_id = '".$keywordID."'");
$KeywordName = mysql_fetch_row($keywordname);
$KeywordName = $KeywordName[0];

echo "<table border='1' width='50%'>
<th colspan='2'>".$KeywordName."</th>
<tr>
<th>File ID</th>
<th>Filename</th>
</tr>";

while($row = mysql_fetch_array($result)){
echo "<tr>";
echo "<td>" . $row['file_id'] . "</td>";
echo "<td>" . $row['file'] . "</td>";
echo "</tr>";
}
echo "</table><br>";
mysql_free_result($result);
}
}
?>

 

P.S There is another alternative that i developed that lumps all the files into one table which is fine but i still want to stop the same files appearing multiple times for each keyword and would rather the ones with more than one keyword to be prioritized and displayed on top.

 

<?php

if(isset($_POST['keywords']) && !empty($_POST['keywords'])){
foreach($_POST['keywords'] as $key=>$value){
if($value==1) $keywords[] = "keyword_id='".mysql_escape_string($key)."'";
}
$keywords = implode(' OR ', $keywords);
}
$query = "SELECT * FROM tbl_index m inner join tbl_filekeywords r on m.file_id = r.file_id WHERE $keywords";
$result = mysql_query($query);


echo "<table border='1' width='50%'>
<tr>
<th>File ID</th>
<th>Filename</th>
<th>Keyword_ID</th>
</tr>";


while($row = mysql_fetch_array($result)){
echo "<tr>";
echo "<td>" . $row['file_id'] . "</td>";
echo "<td>" . $row['file'] . "</td>";
echo "<td>" . $row['keyword_id'] . "</td>";
echo "</tr>";
}
echo "</table><br>";
mysql_free_result($result);


?>

 

And finally the tables are set up like:

 

tbl_index :- file_id, file

tbl_keywords :- keyword_id, keyword

tbl_filekeywords :- file_id, keyword_id

 

Thanks again!