Jump to content


Photo

SELECT help in CHAT/MAIL


  • Please log in to reply
5 replies to this topic

#1 tetecko81sk

tetecko81sk
  • Members
  • PipPip
  • Member
  • 11 posts

Posted 22 September 2006 - 04:25 PM

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?


#2 nickspick

nickspick
  • New Members
  • Pip
  • Newbie
  • 2 posts

Posted 22 September 2006 - 05:21 PM

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:

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


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

NW

#3 tetecko81sk

tetecko81sk
  • Members
  • PipPip
  • Member
  • 11 posts

Posted 26 September 2006 - 06:55 AM

I rebuild my table and it works great but i have other question:


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     


please how to display exactly this?
it means to diplay time_rposta of the last message

              last msg       | by  | nr of msgs
-----------------------------------------------
 2006-09-14 11:33:37  | hu   | 12          <-- this count number of messages sent to and from hu
 2006-09-12 11:23:59  | hula | 4           <-- this count number of messages sent to and from hula
-----------------------------------------------

i use array so please do not use AS
this is my script
SELECT time_rposta, id_from,COUNT(*) FROM rpdb_user GROUP  BY id_from

which works great! THX NICKSPICK

#4 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 26 September 2006 - 06:00 PM

Where is the sent to column?
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#5 tetecko81sk

tetecko81sk
  • Members
  • PipPip
  • Member
  • 11 posts

Posted 03 October 2006 - 11:26 AM

Where is the sent to column?


sent_to column is not important because every user has own table


#6 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 03 October 2006 - 02:48 PM

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).
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users