php checkboxes matching mysql set ...

DeepTii

hi all~ i have searched everywhere for a problem that i am trying to solve and the best i could get was from mysql manual's example, which i couldnt get it to work in my situation.

so i hope that some experts here can give me some light... the problem is this, i have a page that has checkboxes of interests, and i am trying to search against a mysql set datatype.

on my page there is a list of checkboxes that can be checked multiply, and the data is passed via POST as array.

i have no problem manipulating around with this array with implode or explode...

but how do i match it against mysql's set, and display a list of data ranging from best matched interests and least matched interests with sql query?

mysql's manual suggested using bit... but in its example it's comparing a real row data with other rows, whileas the data i have is "on the fly"...

if i insert a temp row into the db and use it to match against others i will have to lock the table (so other people cant change it) and delete the temp row after the query... which is rather troublesome...

is there other ways around it? maybe doing it all within php without the help of sql?

please help~ thanks~ :queasy:

paulnaj

Hi,

Welcome to the forum.

I think you're going to have to show us your database structure ( the CREATE statements if possible).

Paul 🙂

DeepTii

hi~ here is the fill-in~ a short version of the database that i am building...

CREATE TABLE profile (
id mediumint(9) NOT NULL auto_increment,
nickname varchar(24) NOT NULL default '',
language set('English','Japanese','Spanish','French´,'Chinese') default NULL,
PRIMARY KEY (id),
UNIQUE KEY id (id),
) ENGINE=InnoDB DEFAULT CHARSET=gb2312

so if i have the above table... and a search page with checkboxes of different languages... which will search thru the database and find the best matches the user selected... how do i do that? :queasy:

paulnaj

Hi dt,

Well, I had a bit of a think of how this could be done just with the SQL and this could be it. See if you can work out what's going on but come back if you need a bit more explanation ...

<html>
<head>
<title>Test SET</title>
</head>
<body>
<!-- Suppose this was your form ... the value
of each checkbox corresponds to the order of entry
in the field definition and NOT the order they are displayed.-->
<form action="<?php echo $_SERVER['PHP_SELF']; ?>" name="frm" method="get">
	<input type="checkbox" name="language[]" value="0">English
<br><input type="checkbox" name="language[]" value="2">Spanish
<br><input type="checkbox" name="language[]" value="3">French
<br><input type="checkbox" name="language[]" value="1">Japanese
<br><input type="checkbox" name="language[]" value="4">Chinese
<br><input type="submit" name="submit" value="submit">
</form>
<?php 
if(isset($_REQUEST['language'])){
	// Your checkboxes come as an array in the 
	// $_REQUEST (or $_POST, $_GET) called 'language' say ...
	 $languages = $_REQUEST['language'];

// Have a look at what the $languages array looks like ...
echo '<hr><strong>languages</strong>: <pre>';	print_r($languages);	echo '</pre>';

// We convert the 'sent' variables to be used in the sql
// using the & to 'pick out the power of 2'
// The ' > 0' at the end just converts the value to a 
// boolean (1 or 0) that can be added together to 
// count the number of matches *and* be used in
// the WHERE clause to pick out matches.
for($i = 0; $i < count($languages); $i++){
	$languages[$i] = '(language&'.pow(2, $languages[$i]).' > 0)';
}

$sql = "
SELECT id, language, ".join(' + ', $languages)." AS matches
FROM profile 
WHERE ".join(' || ', $languages)."
ORDER BY matches DESC
"; 
	// Have a look at what the SQL looks like ...
	echo '<hr><strong>sql</strong>:<pre>'.$sql.'</pre>';	

// ** CONNECT TO YOUR DATABASE 
// ** CHANGE THE DETAILS
mysql_connect('host', 'user', 'pass'); 
mysql_select_db('dbname'); 

// Get and display the results
$result = mysql_query($sql); 
echo '<hr><strong>data</strong>:';	 
?>  

	<table border="1" cellpadding="2" cellspacing="0"> 
		<tr><th>id</th><th>language</th><th>matches</th></tr>
<?php  

	while($row = mysql_fetch_row($result)){  

    	echo '<tr><td>'.join('</td><td>', $row).'</td></tr>';  

	}  

?>   

	</table> 
<?php  

} 
?>
</body>
</html>

Paul 🙂

paulnaj

Did that work? Or what?