SitePoint Sponsor

User Tag List

Results 1 to 7 of 7

Thread: PHP and MySQL

  1. #1
    SitePoint Member
    Join Date
    Jul 2000
    Location
    Odense - Denmark
    Posts
    24
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Exclusive access to MySQL database in PHP

    Hi!

    I was just thinking(yehr right...) the other day. On my webpage I have a discussionforum. The database keeps track of the total number of posts and the number of headline-posts("toplevel threads"). And when I add a new post I use these numbers for identification of the posts.
    This poses the following problem:
    When I retrieve the numbers from the database and when I add the post to the database there could be the possibility that another php-thread(ie. another user) does the same thing. And then the numbers will change before the new post is inserted into the database and the numbers will be wrong.

    Should I do anything to prevent this myself, or does MySQL do some sort of mutual exclusion itself?

    My thoughts on solving the problem are:
    1. Make some sort of semaphore-based exclusive right access system.
    2. Write the whole databse command in one line to MySQL and hope that it only make one request at a time.

    Am I shooting birds with cannons here?(can you even say that in english?? :)
    And whats the best solution(read: the most easy to implement!)?


  2. #2
    Your Lord and Master, Foamy gold trophy Hierophant's Avatar
    Join Date
    Aug 1999
    Location
    Lancaster, Ca. USA
    Posts
    12,305
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    MySQL allows you to have one "AutoIncrement" field per table. This field is usually used as the Primary Key which makes sure that two records can't have the same number. The syntax for creating the AutoIncrement field is:
    Code:
    CREATE TABLE IF NOT EXISTS table_name (
      id INTEGER UNSIGNED NOT NULL AUTOINCREMENT PRIMARY KEY,
      field2...
      field3...
      field4...
      etc...
    To allow MySQL to automatically increment that field just use a '0' or a 'NULL' when you insert the record.
    Wayne Luke
    ------------


  3. #3
    SitePoint Wizard TWTCommish's Avatar
    Join Date
    Aug 1999
    Location
    Pittsburgh, PA, USA
    Posts
    3,910
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Sorry to "butt in"...just two quick questions:

    - I'm assuming "INTEGER" is the same as "INT"?

    - What's "UNSIGNED" do?

    Thanks.

  4. #4
    Your Lord and Master, Foamy gold trophy Hierophant's Avatar
    Join Date
    Aug 1999
    Location
    Lancaster, Ca. USA
    Posts
    12,305
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    INT is shorthand for Integer. Most programming systems allow this. I was not sure if MySQL did or not so therefore used the whole word.

    By making a number field unsigned you cannot use negative numbers. For article ID's, customer number and other such catalogging systems negative numbers just make things messy and are harder to program around. Basically this resets the lowest number available in a 4 byte integer which is -2147483647 or (-(2^32)/2)+1 to zero. This allows you to have twice as many positive numbers i.e. 4294967296 instead of 2147483648.
    Wayne Luke
    ------------


  5. #5
    SitePoint Wizard TWTCommish's Avatar
    Join Date
    Aug 1999
    Location
    Pittsburgh, PA, USA
    Posts
    3,910
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I see...interesting...thanks!

    I thought INT was short for INTEGER...just wanted to make sure.

  6. #6
    SitePoint Author Kevin Yank's Avatar
    Join Date
    Apr 2000
    Location
    Melbourne, Australia
    Posts
    2,571
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Originally posted by madsen
    When I retrieve the numbers from the database and when I add the post to the database there could be the possibility that another php-thread(ie. another user) does the same thing. And then the numbers will change before the new post is inserted into the database and the numbers will be wrong.
    The other posters have correctly pointed out that an autoincrement column would tell MySQL to manage the indexes, ensuring that they are always unique and consistent. I thought I'd respond to the more technical side of your post, though.

    Should I do anything to prevent this myself, or does MySQL do some sort of mutual exclusion itself?
    MySQL does its own mutual exclusion in that each query may be assumed to be an atomic operation. If you use an UPDATE query, for example, to increase a value by one using SET colName = colName + 1, you don't have to worry about some other query changing the value of colName after your UPDATE reads the old value, but before it writes the new value.

    Multiple queries, on the other hand, are not guaranteed to be atomic operations. In cases where you want to have exclusive access to one or more tables while completing a set of queries, you need to use a LOCK TABLES query first, then an UNLOCK TABLES query when you're done. This issue is discussed in detail in Part 9 of my MySQL/PHP Article Series (see the section: LOCKing TABLES.

    Hope this answers your question!

    -Kev.
    Kevin Yank
    CTO, sitepoint.com
    I wrote: Simply JavaScript | BYO PHP/MySQL | Tech Times | Editize
    Baby’s got back—a hard back, that is: The Ultimate CSS Reference

  7. #7
    SitePoint Member
    Join Date
    Jul 2000
    Location
    Odense - Denmark
    Posts
    24
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for the replies!

    Specially the lock-table thing. It will proberly solve my problem without changing too much of my exsisting code


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
  •