0

I have a filtration system for my MySQL that is based off of URL parameters. One of my parameters needs to be used several times sometimes.

I.E. Sometimes the url may be localhost/?certifications=VALUE&certifications=VALUE2

All of the values for certifications is stored together in my database as value 1, value 2, value 3, etc.

My filter works if one value is listed in the records but once there are multiple and they are seperated by a comma it doesn't work.

I tried using this code I wrote to make a statement for each certification parameter used, but its failing and I don't know why.

if(isset($_GET['certifications'])) {
 foreach ($_GET['certifications']) {
   $certifications = mysqli_real_escape_string($conn,$_GET['certifications']);
   $conditions .= " AND certifications='$certifications'";
}}

What would cause this to not work?

Are there other ways to get multiple values from one database column with the same url parameters?

Full Code

  <div id="search">
   <h2>Filter Results</h2>
  <form method="get" action="test.php">
   <h3>Certifications</h3>
   <label><input type="checkbox" name="certifications" value ="washington"/>Washington</label>
   <label><input type="checkbox" name="certifications" value ="new york"/>New York</label>
   <label><input type="checkbox" name="certifications" value ="virginia"/>Virginia</label>
   <label><input type="checkbox" name="certifications" value ="florida" />Florida</label>
   <label><input type="checkbox" name="certifications" value ="georgia" />Georgia</label>
   <label><input type="checkbox" name="certifications" value ="utah" />Utah</label>
   <h3>Signage</h3>
   <label><input type="checkbox" name="signage" value ="1" /><span class="label">Top Sign</label>
   <h3>State</h3>
   <select name="state">
    <option>Select State</option>
    <option value="AL">Alabama</option>
    <option value="AK">Alaska</option>
    <option value="AZ">Arizona</option>
    <option value="AR">Arkansas</option>
    <option value="CA">California</option>
    <option value="CO">Colorado</option>
    <option value="CT">Connecticut</option>
    <option value="DE">Delaware</option>
    <option value="DC">District Of Columbia</option>
    <option value="FL">Florida</option>
    <option value="GA">Georgia</option>
    <option value="HI">Hawaii</option>
    <option value="ID">Idaho</option>
    <option value="IL">Illinois</option>
    <option value="IN">Indiana</option>
    <option value="IA">Iowa</option>
    <option value="KS">Kansas</option>
    <option value="KY">Kentucky</option>
    <option value="LA">Louisiana</option>
    <option value="ME">Maine</option>
    <option value="MD">Maryland</option>
    <option value="MA">Massachusetts</option>
    <option value="MI">Michigan</option>
    <option value="MN">Minnesota</option>
    <option value="MS">Mississippi</option>
    <option value="MO">Missouri</option>
    <option value="MT">Montana</option>
    <option value="NE">Nebraska</option>
    <option value="NV">Nevada</option>
    <option value="NH">New Hampshire</option>
    <option value="NJ">New Jersey</option>
    <option value="NM">New Mexico</option>
    <option value="NY">New York</option>
    <option value="NC">North Carolina</option>
    <option value="ND">North Dakota</option>
    <option value="OH">Ohio</option>
    <option value="OK">Oklahoma</option>
    <option value="OR">Oregon</option>
    <option value="PA">Pennsylvania</option>
    <option value="RI">Rhode Island</option>
    <option value="SC">South Carolina</option>
    <option value="SD">South Dakota</option>
    <option value="TN">Tennessee</option>
    <option value="TX">Texas</option>
    <option value="UT">Utah</option>
    <option value="VT">Vermont</option>
    <option value="VA">Virginia</option>
    <option value="WA">Washington</option>
    <option value="WV">West Virginia</option> 
    <option value="WI">Wisconsin</option>
    <option value="WY">Wyoming</option>
 </select>              
   <br>
   <input class="submit_filter" type="submit" name="formSubmit"/>
 </form>
  </div>
  <div id="count">
   <?php
    $countListings = mysqli_num_rows(mysqli_query($conn, "SELECT * FROM pilotOperators"));
    echo "Total Listings: " . $countListings;
   ?>
  </div>
 <div id="results">
 <?php

 $start=0;
 $limit=5;

 if(isset($_GET['pg']))
 {
 $pg=$_GET['pg'];
 $start=($pg-1)*$limit;
 }
 else { 
 $pg = 1;
 }
 $sql = mysql_query($query); 

 $conditions = "SELECT * FROM pilotOperators WHERE 1=1 ORDER BY id DESC";
 # append condition for signage (if required)
 if(isset($_GET['signage'])) {
    $conditions .= " AND signage='1'";
 }
 # append condition for certifications (if required)
 if(isset($_GET['certifications'])) {
    $certifications = mysqli_real_escape_string($conn,$_GET['certifications']);
    $conditions .= " AND certifications='$certifications'";
 }
 # append condition for state (if required)
 if(isset($_GET['state'])) {
  if($_GET['state'] != "Select State") {
    $state = mysqli_real_escape_string($conn,$_GET['state']);
    $conditions .= " AND state='$state'";
 }
 }
 $conditions .= " Limit $start, $limit";
 $result = mysqli_query($conn, $conditions); 

 while($row = mysqli_fetch_array($result))
 {
 echo "\n <table border='0' class='resultTable' width='75%'> \n";
 echo "<tr> \n";
 echo "<td width='120px'>Business: </td> \n";
 echo "<td>" . $row['business'] . "</td> \n";
 echo "</tr> \n";
 echo "<tr> \n";
 echo "<td width='120px'>Cars Available: </td> \n";
 echo "<td>" . $row['cars'] . "</td> \n";
 echo "</tr> \n";
 echo "<tr> \n";
 echo "<td>Name: </td> \n";
 echo "<td>" . $row['name'] . "</td> \n";
 echo "</tr> \n";
 echo "<tr> \n";
 echo "<td>Phone: </td> \n";
 echo "<td>" . $row['phone'] . "</td> \n";
 echo "</tr> \n";
 echo "<tr> \n";
 echo "<td>Alt. Phone: </td> \n";
 echo "<td>" . $row['alt_phone'] . "</td> \n";
 echo "</tr> \n";
 echo "<tr> \n";
 echo "<td>Fax: </td> \n";
 echo "<td>" . $row['fax'] . "</td> \n";
 echo "</tr> \n";
 echo "<tr> \n";
 echo "<td>Email: </td> \n";
 echo "<td>" . $row['email'] . "</td> \n";
 echo "</tr> \n";
 echo "<tr> \n";
 echo "<td>Website: </td> \n";
 echo "<td><a href='" . $row['website'] . "' target='_blank'>" . $row['website'] . "</a></td> \n";
 echo "</tr> \n";
 echo "<tr> \n";
 echo "<td>City: </td> \n";
 echo "<td>" . $row['city'] . "</td> \n";
 echo "</tr> \n";
 echo "<tr> \n";
 echo "<td>State: </td> \n";
 echo "<td>" . $row['state'] . "</td> \n";
 echo "</tr> \n";
 echo "<tr> \n";
 echo "<td>Certifications: </td> \n";
 echo "<td>" . $row['certifications'] . "</td> \n";
 echo "</tr> \n";
 echo "<tr> \n";
 echo "<td>Top Sign: </td> \n";
 echo "<td>";
 if($row['signage'] = 1) {
  echo "Has Top Sign";
 }
 else {
  echo "Top Sign Not Listed";
 }
 echo "</td> \n";
 echo "</tr> \n";
 echo "</table> \n\n";
 }

 $countconditions = "SELECT * FROM pilotOperators WHERE 1=1";
 # append condition for signage (if required)
 if(isset($_GET['signage'])) {
    $countconditions .= " AND signage='1'";
 }
 # append condition for certifications (if required)
 if(isset($_GET['certifications'])) {
    $certifications = mysqli_real_escape_string($conn,$_GET['certifications']);
    $countconditions .= " AND certifications='$certifications'";
 }
 # append condition for state (if required)
 if(isset($_GET['state'])) {
  if($_GET['state'] != "Select State") {
    $state = mysqli_real_escape_string($conn,$_GET['state']);
    $countconditions .= " AND state='$state'";
 }
 }
 $rows = mysqli_num_rows(mysqli_query($conn, $countconditions));

 $total=ceil($rows/$limit);
  echo "<div id='paginationLinks'> \n";
  if($pg>1)
 {
 $q2 = http_build_query(array_merge($_GET, ["pg" => $pg-1]));
 echo "<a href='index.php?".$q2."' class='paginationButton'>PREVIOUS</a> \n";
 }
 if($pg!=$total)
 {
 $q = http_build_query(array_merge($_GET, ["pg" => $pg+1]));
 echo "<a href='index.php?".$q."' class='paginationButton'>NEXT</a> \n";
 }

 echo "<ul class='page'> \n";
 for($i=1;$i<=$total;$i++)
 {
     if($i==$pg) { echo "<li class='current'>".$i."</li> \n"; }

     else { echo "<li><a href='?pg=".$i."'>".$i."</a></li> \n"; }
     }
     echo "</ul> \n";
      echo "</div> \n";
     mysqli_close($con);
     ?>
      </div>
      </div>
