Jump to content

SELECT help in CHAT/MAIL


tetecko81sk

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?
Link to comment
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
Link to comment
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
Link to comment
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).
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

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.