SELECT help in CHAT/MAIL

[tetecko81sk]

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?

[nickspick]

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

[tetecko81sk]

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

[fenway]

Where is the sent to column?

[tetecko81sk]

[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

[fenway]

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).