准备好的声明给客户端查询提供不同的结果

Question

Update: If I stop the WHERE clause from parameterizing the second operand so that the query PHP runs is:

SELECT 5c9_dpd_users.id, 5c9_dpd_users.username, 5c9_dpd_users.cms_usergroup_id, 
5c9_dpd_usergroups_cms.usergroup_name, 5c9_dpd_users.email FROM 5c9_dpd_users,    
5c9_dpd_usergroups_cms WHERE 5c9_dpd_usergroups_cms.id = 5c9_dpd_users.cms_usergroup_id
ORDER BY username asc

the correct results are returned. However, if the WHERE clause is parameterized like so:

WHERE 5c9_dpd_usergroups_cms.id = ? ORDER BY username asc

then the incorrect results are returned; it seems to convert the parameterized argument to '5' and return the results that have an ID of 5. Does anybody know why this happens, or how I may find a workaround? Thank you.

Original Post:

I've created a database abstraction layer as part of a wider system I am writing. I am currently using its MySQL implementation and a very strange error is occurring where the query generated by the application runs correctly if I C+P it in the console, but produces incorrect results when parameters are bound etc., through the PHP functions.

Firstly, here is the relevant portion of my DB schema:

Having created some debugging output inside my database class, I am given the following information about the query it produces and executes (with stmt::execute).

SELECT 5c9_dpd_users.id, 5c9_dpd_users.username, 5c9_dpd_users.cms_usergroup_id,
5c9_dpd_usergroups_cms.usergroup_name, 5c9_dpd_users.email FROM 5c9_dpd_users, 
5c9_dpd_usergroups_cms WHERE 5c9_dpd_usergroups_cms.id = ? ORDER BY username asc

Array
(
  [0] => i
  [1] => 5c9_dpd_users.cms_usergroup_id
)

The above shows the generated query as printed to the debugging page, followed by the value (and type) of the placeholdered parameter. Putting the table/column name into the query and running it in the console gives me the following correct output:

As you can just about see, the usergroup_name is different for each usergroup_id. However, the following code does not work:

// I have executed the statement and bound the result up to this point.

self::$stmt->bind_result($test1, $test2, $test3, $test4, $test5);
    while (self::$stmt->fetch()) {
        echo "ID: $test1<br>Username: $test2 <br>Usergroup ID: $test3 Usergroup Name: $test4 <br> Email: $test5<br><br>";
    }

The above code is simplified somewhat but it exemplifies the problem just as well. If you examine the below output from the above code, you will see that the usergroup name remains identical despite the differing usergroup ID.

ID: 7
Username: ADD 
Usergroup ID: 1 Usergroup Name: se 
Email: [e-mail]

ID: 1
Username: New User 
Usergroup ID: 2 Usergroup Name: se 
Email: [e-mail]

ID: 4
Username: test user 
Usergroup ID: 1 Usergroup Name: se 
Email: [e-mail]

ID: 2
Username: User2 
Usergroup ID: 5 Usergroup Name: se 
Email: [e-mail]

ID: 6
Username: username here 
Usergroup ID: 1 Usergroup Name: se 
Email: [e-mail]

ID: 5
Username: xyz 
Usergroup ID: 3 Usergroup Name: se 
Email: [e-mail]

I am completely at a loss! I first thought this to be a problem with my database abstraction class, but I'm not so sure any more since I've essentially removed all abstraction and am still getting the problem. I'm unsure what other information to provide, but if you would like more details on something I will happily provide them.

Answer 1

There is a problem in your query :

SELECT 5c9_dpd_users.id, 5c9_dpd_users.username, 5c9_dpd_users.cms_usergroup_id, 5c9_dpd_usergroups_cms.usergroup_name, 5c9_dpd_users.email
FROM 5c9_dpd_users, 5c9_dpd_usergroups_cms
WHERE 5c9_dpd_usergroups_cms.id = ? ORDER BY username asc

You are never joining your tables on some id's you should do something like this

SELECT 5c9_dpd_users.id, 5c9_dpd_users.username, 5c9_dpd_users.cms_usergroup_id, 5c9_dpd_usergroups_cms.usergroup_name, 5c9_dpd_users.email
FROM 5c9_dpd_users users
LEFT JOIN 5c9_dpd_usergroups_cms cms ON cms.id = users.cms_usergroup_id
WHERE 5c9_dpd_usergroups_cms.id = ? ORDER BY username asc

You have to join your group table and user table see : http://en.wikipedia.org/wiki/Join_%28SQL%29