Dynamic SQL and bindParam in PDO

Question

I have a situation like this:

if ( $level == 1 ) {
        $type = '0';
    } elseif ( $level == 2 ) {
        $type = '1';
    } elseif ( $level == 3 ) {
        $type = '(2 or type = 1)'; // This part does not work.
    } elseif ( $level == 10 ){
        $type = '3';
    } else {
        return false;
    }

    $sqlFindParent = 'select count(id) as parent from datable where id = :parent and uType = :type and countryCode = :countryCode';

    $findParent = $conn->prepare($sqlFindParent);
    $findParent->bindParam(':type',$type);

$type is dynamic. When I bind it, it does not work when it comes to elseif ( $level ==3 ) because of the or in there. Sure, I could use in, but do you have a better way to do this using or itself?

it doesn't work because it binds parameter not a statement. I think you should look for some kind of ORM and then bind parameters. — Robert, Jun 18 '13 at 13:46

score 1 · Answer 1 · answered Jun 18 '13 at 13:49

When you bind param, in a simplified way you tell to the database "this is not part of the statement, but a parameter value for it". Your database will not process it as a piece of SQL, but as a value, so you end theoretically up with something like:

select 
  count(id) as parent 
from 
  datable 
where 
  id = '123' 
  and uType = '(2 or type = 1)' 
  and countryCode = 'US'

Note the ' around the value, it just won't work like that, and the solution is indeed to use an IN statement.

Note: in reality, in a prepare/execute execution flow, by the time you bind the params and execute the statement, the database isn't even using the SQL statement string, instead it uses its internal representation of your query. I wrote the example only to make it clearer to you why your code doesn't work, but that's not actually what happens.

score 0 · Answer 2 · answered Jun 18 '13 at 13:52

0

It doesn't work because bindParam as name of method suggests binds params not the statements. The solution I think of is to always bind 2 parms one can never exists.

$sqlFindParent = 'select count(id) as parent from datable where id = :parent and (uType = :type or uType = :type2) and countryCode = :countryCode';

Then change your ifs to switch

 $type = 0; $type2=-1;
 switch($level)
 { 
    case 1:
    $type = 0;
    break;

    case 2:
    $type = 1;
    break;

    case 3:
    $type = 2;
    $type2 = 3;
    break;
 }

and binding

 $findParent->bindParam(':type',$type, PDO::PARAM_INT);
 $findParent->bindParam(':type2',$type2, PDO::PARAM_INT);

When you look at it from logical point of view. You have 2 variables.

In case 1,2

You have OR so when at least 1 variable is true the statement will be true, default value of type2 is always false so statement will be true when first part of it is true. So nothing will change in script.

In case 3

one of statement parts must me true so when it find type or type2 it will return proper result of uest.

answered Jun 18 '13 at 13:52

Robert

19,800
5
55
85

@Lepidosteus An `IN` is the same as an `OR`? – Norman Jun 18 '13 at 13:56
he mentioned he doesn't want to use `IN()` so I posted the other solution. – Robert Jun 18 '13 at 13:57
@Norman: Now let's say tomorrow you have to do it with 3 cases instead of 2. And after that with 50 cases. You code will be un-maintainable. `IN` is made explicitly for doing this, there is no reason not to use it. If you think it is less efficient performance wise, it is not the case. – Lepidosteus Jun 18 '13 at 13:57
WHERE a `IN(2,3,4)` is the same as `WHERE (a = 2 or a = 3 or a = 4)` – Robert Jun 18 '13 at 13:58
I don't think it is less efficient it's rather less readable. – Robert Jun 18 '13 at 13:59
@Robert: yes I know it is the same, but one case (using `IN`) is saying "one of those values, which might have one or more proposition", while with the other one (using `OR` like this) you actually have to force feed him fake values (the -1) to get it to work. It is not less efficient performance wise, but it is less efficient programming wise, that is a loss of time and comprehension. You should not do that (I'm saying this more at Norman than at you). – Lepidosteus Jun 18 '13 at 14:02
The strange thing is, it isn't working with `in`. How is `in` used with `PDO`. – Norman Jun 18 '13 at 14:09
@Norman http://stackoverflow.com/questions/920353/php-pdo-can-i-bind-an-array-to-an-in-condition – Robert Jun 18 '13 at 19:52

Dynamic SQL and bindParam in PDO

2 Answers2