Get one value from database with multiple values

Question

I can't seem to find an answer to this so I'm gonna ask it here.

I have 6 checkboxes in a form with the name of certifications. Each value is different.

I use this feature to enter them into the DB $certifications = implode(',', $_POST['certification']);

If one checkbox is selected my certifications column has a single value.

But if multiple are selected and submitted it looks like this value1,value2,value3.

I have a query setup with radio buttons to select records with certain values. It works like a charm when the record has a single value, but if there are multiple ones seperated by commas that record get's overlooked since it isn't exactly what it is looking for.

For example if my url says ?certifications=value1 every record with just value1 will show up, BUT if that same url parameter is set and the record has value1,value2 it gets overlooked.

What can solve this?


   Filter Results
  
   Certifications
   Washington
   New York
   Virginia
   Florida
   Georgia
   Utah
   Signage
   Top Sign
   State
                 
   

   
 
  
  
   
  
 
  
";
 echo " 
";
 echo "Business:  
";
 echo "" . $row['business'] . " 
";
 echo " 
";
 echo " 
";
 echo "Cars Available:  
";
 echo "" . $row['cars'] . " 
";
 echo " 
";
 echo " 
";
 echo "Name:  
";
 echo "" . $row['name'] . " 
";
 echo " 
";
 echo " 
";
 echo "Phone:  
";
 echo "" . $row['phone'] . " 
";
 echo " 
";
 echo " 
";
 echo "Alt. Phone:  
";
 echo "" . $row['alt_phone'] . " 
";
 echo " 
";
 echo " 
";
 echo "Fax:  
";
 echo "" . $row['fax'] . " 
";
 echo " 
";
 echo " 
";
 echo "Email:  
";
 echo "" . $row['email'] . " 
";
 echo " 
";
 echo " 
";
 echo "Website:  
";
 echo "" . $row['website'] . " 
";
 echo " 
";
 echo " 
";
 echo "City:  
";
 echo "" . $row['city'] . " 
";
 echo " 
";
 echo " 
";
 echo "State:  
";
 echo "" . $row['state'] . " 
";
 echo " 
";
 echo " 
";
 echo "Certifications:  
";
 echo "" . $row['certifications'] . " 
";
 echo " 
";
 echo " 
";
 echo "Top Sign:  
";
 echo "";
 if($row['signage'] = 1) {
  echo "Has Top Sign";
 }
 else {
  echo "Top Sign Not Listed";
 }
 echo " 
";
 echo " 
";
 echo " 

";
 }

 $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 " 
";
  if($pg>1)
 {
 $q2 = http_build_query(array_merge($_GET, ["pg" => $pg-1]));
 echo "PREVIOUS 
";
 }
 if($pg!=$total)
 {
 $q = http_build_query(array_merge($_GET, ["pg" => $pg+1]));
 echo "NEXT 
";
 }

 echo " 
";
 for($i=1;$i<=$total;$i++)
 {
     if($i==$pg) { echo "".$i." 
"; }

     else { echo "".$i." 
"; }
     }
     echo " 
";
      echo " 
";
     mysqli_close($con);
     ?>

Edit:

I forgot to mention that I edited these lines and tried to use explode:

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

But that didn't do the trick.

I literally just need my code to be able to read one value from a row that has multiple stored :/

rjdown · Accepted Answer

Instead of

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

You need to use find_in_set e.g.

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

Get one value from database with multiple values

Answers (1)

Related Questions