When we started testing Drupal, we noticed that the PM module did not list all of the names to which PMs had recently been sent by a user--sometimes as many as HALF of the recipients would not be listed.
Here's the present code:
$result = db_query("
SELECT DISTINCT(name) AS name
FROM {personalmsg} p, {users} u
WHERE p.author = u.uid
AND recipient = '%d'
AND p.timestamp > (UNIX_TIMESTAMP(NOW()) - (3600 * 24 * 30))
ORDER BY name", $user->uid);
What it's TRYING to do, as I understand it, is:
a. scan the personalmsg table for entries where $user is the author
b. and make a list of recipients, getting the actual names from the user table per the ID number
c. for all entries newer than 30 days.
But it is not getting some of the names. As written, we're only getting two of the four test names to which the user has sent PMs.
I'm not sure what the '%d' is doing--dropping it gives me one more name, but that's still one short. Dropping the recentness check does not pick up the last name in the test, either.
Any thoughts?
Curtis
Comments
Rewritten, now seems to work...
I don't know enough SQL to know why the above would not work, but when I tried an INNER JOIN syntax we were able to get the desired results.
I won't post the code here, as it seems too specific to be of much general value, but it will be in the revised PM module I'll release as soon as it's completed.
Curtis