php/mysql help
Results 1 to 10 of 10

Thread: php/mysql help

  1. #1
    Junior Member
    Join Date
    Mar 2009
    Posts
    28

    php/mysql help

    I am trying to find a way to massively descrease the number of mysql queries i performing.

    I currently have an inventory table which is wrote to each time an item is found, it also has to check if the player already has that item, and adds the quantity to that record or if not exists then creates a new record.

    What I want to do instead is store a string within my LSL script and only update the database in bulk.
    I will remove the inventory table and store all the inventory data in a text field in the player table.

    I receive a string of itemID's and quantities from the LSL script:
    itemID,Quantity,itemID,Quantity,itemID,Quantity,itemID,Quantity

    I dont know how many there will be each time..

    Then i will query the DB to get the existing string of inventory.

    I then want to merge the 2 strings where the inventory already exists and add any new ones to the end.

    So basically:

    $LSLstring = 1,1,2,1,3,1,5,1
    $stringFromDB = 1,10,2,1

    $newString = 1,11,2,2,3,1,5,1

    I hope that makes sense!

  2. #2
    Quote Originally Posted by BeanieMan View Post
    I will remove the inventory table and store all the inventory data in a text field in the player table.
    Can a player have more than one item?

  3. #3
    Junior Member
    Join Date
    Mar 2009
    Posts
    28
    Quote Originally Posted by spufi View Post
    Can a player have more than one item?
    Hi, thanks for your reply

    As shown in the above example, yes they can have many items and quantities of each item.

  4. #4
    It would be better to keep the item table as is. If you store each item in the player table, you are needlessly adding data to the player table.

  5. #5
    Pna lbh ernq guvf¿
    Join Date
    Jul 2004
    Location
    Kansas City area
    Posts
    19,410
    Can you show us the structure of your 'inventory' table?

  6. #6
    Junior Member
    Join Date
    Mar 2009
    Posts
    28
    Quote Originally Posted by bradgrafelman View Post
    Can you show us the structure of your 'inventory' table?
    `id`
    `username`
    `itemID`
    `quantity`

  7. #7
    Quote Originally Posted by BeanieMan View Post
    `id`
    `username`
    `itemID`
    `quantity`
    Why is there an id and itemID?

  8. #8
    Pna lbh ernq guvf¿
    Join Date
    Jul 2004
    Location
    Kansas City area
    Posts
    19,410
    Next question is... why are you getting the data from your "LSL script" as a comma-separated list? Why not return an array that makes it easier to work with the data?

    Regardless, couple comments about your inventory table:

    1. What's the point of the `id` column? Wouldn't it make sense for a given user to only have at most one row in that table for each item ID? In other words, the `id` column is superfluous; the information that really identifies a given row in this table is both the username and itemID values.

      As such, unless you've got any reason why the `id` column might actually be useful, I personally would get rid of it and instead make the PRIMARY KEY on the username and itemID columns.
    2. Assuming you did the above, note that you could do this:
      it also has to check if the player already has that item, and adds the quantity to that record or if not exists then creates a new record.
      in a single query, e.g.:

      Code:
      INSERT INTO inventory
          (username, itemID, quantity)
      VALUES
          ('brad', 123, 1)
      ON DUPLICATE KEY UPDATE
          quantity = quantity + 1

  9. #9
    Junior Member
    Join Date
    Mar 2009
    Posts
    28
    Quote Originally Posted by bradgrafelman View Post
    Next question is... why are you getting the data from your "LSL script" as a comma-separated list? Why not return an array that makes it easier to work with the data?

    Regardless, couple comments about your inventory table:

    1. What's the point of the `id` column? Wouldn't it make sense for a given user to only have at most one row in that table for each item ID? In other words, the `id` column is superfluous; the information that really identifies a given row in this table is both the username and itemID values.

      As such, unless you've got any reason why the `id` column might actually be useful, I personally would get rid of it and instead make the PRIMARY KEY on the username and itemID columns.
    2. Assuming you did the above, note that you could do this: in a single query, e.g.:

      Code:
      INSERT INTO inventory
          (username, itemID, quantity)
      VALUES
          ('brad', 123, 1)
      ON DUPLICATE KEY UPDATE
          quantity = quantity + 1
    yes i agree the `id` field is useless, but i did not know about creating a primary key on 2 columns.

    LSL scripting does not have arrays, only lists..

    i did not know of the ON DUPLICATE KEY UPDATE function, so this will cut my queries in half

    Thank you both for your advice

  10. #10
    Quote Originally Posted by BeanieMan View Post
    LSL scripting does not have arrays, only lists..
    Use arrays instead. Strings are made for a string of text as were arrays are made for strings containing sets of data.

    Arrays
    MySQL Tutorial Book
    MySQL 5 Certification book

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

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