php - Using a fallback value from a table with PDO bound parameters/values -

in particular method need fallback particular piece of data in table if variable isn't given. looks this:

public function readdiscountsforcampaign(cd\campaign $ocampaign, $ipricelistid = 0) {   // stuff } 

so if $ipricelistid === 0 want sql query retrieve pricelist channel.default_pricelist_id instead.

with pdo prepared statements can't bind tables names parameters or values there 2 options.

1: interpolate value query string, like:

public function readdiscountsforcampaign(cd\campaign $ocampaign, $ipricelistid = 0) {    //ensure pricelist id integer   $ipricelistid = (int) $ipricelistid;    //create string insert sql query string   $spricelistinsert = $ipricelistid ? $ipricelistid : "channel.default_pricelist_id";    //sql query string   $squery = "select ... select stuff here "           . "where pricelist.pricelist_id = {$spricelistinsert}"; // <-- either (int) or channel.default_pricelist_id    // other stuff, run query , return  } 

2: use sql if on condition

public function readdiscountsforcampaign(cd\campaign $ocampaign, $ipricelistid = 0) {    //ensure pricelist id integer   $ipricelistid = (int) $ipricelistid;    //sql query string   $squery = "select ... select stuff here "           . "where pricelist.pricelist_id = if(:pricelist_id > 0, :pricelist_id, channel.default_pricelist_id)";    //bind ':pricelist_id' $ipricelistid prepared statement   $aboundvalues = array(':pricelist_id' => $ipricelistid);    // other stuff, run query , return  } 

---- edit : updated include tadman's 3rd option ----

3: build query sequentially , bind pricelist_id if required (generally speaking better option 1 no interpolation of values required)

public function readdiscountsforcampaign(cd\campaign $ocampaign, $ipricelistid = 0) {    //create array of bound parameters/values   $aboundvalues = array();    //ensure pricelist id integer   $ipricelistid = (int) $ipricelistid;    //sql query string   $squery = "select ... select stuff here "           . "where pricelist.pricelist_id = ";    //if have pricelist id ...   if($ipricelistid) {     $squery .= ":pricelist_id";     $aboundvalues[':pricelist_id'] = $ipricelistid;   }    // ... if not, fallback default `channel`.`default_pricelist_id`   else {     $squery .= "channel.default_pricelist_id";   }    // other stuff, run query , return  } 

---- end edit ----

now, of these better option..? i'd kinda reason why ;)

you need compose query conditionally, execute bound parameters. don't use string substitution unless literally have no other option, it's extremely risky.

for example, can build query incrementally:

$squery = "select ... pricelist.pricelist_id="; $bind = array();  if (...) {   $squery .= ":pricelist_id";   $bind[':pricelist_id'] = $pricelist_id; } else {   $squery .= "channel.default_pricelist_id"; } 

then can execute $bind array values.