Results 1 to 2 of 2

Thread: Help with PDO query

  1. #1
    Join Date
    Jul 2010
    Location
    Minnesota
    Posts
    256
    Thanks
    1
    Thanked 21 Times in 21 Posts

    Default Help with PDO query

    So I am just starting to convert my site to PDO but have come to a snag in my knowledge of this. I have this function for a search of products in the db and I don't know how to build this in a way that I can bind the values for security because of how the query is built with the $construct var. Since using PDO doesn't allow for a normal mysql_real_escape_string() escaping I am confused as to how to proceed.

    Here is the function
    PHP Code:
    function searchResults($start)
    {
        global 
    $db;

        
    $search_term sanitize($_GET['search-term']);
        if(empty(
    $search_term))
        {return 
    FALSE;}
        else
        {
            
    $search_xplode preg_split('/[\s]+/'$search_term);
            
    $m=1;
            
    $construct='';
                foreach (
    $search_xplode as $ste)
                {
                    if (
    $m==1)
                    {
                        
    $construct .= "`".PRODUCTS."`.`prod_number` LIKE '%$ste%' AND `".PRODUCTS."`.`active`=TRUE 
                                    OR `"
    .PRODUCTS."`.`prod_desc` LIKE '%$ste%' AND `".PRODUCTS."`.`active`=TRUE
                                    OR `"
    .PRODUCTS."`.`prod_name` LIKE '%$ste%' AND `".PRODUCTS."`.`active`=TRUE"
                    }
                    else
                    {
                        
    $construct .= " OR `".PRODUCTS."`.`prod_number` LIKE '%$ste%' AND `".PRODUCTS."`.`active`=TRUE
                                        OR `"
    .PRODUCTS."`.`prod_desc` LIKE '%$ste%' AND `".PRODUCTS."`.`active`=TRUE
                                        OR `"
    .PRODUCTS."`.`prod_name` LIKE '%$ste%' AND `".PRODUCTS."`.`active`=TRUE";
                    }
                    
    $m++;
                }
    //End foreach loop.
                
            
    $get $db->query("SELECT 
                                     `"
    .BRANDS."`.*,
                                     `"
    .PRODUCTS."`.*
                                    FROM `"
    .PRODUCTS."` 
                                    INNER JOIN `"
    .BRANDS."` ON `".BRANDS."`.`id` = `".PRODUCTS."`.`brand_id`
                                    WHERE 
    $construct
                                    ORDER BY `"
    .BRANDS."`.`brand_name` ASC, `".PRODUCTS."`.`price` ASC
                                    LIMIT 
    $start{$GLOBALS['settings']['prod_per_page']}");
            
            if(
    $get->rowCount() <=0)
            {return 
    FALSE;}
            else
            {
                
    $the_prod_info = array();
                while (
    $prod $get->fetch(PDO::FETCH_ASSOC) !== FALSE)
                { 
    $the_prod_info[] = $prod; }
                
    $the_prod_info[] = recordCount(PRODUCTS"WHERE ".$construct);
                return 
    $the_prod_info;
            }
        }

    Don't be concerned about the sanitize() I use cause that is something I have built but doesn't have any bearing on the issue. Typically I have been doing the binding the easy way like this
    PHP Code:
    $get $db->prepare("BLAH BLAH");
    $get->execute(array($var$var)); 
    Any help would be greatly appreciated.

  2. #2
    Join Date
    Jul 2010
    Location
    Minnesota
    Posts
    256
    Thanks
    1
    Thanked 21 Times in 21 Posts

    Default

    Nevermind, figured it out pretty fast once I thought about it. I also learned of a new SELECT feature to get the total number of rows in the first query without the LIMIT being factored and then you use the SELECT FOUND_ROWS() in the next query right after and gives the result from the first query but is much faster and if you have a complicated query for the first query string you don't have to duplicate it again for the second, VERY nice feature for pagination purposes.

    Here is the result
    PHP Code:
    function searchResults($start)
    {
        global 
    $db;

        
    $search_term sanitize($_GET['search-term']);
        if(empty(
    $search_term))
        {return 
    FALSE;}
        else
        {
            
    $search_xplode preg_split('/[\s]+/'$search_term);
            
    $m=1;
            
    $params = array();
            
    $construct='';
                foreach (
    $search_xplode as $ste)
                {
                    
    $params[$m] = '%'.$ste.'%';
                    
                    if (
    $m==1)
                    {
                        
    $construct .= "`".PRODUCTS."`.`prod_number` LIKE :$m AND `".PRODUCTS."`.`active`=TRUE 
                                    OR `"
    .PRODUCTS."`.`prod_desc` LIKE :$m AND `".PRODUCTS."`.`active`=TRUE
                                    OR `"
    .PRODUCTS."`.`prod_name` LIKE :$m AND `".PRODUCTS."`.`active`=TRUE"
                    }
                    else
                    {
                        
    $construct .= " OR `".PRODUCTS."`.`prod_number` LIKE :$m AND `".PRODUCTS."`.`active`=TRUE
                                        OR `"
    .PRODUCTS."`.`prod_desc` LIKE :$m AND `".PRODUCTS."`.`active`=TRUE
                                        OR `"
    .PRODUCTS."`.`prod_name` LIKE :$m AND `".PRODUCTS."`.`active`=TRUE";
                    }
                    
    $m++; 
                }
    //End foreach loop.
                
            
    $sql "SELECT SQL_CALC_FOUND_ROWS
                         `"
    .BRANDS."`.*,
                         `"
    .PRODUCTS."`.*
                        FROM `"
    .PRODUCTS."` 
                        INNER JOIN `"
    .BRANDS."` ON `".BRANDS."`.`id` = `".PRODUCTS."`.`brand_id`
                        WHERE 
    $construct
                        ORDER BY `"
    .BRANDS."`.`brand_name` ASC, `".PRODUCTS."`.`price` ASC
                        LIMIT 
    $start{$GLOBALS['settings']['prod_per_page']}";
            
    $get $db->prepare($sql);
                                 
            foreach(
    $params as $key => $val)
            { 
    $get->bindValue(':'.$key$valPDO::PARAM_STR); }
            
    $get->execute();
            
            
    $total_rows $db->query('SELECT FOUND_ROWS();')->fetch(PDO::FETCH_COLUMN);
            
            if(
    $get->rowCount() <=0)
            {return 
    FALSE;}
            else
            {
                
    $the_prod_info = array();
                while (
    $prod $get->fetch(PDO::FETCH_ASSOC))
                { 
    $the_prod_info[] = $prod; }
                
    $the_prod_info[] = $total_rows;
                return 
    $the_prod_info;
            }
        }


Similar Threads

  1. Resolved sql query
    By ggalan in forum MySQL and other databases
    Replies: 10
    Last Post: 07-15-2011, 12:50 PM
  2. sql query
    By ggalan in forum PHP
    Replies: 1
    Last Post: 10-26-2010, 08:05 PM
  3. Getting what I need out of a query...
    By ShootingBlanks in forum MySQL and other databases
    Replies: 2
    Last Post: 12-10-2007, 08:16 PM
  4. Query!!!
    By sidarha in forum JavaScript
    Replies: 8
    Last Post: 11-28-2007, 08:09 AM
  5. Help with a query
    By Torin Mai in forum MySQL and other databases
    Replies: 0
    Last Post: 07-11-2007, 07:29 PM

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
  •