Difficulty linking proper $var values and db rows in this PHP foreach statement

Question

I'm trying to insert a PHP function into a foreach loop in order to generate values for each row fetched from the db for the variable $Match.

The db query itself works properly, and the function which assigns values to variable $Match works properly when I test it with hard-coded values, but when I try combining it with the rest of the code in order to use db values it stops working properly. Specifically: 1) It only runs the first IF statement; and 2) If that statement is true, it's adding the same value for every row.

I've uploaded a functional example with hard-coded values to this sandbox http://sandbox.onlinephpfunctions.com/code

Declaring values for test case:

$User_Waist = "26";
$User_Hip = "38";
$Match = Null;
$waistMatch = Null;
$hipMatch = Null;

Query database & fetchAll

$stmt = $conn - > prepare("SELECT * FROM SizeChart WHERE FIND_IN_SET($User_Waist, Waist_Measurement) > 0 OR FIND_IN_SET($User_Hip, Hip_Measurement) > 0;");
$stmt - > bindValue(':Waist_Measurement', $Waist_Measurement, PDO::PARAM_STR);
$stmt - > bindValue(':Hip_Measurement', $Hip_Measurement, PDO::PARAM_STR);
$stmt - > execute();
$rows = $stmt - > fetchAll(PDO::FETCH_ASSOC);

Loop through results

$count = 0;
foreach($rows as $row) {
    $count++;

Adds value to variable $Match

    if (strpos($row['Waist_Measurement'], $User_Waist) !== false) {
        $waistMatch = 'waistFit';
    }
    if (strpos($Hip_Measurement, $User_Hip) !== false) {
        $hipMatch = 'hipFit';
    }
    $Match = $waistMatch.', '.$hipMatch;

Display Results

echo "Size #: ".$row['Size'].";   Fit Matches: ".' '.$Match.";  Waist: ".$row['Waist_Measurement'], "; Hip: ".$row['Hip_Measurement'], ".<br />";

spencer7593 · Accepted Answer · 2014-09-17 18:21:53Z

The SQL text doesn't contain bind placeholders :Waist_Measurement or :Hip_Measurement.

The bindValue calls aren't going to work, since there's no placeholder of the specified name to bind a value to.

Here's an example that uses a bind placeholder named :fum. Note that this string appears both in the SQL text and as an argument to bindValue or bindParam.

$foo = "bar";
$sql = "SELECT fee FROM fi WHERE fo = :fum ";
//                                    ^^^^
$sth = $dbh->prepare($sql);
$sth->bindValue(":fum", $foo, PDO::PARAM_STR);
//               ^^^^ 
$sth->execute();

FOLLOWUP

This is the SQL text in your prepare.

(I notice that there's a semicolon at the end of the SQL text, and that may be causing an error; I normally don't include a trailing semicolon in my SQL text.)

   SELECT *
     FROM SizeChart 
    WHERE FIND_IN_SET($User_Waist, Waist_Measurement) > 0 
       OR FIND_IN_SET($User_Hip, Hip_Measurement) > 0

But the point is that there aren't any bind placeholders in that SQL text. When you do a:

   ->bindValue(":Waist_Measurement",...
                ^^^^^^^^^^^^^^^^^^

That's essentially saying "Hey! There's a string literal ':Waist_Measurement' in the SQL text of the prepared statement", and saying "in place of that string literal, use this value...".

But the thing is, that string literal does not appear in your SQL text. There's no bind placeholder in the statement. (There's not even a placeholder of a different name, I don't see any colon characters anywhere in the SQL.)

I'm surprised that PDO isn't throwing an error. Actually, PDO probably is throwing an error, but your code is ignoring it. If your code isn't going to check the return from prepare, execute, et al. then you can have PDO do the check and throw the exception for you, by specifying an attribute on the connection.

$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

Also...

The inclusion of PHP variables $User_Waist and $User_Hip is a little unusual in a prepared statement. One of the benefits of prepared statements is that variables representing values can be replaced with bind placeholders.

(I'm confused by what you are trying to do, I can't tell you how to fix it.)

I am confused because I have bindValue statements for both Waist_Measurement & Hip_Measurement:-( Would you mind showing me what specifically I should be changing?

Collectives™ on Stack Overflow

Difficulty linking proper $var values and db rows in this PHP foreach statement

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related