PHP mysql count

pld

quick question. I have a few tables holding forum post data. Much like this board, I want to be able to tell the user how many new posts have been made since their last visit...

each table holds all the individual post information, along with a datetime of when it was submitted.

So, I basically want to count all of the new posts, in all the different forums to relay that info when they sign in...

so i guess it boils down to: how can I count rows from multiple tables?

I have tried

SELECT (count(distinct table1.id) + count(distinct table2.id)) from table1, table2

and this gives me a good output. but now i want to limit the results according to the datetime versus the users last login datetime from each forum.

any advice ?

bastien

each item entered in the db uses a timestamp hopefully...on each users computer a cookie with a timestamp of the first time they access a site during the session is placed....then the next time a user accesses the site, the cookie is read and the timestamp is then used to count where all timestamps from the posts are greater than that cookie timestamp

pld

Yes, that is pretty much what I want to achieve. I have already logged the users last visit, and each post has a timestamp on it, now how do i count all the posts made since the last visit by the user across all the different forums? (i have each forum broken down into its own db...)

bastien

SELECT (count(distinct table1.id) + count(distinct table2.id)) from table1, table2 where a.timestampfield>$cookietimestamp and b.timestampfield>$cookietimetamp

etc

pld

excellent. i figured out what my earlier problem was. i was using the same syntax, but one of the tables had nothing in it yet, and it was making the results equal 0 🙁

so i have added at least one entry to each table, and now there is no problem!

thanks alot for the help bastien!

pld

except that if any of the AND statements comes out to 0, I get nothing for the entire result, it all becomes zero any thoughts?

pld

still no luck here. the statement:

select (count(distinct table1.id) + count(distinct table2.id)) FROM table1, table2

will return 0 if there is nothing in one of the tables...

would I be better off just using ONE table for up to 6 different forums, and just adding a column to identify which forum the messages belong to?

at the moment, I have 6 different tables, with 1 serving each forum, and I am switching tables thru php when the user navigates through the site....

any thoughts? hints? suggestions? 🙂

Lars_Berg

would I be better off just using ONE table for up to 6 different forums, and just adding a column to identify which forum the messages belong to?

Yes.

(if you have multiple tables you need support for scalar subqueries in order to get the result you wish.)

pld

Thanks Lars, I'll give it a shot from that direction, then!