SitePoint Sponsor

User Tag List

Results 1 to 2 of 2
  1. #1
    SitePoint Enthusiast MstrBob's Avatar
    Join Date
    Dec 2004
    Location
    New York City
    Posts
    86
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Table for Tallying

    I'll just preface this by saying that, though I am familiar with basic MySQL syntax and commands, I'm not very good at all with general knowledge of database design and/or performance. Know of any good resources?


    My main question is about using a table to keep tally of the amount objects entered by users and a tally of each object's frequency. The problem is that I'll be dealing with usually 20-30 unique objects for a page submission, with repeats of some objects in the submission. Then I have a table with two fields. The object name, which is unique, and the frequency of it appearing. I'll probably be dealing with a couple of tens of thousands of objects, and there will constantly be new ones popping up. So what I'm trying to figure out is, what's the best way to update a page?

    I'll have collected, as I said, 20-30 objects on average and I want to add them to the total tally. For example, let's say I have objects Foo, Bar, Widget. Foo appeared 2 times, Bar appeared 1 time, and Widget appeared 4 times. In my table, I have entries for Foo and Bar, but does not exist (and so needs to be added). Short of Selecting for each row, if it doesn't exist Inserting a new one with 0, and then a 3 query to update the row by adding its current value with the new count. That's 3 queries (or 2) for each one! Surely there must be a more efficient way?

  2. #2
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    read the mysql manual page on the INSERT command. there is a syntax that will help you here...


Bookmarks

Posting Permissions

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