php - Filter mysql results based on multiselect box -
i trying filter mysql table based upon values in multiselect box returns results who's values have been selected. instance multiple select box:
<select name="gift[]" id="gift" multiple="multiple" onchange="change()"> <option value="him">him</option> <option value="her">her</option> <option value="kids">kids</option> <option value="teens">teens</option> <option value="mothersday">mothers day</option> <option value="fathersday">fathers day</option> <option value="valentines">valentines day</option> <option value="gadgets">gadgets</option> <option value="secretsanta">secret santa</option> </select>
if user selects "him" , "gadgets" should return results mysql table either "him" or "gadgets" in it.
the mysql table has data each product stored in single column string (i.e. under column 'gift' might him,her,kids,gadgets or row might her,gadgets. example above want return both rows).
the results returned ajax , have tried use in clause unable working.here code (html select box above):
ajax note: not full code simplicity
function change(){ var giftarray = new array(); $.each($("#gift option:selected"), function(){ giftarray.push($(this).val()); }); var gift = "'" + giftarray.join("','") + "'"; if (window.xmlhttprequest) {// code ie7+, firefox, chrome, opera, safari xmlhttp=new xmlhttprequest(); } else {// code ie6, ie5 xmlhttp=new activexobject("microsoft.xmlhttp"); } xmlhttp.onreadystatechange=function() { if (xmlhttp.readystate==4 && xmlhttp.status==200) { document.getelementbyid("product").innerhtml=xmlhttp.responsetext; } } var url="results.php" url=url+"&gift="+gift; xmlhttp.open("get",url,true); xmlhttp.send(); }
results.php note: not full code simplicity
$gift=$_get['gift']; $gift=str_replace("\\","",$gift); $sql= "select * $productstable gift in ($gift)"; $result = mysql_query($sql) or die ('unable run query:'.mysql_error()); while($item = mysql_fetch_array($result)) { code view products here } <? } mysql_close() ?>
mysql table , data note: i've shown gift column, id , data 2 rows (hope ok)
create table `products` ( `id` int(255) not null auto_increment, `gift` varchar(100) not null, primary key (`id`) ) engine=myisam default charset=latin1 auto_increment=6 ; insert `products` values(1,'him,her'); insert `products` values(2,'him,her,teens,gadgets');
this seems work if product has 1 value assigned in mysql table doesn't when has multiple. i've read think may going wrong storing multiple criteria in single column not sure how fix it. many thanks.
you doing wrong way.
first, read database normalization. must have 3 tables.
type { id, title } storing him,her,teens,gadgets rows unique id product { id, product_title, ...... } storing products product_type {product_id, type_id} store relation product -> type
you must send options data array:
index.php?options[1]=him&options[2]=her&options[3]=kids
and in php as:
$options = $_get['options'];
then can build query using join:
$q = "select a.id, a.product_title product join product_type b on a.a=b.product_id b.type_id in('".implode("','",$options )."')";
p.s. example. insecure. must need prepared statements security, validate input data, create right indexes. can store "type" in array ids, prefer storing in database.
your example wrong side of relation database design. furthermore, using inefficient.
Comments
Post a Comment