equaling the PHP argument and mysql data makes foreach loop not work

Question

When I'm doing this foreach loop perfectly works.

<?php
function checkInDB($NIC){
$user='root';
$pass='123';
$db='mysql:host=localhost;dbname=recruit';
$dbh= new PDO($db,$user,$pass);

$sql = 'SELECT FirstName 
         FROM cv 
           JOIN candidate ON cv.cvID=candidate.cvID 
         WHERE ((submittedDate > NOW() - INTERVAL 365 day) 
           AND (candidate.NIC="906548765V"))';

echo '<h3>candidates</h3>', '<hr />', PHP_EOL;
echo '<table border=1>', PHP_EOL;
foreach ($dbh->query($sql, PDO::FETCH_ASSOC) as $row) {

    echo '<tr><td>', implode('</td><td>', $row), '</td></tr>', PHP_EOL;
}
echo '</table>', PHP_EOL;
}
checkInDB("906548765V");

?>

but when I just changing (candidate.NIC="906548765V") to (candidate.NIC=$NIC) which even makes echo ($NIC==="906548765V"); true, throws an unexpected error

Invalid argument supplied for foreach()

What exactly is happening there?

That is because your string `$sql` is constructed with single quotes and single quote does not interpret variables (see http://stackoverflow.com/questions/3446216/what-is-the-difference-between-single-quoted-and-double-quoted-strings-in-php) — β.εηοιτ.βε, Jun 09 '15 at 18:24

score 5 · Accepted Answer · answered Jun 09 '15 at 18:24

5

In PHP there are 2 kind ways to define a string:

Double quotes
Single quotes

When you use Single quotes, the string will be as defined. When using double quotes, PHP will parse the string.

Then on another note. Never ever include a variable into a query string. Just don't. SQL injection and all that. ALWAYS use prepared statements. You are using PDO, so it's easy:

$stmt = $dbh->prepare('SELECT cols FROM table WHERE col1 = :myCol1Value');
$stmt->bindParam('myCol1Value',$theActualValue);
$stmt->execute();

answered Jun 09 '15 at 18:24

Pinoniq

1,365
9
13

I'm a complete beginner to PHP.So,the problem is not even near in the area I thought as foreach loop! useful suggestion as well. – A Diss Jun 09 '15 at 18:54
True, query does not return an array – Pinoniq Jun 09 '15 at 19:24

score 0 · Answer 2 · edited Jun 09 '15 at 18:30

0

try AND (candidate.NIC="'.$NIC.'") since it's a string you still need the quotes.

edited Jun 09 '15 at 18:30

jeroen

91,079
21
114
132

answered Jun 09 '15 at 18:24

Markus

45
9

Not sure why this is downvoted... It is a valid answer (though it is better to use single-quotes than doubles quotes in MySQL), however Pinoniq is more correct for the best way to do this. – Jacob S Jun 09 '15 at 18:26
1

That's not much of an answer, you should explain why this would solve the problem. – jeroen Jun 09 '15 at 18:29

equaling the PHP argument and mysql data makes foreach loop not work

2 Answers2