Jesse Elser
  • 974
  • 2
  • 11
  • 39
  • 2
    what does var_dump($_GET['certifications']) show? – dbinns66 May 01 '15 at 21:48
  • what do the values of `$certifications` look like? What is the final rendered value of `$conditions`. Do you have error reporting turned on for your code ? – Maximus2012 May 01 '15 at 21:49
  • the values of certification(in the DB) if the user selects and inputs every option when they place their entry should be washington, georgia, virginia, new york, utah and the final value of $conditions once the code is ran should be SELECT * FROM pilotOperators WHERE 1=1 ORDER BY id DESC AND signage='1' AND certifications='$certifications' AND state='$state' but I need the possibility of multiple AND certifications='$certification'. – Jesse Elser May 01 '15 at 21:55
  • Can you please update your question with this information as that would be more helpful. – Maximus2012 May 01 '15 at 21:56
  • 1
    I'm guessing that query wouldn't work, you can't have multiple AND for the same field, you'd have to group them `certifications IN ($certifications[0], $certifications[1])` or something similar – adeneo May 01 '15 at 21:58
  • I've updated my question to include my full code. This is before I made any changes to it today. It works with one certification parameter but not multiple. – Jesse Elser May 01 '15 at 22:27
  • This is certainly the duplicate of http://stackoverflow.com/questions/29988414/php-foreach-loop-with-checkboxes/29988454#29988454 – Stepashka May 01 '15 at 22:48

