I have not been able to find a suitable answer for why my PDO query isnt working with multiple "like" statements on the same column. When using a Joomla framework i can execute the query successfully .. i created output to show the query here:
Joomla core query function Output
SearchValue: Array (
[0] => Test
[1] => Name
)
Query output from joomla "$query->__toString()" command:
SELECT id,name
FROM portal_users
WHERE name LIKE '%Test%' AND name LIKE '%Name%'
**data Set**
id name
1 Test Name
2 Other name
3 Test Example
**Query Results**
Test Name
But when I create the same query using PDO it does not filter by both parameters and returns no results instead.
PDO submission and Output
SearchValue: Array (
[0] => Test
[1] => Name
)
Query submitted to PDO:
SELECT id, name
FROM portal_users
WHERE name LIKE :var1 AND name LIKE :var2
PDO Data: Array (
[:var1] => %Test%
[:var2] => %Name%
)
**data Set**
id name
1 Test Name
2 Other name
3 Test Example
**Query Results**
No results
I am at a loss to explain the cause of identical queries not working unless the PDO values are treated differently. Any input would be appreciated.
--UPDATE--
I have followed the example and request of user yourcommonsense and created the following code to validate my concern:
// first let's create a table
// note that table is temporary so it won't pollute your database
$createQuery = "CREATE temporary TABLE test_users (id int auto_increment primary key, name varchar(255))";
$db->query($createQuery);
// then fill it with sample data
$insertQuery = "INSERT INTO test_users (name) VALUES ('Test Name'),('Another Name'),('Test Example')";
$db->query($insertQuery);
// now let's see if we have our data back all right
$dataCheck = "SELECT name FROM test_users";
echo "Test Query on temp table:\n$dataCheck\n";
$data = $db->query($dataCheck)->fetchAll(PDO::FETCH_ASSOC);
echo print_r($data,true);
// now let's test with a hardcoded value
$hardCodedQuery = "
SELECT id, name
FROM `test_users`
WHERE name LIKE '%test%' AND name LIKE '%name%'
";
echo "Harcoded Query on temp table:\n$hardCodedQuery\n";
$data = $db->query($hardCodedQuery)->fetchAll(PDO::FETCH_ASSOC);
echo print_r($data,true);
// and finally, with a prepared statement
$preparedQuery = "
SELECT id, name
FROM test_users
WHERE name LIKE :var1 AND name LIKE :var2
";
echo "Prepared Query on temp table:\n$preparedQuery\n";
$stmt = $db->prepare($preparedQuery);
$vars = array(":var1" => '%Test%', ":var2" => '%Name%');
$stmt->execute($vars);
$data = $stmt->fetchAll(PDO::FETCH_ASSOC);
echo print_r($data,true);
I see that he is correct about my understanding as both the hardcoded statement, and the prepared statement in this code snippet do indeed result in the same results i was hoping to get from my original post above. I am still at a loss to explain why the original posted queries are not working in the same way. As stated earlier, the Joomla query and the PDO query I am running have the same structure, but the PDO above returns a successful query of 0 results. I may need to compare table structure? I will keep researching, but any other thoughts would be appreciated.
Oddly enough, the MCVE code running on my actual database works as it should, so i have to see what's different on my actual page vs. the MCVE.
Test Query on production table:
SELECT id, name FROM portal_users LIMIT 3
Array (
[0] => Array ( [id] => 244 [name] => User One )
[1] => Array ( [id] => 261 [name] => User Two )
[2] => Array ( [id] => 262 [name] => User Three )
)
Harcoded Query on temp table:
SELECT `users`.`id`,`users`.`name`
FROM `portal_users` AS `users`
WHERE `users`.`name` LIKE '%silver%' AND `users`.`name` LIKE '%tiger%'
Array (
[0] => Array ( [id] => 7101 [name] => Silver Tiger )
)
Prepared Query on temp table:
SELECT `users`.`id`, `users`.`name`
FROM portal_users AS `users`
WHERE `users`.`name` LIKE :var1 AND `users`.`name` LIKE :var2
Supplied variables for prepared statement:
Array ( [:var1] => %silver% [:var2] => %tiger% )
Array (
[0] => Array ( [id] => 7101 [name] => Silver Tiger )
)
Even when I copy and paste the query my PHP creates it will execute in a SQL query window through Navicat and return results...
SELECT id, name
FROM portal_users
WHERE name LIKE CONCAT('%','sil','%') AND name LIKE CONCAT('%','tig','%')
but when submitted via PHP PDO with the ? and array of variables, it has no results. still researching.
