SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Member
    Join Date
    Dec 2012
    Posts
    14
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Building dynamic SQL queries with pdo for search engine

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

    $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:
    PHP Code:
    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%";

    PHP Code:
    $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

  2. #2
    SitePoint Member
    Join Date
    Dec 2012
    Posts
    14
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Any advice?

  3. #3
    SitePoint Wizard silver trophybronze trophy Cups's Avatar
    Join Date
    Oct 2006
    Location
    France, deep rural.
    Posts
    6,869
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    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?

    PHP 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` ?

  4. #4
    SitePoint Member
    Join Date
    Dec 2012
    Posts
    14
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  5. #5
    SitePoint Wizard silver trophybronze trophy Cups's Avatar
    Join Date
    Oct 2006
    Location
    France, deep rural.
    Posts
    6,869
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    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:

    PHP Code:
    if (!empty($input)) { 
        
    $cond[] = "title LIKE '%?%'"
        
    $params[] = $input


  6. #6
    SitePoint Member
    Join Date
    Dec 2012
    Posts
    14
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

    PHP Code:
            $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";


  7. #7
    SitePoint Wizard silver trophybronze trophy Cups's Avatar
    Join Date
    Oct 2006
    Location
    France, deep rural.
    Posts
    6,869
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    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.

    PHP Code:
    if(count($result) !== ){

    // 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)".
    Last edited by Cups; Dec 31, 2012 at 06:45. Reason: Correction to !==


Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •