Jump to content

PHP PDO how to bind values to varibale contains concatenated string


kslakhani
Go to solution Solved by mac_gyver,

Recommended Posts

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 by kslakhani
Link to comment
Share on other sites

$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

Link to comment
Share on other sites

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 by mac_gyver
Link to comment
Share on other sites

  • Solution

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 by mac_gyver
  • Like 1
Link to comment
Share on other sites

@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 by kslakhani
Link to comment
Share on other sites

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.

  • Like 1
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.