Complicated Query

jonno946

Hi there,

I have two tables, a user table, and a post table.

I want to create an option on my site to find posts by people who are currently online. I need to forumulate a MySQL query, that will look in the user table, and select all the users online. I then need to search the post table, and select all the posts that contain the search string, and are by a person is currently online.

I have absolutely no idea how to do this, at first i fought of using some sort of relationship, but im not sure as mysql can handle relationships?

Any help would be great!

Jonathan

mmilano

if you display who's online, then you are probably pulling the users who are online somewhere already.

what i suggest is pulling out the users who's last_visit_time is > 5 minutes ago, .. then use a foreach loop to cycle through the array to find each of their last posts.

perhaps not the most optimal way to go, but that should work.

ultraslacker

Use a join - read up on joins in the mysql manual.

SELECT ....FROM user JOIN post ON (user.user_id = post.user_id AND user.online = 1 AND post.text LIKE '%search_string%')

Lars_Berg

How do you know that a person is online? Is that a column in the user table ? If so the query could be

$query = "select * from post inner join user on post.postedby = user.userid where user.online = 'Yes' and post.message like '%" . $searchString . "%'";

I've made some guesswork about your column names.

If this does not work, you need to supply more information.

Mysql (when not using INNOD😎 can not handle relations but that has nothing to do with the ability to join tables.

KoshNaranek

Shame on me... i use Access to build "inner joins" ... the SQL-Statement is "as usual for M$" not a true SQL-String...
but it helps to get an idea how the query should look like 😃

In your case you need to use a "having"-clause

should be something like " having users.userstatus='online' "

ultraslacker

HAVING is helpful for handling rows after grouping - say if you just want to grab online users who have more than a certain number of posts. But that is not the case here, so Lars' query is correct.

Mine is in postgres syntax.