homepage Welcome to WebmasterWorld Guest from 54.167.173.250
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member

Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
Forum Library, Charter, Moderators: coopster & jatar k

PHP Server Side Scripting Forum

    
Creating a search form that uses multiple checkboxes
yugonomex




msg:4520724
 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);
}
}
?>

 

phranque




msg:4520759
 2:36 pm on Nov 20, 2012 (gmt 0)

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




msg:4520813
 5:26 pm on Nov 20, 2012 (gmt 0)

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

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

phranque




msg:4520815
 5:31 pm on Nov 20, 2012 (gmt 0)

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

swa66




msg:4520817
 5:42 pm on Nov 20, 2012 (gmt 0)

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




msg:4520818
 5:50 pm on Nov 20, 2012 (gmt 0)

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

let us know how it works, yugonomex.

swa66




msg:4520823
 6:08 pm on Nov 20, 2012 (gmt 0)

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




msg:4520918
 11:51 pm on Nov 20, 2012 (gmt 0)

good one - i often forget that trick!

vincevincevince




msg:4520949
 2:47 am on Nov 21, 2012 (gmt 0)

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




msg:4521586
 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

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved