MySQL query

[bokeh]

I have three tables:CREATE TABLE `users` (
`id` INT NOT NULL AUTO_INCREMENT ,
`user` VARCHAR( 255 ) NOT NULL ,
`ts` INT NOT NULL ,
PRIMARY KEY ( `id` ) ,
UNIQUE (
`user`
)
);


CREATE TABLE `messages` (
`id` INT NOT NULL AUTO_INCREMENT ,
`sender` VARCHAR( 255 ) NOT NULL ,
`message` TEXT NOT NULL ,
`ts` INT NOT NULL ,
PRIMARY KEY ( `id` )
);


CREATE TABLE `sent_messages` (
`user_id` INT NOT NULL ,
`message_id` INT NOT NULL ,
`ts` INT NOT NULL
);I want to select, from the messages table, all the messages that the user has not yet received, and that were not posted by himself. Also is it possible at the same time to update the sent_messages table to reflect the current extraction or does this need to be done with a separate query? `ts` is the time that each row was inserted into the table.

[Sheldon]

$q = "SELECT user,ts FROM users WHERE user = '".$_SESSION['user']."' LINIT 1";
$s= mysql_query($q);
$r = mysql_fetch_assoc($s);

//if the user is = $r['user'];
// the timeset is = $r['ts'];

//pull from message where sender is not user and tsis older that user
$m = "SELECT message FROM messages WHERE sender != '".$r['user']."' and ts <= '".$r['ts']."' ORDER BY message ASC";
$k = mysql_query($m);
$n = mysql_num_rows($k);

echo("<ul>");

while($j = mysql_fetch_assoc($k)){

echo("<li>".$j['message']."</li>");

}

echo("<ul>");

$new_time = time(); //HOW ARE YOU SETTING THE TIME???

$u = "UPDATE users SET ts = '".$new_time."' WHERE user = '".$r['user']."' ";
mysql_query($u);

[bokeh]

Thanks for that but I am trying to do this without multiple queries. I shopuld at least be able to do the first two steps with a cross join but the thirds step I'm not so sure about as it is an INSERT and not a SELECT.

[chazzy]

just because it's not immediately obvious to me, how do you know in the messages table what messages are not for a particular user? provided that messages.sender matches up with users.user, this might work


select m.message FROM messages m, users u WHERE u.user != m.sender and m.id NOT IN (select message_id FROM sent_messages);


Since I know you're using MySQL, you'll need something like 4.1.x>=10 in order for this to work. I think this is what you mean..

As for the insert, you can use the same statement, just prepend INSERT INTO your_table(column_list) to select....

[bokeh]

messages.sender will always be one of users.user. All users are to receive all message sent by other users that they have not received yet.

Just to expand on what is going on this is a "chat" application. On each request I am going to return a list of who is still online and any new messages along with the sender's username. The table structure is not set in stone so if there is a more efficient way I will change.

[bokeh]

select m.message FROM messages m, users u WHERE u.user != m.sender and m.id NOT IN (select message_id FROM sent_messages);


.I don't see where we are telling the query who the current user is.

[chazzy]

You didn't tell me that it had to be a current user, besides that's typically outside of SQL, unless you're talking about Oracle bind variables.

Anyways, then you don't need a join at all! Just look at the messages that aren't this current user and haven't been sent


$user = "put the username here";
$sql = "select m.message FROM messages m WHERE m.sender != '".$user."' and m.id NOT IN (select message_id FROM sent_messages)";


Is this work now?

[bokeh]

Is this work now?Not quite but it was pretty helpful. This is what I have now after looking at your query:SELECT m.message, m.sender
FROM messages m
WHERE m.sender != '$user'
AND m.id NOT
IN (

SELECT message_id
FROM sent_messages sm
WHERE sm.user_id NOT
IN (

SELECT id
FROM users
WHERE user = '$user'
)
)
LIMIT 0 , 30I'm not 100% certain it works properly but it hasn't given a false result yet.As for the insert, you can use the same statement, just prepend INSERT INTO your_table(column_list) to select....I cant prepend because the insert is a result of the extraction. Does it have to be a seperate query, or is it still possible to do all in one hit?

[chazzy]

this is a valid insert dude


INSERT INTO `sent_messages`(`user_id`,`message_id`)
SELECT m.sender,m.message
FROM messages m
WHERE m.sender != '$user'
AND m.id NOT
IN (

SELECT message_id
FROM sent_messages sm
WHERE sm.user_id NOT
IN (

SELECT id
FROM users
WHERE user = '$user'
)
)


Also, you will get slightly faster results if you somehow already have both the user's name and their ID. Or you should serialize and make it so that they all reference either the name or the id, but not both.

[bokeh]

this is a valid insert dudeI don't doubt it is valid syntax, it's just that it inserts a row into sent_messages with values 0,0 and doesn't produce any output.

[chazzy]

are you trying to say that the select returns 0 results?

[bokeh]

Chazzy I think we are at cross purposes. This seems to work flawlessly:SELECT m.message, m.sender
FROM messages m
WHERE m.sender != '$user'
AND m.id NOT
IN (

SELECT message_id
FROM sent_messages sm
WHERE sm.user_id NOT
IN (

SELECT id
FROM users
WHERE user = '$user'
)
)
LIMIT 0 , 30It returns several rows of results. Each row outputs the sender and message. I need the results so I can output them to the UA. If I add the insert line at the start it no longer outputs anything to mysql_fetch (I guess because it is now an INSERT query). It does insert rows into sent items but the fields of the inserted row just contain zeros.

Does this mean I can't output to mysql_fetch and do an insert with the same query?

[chazzy]

yeah, that's the problem with php, no understanding of an object.

you issue 2 queries. one starts with select, the other insert. you display the data in the select and you check that your insert worked. no two ways about it, unfortunately.

i'm a little confused as to the issues with the insert. are the columns the same types?

[bokeh]

you issue 2 queries. one starts with select, the other insert. you display the data in the select and you check that your insert worked. no two ways about it, unfortunately.

i'm a little confused as to the issues with the insert. are the columns the same types?They are not the same types no. The SELECT returns messages.sender (VARCHAR) and messages.message (TEXT). The INSERT fields are sent_messages.user_id (INT) and sent_messages.message_id (INT).

Also if I add the insert line to the top of the select query, mysql_num_rows($result) returns invalid resource, meaning I can't get my hands on the output of the select query.

[chazzy]

then you need to select different columns. it gives you 0,0 because it sees mismatch types.

You need 2 queries, I think you missed that part of my message before. You can't do this with one, inserts are not selects they don't return data.

[bokeh]

Ok, I fully understand the data type and insert issue now.

Would I be right in assuming then that a SELECT is the only type of query that will return data of any kind?

[chazzy]

Yes.

Select reads data out of a database, insert puts data in, delete permanently removes data and update alters data.