Welcome to WebmasterWorld Guest from 54.158.175.78

Forum Moderators: coopster & jatar k

Message Too Old, No Replies

Creating a search form that uses multiple checkboxes

   
1:07 pm on Nov 20, 2012 (gmt 0)




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);
}
}
?>
2:36 pm on Nov 20, 2012 (gmt 0)

WebmasterWorld Administrator phranque is a WebmasterWorld Top Contributor of All Time 10+ Year Member Top Contributors Of The Month



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.
5:26 pm on Nov 20, 2012 (gmt 0)

WebmasterWorld Senior Member swa66 is a WebmasterWorld Top Contributor of All Time 10+ Year Member



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

Ref: [dev.mysql.com...]
5:31 pm on Nov 20, 2012 (gmt 0)

WebmasterWorld Administrator phranque is a WebmasterWorld Top Contributor of All Time 10+ Year Member Top Contributors Of The Month



i'm not sure how you could ORDER BY (both and then one and then the other) as specified using a UNION.
5:42 pm on Nov 20, 2012 (gmt 0)

WebmasterWorld Senior Member swa66 is a WebmasterWorld Top Contributor of All Time 10+ Year Member



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)
5:50 pm on Nov 20, 2012 (gmt 0)

WebmasterWorld Administrator phranque is a WebmasterWorld Top Contributor of All Time 10+ Year Member Top Contributors Of The Month



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

let us know how it works, yugonomex.
6:08 pm on Nov 20, 2012 (gmt 0)

WebmasterWorld Senior Member swa66 is a WebmasterWorld Top Contributor of All Time 10+ Year Member



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...]
11:51 pm on Nov 20, 2012 (gmt 0)

WebmasterWorld Administrator phranque is a WebmasterWorld Top Contributor of All Time 10+ Year Member Top Contributors Of The Month



good one - i often forget that trick!
2:47 am on Nov 21, 2012 (gmt 0)

WebmasterWorld Senior Member vincevincevince is a WebmasterWorld Top Contributor of All Time 10+ Year Member



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.
2:03 pm on Nov 22, 2012 (gmt 0)




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