Web Design Forum: AdamConder - Viewing Profile - Web Design Forum

Hey, I'm currently developing a website where it's a job application website.

I am currently trying to retrieve messages in a thread system for the messaging system of the website. The problem I'm having is I have jobseekers and employers (different users) stored in two tables, and also a generic users table that link them both.

The current query i'm performing is:

SELECT userid, userEmail, userAvatar, messageid, senderid, dateSent, timeSent, messageContent, favourite, empName, empDepartment, empIndustry, jsForename, jsSurname, jsEmpStatus
			FROM users
			JOIN messages ON (users.userid = messages.senderid)
			JOIN employers ON (users.userid = employers.empid)
                        JOIN jobseekers ON (jobseekers.jsid = users.userid)
			WHERE recipientid = '2'

recipientid is gathered from a session for the user currently logged in, i.e. all the messages sent to that user.
The query works perfectly fine like this:

SELECT userid, userEmail, userAvatar, messageid, senderid, dateSent, timeSent, messageContent, favourite, jsForename, jsSurname, jsEmpStatus
			FROM users
			JOIN messages ON (users.userid = messages.senderid)
                        JOIN jobseekers ON (jobseekers.jsid = users.userid)
			WHERE recipientid = '2'

And visa versa, however, if I add the fourth table it returns no results.
There are results in the database as I've manually added them.

The other option I have tried is by using a union to perform the search:

SELECT userid, userEmail, userAvatar, messageid, senderid, dateSent, timeSent, messageContent, favourite, empName, empDepartment, empIndustry, NULL , NULL , NULL
FROM users
JOIN messages ON ( users.userid = messages.senderid ) 
JOIN employers ON ( users.userid = employers.empid ) 
WHERE recipientid =  '2'
UNION 
SELECT userid, userEmail, userAvatar, messageid, senderid, dateSent, timeSent, messageContent, favourite, NULL , NULL , NULL , jsForename, jsSurname, jsEmpStatus
FROM users
JOIN messages ON ( users.userid = messages.senderid ) 
JOIN jobseekers ON ( jobseekers.jsid = users.userid ) 
WHERE recipientid =  '2'

However, the column names jsForename, jsSurname and jsEmpStatus are returned as null? Therefore I can't target them when filtering results in while statement.

Hey I've been trying to get this to work for a while now:

<?php if ( have_posts() ) : while ( have_posts() ) : the_post(); ?>

<div class="entry">
 <h3><?php the_title(); ?><span class="comment_count"><?php comments_number('No Comments','One Comment','% Comments'); ?></span></h3>

   <?php the_content(); ?>

   <div class="post_details">
   <p>Author: <span class="author"><?php the_author(); ?>.</span></p>
	<p>Posted On: <span class="date_posted"><?php echo get_the_date(); ?></span></p>
   </div>

 </div> <!-- end entry div -->   

   <h3>Comments</h3>

   <?php if($comments) : ?>  
    <ul class="comments"> 
    <?php foreach($comments as $comment) : ?>
		<div class="comment_meta">
		<?php echo get_avatar(); ?>
			<span class="name"><a href="<?php comment_author_link(); ?>"><?php comment_author(); ?></a></span>
			<span class="date"><?php comment_date(); ?></span>
		</div>
            <?php if($comment->comment_approved == '0') : ?>  
                <li class="comment_content"><p>Your comment is awaiting approval.</p></li>  
            <?php endif; ?>  
				<li class="comment_content"><?php comment_text(); ?></li> 
    <?php endforeach; ?>  
    </ul>  
<?php else : ?>  
    <p>No comments currently for this post.</p>  
<?php endif; ?> 

<?php endwhile; else: ?>
<p><?php _e('Sorry, no posts matched your criteria.'); ?></p>
<?php endif; ?>

   <?php comment_form(); ?>]

Can anyone see a fault with the code as all that's ever returned is 'No comments currently for this post.'

Or is this method now depreciated with the use of wp_list_comments(); ?

Thanks, Adam.