Databases
StackOverflow2013
Postsdbo
POHow to query a table based on up to 17 different variables?
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POHow to query a table based on up to 17 different variables?
    text
    copied!<p>I have a MySQL table which I wish to search via a POSTed form. The trouble is there are some 17 variables that an end user may wish to query, either singly or in combination. This results in too many possibilities for else-if statements. After asking here, I've constructed the following query:</p> <pre><code>$query = "SELECT * FROM profiles"; $postParameters = array("name","height","gender","class","death","appro","born","tobiano","modifier","adult","birth","sire","dam","breeder","owner","breed","location"); $whereClause = " WHERE 1 = 1"; foreach ($postParameters as $param) { if (isset($_POST[$param]) &amp;&amp; !empty($_POST[$param])) { switch ($param) { case "name": $whereClause .= " AND ProfileName='".$_POST[$param]."' "; break; case "height": $whereClause .= " AND ProfileHeight='".$_POST[$param]."' "; break; case "gender": $whereClause .= " AND ProfileGenderID='".$_POST[$param]."' "; break; case "class": $whereClause .= " AND ProfileBreedClassID='".$_POST[$param]."' "; break; case "death": $whereClause .= " AND ProfileYearOfDeath='".$_POST[$param]."' "; break; case "appro": $whereClause .= " AND ProfileYearApproved='".$_POST[$param]."' "; break; case "born": $whereClause .= " AND ProfileYearOfBirth='".$_POST[$param]."' "; break; case "tobiano": $whereClause .= " AND ProfileTobianoTest='".$_POST[$param]."' "; break; case "modifier": $whereClause .= " AND ProfileColourModifier='".$_POST[$param]."' "; break; case "adult": $whereClause .= " AND ProfileAdultColourID='".$_POST[$param]."' "; break; case "birth": $whereClause .= " AND ProfileBirthColourID='".$_POST[$param]."' "; break; case "sire": $whereClause .= " AND ProfileSireReg='".$_POST[$param]."' "; break; case "dam": $whereClause .= " AND ProfileDamReg='".$_POST[$param]."' "; break; case "breeder": $whereClause .= " AND ProfileBreederID='".$_POST[$param]."' "; break; case "owner": $whereClause .= " AND ProfileOwnerID='".$_POST[$param]."' "; break; case "breed": $whereClause .= " AND ProfileBreedID='".$_POST[$param]."' "; break; case "location": $whereClause .= " AND ProfileLocationCountryID='".$_POST[$param]."' "; break; } } } $query .= $whereClause; $result = mysql_query("$query"); while ($row = mysql_fetch_array($result)) { echo $row['ProfileName'] . '&lt;br/&gt;'; } </code></pre> <p>Thanks to Saggi Malachi for this method!</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload