Building dynamic SQL queries with pdo for search engine

[chejug]

Hi there, I've been trying to rewrite my search engine using PDO.
So my old code looks like this:

$input = $_POST['input'];
$categories = $_POST['category'];
$state = $_POST['state'];
$zipcode = $_POST['zipcode'];

 $qq = " SELECT * FROM classified  ";     
 $result = mysql_query($qq);
  $rows = mysql_num_rows($result);


if ($rows>0){
$q = " SELECT * FROM classified where confirm='1' ";      

if(( $_POST['input']!='Search')) {

    $q .= "AND title LIKE '%".$input."%' ";
}
    
    
    if (!empty($_POST['search_category']) )
{
   $q .= "AND id_cat = '" . $categories. "' ";

}

if (!empty($_POST['state']) )
{
   $q .= "AND id_state = '" .$state. "' ";

}


if(($_POST['zipcode']!='Code postale')) {

    $q .= "AND zipcode = '".$zip_Code."' ";
}


$q .= "ORDER BY date ";

// 
}
    $r = mysql_query($q);
    $ro = mysql_num_rows($r);

if ($ro > 0) {
  while($row = mysql_fetch_array($r)) {
 
            echo  $row['title'];
           echo  $row['categories'];
           echo  $row['state'];
           echo  $row['zipcode'];

}
 }else{

echo "No data available ";   
} 

This code is not safe the way it is (posting purposes only)

I gave it a try, but couldn't get results: Also I'm having hard time with this:

if (!empty($input)) {
    $cond[] = "title = ?";
    $params[] = $input;
} 

since it's not a named placeholder I couldn't figure it out:

I can't just do $input = "%$input%";

$qq = $db->prepare(" SELECT * FROM classified  ")or die(print_r($qq->errorInfo(), true));
    /*** execute the prepared statement ***/
    $qq->execute();

    /*** echo number of columns ***/
    $rows = $qq->fetch(PDO::FETCH_NUM); 
if ($rows>0){

$query = (" SELECT * FROM classified WHERE  confirm = '0' ");
$cond = array();
$params = array();


if (!empty($input)) {
    $cond[] = "title = ?";
    $params[] = $input;
}

if (!empty($categories)) {
    $cond[] = "id_cat = ?";
    $params[] = $categories;
}

if (!empty($state)) {
    $cond[] = "id_state = ?";
    $params[] = $state;
}    


if (!empty($zipcode)) {
    $cond[] = "zipcode = ?";
    $params[] = $zipcode;
}



if (count($cond)) {
    $query .= ' WHERE  ' . implode(' AND ', $cond)or die(print_r($query->errorInfo(), true));
}


$stmt = $db->prepare($query);

$stmt->execute($params);

$ro = $stmt->fetch(PDO::FETCH_NUM);
}

    if ($ro > 0) {
foreach ($stmt->fetchAll(PDO::FETCH_ASSOC) as $row) 
   {

             echo  $row['title'];
           echo  $row['categories'];
           echo  $row['state'];
           echo  $row['zipcode'];
}
} 

Thanks in advance

[chejug]

Any advice?

[Cups]

There seem to be some confusing signals here.

The pre-PDO attempt whose code you show, does that actually work or not then?

I gave it a try, but couldn't get results: Also I'm having hard time with this: (code removed)

Then I wonder just what is the purpose of this code?

 $qq = " SELECT * FROM classified  ";      
 $result = mysql_query($qq); 
  $rows = mysql_num_rows($result); 

Is it just to confirm that there is something in the table named `classified` ?

[chejug]

It worked just fine using old mysql connection, but I switched to PDO ang I couldn't run it.
$qq = " SELECT * FROM classified ";
$result = mysql_query($qq);
$rows = mysql_num_rows($result);
right I wanted to confirm that there is some data in the table:

The first block of code works fine

[Cups]

I've seen plenty of discussion on how to handle Mysql LIKE clauses using PDO prepared statements.

I think it has to be handled thus:

if (!empty($input)) { 
    $cond[] = "title LIKE '%?%'"; 
    $params[] = $input; 
} 

[chejug]

I managed to do it this way;
My question know is it safe like this? Did I use PDO statement the right way?
Another problem is; lets break it this way.
Lets say the category "Auto_part" is empty, and a user trys to search that category, it suppose to echo"No data", and I'm not getting that! I don't know what I'm missing here; Look all fine to me

        $input = $_POST['input'];
        $categories = $_POST['category'];
        $state = $_POST['state'];
        $zipcode = $_POST['zipcode'];
     
        $qq = $db->prepare(" SELECT * FROM classified  ")or die(print_r($qq->errorInfo(),
        true));
        /*** execute the prepared statement ***/
        $qq->execute();
     
    /*** echo number of columns ***/
    $rows = $qq->fetch(PDO::FETCH_NUM);
    if ($rows>0){
     
     $query = " SELECT * FROM classified where confirm='0' ";  
     if(!empty( $_POST['input'])) {
     
     $query .= "AND title LIKE '%".$input."%' ";
     }
     
     
    if (!empty($_POST['category']) )
       {
      $query .= "AND id_cat = ".$categories." ";
     
     }
     
      if (!empty($_POST['state']) )
      {
      $query .= "AND id_state = ".$state." ";
     
      }
     
     
      if(!empty($_POST['zipcode'])) {
     
      $query .= "AND zipcode = ".$zipcode." ";
      }
       $query .= "ORDER BY date ";
     
        }
     
       $stmt = $db->prepare($query);
      $stmt->execute($params);
       $result = $stmt->fetchAll();
     //  $ro = $stmt->fetch(PDO::FETCH_NUM);
     
      // it didn't work when I tried to count rows
     if ($result > 0) {
    foreach ($result as $row)
     {
     echo  $row['title'];
     echo  $row['categories'];
     echo  $row['state'];
     echo  $row['zipcode'];
     }
     }else{
echo "No data";
} 

[Cups]

If you var_dump($result) a positive should return an array with elements in it.

If it is negative (as you describe, a category search with no matching results) then it should return an empty array.

It is that which you should be testing for.

if(count($result) !== 0 ){

// positive actions

}else{

// negative message

} 

I read the docs and this is what I understand should happen, I have not tested it.

The advice I often give is : "just prior to forking your code on a condition, temporarily var_dump() the variable you are testing and make sure you are testing for the exact same thing (or lack of it)".