Jump to content

Archived

This topic is now archived and is closed to further replies.

tetecko81sk

SELECT help in CHAT/MAIL

Recommended Posts

pls can somebody help me with this?

i am logged as user1 and this is my database example

id|from  |to      |msg
----------------------------------
1 |axe    |user1 |sdgfsdgfdsghdf
2 |user1 |axe    |jsdfksdhkfsdhk
3 |axe    |user1 |jksajfksajkl
4 |axe    |hula  |skmfhjsdfsd
5 |hula  |user1 |sufgyfsdgy
-----------------------------------

and i want to display this

chat with | number of messages
-----------------------------------
axe        | 3
hula        | 1
-----------------------------------

what php and mysql command i have to use?

Share this post


Link to post
Share on other sites
It looks like you want the 'number of messages' populated with the number of messages 'from' people.

For starters don't name a column 'from' as its a reserved word. Renaming 'from' to 'sentFrom' the following should do the job:

[color=blue][pre]SELECT
    sentFrom AS chat_with,
    COUNT(*) AS number_of_messages
FROM
    testing
GROUP BY
    sentFrom[/pre][/color]

Using COUNT and GROUP BY, it returns the number of rows grouped into each.

[i]N[/i]W

Share this post


Link to post
Share on other sites
I rebuild my table and it works great but i have other question:

[color=blue]
id_rposta |  id_from | rpmsg              | time_rposta               | status
--------------------------------------------------------------------------------------
      1      |      hu   | gdfghdfhgsfgh  | 2006-09-19 10:48:23 | A   
      2      |      hula | skfhdhkfdjkfjds | 2006-09-12 11:23:18 | A     
      3      |      hula | sasjhafsfask    | 2006-09-12 11:23:37 |  A 
      4      |      hula | hsafhjasjkh      | 2006-09-12 11:23:59 | A     
      5      |      hu   | sahfhask         | 2006-09-14 11:33:37  | A     
      6      |      hu   | skdhkalllfshajjk | 2006-09-14 11:34:30  | A     
      7      |    hu     | sahfhask         | 2006-09-14 11:33:37  | A     
      8      |    hu     | sahfhask         | 2006-09-14 11:33:37  | A     
      9      |    hu     | sahfhask         | 2006-09-14 11:33:37  | A     
     10     |    hu     | sahfhask         | 2006-09-14 11:33:37  | A     
     11     |    hu     | sahfhask         | 2006-09-14 11:33:37  | A     
     12     |    hu     | sahfhask         | 2006-09-14 11:33:37  | A     
     13     |    hu     | sahfhask         | 2006-09-14 11:33:37  | A     
     14     |    hu     | sahfhask         | 2006-09-14 11:33:37  | A     
     15     |    hu     | sahfhask         | 2006-09-14 11:33:37  | A     
     16     |    hu     | sahfhask         | 2006-09-14 11:33:37  | A     
[/color]

please how to display exactly this?
it means to diplay time_rposta of the last message
[color=blue]
              last msg       | by  | nr of msgs
-----------------------------------------------
 2006-09-14 11:33:37  | hu   | 12          <-- this count number of messages sent to and from [b]hu[/b]
 2006-09-12 11:23:59  | hula | 4           <-- this count number of messages sent to and from [b]hula[/b]
-----------------------------------------------
[/color]
i use array so please do not use [color=blue]AS[/color]
this is my script
[color=blue][b]SELECT time_rposta, id_from,COUNT(*) FROM rpdb_user GROUP  BY id_from[/b][/color]

which works great! THX NICKSPICK

Share this post


Link to post
Share on other sites
Where is the sent to column?

Share this post


Link to post
Share on other sites
[quote author=fenway link=topic=109092.msg441876#msg441876 date=1159293616]
Where is the sent to column?
[/quote]

sent_to column is not important because every user has own table

Share this post


Link to post
Share on other sites
I was just asking because your total seems to include both... maybe I missed the point?  Also, there should be no problem using column aliases with "AS", particualrly if you're using the array indicies (which you shouldn't be).

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.