PDO or MYSQLI : if (!empty) $query.= WHERE IN

[Marina06]

Hi everyone !

I've been struggling for 5 days do far and I'm still stuck on the piece of code that you'll see right after.
Would someone be kind enough either to show how to do it with mysqli, or to correct what's wrong in my following pdo code :
Thanks a billion !!!!!!!!

<select multiple="multiple" name="secteur_searched[]" >
 <option value="1">Accueil - Secrétariat - Fonctions Administratives</option>
 <option value="2">Achats - Juridique - Qualité; - RH - Direction</option>
     etc...
 </select>

<input type="checkbox" name="type_de_contrat[]" value="CDI" >     

<input type="checkbox" name="type_de_contrat[]" value="CDD" >

try{
$query =  "SELECT * FROM interim_job_offers j JOIN villes v ON v.ville_id =j.job_ville  ";
 $where = array();
 $param = array();

 if (!empty($_REQUEST['secteur_searched']) AND is_array($_REQUEST["secteur_searched"]) ) // esta parte
    {
     $where[] = "j.job_secteur IN (:job_secteur)";
     $param[":job_secteur"] = "'".implode("','",    $_REQUEST['secteur_searched'])."'";
    }

 if(!empty($_REQUEST['type_de_contrat']) AND count($_REQUEST['type_de_contrat']) > 0 ) // y esta
   {
    $where[] = "j.job_contrat IN (:job_contrat)";
    $param[":job_contrat"] = "'".implode("','", $_REQUEST['type_de_contrat'])."'";
   }   

if (!empty($where))
   {$query.= ' WHERE ' . implode(' AND ', $where);
   }

 $query.= " ORDER BY j.job_date_insertion DESC";

$sth =$mInterim ->prepare($query);
$sth->execute($param);

Many Thaaanks!

[laserlight]

It might help if you explained what you are trying to do, e.g., tell us what are the tables involved and there relevant columns, and what exactly it is that you're trying to select based on the form input.

[Marina06]

It's a search by criterion form.
I want to display the results corresponding to the values selected in my <select multiple="multiple" name="secteur_searched[]" > together with those that match the checked type_of_contrat checkboxes (if checked). The user can fill in / select / check as many criterions as desired, or none for a general output.

My problem lays is the WHERE IN condition = in the multiselect and checkboxes. For now let's focus only on my multiselect.
How would you write the code supposed to go into the curly brackets in here :?
if (!empty($REQUEST['secteur_searched']) AND is_array($REQUEST["secteur_searched"]) )
{
$where[] = "j.job_secteur IN (:job_secteur)";
$param[":job_secteur"] = implode(",", $_REQUEST['secteur_searched']); // This code returns errors
}

Thank u very very very much 🙂

Here's my almost full code so you can see what I've done so far to display the results:

$query =  "SELECT * FROM marinterim_job_offers j JOIN marimmo_villes v ON v.ville_id =j.job_ville  "; 
 $where = array();
$param = array();

  if (!empty($_REQUEST['job_searched']))
     { 	$where[] = "j.job_intitule=:job_intitule ";
        $param[':job_intitule'] = $_REQUEST['job_searched'];
     }

 if (!empty($_REQUEST['job_keyword']))
    { $where[] =  " j.job_descriptif LIKE CONCAT('%', :job_descriptif, '%') " ;
      $param[':job_descriptif'] = $_REQUEST['job_keyword'];
    }

/* if (!empty($_REQUEST['secteur_searched']) AND is_array($_REQUEST["secteur_searched"]) )
    {
     $where[] = "j.job_secteur IN (:job_secteur)";
     $param[":job_secteur"] = implode(",",    $_REQUEST['secteur_searched']);
    }
	*/	
if (!empty($_REQUEST["secteur_searched"]) && is_array($_REQUEST["secteur_searched"]))
    {  $x= array_values($_REQUEST["secteur_searched"]);
      $secteur = implode(',', array_fill(0, count($_REQUEST["secteur_searched"]), '?')); 

  $where[] =  " j.job_secteur IN ( :job_secteur )" ;     
  //$param[':job_secteur']= $secteur; 
  $param = explode(",", "$x"); 
 }



if (!empty($where)) 
   { 
	 $query.= ' WHERE ' . implode(' AND ', $where); 
   }

 $query.= " ORDER BY j.job_date_insertion DESC"; 

$sth =$marInterim ->prepare($query); 
$sth->execute($param);


$compte = $sth->fetchAll();
$nb_resultats = count($compte); 
$errors['nb_resultats_recherche'] = $nb_resultats; // ok 

/********************/
   if ( !empty($_REQUEST['afficher_x_resultats']))   // Mostrar x resultados
	  { $per_page=$_REQUEST['afficher_x_resultats']; 
	  }
 else {$per_page=10; }


/* Results per page */
$nb_pages = ceil($nb_resultats/$per_page);  

$current_page = isset($_REQUEST['page']) && ($_REQUEST['page'] > 0) && ($_REQUEST['page'] <= $nb_pages) ? $_REQUEST['page'] : 1;
$start = ($current_page-1)*$per_page; 

$query2= $query." LIMIT $start,$per_page ";
// echo '<br /> $query2 =  '.$query2;

$sth2 =$marInterim ->prepare($query2);  

$sth2-> execute($param); 
echo "<pre>";
echo "<br>QUERY <br>";
print_r($query2);

echo "<br>PARAM<br>";
print_r($param);
echo "</pre>";


 while($datos= $sth2->fetch(PDO::FETCH_ASSOC))
      { $en_date_insertion=$datos['job_date_insertion'];	  

		 $explode_insertion= explode("-", $en_date_insertion);
		 $date_insertion_fr = $explode_insertion[2]."-".$explode_insertion[1]."-".$explode_insertion[0];		 
		 $job_id= $datos['job_id'];
		 $job_intitule= strtoupper($datos['job_intitule']);
		 $job_ville = $datos['ville_nom'];
		 $job_cp = $datos['cp'];


	echo "
				 <div class='offers btns'>
				 <a class='link_vers_offre' href='job_offer_detail.php?job_id=$job_id'>
				 <table id='table_liste_des_offres'>
				 <tr>
				 <td class='liste_intitule'> " .$job_intitule." </td>
				 <td class='liste_ref'>Offre n°" .$job_id."   du   ".$date_insertion_fr ." </td>
				 <td class='liste_ville'> ".$job_cp. " ".$job_ville." </td>
				 </tr>
				 </table>
				 </a>
				 </div>
			   ";

  }



} catch(Exception $e)
			  {	exit('<b>Catched exception at line '. $e->getLine() .' :</b> '. $e->getMessage());
			  }	
}

[Marina06]

I've posted a more comprehensive answer but I don't see my thread updated... Humm..? Let's wait to see if it updates...

[Marina06]

It's a search by criterion form.
I want to display the results corresponding to the values selected in my <select multiple="multiple" name="secteur_searched[]" > together with those that match the checked type_of_contrat checkboxes (if checked). The user can fill in / select / check as many criterions as desired, or none for a general output.

My problem lays is the WHERE IN condition = in the multiselect and checkboxes. For now let's focus only on my multiselect.
How would you write the code supposed to go into the curly brackets in here :?
if (!empty($REQUEST['secteur_searched']) AND is_array($REQUEST["secteur_searched"]) )
{
$where[] = "j.job_secteur IN (:job_secteur)";
$param[":job_secteur"] = implode(",", $_REQUEST['secteur_searched']); // This code returns errors
}

Thank u very very very much 🙂

Here's my almost full code so you can see what I've done so far to display the results:

$query =  "SELECT * FROM marinterim_job_offers j JOIN marimmo_villes v ON v.ville_id =j.job_ville  "; 
 $where = array();
$param = array();

  if (!empty($_REQUEST['job_searched']))
     { 	$where[] = "j.job_intitule=:job_intitule ";
        $param[':job_intitule'] = $_REQUEST['job_searched'];
     }

 if (!empty($_REQUEST['job_keyword']))
    { $where[] =  " j.job_descriptif LIKE CONCAT('%', :job_descriptif, '%') " ;
      $param[':job_descriptif'] = $_REQUEST['job_keyword'];
    }

/* if (!empty($_REQUEST['secteur_searched']) AND is_array($_REQUEST["secteur_searched"]) )
    {
     $where[] = "j.job_secteur IN (:job_secteur)";
     $param[":job_secteur"] = implode(",",    $_REQUEST['secteur_searched']);
    }
	*/	
if (!empty($_REQUEST["secteur_searched"]) && is_array($_REQUEST["secteur_searched"]))
    {  $x= array_values($_REQUEST["secteur_searched"]);
      $secteur = implode(',', array_fill(0, count($_REQUEST["secteur_searched"]), '?')); 

  $where[] =  " j.job_secteur IN ( :job_secteur )" ;     
  //$param[':job_secteur']= $secteur; 
  $param = explode(",", "$x"); 
 }



if (!empty($where)) 
   { 
	 $query.= ' WHERE ' . implode(' AND ', $where); 
   }

 $query.= " ORDER BY j.job_date_insertion DESC"; 

$sth =$marInterim ->prepare($query); 
$sth->execute($param);


$compte = $sth->fetchAll();
$nb_resultats = count($compte); 
$errors['nb_resultats_recherche'] = $nb_resultats; // ok 

/********************/
   if ( !empty($_REQUEST['afficher_x_resultats']))   // Mostrar x resultados
	  { $per_page=$_REQUEST['afficher_x_resultats']; 
	  }
 else {$per_page=10; }


/* Results per page */
$nb_pages = ceil($nb_resultats/$per_page);  

$current_page = isset($_REQUEST['page']) && ($_REQUEST['page'] > 0) && ($_REQUEST['page'] <= $nb_pages) ? $_REQUEST['page'] : 1;
$start = ($current_page-1)*$per_page; 

$query2= $query." LIMIT $start,$per_page ";
// echo '<br /> $query2 =  '.$query2;

$sth2 =$marInterim ->prepare($query2);  

$sth2-> execute($param); 
echo "<pre>";
echo "<br>QUERY <br>";
print_r($query2);

echo "<br>PARAM<br>";
print_r($param);
echo "</pre>";


 while($datos= $sth2->fetch(PDO::FETCH_ASSOC))
      { $en_date_insertion=$datos['job_date_insertion'];	  

		 $explode_insertion= explode("-", $en_date_insertion);
		 $date_insertion_fr = $explode_insertion[2]."-".$explode_insertion[1]."-".$explode_insertion[0];		 
		 $job_id= $datos['job_id'];
		 $job_intitule= strtoupper($datos['job_intitule']);
		 $job_ville = $datos['ville_nom'];
		 $job_cp = $datos['cp'];


	echo "
				 <div class='offers btns'>
				 <a class='link_vers_offre' href='job_offer_detail.php?job_id=$job_id'>
				 <table id='table_liste_des_offres'>
				 <tr>
				 <td class='liste_intitule'> " .$job_intitule." </td>
				 <td class='liste_ref'>Offre n°" .$job_id."   du   ".$date_insertion_fr ." </td>
				 <td class='liste_ville'> ".$job_cp. " ".$job_ville." </td>
				 </tr>
				 </table>
				 </a>
				 </div>
			   ";

  }



} catch(Exception $e)
			  {	exit('<b>Catched exception at line '. $e->getLine() .' :</b> '. $e->getMessage());
			  }	
}

[Marina06]

The mysql colums are job_secteur (= job sector ), job_intitule (=job_title), job_descriptif (=description > find keyword in job description), job_contrat (= contract could be permanent, temporary, part-time, etc)

[Marina06]

Sorry for the double post #5.

I rephrase :

I want to translate in pdo or in mysqli :

$query =  "SELECT * FROM marinterim_job_offers j JOIN marimmo_villes v ON v.ville_id =j.job_ville WHERE "; 

	if (!empty($_REQUEST['job_searched']))
	   { $job_searched = mysql_real_escape_string($_REQUEST['job_searched']);
	     $query .= "j.job_intitule= $job_searched AND "; 
	   }

   if (!empty($_REQUEST['job_keyword']))
	  { $job_keyword = mysql_real_escape_string($_REQUEST['job_keyword']);
		$query.= "j.job_descriptif LIKE ('%job_keyword%') AND";
	  } 


/**/
/**/   if (!empty($_REQUEST['secteur_searched']) && is_array($_REQUEST['secteur_searched']))
/**/      { $secteur_searched = implode(',', $_REQUEST['secteur_searched']);
/**/ 	    $query.= " j.job_secteur IN ( $secteur_searched ) AND ";
/**/      }
/**/ 	 
/**/  if (!empty($_REQUEST['type_de_contrat']) AND count($_REQUEST['type_de_contrat']) > 0 )	 
/**/ 	 { $type_de_contrat =  implode(',', $_REQUEST['type_de_contrat']);
/**/ 	   $query .= " j.job_contrat IN ($type_de_contrat) AND ";						  

/**/ 	 }		   

/**/ 		 
/**/  $query =  rtrim($query, 'AND');	// if the user refinex its search by selecting several criteria 


$query.= " ORDER BY j.job_date_insertion DESC"; 

$sth =$marInterim ->prepare($query); 
$sth->execute();

Thanks

[sneakyimp]

mysqli would certainly be easier as it is nearly identical to mysql. The big difference is that mysqli requires you to pass around a reference to your db connection.

One thing that's confusing about your code is that you refer to $marInterim as an object but you haven't defined this object anywhere:

$sth =$marInterim ->prepare($query);