stanhook's avatar

Removing Quotes to use in SQL Statement

I have a query:

WHERE tblSU.Site = :id AND tblSU.SUTypCode != 'GEN' AND tblSU.SUNum >= :scope AND tblSU.SUNum <= :endNumber

And then I run it:

$studyUnitsSql = DB::getPdo()->prepare($studyUnitsSql);
        $studyUnitsSql->setFetchMode(\PDO::FETCH_OBJ);
        $studyUnitsSql->execute([
            'id' => $id,
            'scope' => $scope,
            'endNumber' => $endNumber
        ]);
        $studyUnits = collect($studyUnitsSql->fetchAll());

This issue is that $id needs quotes but $scope and $endNumber also get passed with quotes ("100") and I need them without quotes(100) while retaining the quotes for $id. How do I do that?

I need my SQL to work like:

tblSU.SUNum >= 100 AND tblSU.SUNum <= 200

If I have quotes around the numbers I do not get the correct results.

Thanks!

0 likes
6 replies
Snapey's avatar

try casting:

            'id' => (string) $id,
            'scope' => (int) $scope,
            'endNumber' => (int) $endNumber
stanhook's avatar

@snapey That didn't work. I did notice this:

#items: array:137 [▶] 
#escapeWhenCastingToString: false

That happens whether I do what you suggested or not.

tykus's avatar

@stanhook in that case you already have a Collection with 137 items in it - your query has returned its resultset.

stanhook's avatar

@tykus Yes. But those aren't the correct results. I need those numbers without the quotes.

tblSU.SUNum >= 100 AND tblSU.SUNum <= 200

If I have quotes arond the numbers I do not get the correct results.

tykus's avatar
tykus
1 year ago
Best Answer
Level 104

@stanhook oh, okay. Did you try bindValue to set the type?

$studyUnitsSql->setFetchMode(\PDO::FETCH_OBJ);
$sql->bindValue('id', $id, PDO::PARAM_STR);
$sql->bindValue('scope', $id, PDO::PARAM_INT);
$sql->bindValue('endNumber', $endNumber, PDO::PARAM_INT);
$studyUnitsSql->execute();
stanhook's avatar

@tykus Brilliant! I had to make two small changes and it works.

$studyUnitsSql->bindValue('id', $id, \PDO::PARAM_STR);
$studyUnitsSql->bindValue('startNumber', $startNumber, \PDO::PARAM_INT);
$studyUnitsSql->bindValue('endNumber', $endNumber, \PDO::PARAM_INT);

Add the backslash so it would find the class and update the variables because I changed them.

Thanks!!

Please or to participate in this conversation.