[SOLVED] Help!.. Getting duplicated records from MySql

[dharm]

Hi, i am having trouble with this bit of code. Im trying to get records from two different message tables "Q_Qestion" and "Msg_b". My aim is to display all New messages in Date order. But i seem to be getting all new messages displayed X 3!. Can anyone tell me where im goin wrong? thanks!

 

$q = "SELECT q_questions.id, q_questions.date, q_questions.question, msg_b.id_project_id, msg_b.date_time, msg_b.msg FROM 
".$DBprefix."msg_b, q_questions WHERE q_questions.id2_id2 LIKE '%".$row['id']."' AND(/OR not sure!) msg_b.id3_id3 LIKE '".$row['id']."%'  AND msg_b.isRead='1' ORDER BY msg_b.date_time, q_questions.date";

$r = mysql_query($q); if (mysql_num_rows ($r)==0) {echo "<center>(Currently no messages!)</center><br>";} else { 
while ($rmsg = mysql_fetch_array($r)) {  
echo $rmsg['question'];
} 

 

outputs:

 

message 1

message 1 ( duplicated! this is not needed )

message 1 ( duplicated! this is not needed )

 

message 2

message 2 ( duplicated! this is not needed )

message 2 ( duplicated! this is not needed )

[shoz]

Next time don't create duplicate topics on the board. Your other topic in the PHP-Help forum has been removed.

 

Board Guidelines

 

To answer your question. You are doing a JOIN on tables q_questions and ...msgb but you've provided no JOIN condition. Below I've provided an example of what the query should look like if there is an id in one of the tables that references the other.

 

SELECT
q_questions.id, q_questions.date, q_questions.question,
msg_b.id_project_id, msg_b.date_time, msg_b.msg
FROM 
".$DBprefix."msg_b AS m
INNER JOIN
q_questions AS q
ON
m.questionsrefid=q.id

 

If the tables do not reference each other in any way then perhapsd you're looking for a UNION

eg:

(SELECT idcol, messagecol, date AS d FROM questions AS q WHERE q.text LIKE %searchvar%)
UNION ALL
(SELECT idcol, messagecol, date_time AS d FROM messagetbl AS m WHERE m.text LIKE %searchvar%)
ORDER BY d DESC

 

http://dev.mysql.com/doc/refman/5.0/en/join.html

http://dev.mysql.com/doc/refman/5.0/en/union.html

 

[dharm]

hummm.. will look into that. thx!!