PHP PDO Bind Param with Having Clause where aggregate function mixed with variable

Question

I am building an SQL Library for my framework which I am making for a university project. I have finished all the helper methods and I am confused about one thing.

How do I bind a parameter or value in a Having clause where the left hand side argument is an aggregate function and the right hand side argument is a variable?

I have created helper methods which take associative arrays and format them ready for prepared statements so all I have to do is execute,

Please see the last example below with the having clause, when the left hand side argument is an aggregate function and the right hand side is a variable, how could I set a parameter to bind by value/param?

Also as a side question, is it possible to have BOTH bindparam and bind value in the same stmt?

Any advice would be helpful and please do let me know if there is a better way, I know I could always just change it around but I would prefer to have this option

public function test_having()
{
    $query = new Query();

    $query
        ->select('name','age','height')
        ->from('Person')
        ->where('id','=',33)
        ->having('name','>',9867);
    //Works perfectly
    $this->assertEqualsIgnoringCase(
        "SELECT name,age,height FROM Person WHERE id = :id HAVING name > :name ",
        $query->get_query()
    );
}

public function test_having_with_left_hand_side_aggregage_function($value='')
{
    $query = new Query();

    $height = 220;

    $query
        ->select('max(age) as max_age','person')
        ->from('Person')
        ->where('age','>',50)
        ->group_by('max_age, person')
        ->having('max(height)','>',$height);

    $this->assertEqualsIgnoringCase(
        "SELECT max(age) as max_age,person
        FROM Person WHERE age > :age GROUP BY max_age,person
        //Not sure if this is a valid prepared stmt?:
        HAVING count(reviews) > :count(reviews) ",
        $query->get_query()
    );
}

I have a helper method which loops through arrays and passes the key, value, and operand to a method which adds these to an array, Now the parameters are properly set, im just not sure how can I bind a variable to an aggregate method?

protected static function set_stmt($key, $op, $value, $logic_ops='')
{
    if (is_object($value) && $value instanceof Query) {

        $subquery = self::sort_subquery($value);
        $stmt = $subquery['query'];
        $stmt = ($logic_ops != '') ?
        "$key $op $stmt $logic_ops " :
        "$key $op $stmt ";

        $result = [
            'stmt' => $stmt,
            'exec' => $subquery['exec'],
            'params' => $subquery['params']
        ];

    }else{

        $params[$key] = ['key' => ":$key", 'value' => $value];
        $stmt = ($logic_ops != '') ?
        "$key $op :$key $logic_ops " :
        "$key $op :$key ";

        $result = [
            'stmt' => $stmt,
            'params' => $params,
        ];

    }

    return $result;
}

That would make some gibberish that makes no sense neither in SQL or PDO. Why do you need an aggregate function on the right? — Your Common Sense, Apr 11 '20 at 19:45
If I wanted to compare the result of an aggregate function to a variable basically — , Apr 11 '20 at 20:38
How to add a placeholder to your query is explained in the linked answer — Your Common Sense, Apr 11 '20 at 20:39
I already know how to do that as you can see from the code examples I have shown, I just wanted to know if it is possible to give an aggregate function as a placeholder if I was doing foe example: max(height) > $height for example — , Apr 11 '20 at 20:46
If you already know that, then replace a variable with a placeholder — Your Common Sense, Apr 11 '20 at 20:50
Oh okay but usually the variable is replaced by its corresponding key, I guess I have to do bind value instead of bindparam, I will try it — , Apr 11 '20 at 20:51
is it acceptable to have bindvalue for some variables and bindparam for some variables in the same statement? — , Apr 11 '20 at 20:59
Why do you bother with this question? Why not to send all values into execute()? — Your Common Sense, Apr 11 '20 at 21:00
Yes I could do this, I wanted to have the option to use the bindparam/bindvalue method explicitly this way I have more flexibility and I can check datatypes before executing, I read a few articles here that using bindparam explicitly is better practice — , Apr 11 '20 at 22:51
Yes and because I am creating a low level library, I need it to be flexible for all use cases and do not want restrictions, which is why I wanted to do it like this, anyway I think I figured it out now — , Apr 12 '20 at 13:51
you are creating a *high* level library. And because of that you have to give a user choice, whether they are going to bother with one-by-one binding or want to make it smooth sending an array in to execute — Your Common Sense, Apr 12 '20 at 14:02
I think the most confusing aspect is the inserts, for select, update and delete its pretty straight forward — , Apr 12 '20 at 14:10
How's that? I don't see *any* difference from the PDO/SQL point of view. How `$db->run("INSERT INTO t (f1,f2) VALUES (?,?)", [$f1,$f2])` is any different from `$db->run("DELETE FROM t WHERE id=?", [$idf])`? — Your Common Sense, Apr 12 '20 at 14:14
Only difference I see is, what if I am inserting 100 records in the database, or for example 100 users and one email field, I would have to call bindparam 100 times in a loop I guess? With other statements there is usually only one value used for comparison — , Apr 12 '20 at 14:18
You don't have to run bindparam in a loop,that's the exact purpose of this method that you call it only once. with bind value - yes, you will have to call it 100 times. not a big deal though — Your Common Sense, Apr 12 '20 at 14:20
You saved my life with the other question but unfortunately I cant mix positional and named parameters, PDO throws an error — , Apr 16 '20 at 10:49

PHP PDO Bind Param with Having Clause where aggregate function mixed with variable

0 Answers0