3 Answers3

1

Disclaimer: Use better coding practices.

PHP will parse URL params as arrays, if you use:

?certificates[]=aaa&certificates[]=bbb

Then your Database snippet will work.

UPDATE:

After reading your full script, a further problem turns out to be the query as pointed out in the comments. if you put 

echo $countconditions; die;

You will see the parameters are all there, but your query logic is wrong. Use something like PHPMyAdmin to execute the statement and edit it. I think the 

'certificated IN (' . implode(',' $$certificates) . ')'

condition could be what you are looking for.

FloydThreepwood
  • 1,587
  • 14
  • 24
  • _PHP The Right Way_ is a huge site. Could you be more specific about which recommendations he needs to adopt in this tiny snippet of code? – Barmar May 01 '15 at 21:55
  • You and @adeneo each got half the solution. – Barmar May 01 '15 at 21:56
  • Ok, I would recommend to at least read the Database and Security part. Wich will tell you about prepared statements and proper input escaping. But really: read all of it it may seem like a lot, but it is really worth it! – FloydThreepwood May 01 '15 at 21:59
  • How do I get my url to add the brackets? I tried adding it to my forms but when the url was submitted they didn't appear but in place. I.E. name="certifications[]" When submitted became certifications%5B%5D=washington – Jesse Elser May 01 '15 at 22:00
  • There is a form? Could you edit your question to show the markup? – FloydThreepwood May 01 '15 at 22:02
  • @JesseElser Those are the brackets. Since they're special characters, they're encoded with percents. – Barmar May 01 '15 at 22:03
  • ok, so my answer was basically correct. see: http://stackoverflow.com/questions/1833330/how-to-get-php-get-array – FloydThreepwood May 01 '15 at 22:28
  • I've actually ran into a problem which i think is preventing any solution from working. When the user is submitting their info to my db they have checkboxes they choose for certifications. If they choose one it is entered into the database as "value". But if they choose multiple it is entered as "value1,value2". Since my filter checks for an exact value it is overlooking any records with multiple. How can I solve it to where it finds all records with a certain word even if there are other values seperated by comma. This is my entry: $certifications = implode(',', $_POST['certification']); – Jesse Elser May 01 '15 at 22:59
  • Read about the SQL LIKE statement. – FloydThreepwood May 02 '15 at 09:47
1

In case you have parameters like in your example, PHP will put latest one to $_GET['certifications'] and previous params will be lost. You can either follow the FloydThreepwood's advice or parse query string manually.

In case you have GET request get query string in $_SERVER['QUERY_STRING'], if you use POST then use the following solution: How to get body of a POST in php?

Then with couple explode you can get array of 'certifications'.

Community
  • 1
  • 1
Stepashka
  • 2,648
  • 20
  • 23
0

You can pass multiple value in query string separating them by the comma, then you can get these value and put these values in array by php "explode" method. loop though the array to pick these values by using "for or foreach" loop

//$conditions="";
if(isset($_GET['certifications'])) {
     $values =explode (",",$_GET['certifications']);
     foreach ($values as $value ) {
     $certifications= mysqli_real_escape_string($conn, $value );
     $conditions .= " AND. certifications= '$certifications'";
     } 
}

Example Url: example.com&certifications=value 1,value2,value3....

Aman Maurya
  • 1,305
  • 12
  • 26