biff423 Posted July 25, 2011 Share Posted July 25, 2011 I am trying to query using the results of a search form. One of the fields I have is "state". I would like the user to be able to select multiple states and then get a list of results reflecting all the states selected. This is the code I have used for a single select form element for state. It reflects selecting all records if no selection is made for state, as well as sex, sale and salename: $query = "SELECT *, DATE_FORMAT(saledate,'%m/%d/%Y') as saledatex FROM sdusa WHERE"; if (!empty($state)){ $query .= " state = '$state' AND";} if (!empty($sex)){ $query .= " sex = '$sex' AND";} if (!empty($sale)){ $query .= " breeder = '$sale' AND";} if (!empty($salename)){ $query .= " salename LIKE '%$salename%' AND";} $query .= " saledate >= $salestart AND saledate <= $saleend AND birthdate >= $birthstart AND birthdate <= $birthend AND actbw <= $actbw1 AND adj205ww >= $adj205ww1 AND adj365yw >= $adj365yw1 AND rea >= $rea1AND imf >= $imf1 AND sc >= $sc1$result = mysql_query($query) or die('Error, query failed'); How would I adjust the query to get results from a multiple select list for state that would give me a list of results that reflects all states selected? I think I need to add a "foreach" statement possibly or a counter - but since I am newer to php am not real sure of the code. Current output looks like this: <table align='center' width='780' border='1' cellpadding='0' cellspacing='0' bordercolor='#000000'> <tr><td width='55' class='s11'><div align='center'>Reg #</div></td> <td width='100' class='s11'><div align='center'>Name</div></td> <td width='135' class='s11'><div align='center'>Sire</div></td> <td width='125' class='s11'><div align='center'>MGS</div></td> <td width='180' class='s11'><div align='center'>Breeder</div></td> <td width='25' class='s11'><div align='center'>St</div></td> <td width='60' class='s11'><div align='center'>Sale Date</div></td> <td width='25' class='s11'><div align='center'>BW</div></td> <td width='25' class='s11'><div align='center'>WW</div></td> <td width='25' class='s11'><div align='center'>YW</div></td> <td width='25' class='s11'><div align='center'>M</div></td> </tr> <?php while($row = mysql_fetch_array($result)){ echo "<tr height='20'>"; echo "<td class='s11'><div align='center'><a href='sdindselect.php?registration_num=". $row ['registration_num'] ."'>" . $row['registration_num'] . "</a> </div></td>"; echo "<td class='s11'><div align='center'>" . $row['name'] . " </div></td>"; echo "<td class='s11'><div align='center'>" . $row['sire'] . " </div></td>"; echo "<td class='s11'><div align='center'>" . $row['sire3'] . " </div></td>"; echo "<td class='s11'><div align='center'>" . $row['breeder'] . $row['salname'] ." </div></td>"; echo "<td class='s11'><div align='center'>" . $row['salestate'] . " </div></td>"; echo "<td class='s11'><div align='center'>" . $row['saledatex'] . " </div></td>"; echo "<td class='s11'><div align='center'>" . $row['bw'] . " </div></td>"; echo "<td class='s11'><div align='center'>" . $row['ww'] . " </div></td>"; echo "<td class='s11'><div align='center'>" . $row['yw'] . " </div></td>"; echo "<td class='s11'><div align='center'>" . $row['milk'] . " </div></td>"; echo "</tr>";}?> Link to comment https://forums.phpfreaks.com/topic/242771-form-multiple-select-use-in-a-mysql-query/ Share on other sites More sharing options...
AbraCadaver Posted July 25, 2011 Share Posted July 25, 2011 Something like this, using the IN clause in the query: http://www.phpfreaks.com/forums/index.php?topic=339076.msg1598271#msg1598271 Link to comment https://forums.phpfreaks.com/topic/242771-form-multiple-select-use-in-a-mysql-query/#findComment-1246923 Share on other sites More sharing options...
biff423 Posted July 25, 2011 Author Share Posted July 25, 2011 That worked fantastically as long as i am selecting states. Hoever if i don't make a selection - I want it to select ALL states. Used to acomplish this by having: <option value="" selected="selected">All States</option> Then in the query: if (!empty($state)){ $query .= " salestate = '$state' AND"; } Any ideas? Link to comment https://forums.phpfreaks.com/topic/242771-form-multiple-select-use-in-a-mysql-query/#findComment-1246944 Share on other sites More sharing options...
AbraCadaver Posted July 25, 2011 Share Posted July 25, 2011 Following with your previous code, this would do it wouldn't it? if (!empty($_POST['state'])) { $state_list = $makes = "'" . implode("','", array_map('mysql_real_escape_string', $_POST['state'])) . "'"; $query .= " state IN($state_list) AND"; } Link to comment https://forums.phpfreaks.com/topic/242771-form-multiple-select-use-in-a-mysql-query/#findComment-1246964 Share on other sites More sharing options...
biff423 Posted July 25, 2011 Author Share Posted July 25, 2011 First question - do I need to declare the variable at the top: $state=$_POST['state']; Second question - here is the code I used per your suggestion: if (!empty($_POST['state'])) { $state_list = $makes = "'" . implode("','", array_map('mysql_real_escape_string', $_POST['state'])) . "'"; $query .= " salestate IN($state_list) AND";} It give me a correct list when I select multiple states. But if I select this: <option value="">All States</option> ...I get no results. Thoughts? Link to comment https://forums.phpfreaks.com/topic/242771-form-multiple-select-use-in-a-mysql-query/#findComment-1246983 Share on other sites More sharing options...
AbraCadaver Posted July 25, 2011 Share Posted July 25, 2011 Probably because if you don't supply a value it uses the text as the value. Try setting the value to 0 (which will be considered empty). Or you could do this: if (!empty($_POST['state']) && $_POST['state'] != 'All States' ) { $state_list = "'" . implode("','", array_map('mysql_real_escape_string', $_POST['state'])) . "'"; $query .= " state IN($state_list) AND"; } I would just set the All States value to 0. Link to comment https://forums.phpfreaks.com/topic/242771-form-multiple-select-use-in-a-mysql-query/#findComment-1246986 Share on other sites More sharing options...
biff423 Posted July 25, 2011 Author Share Posted July 25, 2011 Thank you for your patience with me!! I changed value of All States to 0 - no results - no rows returned. I used your suggested code - no results- no rows returned. You are so close - just need to be able to be able to select all records if no state is specified. Link to comment https://forums.phpfreaks.com/topic/242771-form-multiple-select-use-in-a-mysql-query/#findComment-1246989 Share on other sites More sharing options...
AbraCadaver Posted July 25, 2011 Share Posted July 25, 2011 echo out your query and post it. Link to comment https://forums.phpfreaks.com/topic/242771-form-multiple-select-use-in-a-mysql-query/#findComment-1246993 Share on other sites More sharing options...
Psycho Posted July 25, 2011 Share Posted July 25, 2011 The problem is not whether you use an empty string or a "0".. The problem is that you are not checking that value correctly If using an empty string for "All States" (my preference) if ( isset($_POST['state']) && !empty($_POST['state']) ) { $state_list = "'" . implode("','", array_map('mysql_real_escape_string', $_POST['state'])) . "'"; $query .= " state IN($state_list) AND"; } If using "0" for "All States" if ( isset($_POST['state']) && $_POST['state']!='0' ) { $state_list = "'" . implode("','", array_map('mysql_real_escape_string', $_POST['state'])) . "'"; $query .= " state IN($state_list) AND"; } Link to comment https://forums.phpfreaks.com/topic/242771-form-multiple-select-use-in-a-mysql-query/#findComment-1246995 Share on other sites More sharing options...
biff423 Posted July 25, 2011 Author Share Posted July 25, 2011 SELECT *, DATE_FORMAT(saledate,'%m/%d/%Y') as saledatex FROM sdusa WHERE salestate IN('0') AND saledate >= 20110101 AND saledate <= 20130101 AND birthdate >= 20000101 AND birthdate <= 20130101 AND actbw <= 9999 AND adj205ww >= 0 AND adj365yw >= 0 AND rea >= -9999 AND imf >= -9999 AND sc >= -9999 AND ced <= 9999 AND bw <= 9999 AND ww >= -9999 AND yw >= -9999 AND radg >= -9999 AND yh <= 9999 AND scepd >= -9999 AND doc >= -9999 AND hp >= -9999 AND cem >= -9999 AND milk >= -9999 AND mkh >= -9999 AND mw >= -9999 AND mh >= -9999 AND en >= -9999 AND cw >= -9999 AND marb >= -9999 AND re >= -9999 AND fat >= -9999 AND carcgrp >= -9999 AND usndgrp >= -9999 AND w >= -9999 AND f >= -9999 AND g >= -9999 AND qg >= -9999 AND yg >= -9999 AND b >= -9999 ORDER BY registration_num ASC Link to comment https://forums.phpfreaks.com/topic/242771-form-multiple-select-use-in-a-mysql-query/#findComment-1247004 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.