Solved

MySQL: How can I return true if a field matches an entry in another table?

Posted on 2010-11-13
7
391 Views
Last Modified: 2012-05-10
This is going to be hard to explain but I have a while loops which loops through results.  The results are of threads that I have on my forum as seen here:
http://www.vizzed.com/vizzedboard/forum.php?id=5

What I want to do is while retrieving the threads from the DB, I want the query to also compare each thread with another table called 'auto_mark_forums_read' which basically checks to see if a user has read that thread and if they haven't, display a New icon.

Below in my code you'll see the query I have which retrieves all of the threads from the DB.
SELECT t.*,u1.name AS name1,u1.sex AS sex1,u1.powerlevel AS power1,u2.name AS name2,u2.sex AS sex2,u2.powerlevel AS power2 FROM threads t,users u1,users u2 WHERE forum=$id AND u1.id=t.user AND u2.id=t.lastposter $subforumtext ORDER BY sticky DESC,lastpostdate DESC

Open in new window

0
Comment
Question by:davideo7
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
7 Comments
 
LVL 13

Assisted Solution

by:AngryBinary
AngryBinary earned 500 total points
ID: 34129379
You will want to join the auto_mark table to your query, and add a conditional column to the SELECT statement. Also, I'd specify the types of joins you use in your queries:

select ..., (a.somecolumn is not null) isRead
from thread t
inner join user u1 on u1.id = t.user
inner join user u2 on u2.id = t.lastposter
left outer join auto_mark_forums_read a on a.thread = t.id
where a.user = $userid and forum = $id
order by ...

So, you'll need to tweak the query to suit your data model, or you can post your table names and columns for a more specific solution. I made the assumption that a row will only exist in the auto_mark_forums_read for a thread and user if the user has read that thread, and that $userid is the id of the currently logged in user.
0
 

Author Comment

by:davideo7
ID: 34129787
Not sure if that made much sense.  Here are the 2 fields from the auto_mark_threads table
forumID       user

0
 
LVL 13

Accepted Solution

by:
AngryBinary earned 500 total points
ID: 34129839
More information would be better. I still don't know how the auto_mark_threads table relates to the thread table - based on the column names, it looks like it contains the ID of a forum, which isn't specific enough to tell if a user has read a particular thread.

So, the key information missing is, how does auto_mark_threads identify a thread. I'm going to take a wild guess for the sake of expediency - "auto_mark_threads.forumID = thread.id" - but if that's wrong, then modify that part of the following query:


SELECT t.*,u1.name AS name1,u1.sex AS sex1,u1.powerlevel AS power1,u2.name AS name2,u2.sex AS sex2,u2.powerlevel AS power2, (a.forumID is NULL) AS isRead
FROM threads t
inner join users u1 on u1.id = t.user
inner join users u2 on u2.id = t.lastposter
left join auto_mark_threads a on (a.forumID = t.id and a.user = $userid)
WHERE forum=$id $subforumtext
ORDER BY sticky DESC,lastpostdate DESC

Try out that query. The result will have a column called "isRead" which will be true if the user has visited the thread and false if the user hasn't. Again, $userid will have to be set to the currently logged in user's id.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:davideo7
ID: 34129996
Ok I know we're getting close.  I'm sorry for the info I didn't give you, I assumed I'd be able to figure out most of it on my own but I was wrong.

Anyways, here's the query, I've made modifications to it.  I also want to point out that they thread would only be marked as read if the lastpostdate of the thread is mor erecent than the date of the auto_mark_threads table, which gets updated each time a user goes to a thread.  Code still doesn't work but it's probably close, it's currently retrieving all the threads but it's showing them all as marked unread.
SELECT t.*,u1.name AS name1,u1.sex AS sex1,u1.powerlevel AS power1,u2.name AS name2,u2.sex AS sex2,u2.powerlevel AS power2, (a.date > t.lastpostdate) AS isRead

FROM threads t
inner join users u1 on u1.id = t.user
inner join users u2 on u2.id = t.lastposter
left join auto_mark_threads a on (a.forumID = t.id and a.user = 1)

WHERE forum=5

ORDER BY sticky DESC,lastpostdate DESC

LIMIT 0,50

Open in new window

0
 
LVL 13

Assisted Solution

by:AngryBinary
AngryBinary earned 500 total points
ID: 34130048
What about the forumID column in auto_mark_threads... is that the id of a thread, or the id for an entire forum? If it's a forum, then you will want to change it to this:

SELECT t.*,u1.name AS name1,u1.sex AS sex1,u1.powerlevel AS power1,u2.name AS name2,u2.sex AS sex2,u2.powerlevel AS power2, (a.date > t.lastpostdate) AS isRead

FROM threads t
inner join users u1 on u1.id = t.user
inner join users u2 on u2.id = t.lastposter
left join auto_mark_threads a on (a.forumID = t.forum and a.user = 1)

WHERE forum=5

ORDER BY sticky DESC,lastpostdate DESC

LIMIT 0,50
0
 

Author Comment

by:davideo7
ID: 34130112
Good catch, it's suppose to be a.threadID

Well that fixed the problem.  Thanks for all your help, it would've taken me many hours to figure this out on my own, you saved me probably a full day worth of work :)
0
 

Author Closing Comment

by:davideo7
ID: 34130113
Thanks so much
0

Featured Post

Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Build an array called $myWeek which will hold the array elements Today, Yesterday and then builds up the rest of the week by the name of the day going back 1 week.   (CODE) (CODE) Then you just need to pass your date to the function. If i…
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

687 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question