Matthew B Matthew B - 2 months ago 7
MySQL Question

Wrong results when query run in Symfony as opposed to PHPMyAdmin

I have the following code in a controller. The query given runs on a MediaWiki database:

// Grab the connection to the replica database (which is separate from the above)
$conn = $this->get('doctrine')->getManager("replicas")->getConnection();

// Prepare the query and execute
$resultQuery = $conn->prepare( "
SELECT 'id' as source, user_id as value FROM $dbName.user WHERE user_name = :username
UNION
SELECT 'arch' as source, COUNT(*) AS value FROM $dbName.archive_userindex WHERE ar_user_text = :username
UNION
SELECT 'rev' as source, COUNT(*) AS value FROM $dbName.revision_userindex WHERE rev_user_text = :username
UNION
SELECT 'groups' as source, ug_group as value FROM $dbName.user_groups JOIN user on user_id = ug_user WHERE user_name = :username
");

$resultQuery->bindParam("username", $username);
$resultQuery->execute();


(I know my SQL isn't efficient, it's legacy code that I'm working on cleaning up)

The fourth query is the focus of this question. When the query is run in PHPMyAdmin or MySQLWorkbench it returns the correct results. However, when run in Symfony sometimes it returns the improper results. How can I ensure that it returns the correct results?

(Full full code: https://github.com/Matthewrbowker/xtools-rebirth/blob/master/src/AppBundle/Controller/SimpleEditCounterController.php#L95)

Answer

Figured out my question. One thing that wasn't noted was the fact that I wound up selecting the right database name out of a table. This was stored in the variable $dbName. However, it is possible for $dbName to be different than the database that I connected to from paramaters.yml.

Note:

JOIN user on user_id

This portion of the query was missing $dbName. To fix, just add the variable. Like so:

JOIN $dbName.user on user_id
Comments