Unanswered: Counting Rows and inserting result into a new table
I'm a beginner with PHP and MySQL, and as a means to learning it, I'm creating a simple message board application.
It has two tables: 'boards' which stores information about each of the boards, and 'messages' which stores the messages. A board_id column is used to link the two tables.
When displaying the messages, I need to know how many total messages have been posted to the requested board. This is so that I can calculate page numbers and only SELECT the messages I need for that page.
One way of doing this is by counting the messages every time a page is viewed, using SELECT COUNT(*).... Then, using LIMIT and so on, I can SELECT a portion of the messages from the table. This doesn't strike me as a particularly efficient way of doing it, though.
What I'd like to do is have a total_number_of_messages column in the 'boards' table which keeps a record of the number of messages currently posted to the board. Every time a message is posted or removed, this value is altered accordingly.
This is where I'm having trouble. I could use three separate MySQL statements: one to INSERT the new message, one to SELECT COUNT(*)... the total number of messages and the other to UPDATE the 'boards' table with the new total_number_of_messages value. This seems a little inefficient to me, too.
Is there anyway I can integrate the 3 statements into one? Or maybe into 2 statements: one to INSERT the message and only 1 other to COUNT and UPDATE the database?