How to bind a parameter once as INT once as STR

[developer707]

Hello,

Recently I am starting to code my scripts in PDO method to manage the information in a mysql database.
In this case I am having an issue how to use the best practice conditions when dealing with a POST int value.

With the posted values I am making a select query, a short version of it is as follows

if( isset($_POST['gjendja_id']) ){
    $gjendja_id     = intval($_POST['gjendja_id']);
    if( $gjendja_id > 0 ){
        $gjendja_id = $_POST['gjendja_id'];
    }else{
        $gjendja_id = 'IS NOT NULL';
    }
}else{
    $gjendja_id     = 'IS NOT NULL';
}

The query that would be executed

try {
    $counter    = 1;
    $response   = '';
    $stmt       = $dbconnection->prepare('SELECT a.dokumenti, a.datafillimit, a.datambarimit, b.programi, c.lloji_diplomes, d.ial, e.akreditimi FROM programet_akreditimet AS a INNER JOIN programet AS b ON a.programi_id = b.id INNER JOIN programet_llojet_diplomave AS c ON b.lloji_diplomes_id = c.id INNER JOIN institucionet AS d ON b.ial_id = d.id INNER JOIN akreditimet_llojet AS e ON a.lloji_akreditimit_id = e.id WHERE a.datambarimit >= CURDATE() AND b.gjendja_id =:gjendja_id AND d.gjendja_id = 1 AND a.trashed = 0 ORDER BY d.ial ASC;');
    $stmt->bindParam(':gjendja_id', $gjendja_id, PDO::PARAM_INT);
    $stmt->execute();
    $result     = $stmt->fetchAll();
    foreach($result as $row){
        $response   .='';
        $counter++;
    }
    $response       = array('error_code' => '0', 'response' => $response);
}catch(PDOException $exception){
    $response       = array('error_code' => '1', 'response' => 'Gabim gjatë kërkimit në databazë.');
    //$exception->getMessage();
}

In this case I would have two scenarios
1- When the value of gjendja_id > 0 the condition would be b.gjendja_id =:gjendja_id(1, 2, 3, 4, ....)
2- When the value of gjendja_id < 1 the condition would be b.gjendja_id =:gjendja_id(IS NOT NULL)

Is any good example how can I deal with such cases?!
Thank you!

[cereal]

Hi,

IS NOT NULL is not a string, it's an expression. So, as far as I know, you cannot bind it into a prepared statement. Do two queries:

if($gjendja_id > 0)
{
    $stmt = $dbconnection->prepare('SELECT a.dokumenti, a.datafillimit, a.datambarimit, b.programi, c.lloji_diplomes, d.ial, e.akreditimi FROM programet_akreditimet AS a INNER JOIN programet AS b ON a.programi_id = b.id INNER JOIN programet_llojet_diplomave AS c ON b.lloji_diplomes_id = c.id INNER JOIN institucionet AS d ON b.ial_id = d.id INNER JOIN akreditimet_llojet AS e ON a.lloji_akreditimit_id = e.id WHERE a.datambarimit >= CURDATE() AND b.gjendja_id =:gjendja_id AND d.gjendja_id = 1 AND a.trashed = 0 ORDER BY d.ial ASC');
    $stmt->bindParam(':gjendja_id', $gjendja_id, PDO::PARAM_INT);
}

// Is not null
else
{
    $stmt = $dbconnection->prepare('SELECT a.dokumenti, a.datafillimit, a.datambarimit, b.programi, c.lloji_diplomes, d.ial, e.akreditimi FROM programet_akreditimet AS a INNER JOIN programet AS b ON a.programi_id = b.id INNER JOIN programet_llojet_diplomave AS c ON b.lloji_diplomes_id = c.id INNER JOIN institucionet AS d ON b.ial_id = d.id INNER JOIN akreditimet_llojet AS e ON a.lloji_akreditimit_id = e.id WHERE a.datambarimit >= CURDATE() AND b.gjendja_id IS NOT NULL AND d.gjendja_id = 1 AND a.trashed = 0 ORDER BY d.ial ASC');
}

$stmt->execute();

[developer707]

Thank you for the fast reply.
This was the way I wanted to avoid :(
I have many other parameters like $gjendja_id, so the only solution is to have many if else conditions for the($stmt = $dbconnection->prepare())?!
I think there must be a way to deal with this kind of query.

[diafol]

Not necessarily. What are all the fields/variables/parameters that you have which require a different query depending on their values? There is a possibility that you can "chunk" up your statement and build it dynamically, but we need to know which fields (and where in the statement) that the variations should take place.

[cereal]

In addition: consider that a bind can be defined in the execute() method, so:

$stmt->execute([':id' => $id]);

You could change the queries to whitelist some expressions and add it as a variable, something like this should work and allow you to define multiple conditions:

$sql = "SELECT * FROM `names` WHERE %s";

if($int > 0)
{
    $condition = "`fname` = :fname";
    $data = [':fname' => 'klaus'];
}

else
{
    $condition = "`fname` IS NOT NULL";
    $data = NULL;
}

$stmt = $db->prepare(sprintf($sql, $condition));
$stmt->execute($data);

Bye!

[diafol]

Ah the old sprintf ploy, eh? +1 sorry can.t give comment as on mobile. Wish Dani would sort that out!

[developer707]

Sorry for my insecurities, but as I am not very good on this topics I got confused.
How can I deal with the query when I have many parameters like gjendja_id, or fname as you called it in your example?
Is the following code the correct way to implement this logic?!

$sql = "SELECT * FROM `names` WHERE %s %s";
if($int > 0)
{
    $condition = "`fname` = :fname";
    $data = [':fname' => 'klaus'];
}
else
{
    $condition = "`fname` IS NOT NULL";
    $data = NULL;
}

if($int2 > 0)
{
    $condition2 = "`fname2` = :fname2";
    $data2 = [':fname2' => 'klaus2'];
}
else
{
    $condition2 = "`fname2` IS NOT NULL";
    $data2 = NULL;
}

$stmt = $db->prepare(sprintf($sql, $condition, $condition2));
$stmt->execute($data, $data2);

What about PDO::PARAM_INT, PDO::PARAM_STR etc, is a big deal to not specify them, at first sight it might cause slower performance.

[cereal]

Almost,

in the query you should write AND/OR between the WHERE clauses, and execute() does not allow two parameters, so you should merge the array, and in this case convert $data = NULL; to $data = []; because otherwise you cannot merge it. So:

Line 1:

$sql = "SELECT * FROM `names` WHERE %s OR %s";

Line 10, 21:

$data = [];
$data2 = [];

Line 25:

$stmt->execute(array_merge($data, $data2));

About bindParam(), it's up to you, I was showing how you can build the query, now if you want something more complex, you can stack the params into an array and loop it, like this:

if($int > 0)
{
    $condition = "`fname` = :fname";
    $data[] = [':fname', 'klaus', PDO::PARAM_STR];
}

else
{
    $condition = "`fname` IS NOT NULL";
    $data[] = [];
}

if($int2 > 0)
{
    $condition2 = "`fname` = :fname2";
    $data[] = [':fname2', 'klaus2', PDO::PARAM_STR];
}

else
{
    $condition2 = "`fname` IS NOT NULL";
    $data[] = [];
}

$stmt = $db->prepare(sprintf($sql, $condition, $condition2));

foreach($data as $k => $v)
    if(count($v) > 0)
        $stmt->bindParam($v[0], $v[1], $v[2]);

$stmt->execute();

However, by using sprintf() the query will always expect two clauses, if you want to make it more dynamic then you have to change that part and build the query with an assignement operator .= or an array and then you implode by space. As example, consider to have an if statement for $int3, but no else statement:

if($int3 > 0)
{
    # ...
}

The origin query does not work anymore because it could be:

$sql = "SELECT * FROM `names` WHERE %s OR %s"; # case 1
$sql = "SELECT * FROM `names` WHERE %s OR %s AND %s"; # case 2

So you could do:

$sql = "SELECT * FROM `names` WHERE";

if($int > 0)
{
    $sql   .= "`fname` = :fname";
    $data[] = [':fname', 'klaus', PDO::PARAM_STR];
}

else
{
    $sql   .= "`fname` IS NOT NULL";
    $data[] = [];
}

if($int2 > 0)
{
    $sql   .= " OR `fname` = :fname2";
    $data[] = [':fname2', 'klaus2', PDO::PARAM_STR];
}

else
{
    $sql   .= " OR `fname` IS NOT NULL";
    $data[] = [];
}

if($int3 > 0)
{
    $sql   .= " AND condition > :condition"
    $data[] = [':condition', 123, PDO::PARAM_INT];
}

But it is still not flexible enough, because if all conditions are like $int3, then it's not sure you have a default for each case and concatenating WHERE AND condition ... will generate a syntax error in your query. To solve this kind of issues you can look at an ORM (Object Relational Model) which allows to build complex queries. Look for example at Propel, Doctrine and RedBean:

• http://propelorm.org/
• http://www.doctrine-project.org/
• http://www.redbeanphp.com/

I have used them and also built my own. And I still prefer writing plain SQL. But mine is just an opinion and for others could be different. Just an end note, when writing statements I find the debugdumpparams() method really useful:

• http://php.net/manual/en/pdostatement.debugdumpparams.php

See if helps you, bye!