Tell me more ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 1 down vote favorite

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 >= $rea1
AND 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>";
}
?>

Thanks for the help!

share|improve this question
2  
on a side note why don't you put your center in the s11 class, just think of all the bandwidth your save... – Lawrence Cherone Jul 25 '11 at 17:51

2 Answers

up vote 0 down vote

UPDATED! A multiple select list will pass its values as an array. What you'll need to do is something like this:

if (!empty($state))
{
  $query .= "("; // Encapsulate state values so they'll be resolve independently

  foreach($states as $term)
  {
    if($term !== '')
    {
      $query .= " state = '".trim($term)."' OR";
    }
  }
  $query .= ") AND";
}
share|improve this answer
1  
yes, a precise excample would be helpful – Marci Smith Jul 25 '11 at 17:52
Stack 101 has the right idea above, but I would suggest using OR instead of AND because no rows with a single state assigned will be in multiple states. – Tanoro Jul 25 '11 at 18:07
No problem. That's also why I suggested encapsulating this part of the query in parentheses. When you start mixing AND statements with OR statements, the results are often hard to predict unless you separate them this way. – Tanoro Jul 25 '11 at 18:17
You'll have a trailing OR at the end... Which will fire a mysql error – Stack 101 Jul 25 '11 at 18:22
Getting a query failed message - using code'$query = "SELECT *, DATE_FORMAT(saledate,'%m/%d/%Y') as saledatex FROM sdusa WHERE"; $states = array(); $states = explode(" ",$state); if (!empty($state)){ $query .= "("; // Encapsulate state values so they'll be resolve independently foreach($states as $term) { if($term !== '') { $query .= " salestate = '".trim($term)."' OR"; } } $query .= ") AND";} code` – Marci Smith Jul 25 '11 at 18:28
up vote 0 down vote

This should do it for you :

if (!empty($state)){

$states = array();

Depending on how each $state value is sepertated in you query string

Exemple : $state = "New Jersey,Wyoming,Oregon,Georgia,Maine,Arkansas"

$states  = explode(",",$state); 
$first = true;

foreach($states as $term){

    if($term !== ''){

        if(!$first) $query .= " OR";

        $query .= " state = '".trim($term)."'";
        $first = false;

    }
}

$query .= " AND";

}

Hope it helps.

share|improve this answer
Query is running, but no results - thank you for all you ideas - keep em coming! – Marci Smith Jul 25 '11 at 18:50
What does your $state string look like, when you hav multiple values? – Stack 101 Jul 25 '11 at 19:13
ack - i don't know - when I put: echo $state: it just says Array. Sorry I am such a newbie. Thanks for your patience – Marci Smith Jul 25 '11 at 19:57
What does printf($state); give you? – Stack 101 Jul 25 '11 at 20:00
It says Array. Sould I be declaring the $state variable - ie $state=$_POST['state']; – Marci Smith Jul 25 '11 at 20:36
show 5 more comments

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.