Identifying why identical query returns result in phpMyAdmin but not when run in PHP

Question

Current problem is that the following SQL query finds and returns a result when run through the phpMyAdmin SQL page, but does not return any results when run through a PHP script using PDO. (as in no matching result is found)

Problematic query:

SELECT `p_id` FROM `p_desc` WHERE `lang_id` = 1 AND (`p_name` LIKE '%nål%' OR `p_desc` LIKE '%nål%')

I'm suspecting that it has something to do with either collation or the character set being used, as not using any special characters gives the expected result.

The database is using latin1_swedish_ci collation. I've tried setting PDO::MYSQL_ATTR_INIT_COMMAND to "SET NAMES 'latin1' COLLATION 'latin1_swedish_ci', and also tried using utf8 or utf8mb4 accompanied with utf8_unicode_ci etc.

PDO Creation:

$dsn = "mysql:host=" . self::$serverHost . 
          ";dbname=" . self::$databaseName . 
         ";charset=" . self::$charset;

$options = [
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::ATTR_EMULATE_PREPARES   => false,
    PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES \'' . self::$charset . '\'  COLLATE \'' . self::$collation . '\'',
];
$connection = new PDO($dsn, self::$username, self::$password, $options);

Where I execute the query:

$conn = Database::getConnection();
$stmt = $conn->prepare('SELECT `p_id` FROM `p_desc` WHERE `lang_id` = ' . $langID . ' AND (`p_name` LIKE \'%' . $searchText . '%\' OR `p_desc` LIKE \'%' . $searchText . '%\')');
$stmt->execute();
$matchingIDs = $stmt->fetchAll();

As stated above, running the query in phpMyAdmin returns matching results as expected, but running the query in the PHP script returns no matching results.

No error messages are generated.
The queries are identical.

Keep in mind that I am kind of new to PHP and MySQL and may have done some things incorrectly, and any input is appreciated.

taekwondoalex · Answer 1 · 2019-01-30T14:34:27.583

0

I would suggest using bindParam, you can read more about this in the php manual with some really good examples in the comments below.

Regarding your above script, you could try this:

$conn = Database::getConnection();

$stmt = $conn->prepare('SELECT `p_id` FROM `p_desc` WHERE `lang_id` = :langID AND (`p_name` LIKE :searchText1 OR `p_desc` LIKE :searchText2)');

$stmt->bindParam(':langID', $langID);
$stmt->bindParam(':searchText1', '%' . $searchText . '%');
$stmt->bindParam(':searchText2', '%' . $searchText . '%');

$stmt->execute();
$matchingIDs = $stmt->fetchAll();

edited Jan 30 '19 at 14:34

answered Jan 30 '19 at 12:51

taekwondoalex

386
4
12

Unfortunately using bindParam is not possible when having multiple instances of the same "binding". This means that using :searchText more than once (which is what I'm doing) results in PDO throwing a "Invalid Parameter number" exception. Using string concatenation is a workaround of this issue. – Kasper H Jan 30 '19 at 13:05
Ah ok, well you can always have :searchText1 and :searchText2 and bind it twice. I will edit my answer. – taekwondoalex Jan 30 '19 at 14:33

Identifying why identical query returns result in phpMyAdmin but not when run in PHP

1 Answers1