Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2002
    Posts
    1

    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?

    Or am I worrying about nothing?

    Any advice is greatly appreciated.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    because mysql does not support subselects or triggers, you have to use 3 statements as you outlined

    but it was a good idea


    rudy
    http://rudy.ca/

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •