Welcome to the Treehouse Community

Want to collaborate on code errors? Have bugs you need feedback on? Looking for an extra set of eyes on your latest project? Get support with fellow developers, designers, and programmers of all backgrounds and skill levels here with the Treehouse Community! While you're at it, check out some resources Treehouse students have shared here.

Looking to learn something new?

Treehouse offers a seven day free trial for new students. Get access to thousands of hours of content and join thousands of Treehouse students and alumni in the community today.

Start your free trial

PHP

Posted by Eric Drake
Eric Drake
6,339 Points

How can I bind an unknown number of variables to a prepared query statement using PDO?

I am querying a database for multiple values, but I won't know exactly how many values until the user submits the values to my script. These values may be numbers, but I have to force them to be treated as strings. I have tried several things to no avail. The code I've used gives me no errors, but it returns no results either. Running the same query with the values statically entered returns a full result set. Here is the code:

        $db = new VisualDatabase();

        $rmQuery = "select w.BASE_ID as WORKORDER_NUMBER, p.ID as PART_NUMBER, p.DESCRIPTION, w.DESIRED_QTY as DESIRED_PART_QTY, sum(r.QTY_PER) as TOTAL_UNIT_QTY, (w.DESIRED_QTY * SUM(r.QTY_PER)) as TOTAL_ORDER_QTY, p.STOCK_UM, sum(r.ISSUED_QTY) as TOTAL_ISSUED_QTY, p.COMMODITY_CODE, p.PRODUCT_CODE, p.QTY_ON_HAND, p.QTY_ON_ORDER, p.QTY_IN_DEMAND FROM  WORK_ORDER w join REQUIREMENT r on w.BASE_ID = r.WORKORDER_BASE_ID join PART p on r.PART_ID=p.ID where w.LOT_ID=1 and r.WORKORDER_LOT_ID=1 and SUBSTRING(p.DESCRIPTION, 1, 2)='RM' and (";
        for($i=0;$i<count($serials);$i++)
        {
            if($i==0)
            {
                $rmQuery .= 'r.WORKORDER_BASE_ID=:workorder' + $i;
            } else
            {
                $rmQuery .= ' or r.WORKORDER_BASE_ID=:workorder' + $i;
            }
        }
        $rmQuery .= ') group by w.BASE_ID, w.DESIRED_QTY, p.ID, p.DESCRIPTION, p.STOCK_UM, p.COMMODITY_CODE, p.PRODUCT_CODE, p.QTY_ON_HAND, p.QTY_ON_HAND, p.QTY_ON_ORDER, p.QTY_IN_DEMAND order by w.BASE_ID';

        $rmStmt = $db->prepare($rmQuery);
        for($i=0;$i<count($serials);$i++)
        {
            $rmStmt->bindValue('workorder' + $i, $serials[$i], PDO::PARAM_STR);
        }
        $rmStmt->execute();

VisualDatabase is a wrapper class that I used to extend PDO. Can anyone help me see where I'm going wrong in my code?

Eric Drake
Eric Drake
6,339 Points

I found the problem, and it had nothing to do with PDOStatement::bindValue. It had everything to do with the idiotic way the database I'm querying was designed. The attribute, w.LOT_ID, is a varchar datatype! When I added single quotes around the value, I got results out of my query.

Sorry to lead you on a goose chase.

1 Answer

jamesjones21
jamesjones21
9,260 Points
jamesjones21
jamesjones21
9,260 Points

You can use a WHERE IN clause, this acts as an array:

WHERE <column name> IN (:val1, :val2, :val3)

The values the user enters will be placed where :val1 to :val3 is.

https://www.w3schools.com/sql/sql_in.asp

Hope this helps

Eric Drake
Eric Drake
6,339 Points

Thank you for weighing in on my issue. Your suggestion cleaned up my query. I'm still having issues with the PDOStatement::bindValue function. No matter how I designate my variables within my query, bindValue doesn't replace the placeholder with the variable value when the PDOStatement::execute function runs. My result set always comes back empty. If I drop the variable values into the query myself, which I realize is not best practice, the query executes properly, and I get my full result set:

        $db = new VisualDatabase();

        $rmQuery = "select w.BASE_ID as WORKORDER_NUMBER, p.ID as PART_NUMBER, p.DESCRIPTION, w.DESIRED_QTY as DESIRED_PART_QTY, sum(r.QTY_PER) as TOTAL_UNIT_QTY, (w.DESIRED_QTY * SUM(r.QTY_PER)) as TOTAL_ORDER_QTY, p.STOCK_UM, sum(r.ISSUED_QTY) as TOTAL_ISSUED_QTY, p.COMMODITY_CODE, p.PRODUCT_CODE, p.QTY_ON_HAND, p.QTY_ON_ORDER, p.QTY_IN_DEMAND FROM  WORK_ORDER w join REQUIREMENT r on w.BASE_ID = r.WORKORDER_BASE_ID join PART p on r.PART_ID=p.ID where w.LOT_ID=1 and r.WORKORDER_LOT_ID=1 and SUBSTRING(p.DESCRIPTION, 1, 2)='RM' and r.WORKORDER_BASE_ID in (";
        for($i=0;$i<count($serials);$i++)
        {
            $serials[$i] = strval($serials[$i]);
            if($i==0)
            {
                $rmQuery .= "'" . $serials[$i] . "'";
            } else
            {
                $rmQuery .= ", '" . $serials[$i] . "'";
            }
        }
        $rmQuery .= ') group by w.BASE_ID, w.DESIRED_QTY, p.ID, p.DESCRIPTION, p.STOCK_UM, p.COMMODITY_CODE, p.PRODUCT_CODE, p.QTY_ON_HAND, p.QTY_ON_HAND, p.QTY_ON_ORDER, p.QTY_IN_DEMAND order by w.BASE_ID';

        $rmStmt = $db->prepare($rmQuery);
        /*for($i=0;$i<count($serials);$i++)
        {
            $rmStmt->bindValue(($i + 1), $serials[$i], PDO::PARAM_STR);
        }*/
        $rmStmt->execute();

I'd like to find a way to force my variables to be treated as strings and to have the bindValue function actually do what it's supposed to. Do you have any other ideas?

jamesjones21
jamesjones21
9,260 Points
jamesjones21
jamesjones21
9,260 Points

You'll need to bind each variable in their own individual bindParam(), I can take a look when I'm at my laptop so I can get a better understanding of where you are going wrong :) so will reply in the morning for you

Eric Drake
Eric Drake
6,339 Points

I found one goof-ball mistake that I made. I tried to concatenate value names and the for index variable using a '+' sign instead of a period. I fixed that in my code, but I still get an empty result set.

        $db = new VisualDatabase();

        $rmQuery = "select w.BASE_ID as WORKORDER_NUMBER, p.ID as PART_NUMBER, p.DESCRIPTION, w.DESIRED_QTY as DESIRED_PART_QTY, sum(r.QTY_PER) as TOTAL_UNIT_QTY, (w.DESIRED_QTY * SUM(r.QTY_PER)) as TOTAL_ORDER_QTY, p.STOCK_UM, sum(r.ISSUED_QTY) as TOTAL_ISSUED_QTY, p.COMMODITY_CODE, p.PRODUCT_CODE, p.QTY_ON_HAND, p.QTY_ON_ORDER, p.QTY_IN_DEMAND FROM  WORK_ORDER w join REQUIREMENT r on w.BASE_ID = r.WORKORDER_BASE_ID join PART p on r.PART_ID=p.ID where w.LOT_ID=1 and r.WORKORDER_LOT_ID=1 and SUBSTRING(p.DESCRIPTION, 1, 2)='RM' and r.WORKORDER_BASE_ID in (";
        for($i=0;$i<count($serials);$i++)
        {
            $serials[$i] = strval($serials[$i]);
            if($i==0)
            {
                $rmQuery .= ":val" . (string)($i + 1);
            } else
            {
                $rmQuery .= ", :val" . (string)($i + 1);
            }
        }
        $rmQuery .= ') group by w.BASE_ID, w.DESIRED_QTY, p.ID, p.DESCRIPTION, p.STOCK_UM, p.COMMODITY_CODE, p.PRODUCT_CODE, p.QTY_ON_HAND, p.QTY_ON_HAND, p.QTY_ON_ORDER, p.QTY_IN_DEMAND order by w.BASE_ID';

        $rmStmt = $db->prepare($rmQuery);
        for($i=0;$i<count($serials);$i++)
        {
            $rmStmt->bindValue(':val' . (string)($i + 1), $serials[$i], PDO::PARAM_STR);
        }
        $rmStmt->execute();
jamesjones21
jamesjones21
9,260 Points
jamesjones21
jamesjones21
9,260 Points

where is your $serials variable coming from? If I can see that, maybe I can guide you in a direction. Also it may be returning 0 as the for loop cannot iterate, due to the condition of:

$i < count($serials)

As $serials may be null to begin with.

Eric Drake
Eric Drake
6,339 Points

Here is the section of my code that sets the $serials variable:

    $serialsInput = filter_var($_POST['serials'], FILTER_SANITIZE_STRING);
    $serialsInput = preg_replace('/\s+/', ':', $serialsInput);
    $serials = explode(':', $serialsInput);

When I do a var_dump of the $serials array just before the PDOStatement::bindValue call, I get the following output:

array(4) { [0]=> string(5) "45259" [1]=> string(5) "45258" [2]=> string(5) "45369" [3]=> string(5) "45450" }