PHP PDO how to bind values to varibale contains concatenated string

[kslakhani]

How do i bind values to a variable which is partially processed with diffrent statements and then concatenated using php .= operator

 

below is piece of code

$wher = '';

now I have added few varibles to $wher like

if (!empty($_SESSION['advs']['title']))
{
$wher .= '(';
if (isset($_SESSION['advs']['desc']))
{
$wher .= "(au.description like '%" . $system->cleanvars($_SESSION['advs']['title']) . "%') OR ";
}
$wher .= "(au.title like '%" . $system->cleanvars($_SESSION['advs']['title']) . "%' OR au.id = " . intval($_SESSION['advs']['title']) . ")) AND ";
}

more addition to $wher

if (isset($_SESSION['advs']['buyitnow']))
{
$wher .= "(au.buy_now > 0 AND (au.bn_only = 'y' OR au.bn_only = 'n' && (au.num_bids = 0 OR (au.reserve_price > 0 AND au.current_bid < au.reserve_price)))) AND ";
}

if (isset($_SESSION['advs']['buyitnowonly']))
{
$wher .= "(au.bn_only = 'y') AND ";
}

if (!empty($_SESSION['advs']['zipcode']))
{
$userjoin = "LEFT JOIN " . $DBPrefix . "users u ON (u.id = au.user)";
$wher .= "(u.zip LIKE '%" . $system->cleanvars($_SESSION['advs']['zipcode']) . "%') AND ";
}

now I am using $wher in database SELECT query like

// get total number of records
$query = "SELECT count(*) AS total FROM " . $DBPrefix . "auctions au
" . $userjoin . "
WHERE au.suspended = 0
AND ". $wher . $ora . "
au.starts <= " . $NOW . "
ORDER BY " . $by;

$wher is being used in SQL select query.

My problem is, How do I put placeholders to $wher and bind the values?? 

Edited November 14, 2015 by kslakhani

[Stefany93]

All I see is a big ass query.

 

In PDO Prepared Statements, you bind the values separated with bindParam or bindValue.

 

It is unclear what you are trying to accomplish could you give more details?

[kslakhani]

$query = "SELECT count(*) AS total FROM " . $DBPrefix . "auctions au
" . $userjoin . "
WHERE au.suspended = 0
AND ". $wher . $ora . "
au.starts <= " . $NOW . "
ORDER BY " . $by;

The above query contains $wher.

This $wher is a string containing diff. variables like

$wher .= "(au.description like '%" . $something . "%') OR ";

if (isset($_SESSION['one']))
{
$wher .= "(au.bn_only = " . $x .") AND ";
}

if IF statements is true 

$wher will be like  " (au.description like '%" . $something . "%') OR (au.bn_only = " . $x . " ) AND "

 

So how would I put placeholders and bind values to them

[mac_gyver]

as you are dynamically building the sql query statement, you need to put a place-holder into the sql statement and add the data values as elements in an array.

 

at the end, you would loop over the array of data values and run a bindvalue() statement for each place-holder/value in the array OR if all the values can be treated as strings or quoted-numbers, you can just supply the array as a parameter to the ->execute(...) method

 

for LIKE comparisons, the wild-card % characters must be in with the data value, not in the sql statement.

Edited November 14, 2015 by mac_gyver

[mac_gyver]

here's something else that you can do that will generalize your code. for the dynamic/conditional parts of the sql statement, add the different terms to arrays, then implode the contents of the array using either ' OR ' or ' AND ' as the separator string.

 

for sections where you are producing something1 OR something2 OR ..., you would add each of the something... to an array, then implode the array using ' OR ' to give that part of the sql statement.

 

your overall WHERE clause is a collection of AND'ed terms. you can have a main array that holds each of the individual parts as they are being built, then implode this array using ' AND ' (along with a few ( and ) ) to give the total AND term.

 

here are your snippets of the query showing these methods (untested, could contain typo's) -

$params = array();
$and_terms = array();
$and_terms[] = "au.suspended = 0";

if (!empty($_SESSION['advs']['title']))
{
    $terms = array(); // always initialize (array) variables
    if (isset($_SESSION['advs']['desc']))
    {
        $terms[] = "au.description like ?";
        $params[] = "%{$_SESSION['advs']['title']}%";
    }
    $terms[] = "au.title like ?";
    $terms[] = "au.id = ?";
    $params[] = "%{$_SESSION['advs']['title']}%";
    $params[] = $_SESSION['advs']['title'];    
    $and_terms[] = implode(' OR ', $terms);
}

if (isset($_SESSION['advs']['buyitnow']))
{
    $and_terms[] = "au.buy_now > 0 AND
    (au.bn_only IN('y','n') AND (au.num_bids = 0 OR (au.reserve_price > 0 AND au.current_bid < au.reserve_price)))";
}

if (isset($_SESSION['advs']['buyitnowonly']))
{
    $and_terms[] = "au.bn_only = 'y'";
}

if (!empty($_SESSION['advs']['zipcode']))
{
    $userjoin = "LEFT JOIN " . $DBPrefix . "users u ON (u.id = au.user)";
    $and_terms[] = "u.zip LIKE ?";
    $params[] = "%{$_SESSION['advs']['zipcode']}%";
}

$wher = "(".implode(') AND (',$and_terms).")";

// bind the data in $params here or use $params as a parameter to the ->execute($params) method

Edited November 14, 2015 by mac_gyver

[kslakhani]

@mac_gyver,

First of all thanks for taking your personal attention to my question.

I am converting mysql_ to PDO and am noob to this.

I got your above post. That logic will definately work.

I got one issue though, I am using wrapper/class and that class I need to bind each values seperately means It dosnt use array below is that function 

public function bind($param, $value, $type = null)    {
        if (is_null($type)) {
            switch (true) {
                case is_int($value):
                    $type = PDO::PARAM_INT;
                    break;
                case is_bool($value):
                    $type = PDO::PARAM_BOOL;
                    break;
                case is_null($value):
                    $type = PDO::PARAM_NULL;
                    break;
                default:
                    $type = PDO::PARAM_STR;
            }
        }
        $this->stmt->bindValue($param, $value, $type);
    }

suppose my query is like

 ( SELECT * FROM users WHERE age = :age AND balance >= :balance);

so I do bind the values is like:

$db->bind(':age' , $age);
$db->bind(':balance' , intval($balance));

so How would I bind according to your suggestion

// bind the data in $params here or use $params as a parameter to the ->execute($params) method   

Edited November 17, 2015 by kslakhani

[mac_gyver]

so How would I bind according to your suggestion

 

 

at the end, you would loop over the array of data values and run a bindvalue() statement for each place-holder/value in the array OR if all the values can be treated as strings or quoted-numbers, you can just supply the array as a parameter to the ->execute(...) method

the entries you add to the $params array would need to each be a an array of the set of parameters that your ->bind() method expects. this would be an array containing two or three elements, the place-holder name, the value, and the optional type.

 

when you loop over the $params array, you would call your ->bind() method inside the loop, using the elements from each entry in the $params array. you would need to test if the third, type, parameter has been supplied in the $params array entry it to avoid producing php errors or you could always build the element in the $params array with the third element.

 

if you can use the second suggestion, of supplying the data to the ->execute(....) method and the $params array only contains the values (per the code i posted, using ? place-holders) or it only contains the place-holder name and the values (no type elements), the $params array IS the array you can supply to the ->execute() method as a parameter.

[kslakhani]

@mac_gyver,

Thanks pal, will try it and let you know

[kslakhani]

@mac_gyver,

Thanks mate, That works perfect