DISTINCT id

Databases
1

This may sound stupid but I have problems to pull all records(messages) from just 1 ID. The messages are stored in a table berichten


CREATE TABLE IF NOT EXISTS `berichten` (
  `bericht_id` int(11) NOT NULL AUTO_INCREMENT,
  `gebruiker_id` smallint(4) NOT NULL DEFAULT '0',
  `admin_id` smallint(3) NOT NULL DEFAULT '0',
  `bericht` text,
  `verzonden` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `isNieuw` tinyint(1) NOT NULL DEFAULT '1',
  PRIMARY KEY (`bericht_id`),
  KEY `gebruiker_id` (`gebruiker_id`),
  KEY `admin_id` (`admin_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

In the admin section I have a page berichten(messages) which is refreshing every 10 seconds. On this page I first run a query to see if there are any new messages.


SELECT *
  FROM  berichten
WHERE isNieuw = 1;

If there is a new message, the entire message history from that particular user (gebruiker_id) should be forwarded to an available admin


SELECT gebruiker_id,
               bericht,
               verzonden
  FROM  berichten

and here I lost it. I can’t use gebruiker_id in a WHERE clause since it can be any gebruiker. I tried

SELECT DISTINCT gebruiker_id

and

ORDER BY gebruiker_id LIMIT 0,1

But I still get all messages. Maybe I am looking at this to long.

2

skip this query, you don’t need it

just go to the next one like this –

SELECT gebruiker_id
     , bericht
     , verzonden
  FROM berichten
 WHERE isNieuw = 1
3

Hi Rudy. I do need that first query. I don’t need to show just a new message from a certain user, I need to output the new message and the message history from that user! The problem is that no matter what I try can’t limit the output to just one user. Like I said in the opening post I tried DISTINCT (gebruiker_id) but still get all messages and I tried ORDER BY gebruiker_id LIMIT 1, but then the number of messages are limited. What do I need to do to output all messages from just 1 gebruiker_id as described above? Thank you in advance!

4

Could you not call the table twice in the same query?

$sql = "SELECT
	b2.gebruiker_id,
	b2.bericht,
	b2.verzonden
	FROM berichten AS b1
		LEFT JOIN berichten AS b2
			ON b2.gebruiker_id = b1.gebruiker_id
	WHERE b1.isNieuw = 1";
5

Hi Drummin. Thanks for the reply. Do you refer to the first query? The one that checks for new messages? or to the second one?

6

okay, i see that now, it’s in the first line of your first post

how do you know which id you want?

7

You are querying the table WHERE b1.isNieuw = 1 then using left join to get any records ON b2.gebruiker_id = b1.gebruiker_id so it’s getting gebruiker_id for any records where isNieuw = 1 and using that gebruiker_id to grab any records that match gebruiker_id. See how I’ve labeled the querys b1 and b2? Not sure how else to say it.

8

Hi Rudy that is my point. I don’t know a id. Like I said it should be randomly forwarded to any available admin

9

Hi Drummin. I think I understand the principle. When using your query, I indeed don’t need 2 separate queries, but still I get all messages. Let me try to explain it a bit better. To demonstrate what I would like to accomplich I have added some test data to the table berichten as you see below. (To keep it simple, the message text and the delivery date are omitted)


INSERT INTO `berichten` (`bericht_id`, `gebruiker_id`, `isNieuw`) VALUES
(1, 13, 0),
(2, 13, 0),
(3, 12, 0),
(4, 12, 0),
(5, 13, 0),
(6, 13, 0),
(7, 12, 1),
(8, 13, 1);

As you can see are there 8 messages, the last two (7 and 8) are new (isNieuw = 1). Message 7 comes from gebruiker_id 12 and post 8 from gebruiker_id 13. What I’m trying to achieve is that the new message from gebruiker_id 12 plus other posts by gebruiker_id 12 will be redirected to any available admin and the new message from gebruiker_id 13 plus other posts from gebruiker_id 13 to another available admin.

So lets say I am an admin (and I am online) and you are an admin (and you are online as well) I should get the messages from gebruiker_id = 12 and you the messages from gebruiker_id = 13 (or the other way arround ofcource)

Ofcource should the status isNieuw for the last message be set to 0 so they are out of the flow of new messages and won’t be forwarded to other available admins

10

i think you missed the point of my question

i’ll quote you again –

I have problems to pull all records(messages) from just 1 ID
…
I don’t need to show just a new message from a certain user, I need to output the new message and the message history from that user!
since you say “from just 1 ID” and “from that user” may i ask you to clarify which user you want…

or is it perhaps more than one…

or is it all of them who have a new message…

11

Seems he wants only one result OF WHERE isNieuw = 1 and then their matching records.
So my LEFT JOIN sample above will grab all results OF WHERE isNieuw = 1 and then their matching records.
I’ve made a number of tries in a single query without success. Unless someone knows an answer, he might need to resort to a looped query with LIMIT 1 on the first query.

12

okay, that makes sense now, thanks

but LIMIT 1 doesn’t make a lot of sense without an ORDER BY clause

let me turn back to donboe and ask what kind of sequence should be applied to all these messages, so as to pick the top one from, and then do all the messages for that guy

13

Based on my query it would be the date ASC to grab the oldest one.

WHERE b1.isNieuw = 1 ORDER BY b1.verzonden ASC
14 
SELECT gebruiker_id
     , bericht
     , verzonden
  FROM berichten
 WHERE gebruiker_id =
       ( SELECT gebruiker_id
           FROM berichten
          WHERE verzonden =
                ( SELECT MAX(verzonden)
                    FROM berichten
                   WHERE isNieuw = 1 ) )
15

WOW beautiful!!!

Only probably want older one, so I assume we’d go with MIN.

SELECT gebruiker_id
     , bericht
     , verzonden
  FROM berichten
 WHERE gebruiker_id =
       ( SELECT gebruiker_id
           FROM berichten
          WHERE verzonden =
                ( SELECT MIN(verzonden)
                    FROM berichten
                   WHERE isNieuw = 1 ) )

WORKS GREAT ON MY TEST!

THANK YOU

16

Your absolutely right Drummin, that is indeed where I am/was after

That is indeed here I was after Rudy. Thanks a lot. It is as Drummin said beautiful!!!

Yes I wanted to catch the oldes one first indeed so I changed the MAX into MIN. I have added an ORDER BY clause as well

ORDER BY verzonden DESC

and it is working flawlessly

Again Thank you both Rudy and Drummin.:tup: This was doing my head in for days

17

Hi Rudy. I hope I can ask you another question about this query. I need to find a way to prevent that when a new message from a gebruiker_id along with the other message from that same gebruiker id are forwarded to an available moderator/admin, that the same messages won’t be forwarded to another available moderator/admin. I know I could update the table berichten right after the select query to set isNieuw to 0 but then I’m facing another problem. I am using Javascript to refresh the berichten page every x seconds to check for any new messages using your query. So when I update the table straight after your select query, the query won’t give any more results after that amount of seconts so the messages will disapear from the admins page since isNieuw is not longer 1:


SELECT MAX(verzonden)
                    FROM berichten
                   WHERE isNieuw = 1

So I actually would like to set the isNieuw to 0 after the moderator/admin has replied to the message. What would you do in such a case?

18

sorry, too complicated a question, involving application behaviour and user expectations

19

Hi Rudy. I have an admin_id in the table berichten as well. Is there not something I can do with that, for example to run a update query just before the select query in which I update the field admin_id with SESSION admin_id and use the admin_id in your select query someting in the order of:

 "UPDATE berichten SET admin_id =  SESSION.admin WHERE (SELECT * FROM berichten WHERE verzonden = ( SELECT MIN(verzonden) FROM berichten WHERE isNieuw = 1))"

I tried this but that is obiously not working.

20

there must be

i don’t know how to say this, but it’s up to you to decide what you want to do