Combining Search results

[ambo]

Ok Im making a forum there are three Tables forum_cat forum_question forum_answer and i want to show a users most recent posts so a users post could be a question or answer so there is 2 sql commands

<?php 
$sql="SELECT * FROM forum_question WHERE username = '$req_user' ";
$sql2="SELECT * FROM forum_answer WHERE username = '$req_user' ";

that grabs the data is there any way to combine the data and limit the amout show of rows to say 6 the Loop

$result=mysql_query($sql);
while($row = mysql_fetch_array($result)){

echo $row['fname'];
  

[Maq]

Try something like:

 

$sql = "SELECT * FROM forum_question fq LEFT JOIN forum_answer fa ON fq.id = fa.id WHERE fa.username = q_user'  ORDER BY date DESC LIMIT 6";

 

You have to join on the common columns to prevent duplicates.  It also ORDERS BY date DESC (if you have a date field or something similar) so it will grab the most recent posts and finally does LIMIT 6 which only grabs 6 records.  Hope this is what you're looking for.

[ambo]

I think this will work but im haveing trouble under standing your syntax

These are the question and answers tables

Question

   id int(4)        
  topic varchar(255)                 
  detail    
  name varchar(65)         
  datetime varchar(25)              
  view int(4)         
  reply int(4)                
  cat varchar(6)             
      

Answers

  question_id int(4)      
  a_id int(4)             
  a_name varchar(65)                 
  a_email varchar(65)                 
  a_answer                 
  a_datetime varchar(25)                 
  cat varchar(6)

[Maq]

In your first post you were using "username", I don't see that field in either table from your table structure...

[ambo]

Would something like this work

Error
SQL query:  

SELECT * 
FROM forum_answer
WHERE a_name = 'Ryan'
UNION ALL SELECT * 
FROM forum_question
WHERE name = 'Ryan'
LIMIT 0 , 30 

MySQL said:  

#1222 - The used SELECT statements have a different number of columns 

[Maq]

Check out: MySQL - Union

 

I'm not SQL expert but I think something like this should work:

 

(SELECT * FROM forum_answer WHERE a_name = 'Ryan)
UNION ALL
(SELECT * FROM forum_question WHERE name = 'Ryan')
LIMIT 0, 30

[ambo]

It still Returns

MySQL said:  

#1222 - The used SELECT statements have a different number of columns