Jump to content


Photo

Help For Building This Query.

mysql

  • Please log in to reply
9 replies to this topic

#1 manishk3008

manishk3008

    Newbie

  • New Members
  • Pip
  • 6 posts

Posted 19 November 2012 - 09:54 AM

I have this database table named messages:

global_ref_id int(12)
to int(12)
from int(12)
message text
status int(1)
viewed int(1)

where global_ref_id is id of messages (foreign key from table events), to is user id of user who received message, from is user id of user who send the message.
I want to generate a query which gives me result displaying latest message from each user to a user (say whose user_id is 192) also giving total number of messages from each user to user 192.
Till now I came up with this query to get results:
SELECT
messages.*,events.event_time, COUNT(messages.from) as "Total number of Messages to User"
FROM
messages, events
WHERE
events.global_id = messages.global_ref_id
AND messages.to = 192
GROUP BY messages.from
ORDER BY events.event_time DESC

now problem is that, this query is displaying the oldest message instead of latest message what I need is Latest message.
Using MYSql version:5.5.25a

For easy understanding I have created table in sql fiddle:

http://sqlfiddle.com/#!2/c68dc/7

#2 Muddy_Funster

Muddy_Funster

    Advanced Member

  • Members
  • PipPipPip
  • 2,993 posts

Posted 19 November 2012 - 10:54 AM

if the problem is that the oldest date is coming back per group then selecting the MAX() value of Event_Time per the grouping should help, something like this :
SELECT messages.global_ref_id,
messages.`to`,
messages.`from`,
messages.message,
messages.status,
messages.viewed,
latest_events.last_event,
count(*) as `TOTAL NUMBER OF MESSAGES TO USER`
FROM messages INNER JOIN
 (SELECT global_id, MAX(event_time) as last_event FROM events GROUP BY event_creator)
 as latest_events
 ON(messages.global_ref_id = latest_events.global_id)
WHERE messages.`to` = 192
GROUP BY messages.`from`
ORDER BY latest_events.last_event DESC

However, if it's only displaying in the wrong order, change the DESC in the order by to ASC to reverse which way it is being ordered.
A simple password hash :

function makePass($word=''){
  $dbSalt = '$2a$07$'.substr(hash('whirlpool',$word),0,22);
  $dbPass = crypt($word, $dbSalt);
 return substr($dbPass,12);
}



My SQL/PHP Blog

#3 Muddy_Funster

Muddy_Funster

    Advanced Member

  • Members
  • PipPipPip
  • 2,993 posts

Posted 19 November 2012 - 11:17 AM

