Creating a search form that uses multiple checkboxes

[yugonomex]

Hey,

new to webmaster 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);
}
}
?>

[phranque]

welcome to WebmasterWorld, yugonomex!

you either have to use an OR in your WHERE clause and do the sort in PHP,
or make 3 queries in order, using an AND clause in your WHERE clause for the first query.

[swa66]

You can also use a union to let mysql combine the 3 selects into one result set.

Ref: [dev.mysql.com...]

[phranque]

i'm not sure how you could ORDER BY (both and then one and then the other) as specified using a UNION.

[swa66]

In pseudo code:

( select * from whatever where "Narberth" and "Pembrokeshire" order by whatever limit howmany)
union all
( select * from whatever where "narberth" and not "Pembrokeshire" order by whatever limit howmany)
union all
( select * from whatever where "pembrokshire" and not "narberth" order by whatever limit howmany)

AFAIK the order the results come in are the same as the order of the union - although I admit this does not seem to be documented as such.
You can order and limit the individual results and the complete set by using parentheses.

Note that I'm myself avoiding duplicates so union all can be used and that might be the trick to keep the order. (Not tested it)

[phranque]

i had never assumed the order would be maintained nor had i tested it.

let us know how it works, yugonomex.

[swa66]

Regardless you could always include an additional column you add the select it came from and then sort on that, SQL is a fully featured language. Most you can imagine to do can be done in one (complex) query.

Actually the docs state the approach by adding a column to indicate what select the row comes from

To cause rows in a UNION result to consist of the sets of rows retrieved by each SELECT one after the other, select an additional column in each SELECT to use as a sort column and add an ORDER BY following the last SELECT:

(SELECT 1 AS sort_col, col1a, col1b, ... FROM t1)
UNION
(SELECT 2, col2a, col2b, ... FROM t2) ORDER BY sort_col;

Quoted from [dev.mysql.com...]

[phranque]

good one - i often forget that trick!

[vincevincevince]

You should be able to include a sorting column something like:

select .... rest of the query here, for both keywords ... ORDER BY (**kw-query1**)+(**kw-query2**) DESC

Or; use MATCH AGAINST as that automatically does this kind of scoring for you.

[yugonomex]

select t.*
 from 
  tbl_index t inner join  
  ( 
  select 
   r.file_id, 
   count(*) as rank
  from
   tbl_filekeywords r
  where
   r.keyword_id in ( $keywords )
  group by 
   r.file_id      
  ) r2
   on r2.file_id = t.file_id
 order by
  r2.rank

Hey guys thanks again for the info provided, the solution in the end is shown above. I used ranks to place the most appropriate rows at the top of the table. All the help was really appreciated and you'll most likely be hearing from me again soon haha :)

Ad