easy mysql query question

Question

here is the "msg" table on mysql

sent_to  customer   msg      read
-------  --------  ------   -----
45          3       bla       0
34          4        bla       1
34          6        bla       0
45          3        bla       0
56          7        bla       1
45          8        bla       0

for example user whose id number is 45 logs in,

i want him to see this,

you have 2 unread msg to your "number 3" customer
you have 1 unread msg to your "number 8" customer

like a news feed

what query should i use for this ?

thx

Daniel Vassallo · Accepted Answer · 2010-04-21 17:31:54Z

1

You may want to use the following query.

SELECT   CONCAT('You have ', 
                COUNT(`read`), 
                ' unread msg to your number ', 
                customer, 
                ' customer') AS news
FROM     msg 
WHERE    `read` = '0' AND `sent_to` = '45'
GROUP BY customer;

Note that read is a reserved word in MySQL, so you have to enclose it in backticks. (Source)

Test case:

CREATE TABLE msg (
    `sent_to`    int,
    `customer`   int,
    `msg`        varchar(10),
    `read`       int
);

INSERT INTO msg VALUES(45, 3, 'bla', 0);
INSERT INTO msg VALUES(34, 4, 'bla', 1);
INSERT INTO msg VALUES(34, 6, 'bla', 0);
INSERT INTO msg VALUES(45, 3, 'bla', 0);
INSERT INTO msg VALUES(56, 7, 'bla', 1);
INSERT INTO msg VALUES(45, 8, 'bla', 0);

Query result:

+-------------------------------------------------+
| news                                            |
+-------------------------------------------------+
| You have 2 unread msg to your number 3 customer |
| You have 1 unread msg to your number 8 customer |
+-------------------------------------------------+
2 rows in set (0.00 sec)

edited Apr 21, 2010 at 17:31

answered Apr 21, 2010 at 17:21

Daniel Vassallo

346k72 gold badges514 silver badges447 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Utku Dalmaz Over a year ago

thanks for great answer, and also giving info about "read", i have a problem, mysql_num_rows gives 0 even though i have "read = 0" in the database ?

darcy · Accepted Answer · 2010-04-21 17:23:16Z

1

SELECT COUNT(read), customer FROM msg WHERE read = 0 AND sent_to = '45' GROUP BY customer;

answered Apr 21, 2010 at 17:23

darcy

4652 silver badges8 bronze badges

Comments

geeko · Accepted Answer · 2010-06-14 19:00:35Z

1

select count(sent_to), customer
from msg 
where read < 1
and sent_to = 45
group by customer

answered Jun 14, 2010 at 19:00

geeko

313 bronze badges

Comments

Sarfraz · Accepted Answer · 2010-04-21 17:20:23Z

0

...

select count(*), customer from msg where read = 0 group by customer

edited Apr 21, 2010 at 17:20

Sarfraz

384k81 gold badges561 silver badges613 bronze badges

answered Apr 21, 2010 at 17:19

El Guapo

5,7917 gold badges58 silver badges88 bronze badges

Collectives™ on Stack Overflow

easy mysql query question

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related