I only realised after I posted that I could actualy play with your fiddle (I can't be the only one thinking that sounds wrong...). Being able to use your tables and sample data I came up with this:
SELECT messages.global_ref_id,
messages.`to`,
messages.`from`,
messages.message,
messages.status,
messages.viewed,
events.event_time,
count_table.msg_count as `total Number of messages`
FROM
messages
INNER JOIN
(select max(global_ref_id) as ref_id, count(*) msg_count from messages group by `from`)
as count_table
on (messages.global_ref_id = count_table.ref_id)
INNER JOIN events
on (events.global_id = messages.global_ref_id)
where messages.`to` = 192
order by events.global_id DESC

It preserves the overall message count while only showing the most recent record activity. I hope that's what you are after.
A simple password hash :

function makePass($word=''){
  $dbSalt = '$2a$07$'.substr(hash('whirlpool',$word),0,22);
  $dbPass = crypt($word, $dbSalt);
 return substr($dbPass,12);
}



My SQL/PHP Blog

#4 manishk3008

manishk3008

    Newbie

  • New Members
  • Pip
  • 6 posts

Posted 19 November 2012 - 11:28 AM

That worked like charm!!!
There is one more thing I would like to add to query, that if I can get count of messages which have message.status=1 for each user (by this I meant to get count of unread messages) in the query, can I count multiple things in a single query?

Edited by manishk3008, 19 November 2012 - 11:30 AM.


#5 Muddy_Funster

Muddy_Funster

    Advanced Member

  • Members
  • PipPipPip
  • 2,993 posts

Posted 19 November 2012 - 11:45 AM

Sure you can, to be honest, there isn't a whole lot you can't do with SQL. Give me 10min to have a play with the query again and I'll get back to you (or someone better will have the answer before then...).
A simple password hash :

function makePass($word=''){
  $dbSalt = '$2a$07$'.substr(hash('whirlpool',$word),0,22);
  $dbPass = crypt($word, $dbSalt);
 return substr($dbPass,12);
}



My SQL/PHP Blog

#6 Muddy_Funster

Muddy_Funster

    Advanced Member

  • Members
  • PipPipPip
  • 2,993 posts

Posted 19 November 2012 - 11:58 AM

OK, here you go, this counts the status = 1 as well as the total number of messages. I reffered to it as unread because of what you were saying earlier, but I don't know why you would need a field for status for unread as well as a field for viewed. The key player, asside from the count(*) is the JOIN statement. It's one of the most powerfull tools you get when building queries, you should look into learning more about them if you have the time. Still, here's the code.
SELECT messages.global_ref_id,

messages.`to`,

messages.`from`,

messages.message,

messages.status,

messages.viewed,

events.event_time,

count_table.msg_count as `total Number of messages`,

get_unread.unread_count as `Number of unread messages`

FROM

messages

INNER JOIN

(select max(global_ref_id) as ref_id, count(*) msg_count from messages group by `from`)

as count_table

on (messages.global_ref_id = count_table.ref_id)

INNER JOIN events

on (events.global_id = messages.global_ref_id)



INNER JOIN

(SELECT MAX(global_ref_id) as unread_ref_id, count(*) as unread_count FROM messages WHERE status=1 GROUP BY `from`)

as get_unread

ON (get_unread.unread_ref_id = messages.global_ref_id)



where messages.`to` = 192

order by events.global_id DESC

A simple password hash :

function makePass($word=''){
  $dbSalt = '$2a$07$'.substr(hash('whirlpool',$word),0,22);
  $dbPass = crypt($word, $dbSalt);
 return substr($dbPass,12);
}



My SQL/PHP Blog

#7 manishk3008

manishk3008

    Newbie

  • New Members
  • Pip
  • 6 posts

Posted 19 November 2012 - 01:08 PM

Thanks you build this one perfect too!!!
SQL is like super cool, I really need to do some home work before proceeding with my project.

#8 Muddy_Funster

Muddy_Funster

    Advanced Member

  • Members
  • PipPipPip
  • 2,993 posts

Posted 20 November 2012 - 03:42 AM

Glad I could help. SQL is a lot more powerfull that it looks on the surface. I wouold also like to say that, for the future, you should get a list of MySQL reserved words and either print it off or bookmark it. These are words that are generaly core commands sich as DATE that, while some front ends will let you use them to create column names in your tables, can cause no end of problems when trying to run queries if you use them outside of their reserved purpose. Good luck, and you know where to find us if you have any more questions.
A simple password hash :

function makePass($word=''){
  $dbSalt = '$2a$07$'.substr(hash('whirlpool',$word),0,22);
  $dbPass = crypt($word, $dbSalt);
 return substr($dbPass,12);
}



My SQL/PHP Blog

#9 manishk3008

manishk3008

    Newbie

  • New Members
  • Pip
  • 6 posts

Posted 21 November 2012 - 03:42 PM

I only realised after I posted that I could actualy play with your fiddle (I can't be the only one thinking that sounds wrong...). Being able to use your tables and sample data I came up with this:

SELECT messages.global_ref_id,
messages.`to`,
messages.`from`,
messages.message,
messages.status,
messages.viewed,
events.event_time,
count_table.msg_count as `total Number of messages`
FROM
messages
INNER JOIN
(select max(global_ref_id) as ref_id, count(*) msg_count from messages group by `from`)
as count_table
on (messages.global_ref_id = count_table.ref_id)
INNER JOIN events
on (events.global_id = messages.global_ref_id)
where messages.`to` = 192
order by events.global_id DESC

It preserves the overall message count while only showing the most recent record activity. I hope that's what you are after.



I tested this query on original database, there is one problem that it shows only 2 results only no matter what should be the number of rows it should return.

#10 Muddy_Funster

Muddy_Funster

    Advanced Member

  • Members
  • PipPipPip
  • 2,993 posts

Posted 21 November 2012 - 06:58 PM

Then you'll need to let us see a more accurate dataset. if the structure is the same then it must be something to do with how the data is grouped. You did change the where messages.to = to the appropriate value yeah?
A simple password hash :

function makePass($word=''){
  $dbSalt = '$2a$07$'.substr(hash('whirlpool',$word),0,22);
  $dbPass = crypt($word, $dbSalt);
 return substr($dbPass,12);
}



My SQL/PHP Blog




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users

Cheap Linux VPS from $5
SSD Storage, 30 day Guarantee
1 TB of BW, 100% Network Uptime

AlphaBit.com