Parameterised SQL query using LIKE not working in PHP with PDO

General PHP Help
#1

I have a parameterised SQL PDO query using LIKE in a php script that I can’t get to work.
The original MYSQL version of the script works fine.
And I can get parameterised SQL PDO queries without LIKE to work.
So I think it’s a syntax problem with PDO around the LIKE keyword.
Any help much appreciated!

The parameters are coming from two arrays:

$levels = array('A1', 'A2', 'B1', 'B2', 'C1');
$grades = array('Merit', 'Pass', 'Fail');

The query is

$query1 = "SELECT COUNT(totals.Grade) 
	   FROM totals
	   WHERE totals.Grade=:grade  
	   AND totals.ExamNumber LIKE :level";

Query processing is:

$result1 = $db->prepare($query1);
$result1->bindParam(':grade', $grade);
$result1->bindParam(':level', "%{$level}%");
$result1->execute();

I’ve tried many variants on the syntax to handle the parameter that needs LIKE. E.g.

totals.ExamNumber LIKE :level in the actual query

and bindValue instead of bindParam

and "%$level%" instead of "%{$level}%"

and different execution:

$result1 = $db->prepare($query1);
$result1->execute(array(':grade' => $grade, ':level' => '%'.$level.'%'));

My results display uses:

$row = $result1->fetch(PDO::FETCH_ASSOC);

if ($row) 
	    {
            foreach ($row as $key => $value) 
	       {
                echo '<td>';
                echo $value;
                echo '</td>';
            }

etc.

The nested loops and the HTML are fine; it’s just that no values are being pulled from the DB to populate the table and cells that the loops generate.

#2

How are the $level and $grade variables being produced from the $levels and $grades arrays? If this whole thing is inside of a loop(s), that is not how to do this. You would write one query that gets the data you want in the order that you want it, then loop over the result from the one query to produce the desired output.

What do some of the ExamNumber column values look like?

Do you have the PDO error mode set to exceptions, so that an actual query error would cause an exception, which if you let php handle, will cause the actual error information to be displayed or logged the same as php errors?

Using a foreach() loop to loop over the single ASSOC element in the fetched row is not how to do this. You should just directly reference the element in $row (for a COUNT(…) expression, you should use an alias name in the query, so that you can reference the value using the alias in the php code.)

#3

Thanks for the feedback!

I don’t have error mode set up. I tried, but will give it another shot.

Regarding array variables and loops, it’s probably better to show you the whole code section. There are actually 2 queries. One for the number of candidates at each level who got a particular grade, another for the % of the total that this number represents. So at A1 you get

A1

A1 : Merit
10 (25%)

A1 : Pass
10 (25%)

A1 : Pass
20 (50%)

And then across the screen for A2, B1 etc.

(The arrays declared at the outset as shown above.)

Then

/*//////////////////////////////////////////////////////////////////////////////////////////////*/
/* DISPLAYS the NUMBER of different grades at each level  */
/*        and loops through levels and grades             */
/*/////////////////////////////////////////////////////////////////////////////////////////////*/

		$k=0; 		 
foreach($levels as $level)
   {
    echo '<div id="'.$level.'_column">
      	  <h2 class="'.$level.'">'.$level.'</h2>';
    	     foreach ($grades[$k] as $grade) 
	   	{
      	 $query1 = "SELECT COUNT(totals.Grade) 
			   	  FROM totals
				  WHERE totals.Grade=:grade  
				  AND totals.ExamNumber LIKE :level";

$result1 = $db->prepare($query1);
$result1->execute(array(':grade' => $grade, ':level' => '%'.$level.'%'));

$row = $result1->fetch(PDO::FETCH_ASSOC);

$query2 = "SELECT (COUNT(Grade)* 100 / (SELECT SUM(IF(`ExamNumber` LIKE :level AND `Grade` IS NOT NULL, 1, 0)) 
	FROM totals)) 
	FROM totals
	WHERE Grade=:grade 
	AND ExamNumber LIKE :level";

$result2 = $db->prepare($query2);

$result2 = $db->prepare($query1);
$result2->execute(array(':grade' => $grade, ':level' => '%'.$level.'%'));

$row2 = $result2->fetch(PDO::FETCH_ASSOC);

echo '<table class="Font3">
      <tr class="Title">
      <td class="width5">'.$levels[$j].' <b>:</b> '.$grades[$k].'</td>
   <tr>';
        
        if ($row && $row2) 
	    {
            foreach ($row as $key => $value) 
	       {
                echo '<td>';
                echo $value;
                echo '</td>';
            }

            foreach ($row2 as $key2 => $value2) 
	       {
                echo '<td> (';
                echo $value2;
                echo ')</td>';
            }
            echo '</tr>';
         }
        echo '</table>';
     } echo '</div>'; $k++;
  } 

echo '</div>
    </body>
   </html>';

This may be not the best way of reading the query result, but I promise it works in deprecated mysql(), and a very similar query/loop structure works with PDO where the query doesn’t use LIKE.

I’m convinced the problem is with the way I handle the parameter with LIKE.

#4

You are not getting the expected results because the - foreach ($grades[$k] as $grade) line is nonsense. Each entry in $grades is a string, not an array. In the latest php version, you would be getting a - Warning: Invalid argument supplied for foreach() … error. In earlier php versions, this may have ‘functioned’, but you would be looping over the letters in “Merit.” That loop should be - foreach ($grades as $grade)

Next, it appears that the ExamNumber values are just A1, A2, … There’s no point in using a LIKE comparison. Just use an = comparison.

Lastly, since you are probably not gong to fix the query(ies), at least receive some performance benefit of using prepped queries, and prepare them once, before the start of any looping, then just execute them inside of the loop, supplying the data values via the ->execute([…]) calls. Note: your code has a mistake in that it is re-preparing the 1st query as the second one, right after it prepared the 2nd one.

#5

Thank you so much!!
I took the [$k] out of the foreach loop and it works!
And that stupid prepare($query1); - I corrected that as well.

Just as a note, the ExamNumber values did need LIKE because an actual number is in the form MN024A1F, GD219B2X etc, where A1, B2 etc specify level. So I needed to capture all numbers with a particular level in them. I didn’t spell everything out because I didn’t want the question to get too long, but I appreciate it wasn’t clear why LIKE was needed in the first place. Sorry!

You’ve been very helpful.

Sponsor our Newsletter | Privacy Policy | Terms of Service