This is my query:

$on=@mysql_query("SELECT * FROM messages, whos WHERE (value = '$user' OR value = 'everybody') OR (room = '$room' OR loggedon <= 'time') ORDER BY messid DESC LIMIT 30");

It displays the messages, but displays them twice I can't understand why.

What i'm trying to achieve here is

1) it selects messages sent to everybody and the user privately
2) it shows messages for the room they have logged into
3) only shows messages from the point where they login (doesn't seem to work, im comparing the logged on time with the time of the message stamp)

I've been staring at this for any hour now and can't see what's wrong.

Shouldn't it be an AND not OR between the value, room sections?

$on=@mysql_query("SELECT * FROM messages, whos WHERE (value = '$user' OR value = 'everybody') AND (room = '$room' OR loggedon <= 'time') ORDER BY messid DESC LIMIT 30");

Thanks, seem's to do same as mine, it shows the messages intended for a certain room however it displays each message twice.

If I'm not mistaken, you're doing a theta join of 2 tables - use ANSI join instead, it's more clear to read. Also, what's 'time'? Is it supposed to be unix timestamp?
What are the layouts of your tables? How are you iterrating the resultset from withing php?

Use some visual MySQL tool (such as sqlyog) and run your query there. Also, you could use EXPLAIN SELECT to see how's MySQL understanding your query internally.

maxymizer is right, you need to show not only the query but the code used to display the result if you want to know why it's showing up twice. My (un)educated guess is that it's printing the result for both tables within the same loop, but as lauriate pointed out, the AND should've taken care of that one would think.

The tables have:

whos:

id, username, room, time, loggedon

messages:

id, username, room, style, message, latest, ip, value

my insert command is:

@mysql_query("INSERT INTO messages SET
user = '$uid',
value = '$value',
message = '$msg',
style = '$color',
latest = '$utime',
ip ='$ip',
room='$room'");

I want to tie the two tables together so that when the sql query in the first post checks the latest column which uses

$utime=time();

To grab the time, but whenever i use messages,whos it displays the messages twice but desn't hide messages before the loggedon time.

The code that displays this is as so:

$on=@mysql_query("SELECT * FROM messages WHERE (value = '$user' OR value = 'everybody')

AND room = '$room' ORDER BY messid DESC LIMIT 30");

while ($no=@mysql_fetch_array($on)){

/*
Message Formatting
*/

$message =

preg_replace("/\[url\](http:\/\/)?(([a-z0-9_-]+\.)+[a-z]{2,4}[\/\.\,\?\%a-zA-Z0-9-_\=\;\\\&\*\@\~

\#]*)\[\/url\]/i","<a href=\"\\1\\2\" target=\"_new\">\\1\\2</a>",$message);

$message = strip_tags($message,'<b><img><font><i><a href>');

$message = str_replace(array_keys($codes),array_values($codes),$no['message']);

/*
alternates the colours
*/

$bgcolor = $colorback;

if($no['value'] !='everybody'){

$bgcolor=$pmbgcolor;

}

$words = explode(' ', $message);


foreach($words as $txt)

{

if(strlen($txt) > 64)

{

$message = 'Please do not flood the screen.';

break;
}

}

echo ("<div style=\"background-color:$bgcolor;\"><font face=verdana size=2

color=".$no['style']."><b>".$no['user']."</b> - $message </font> </div> ") ;

}

exit("") ;

maxymizer is right, you need to show not only the query but the code used to display the result if you want to know why it's showing up twice. My (un)educated guess is that it's printing the result for both tables within the same loop, but as lauriate pointed out, the AND should've taken care of that one would think.
make sense
when you match two tables, and you have 2 matches in table A but one match in table B, Mysql just repeats table B matches for each match of table A...

hmmm.. what did say? :nuts:

Now in the code just posted, there is no passive table join... ??

Sorry, just change the the sql query to the one above. I changed it to that so i can continue working on other aspects:

$on=@mysql_query("SELECT * FROM messages, whos WHERE (value = '$user' OR value = 'everybody') OR (room = '$room' OR loggedon <= 'time') ORDER BY messid DESC LIMIT 30");

AND room = '$room' ORDER BY messid DESC LIMIT 30");

while ($no=@mysql_fetch_array($on)){

/*
Message Formatting
*/

$message =

preg_replace("/\[url\](http:\/\/)?(([a-z0-9_-]+\.)+[a-z]{2,4}[\/\.\,\?\%a-zA-Z0-9-_\=\;\\\&\*\@\~

\#]*)\[\/url\]/i","<a href=\"\\1\\2\" target=\"_new\">\\1\\2</a>",$message);

$message = strip_tags($message,'<b><img><font><i><a href>');

$message = str_replace(array_keys($codes),array_values($codes),$no['message']);

/*
alternates the colours
*/

$bgcolor = $colorback;

if($no['value'] !='everybody'){

$bgcolor=$pmbgcolor;

}

$words = explode(' ', $message);


foreach($words as $txt)

{

if(strlen($txt) > 64)

{

$message = 'Please do not flood the screen.';

break;
}

}

echo ("<div style=\"background-color:$bgcolor;\"><font face=verdana size=2

color=".$no['style']."><b>".$no['user']."</b> - $message </font> </div> ") ;

}

exit("") ;

Today 01:11 PM


This is what it would be like with the join.

OK, now I can see what your doing

SELECT * FROM messages, whos WHERE (value = '$user' OR value = 'everybody') AND room = '$room' AND loggedon <= latest

Main changes are
OR to AND

loggedon <= latest
you need to use the label from the messages table not quoted otherwise you will be comparing it to the string 'time'

Unfortunatly that doesn't display anything :(

Thanks tho.

Use ANSI join syntax. Seriously.

I still cannot figure out how you are joining the two tables. Your table structure makes no sense - Why would you store the 'username' and 'room' twice?

It would make a lot more sense to use proper DB design and foreign keys so that you can do something like this:


SELECT m.*, w.*
FROM messages AS m
INNER JOIN whos AS w
ON m.userID=w.userID AND m.room=w.room
WHERE <rest of criteria>


With your existing query and/or design you are getting a cartesian product, or in simple terms if there are 4 rows in 'whos' and 10 in 'messages' you will get 40 results.

Could you expand more on what you mean by proper database design?

My comments before were based on my assumptions from looking at your table structure, which may or may not be correct.

For example: Why do you store usernames in both tables? And rooms? If they are intended to link the two tables together, then you are storing redundant data. What if one user decides to change their name? Then, you have to update both tables, and if you dont you have corruption and inconsistency.

By using foreign keys you can help avoid some of those things. If in fact the usernames and rooms are common between the two tables, you might want to start by restructuring:

messages:
message_id
user_id
other fields ...

whos:
user_id
username
other fields ...

This way, the tables will be linked (up to you, or use InnoDB to enforce constraints) by a common user_id field. Read up on 'database normalization' for more info, this was just a short example. Happy coding :)

After trying what you said I tried to use the inner join and it still doesn't display the messages but according to phpmyadmin theres no errors in the code but i did do explain sql and got this:

Host: localhost:3306
Database: chat
Generation Time: Nov 08, 2005 at 06:23 PM
Generated by: phpMyAdmin 2.6.1-pl3 / MySQL 4.1.10a-nt
SQL-query: EXPLAIN SELECT m.*, w.* FROM messages AS m INNER JOIN whos AS w ON m.user=w.visitor AND m.room=w.visitor WHERE (m.value = '$user' OR m.value = 'everybody') AND (w.room = '$room' OR w.loggedon <= 'm.time') ORDER BY messid DESC LIMIT 30;
Rows: 1

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const table...


I'm gonna keep playing with this but cannot see what is wrong.

This is the select i'm using:

SELECT m.*, w.* FROM messages AS m INNER JOIN whos AS w ON m.user=w.visitor AND m.room=w.visitor WHERE (m.value = '$user' OR m.value = 'everybody') AND (w.room = '$room' OR w.loggedon <= 'm.time') ORDER BY messid DESC LIMIT 30

My query only works after you revise your table structure. I am trying to steer you towards reconsidering your table design (with an example). The query will not work as-is, and to be honest its not a great query considering you wouldnt ever need to select * from BOTH columns when they have a common key :)

If you want to talk off-boards I can help you in a more complete fashion - I was just trying to give you a few examples to get you started.