Professional Web Applications Themes

Conditional INSERT - MySQL

I have a pair of tables. The first one contains information about courses and 2 of its columns are `id` (the id of the course) and `places` (the total number of places available for the course). The second one contains one row per booking. Its columns are `course_id`, `user_id`, `booking_date`. To display to users the list of courses and how many places are left, I use a query like: SELECT SQL_CALC_FOUND_ROWS mybookings.course_id booked, courses.id, (courses.places - COUNT( coursebookings.course_id )) places_remaining FROM `courses` courses LEFT JOIN `course_bookings` coursebookings ON courses.id = coursebookings.course_id LEFT JOIN `course_bookings` mybookings ON mybookings.user_id = 63 AND ...

Sponsored Links
  1. #1

    Default Conditional INSERT

    I have a pair of tables.

    The first one contains information about courses and 2 of its columns are
    `id` (the id of the course) and `places` (the total number of places
    available for the course).

    The second one contains one row per booking. Its columns are `course_id`,
    `user_id`, `booking_date`.

    To display to users the list of courses and how many places are left, I use
    a query like:

    SELECT SQL_CALC_FOUND_ROWS mybookings.course_id booked,
    courses.id, (courses.places - COUNT( coursebookings.course_id ))
    places_remaining
    FROM `courses` courses
    LEFT JOIN `course_bookings` coursebookings ON courses.id =
    coursebookings.course_id
    LEFT JOIN `course_bookings` mybookings ON mybookings.user_id = 63 AND
    mybookings.course_id = courses.id
    GROUP BY courses.id

    The column "booked" tells me if this user has a particular course booked and
    the "63" is changed to match the user making the enquiry on the web site.
    `places_remaining` allows me to show how many places are left and lets me
    use javascript to stop someone trying to book a course where there are no
    places left.

    So far so good. BUT... If 2 or more users look at the list of courses and 2
    or more of them click to book a course with only 1 place left, I need only
    the first insert to course_bookings to succeed and the rest should fail
    because there are now no places left.

    Effectively I need to:

    INSERT INTO `course_bookings` VALUES (thiscourse,myid,null)
    WHERE
    (SELECT places FROM `courses` WHERE id = thiscourse) >
    (SELECT COUNT(course_id ) FROM `course_bookings`)

    I have to admit that this one has got me well stumped. Anyone got any ideas?

    TIA


    Sponsored Links
    Paul Lautman Guest

  2. #2

    Default Re: Conditional INSERT

    Paul Lautman wrote:
    > I have a pair of tables.
    >
    > The first one contains information about courses and 2 of its columns are
    > `id` (the id of the course) and `places` (the total number of places
    > available for the course).
    >
    > The second one contains one row per booking. Its columns are `course_id`,
    > `user_id`, `booking_date`.
    >
    > To display to users the list of courses and how many places are left, I use
    > a query like:
    >
    > SELECT SQL_CALC_FOUND_ROWS mybookings.course_id booked,
    > courses.id, (courses.places - COUNT( coursebookings.course_id ))
    > places_remaining
    > FROM `courses` courses
    > LEFT JOIN `course_bookings` coursebookings ON courses.id =
    > coursebookings.course_id
    > LEFT JOIN `course_bookings` mybookings ON mybookings.user_id = 63 AND
    > mybookings.course_id = courses.id
    > GROUP BY courses.id
    >
    > The column "booked" tells me if this user has a particular course booked and
    > the "63" is changed to match the user making the enquiry on the web site.
    > `places_remaining` allows me to show how many places are left and lets me
    > use javascript to stop someone trying to book a course where there are no
    > places left.
    >
    > So far so good. BUT... If 2 or more users look at the list of courses and 2
    > or more of them click to book a course with only 1 place left, I need only
    > the first insert to course_bookings to succeed and the rest should fail
    > because there are now no places left.
    >
    > Effectively I need to:
    >
    > INSERT INTO `course_bookings` VALUES (thiscourse,myid,null)
    > WHERE
    > (SELECT places FROM `courses` WHERE id = thiscourse) >
    > (SELECT COUNT(course_id ) FROM `course_bookings`)
    >
    > I have to admit that this one has got me well stumped. Anyone got any ideas?
    >
    > TIA
    >
    >
    Just a slightly different approach:

    You first want to set AUTOCOMMIT to 0 to keep an implicit COMMIT from
    prematurely unlocking the tables.

    Lock all the tables you're going to use in your queries, and again check
    for free seats in your course.

    If there are free seats, update the bookings table with the new seat and
    COMMIT the transaction.

    If there are no free seats, issue a ROLLBACK and present an error
    message to the user (you could also COMMIT - no difference since you
    made no changes).

    Either COMMIT or ROLLBACK will unlock the tables.

    Does this help?


    --
    ==================
    Remove the "x" from my email address
    Jerry Stuckle
    JDS Computer Training Corp.
    [email]jstucklexattglobal.net[/email]
    ==================
    Jerry Stuckle Guest

  3. #3

    Default Re: Conditional INSERT

    Jerry Stuckle wrote:
    > Paul Lautman wrote:
    >> I have a pair of tables.
    >>
    >> The first one contains information about courses and 2 of its
    >> columns are `id` (the id of the course) and `places` (the total
    >> number of places available for the course).
    >>
    >> The second one contains one row per booking. Its columns are
    >> `course_id`, `user_id`, `booking_date`.
    >>
    >> To display to users the list of courses and how many places are
    >> left, I use a query like:
    >>
    >> SELECT SQL_CALC_FOUND_ROWS mybookings.course_id booked,
    >> courses.id, (courses.places - COUNT( coursebookings.course_id ))
    >> places_remaining
    >> FROM `courses` courses
    >> LEFT JOIN `course_bookings` coursebookings ON courses.id =
    >> coursebookings.course_id
    >> LEFT JOIN `course_bookings` mybookings ON mybookings.user_id = 63 AND
    >> mybookings.course_id = courses.id
    >> GROUP BY courses.id
    >>
    >> The column "booked" tells me if this user has a particular course
    >> booked and the "63" is changed to match the user making the enquiry
    >> on the web site. `places_remaining` allows me to show how many
    >> places are left and lets me use javascript to stop someone trying to
    >> book a course where there are no places left.
    >>
    >> So far so good. BUT... If 2 or more users look at the list of
    >> courses and 2 or more of them click to book a course with only 1
    >> place left, I need only the first insert to course_bookings to
    >> succeed and the rest should fail because there are now no places
    >> left. Effectively I need to:
    >>
    >> INSERT INTO `course_bookings` VALUES (thiscourse,myid,null)
    >> WHERE
    >> (SELECT places FROM `courses` WHERE id = thiscourse) >
    >> (SELECT COUNT(course_id ) FROM `course_bookings`)
    >>
    >> I have to admit that this one has got me well stumped. Anyone got
    >> any ideas? TIA
    >>
    >>
    >
    > Just a slightly different approach:
    >
    > You first want to set AUTOCOMMIT to 0 to keep an implicit COMMIT from
    > prematurely unlocking the tables.
    >
    > Lock all the tables you're going to use in your queries, and again
    > check for free seats in your course.
    >
    > If there are free seats, update the bookings table with the new seat
    > and COMMIT the transaction.
    >
    > If there are no free seats, issue a ROLLBACK and present an error
    > message to the user (you could also COMMIT - no difference since you
    > made no changes).
    >
    > Either COMMIT or ROLLBACK will unlock the tables.
    >
    > Does this help?
    As I understand it, in order to use COMMIT & ROLLBACK I need to create the
    table using InnoDB rather than MyISAM. Whenever I create a table with
    ENGINE=InnoDB it seems to have worked, but when I go into Operations on or
    Export from phpMyAdmin, it seems to suggest that the table has been created
    using MyISAM.

    This leads me to think that the InnoDB engine is not available, or is it
    posible that phpMyAdmin is fooloing me?


    Paul Lautman Guest

  4. #4

    Default Re: Conditional INSERT

    Paul Lautman wrote:
    > Jerry Stuckle wrote:
    >> Paul Lautman wrote:
    Assuming that I can't use InnoDB, I did think of the following idea.

    Add a column to the courses database to hold the current number of bookings
    (call it `bookings` say).

    Then, to perform a booking:
    1) run the query "update courses set bookings=bookings+1 where
    bookings<places and id = thiscourse"
    2) If the update was successful then insert a record into the bookings
    database
    3) If no rows were updated then the course had already run out of places.

    It won't be as neat having to keep track of the number of bookings in 2
    places (I'd have to decrement the bookings field if a course was cancelled
    as well as deleting the record from the bookings database.

    But would this work OK?


    Paul Lautman Guest

  5. #5

    Default Re: Conditional INSERT

    Paul Lautman wrote:
    > Jerry Stuckle wrote:
    >
    >>Paul Lautman wrote:
    >>
    >>>I have a pair of tables.
    >>>
    >>>The first one contains information about courses and 2 of its
    >>>columns are `id` (the id of the course) and `places` (the total
    >>>number of places available for the course).
    >>>
    >>>The second one contains one row per booking. Its columns are
    >>>`course_id`, `user_id`, `booking_date`.
    >>>
    >>>To display to users the list of courses and how many places are
    >>>left, I use a query like:
    >>>
    >>>SELECT SQL_CALC_FOUND_ROWS mybookings.course_id booked,
    >>>courses.id, (courses.places - COUNT( coursebookings.course_id ))
    >>>places_remaining
    >>>FROM `courses` courses
    >>>LEFT JOIN `course_bookings` coursebookings ON courses.id =
    >>>coursebookings.course_id
    >>>LEFT JOIN `course_bookings` mybookings ON mybookings.user_id = 63 AND
    >>>mybookings.course_id = courses.id
    >>>GROUP BY courses.id
    >>>
    >>>The column "booked" tells me if this user has a particular course
    >>>booked and the "63" is changed to match the user making the enquiry
    >>>on the web site. `places_remaining` allows me to show how many
    >>>places are left and lets me use javascript to stop someone trying to
    >>>book a course where there are no places left.
    >>>
    >>>So far so good. BUT... If 2 or more users look at the list of
    >>>courses and 2 or more of them click to book a course with only 1
    >>>place left, I need only the first insert to course_bookings to
    >>>succeed and the rest should fail because there are now no places
    >>>left. Effectively I need to:
    >>>
    >>>INSERT INTO `course_bookings` VALUES (thiscourse,myid,null)
    >>>WHERE
    >>>(SELECT places FROM `courses` WHERE id = thiscourse) >
    >>>(SELECT COUNT(course_id ) FROM `course_bookings`)
    >>>
    >>>I have to admit that this one has got me well stumped. Anyone got
    >>>any ideas? TIA
    >>>
    >>>
    >>
    >>Just a slightly different approach:
    >>
    >>You first want to set AUTOCOMMIT to 0 to keep an implicit COMMIT from
    >>prematurely unlocking the tables.
    >>
    >>Lock all the tables you're going to use in your queries, and again
    >>check for free seats in your course.
    >>
    >>If there are free seats, update the bookings table with the new seat
    >>and COMMIT the transaction.
    >>
    >>If there are no free seats, issue a ROLLBACK and present an error
    >>message to the user (you could also COMMIT - no difference since you
    >>made no changes).
    >>
    >>Either COMMIT or ROLLBACK will unlock the tables.
    >>
    >>Does this help?
    >
    >
    > As I understand it, in order to use COMMIT & ROLLBACK I need to create the
    > table using InnoDB rather than MyISAM. Whenever I create a table with
    > ENGINE=InnoDB it seems to have worked, but when I go into Operations on or
    > Export from phpMyAdmin, it seems to suggest that the table has been created
    > using MyISAM.
    >
    > This leads me to think that the InnoDB engine is not available, or is it
    > posible that phpMyAdmin is fooloing me?
    >
    >
    The default is ISAM. If InnoDB is installed, you need to specify it
    when you create your table.

    And ISAM doesn't support transactions. But COMMIT and ROLLBACK also
    unlock the table - which is why I recommended them. They won't affect
    transactions (since you don't have any on ISAM), but they shouldn't
    cause an error.

    --
    ==================
    Remove the "x" from my email address
    Jerry Stuckle
    JDS Computer Training Corp.
    [email]jstucklexattglobal.net[/email]
    ==================
    Jerry Stuckle Guest

  6. #6

    Default Re: Conditional INSERT

    Jerry Stuckle wrote:
    > Paul Lautman wrote:
    >> Jerry Stuckle wrote:
    >>
    >>> Paul Lautman wrote:
    >>>
    >>>> I have a pair of tables.
    >>>>
    >>>> The first one contains information about courses and 2 of its
    >>>> columns are `id` (the id of the course) and `places` (the total
    >>>> number of places available for the course).
    >>>>
    >>>> The second one contains one row per booking. Its columns are
    >>>> `course_id`, `user_id`, `booking_date`.
    >>>>
    >>>> To display to users the list of courses and how many places are
    >>>> left, I use a query like:
    >>>>
    >>>> SELECT SQL_CALC_FOUND_ROWS mybookings.course_id booked,
    >>>> courses.id, (courses.places - COUNT( coursebookings.course_id ))
    >>>> places_remaining
    >>>> FROM `courses` courses
    >>>> LEFT JOIN `course_bookings` coursebookings ON courses.id =
    >>>> coursebookings.course_id
    >>>> LEFT JOIN `course_bookings` mybookings ON mybookings.user_id = 63
    >>>> AND mybookings.course_id = courses.id
    >>>> GROUP BY courses.id
    >>>>
    >>>> The column "booked" tells me if this user has a particular course
    >>>> booked and the "63" is changed to match the user making the enquiry
    >>>> on the web site. `places_remaining` allows me to show how many
    >>>> places are left and lets me use javascript to stop someone trying
    >>>> to book a course where there are no places left.
    >>>>
    >>>> So far so good. BUT... If 2 or more users look at the list of
    >>>> courses and 2 or more of them click to book a course with only 1
    >>>> place left, I need only the first insert to course_bookings to
    >>>> succeed and the rest should fail because there are now no places
    >>>> left. Effectively I need to:
    >>>>
    >>>> INSERT INTO `course_bookings` VALUES (thiscourse,myid,null)
    >>>> WHERE
    >>>> (SELECT places FROM `courses` WHERE id = thiscourse) >
    >>>> (SELECT COUNT(course_id ) FROM `course_bookings`)
    >>>>
    >>>> I have to admit that this one has got me well stumped. Anyone got
    >>>> any ideas? TIA
    >>>>
    >>>>
    >>>
    >>> Just a slightly different approach:
    >>>
    >>> You first want to set AUTOCOMMIT to 0 to keep an implicit COMMIT
    >>> from prematurely unlocking the tables.
    >>>
    >>> Lock all the tables you're going to use in your queries, and again
    >>> check for free seats in your course.
    >>>
    >>> If there are free seats, update the bookings table with the new seat
    >>> and COMMIT the transaction.
    >>>
    >>> If there are no free seats, issue a ROLLBACK and present an error
    >>> message to the user (you could also COMMIT - no difference since you
    >>> made no changes).
    >>>
    >>> Either COMMIT or ROLLBACK will unlock the tables.
    >>>
    >>> Does this help?
    >>
    >>
    >> As I understand it, in order to use COMMIT & ROLLBACK I need to
    >> create the table using InnoDB rather than MyISAM. Whenever I create
    >> a table with ENGINE=InnoDB it seems to have worked, but when I go
    >> into Operations on or Export from phpMyAdmin, it seems to suggest
    >> that the table has been created using MyISAM.
    >>
    >> This leads me to think that the InnoDB engine is not available, or
    >> is it posible that phpMyAdmin is fooloing me?
    >>
    >>
    >
    > The default is ISAM. If InnoDB is installed, you need to specify it
    > when you create your table.
    As I said, I specified ENGINE=InnoDB and phpMyAdmin said "Your SQL-query has
    been executed successfully". But when I went into Operations or Exported the
    table, phpMyAdmin was showing the ENGINE as MyISAM.
    > And ISAM doesn't support transactions. But COMMIT and ROLLBACK also
    > unlock the table - which is why I recommended them. They won't affect
    > transactions (since you don't have any on ISAM), but they shouldn't
    > cause an error.
    So are you saying that I can use LOCK TABLES and COMMIT and ROLLBACK with
    MyISAM tables?



    Paul Lautman Guest

  7. #7

    Default Re: Conditional INSERT

    Jerry Stuckle wrote:
    >>>
    >>> Just a slightly different approach:
    >>>
    >>> You first want to set AUTOCOMMIT to 0 to keep an implicit COMMIT
    >>> from prematurely unlocking the tables.
    The manual says:
    If you are using a transaction-safe storage engine (such as InnoDB, BDB, or
    NDB Cluster), you can disable autocommit mode with the following statement:

    SET AUTOCOMMIT=0;
    But since I will be using MyISAM, do I need to do the SET AUTOCOMMIT?


    Paul Lautman Guest

  8. #8

    Default Re: Conditional INSERT

    Jerry Stuckle wrote:
    > Paul Lautman wrote:
    >> Jerry Stuckle wrote:
    >>
    >>> Paul Lautman wrote:
    >>>
    >>>> I have a pair of tables.
    >>>>
    >>>> The first one contains information about courses and 2 of its
    >>>> columns are `id` (the id of the course) and `places` (the total
    >>>> number of places available for the course).
    >>>>
    >>>> The second one contains one row per booking. Its columns are
    >>>> `course_id`, `user_id`, `booking_date`.
    >>>>
    >>>> To display to users the list of courses and how many places are
    >>>> left, I use a query like:
    >>>>
    >>>> SELECT SQL_CALC_FOUND_ROWS mybookings.course_id booked,
    >>>> courses.id, (courses.places - COUNT( coursebookings.course_id ))
    >>>> places_remaining
    >>>> FROM `courses` courses
    >>>> LEFT JOIN `course_bookings` coursebookings ON courses.id =
    >>>> coursebookings.course_id
    >>>> LEFT JOIN `course_bookings` mybookings ON mybookings.user_id = 63
    >>>> AND mybookings.course_id = courses.id
    >>>> GROUP BY courses.id
    >>>>
    >>>> The column "booked" tells me if this user has a particular course
    >>>> booked and the "63" is changed to match the user making the enquiry
    >>>> on the web site. `places_remaining` allows me to show how many
    >>>> places are left and lets me use javascript to stop someone trying
    >>>> to book a course where there are no places left.
    >>>>
    >>>> So far so good. BUT... If 2 or more users look at the list of
    >>>> courses and 2 or more of them click to book a course with only 1
    >>>> place left, I need only the first insert to course_bookings to
    >>>> succeed and the rest should fail because there are now no places
    >>>> left. Effectively I need to:
    >>>>
    >>>> INSERT INTO `course_bookings` VALUES (thiscourse,myid,null)
    >>>> WHERE
    >>>> (SELECT places FROM `courses` WHERE id = thiscourse) >
    >>>> (SELECT COUNT(course_id ) FROM `course_bookings`)
    >>>>
    >>>> I have to admit that this one has got me well stumped. Anyone got
    >>>> any ideas? TIA
    >>>>
    >>>>
    >>>
    >>> Just a slightly different approach:
    >>>
    >>> You first want to set AUTOCOMMIT to 0 to keep an implicit COMMIT
    >>> from prematurely unlocking the tables.
    >>>
    >>> Lock all the tables you're going to use in your queries, and again
    >>> check for free seats in your course.
    >>>
    >>> If there are free seats, update the bookings table with the new seat
    >>> and COMMIT the transaction.
    >>>
    >>> If there are no free seats, issue a ROLLBACK and present an error
    >>> message to the user (you could also COMMIT - no difference since you
    >>> made no changes).
    >>>
    >>> Either COMMIT or ROLLBACK will unlock the tables.
    >>>
    >>> Does this help?
    >>
    >>
    >> As I understand it, in order to use COMMIT & ROLLBACK I need to
    >> create the table using InnoDB rather than MyISAM. Whenever I create
    >> a table with ENGINE=InnoDB it seems to have worked, but when I go
    >> into Operations on or Export from phpMyAdmin, it seems to suggest
    >> that the table has been created using MyISAM.
    >>
    >> This leads me to think that the InnoDB engine is not available, or
    >> is it posible that phpMyAdmin is fooloing me?
    >>
    >>
    >
    > The default is ISAM. If InnoDB is installed, you need to specify it
    > when you create your table.
    >
    > And ISAM doesn't support transactions. But COMMIT and ROLLBACK also
    > unlock the table - which is why I recommended them. They won't affect
    > transactions (since you don't have any on ISAM), but they shouldn't
    > cause an error.
    I tried "set autocommit=0;lock table atest write" in phpMyAdmin and then on
    a form on a web page submitting
    UPDATE `atest` SET b = b + 1
    and the update succeeded even though a lock should have been on?


    Paul Lautman Guest

  9. #9

    Default Re: Conditional INSERT

    Paul Lautman wrote:
    > Jerry Stuckle wrote:
    >> Paul Lautman wrote:
    >>> Jerry Stuckle wrote:
    >>>
    >>>> Paul Lautman wrote:
    >>>>
    >>>>> I have a pair of tables.
    >>>>>
    >>>>> The first one contains information about courses and 2 of its
    >>>>> columns are `id` (the id of the course) and `places` (the total
    >>>>> number of places available for the course).
    >>>>>
    >>>>> The second one contains one row per booking. Its columns are
    >>>>> `course_id`, `user_id`, `booking_date`.
    >>>>>
    >>>>> To display to users the list of courses and how many places are
    >>>>> left, I use a query like:
    >>>>>
    >>>>> SELECT SQL_CALC_FOUND_ROWS mybookings.course_id booked,
    >>>>> courses.id, (courses.places - COUNT( coursebookings.course_id ))
    >>>>> places_remaining
    >>>>> FROM `courses` courses
    >>>>> LEFT JOIN `course_bookings` coursebookings ON courses.id =
    >>>>> coursebookings.course_id
    >>>>> LEFT JOIN `course_bookings` mybookings ON mybookings.user_id = 63
    >>>>> AND mybookings.course_id = courses.id
    >>>>> GROUP BY courses.id
    >>>>>
    >>>>> The column "booked" tells me if this user has a particular course
    >>>>> booked and the "63" is changed to match the user making the enquiry
    >>>>> on the web site. `places_remaining` allows me to show how many
    >>>>> places are left and lets me use javascript to stop someone trying
    >>>>> to book a course where there are no places left.
    >>>>>
    >>>>> So far so good. BUT... If 2 or more users look at the list of
    >>>>> courses and 2 or more of them click to book a course with only 1
    >>>>> place left, I need only the first insert to course_bookings to
    >>>>> succeed and the rest should fail because there are now no places
    >>>>> left. Effectively I need to:
    >>>>>
    >>>>> INSERT INTO `course_bookings` VALUES (thiscourse,myid,null)
    >>>>> WHERE
    >>>>> (SELECT places FROM `courses` WHERE id = thiscourse) >
    >>>>> (SELECT COUNT(course_id ) FROM `course_bookings`)
    >>>>>
    >>>>> I have to admit that this one has got me well stumped. Anyone got
    >>>>> any ideas? TIA
    >>>>>
    >>>>>
    >>>> Just a slightly different approach:
    >>>>
    >>>> You first want to set AUTOCOMMIT to 0 to keep an implicit COMMIT
    >>>> from prematurely unlocking the tables.
    >>>>
    >>>> Lock all the tables you're going to use in your queries, and again
    >>>> check for free seats in your course.
    >>>>
    >>>> If there are free seats, update the bookings table with the new seat
    >>>> and COMMIT the transaction.
    >>>>
    >>>> If there are no free seats, issue a ROLLBACK and present an error
    >>>> message to the user (you could also COMMIT - no difference since you
    >>>> made no changes).
    >>>>
    >>>> Either COMMIT or ROLLBACK will unlock the tables.
    >>>>
    >>>> Does this help?
    >>>
    >>> As I understand it, in order to use COMMIT & ROLLBACK I need to
    >>> create the table using InnoDB rather than MyISAM. Whenever I create
    >>> a table with ENGINE=InnoDB it seems to have worked, but when I go
    >>> into Operations on or Export from phpMyAdmin, it seems to suggest
    >>> that the table has been created using MyISAM.
    >>>
    >>> This leads me to think that the InnoDB engine is not available, or
    >>> is it posible that phpMyAdmin is fooloing me?
    >>>
    >>>
    >> The default is ISAM. If InnoDB is installed, you need to specify it
    >> when you create your table.
    >>
    >> And ISAM doesn't support transactions. But COMMIT and ROLLBACK also
    >> unlock the table - which is why I recommended them. They won't affect
    >> transactions (since you don't have any on ISAM), but they shouldn't
    >> cause an error.
    >
    > I tried "set autocommit=0;lock table atest write" in phpMyAdmin and then on
    > a form on a web page submitting
    > UPDATE `atest` SET b = b + 1
    > and the update succeeded even though a lock should have been on?
    >
    >
    The lock works until you close the session.
    If the session is closed (as it is with phpMyAdmin), the lock is automatically released.
    Try with the command line client (not the Query Browser, because it will close the session
    as well).

    You can lock/unlock the tables within your application, being careful not to close the session
    between the LOCK statement and the subsequent updates.

    ciao
    gmax



    --
    _ _ _ _
    (_|| | |(_|>< The Data Charmer
    _|
    [url]http://datacharmer.org/[/url]
    Giuseppe Maxia Guest

  10. #10

    Default Re: Conditional INSERT

    Giuseppe Maxia wrote:
    > The lock works until you close the session.
    > If the session is closed (as it is with phpMyAdmin), the lock is
    > automatically released.
    > Try with the command line client (not the Query Browser, because it
    > will close the session
    > as well).
    >
    > You can lock/unlock the tables within your application, being careful
    > not to close the session between the LOCK statement and the
    > subsequent updates.
    >
    > ciao
    > gmax
    Thanks gmax.
    Can you confirm that I am reading the manual correctly in that, since I am
    using MyISAM I do not need to do a
    "SET AUTOCOMMIT=0" before doing the lock and insert?

    The manual says:
    If you are using a transaction-safe storage engine (such as InnoDB, BDB, or
    NDB Cluster), you can disable autocommit mode with the following statement:
    SET AUTOCOMMIT=0;

    Thus all I need to do is:

    LOCK TABLES courses READ, course_bookings coursebookings WRITE

    SELECT (courses.places - COUNT( coursebookings.course_id )) places_remaining
    FROM `courses` courses
    LEFT JOIN `course_bookings` coursebookings ON courses.id =
    coursebookings.course_id
    WHERE courses.id =thiscourse
    GROUP BY courses.id

    if places_remaining > 0 then
    INSERT INTO course_bookings VALUES (thiscourse,myid,null)

    UNLOCK TABLES


    Paul Lautman Guest

  11. #11

    Default Re: Conditional INSERT

    Jerry Stuckle wrote:
    [SNIP]
    > The default is ISAM. If InnoDB is installed, you need to specify it
    > when you create your table.
    >
    > And ISAM doesn't support transactions. But COMMIT and ROLLBACK also
    > unlock the table - which is why I recommended them. They won't affect
    > transactions (since you don't have any on ISAM), but they shouldn't
    > cause an error.
    >
    COMMIT and ROLLBACK don't release an explicit lock on MyISAM tables.
    BEGIN WORK does, though. (And, of course, UNLOCK TABLES or quitting the session)

    [url]http://dev.mysql.com/doc/refman/5.1/en/lock-tables.html[/url]

    ciao
    gmax

    --
    _ _ _ _
    (_|| | |(_|>< The Data Charmer
    _|
    [url]http://datacharmer.org/[/url]
    Giuseppe Maxia Guest

  12. #12

    Default Re: Conditional INSERT

    Paul Lautman wrote:
    > Jerry Stuckle wrote:
    >
    >>Paul Lautman wrote:
    >>
    >>>Jerry Stuckle wrote:
    >>>
    >>>
    >>>>Paul Lautman wrote:
    >>>>
    >>>>
    >>>>>I have a pair of tables.
    >>>>>
    >>>>>The first one contains information about courses and 2 of its
    >>>>>columns are `id` (the id of the course) and `places` (the total
    >>>>>number of places available for the course).
    >>>>>
    >>>>>The second one contains one row per booking. Its columns are
    >>>>>`course_id`, `user_id`, `booking_date`.
    >>>>>
    >>>>>To display to users the list of courses and how many places are
    >>>>>left, I use a query like:
    >>>>>
    >>>>>SELECT SQL_CALC_FOUND_ROWS mybookings.course_id booked,
    >>>>>courses.id, (courses.places - COUNT( coursebookings.course_id ))
    >>>>>places_remaining
    >>>>>FROM `courses` courses
    >>>>>LEFT JOIN `course_bookings` coursebookings ON courses.id =
    >>>>>coursebookings.course_id
    >>>>>LEFT JOIN `course_bookings` mybookings ON mybookings.user_id = 63
    >>>>>AND mybookings.course_id = courses.id
    >>>>>GROUP BY courses.id
    >>>>>
    >>>>>The column "booked" tells me if this user has a particular course
    >>>>>booked and the "63" is changed to match the user making the enquiry
    >>>>>on the web site. `places_remaining` allows me to show how many
    >>>>>places are left and lets me use javascript to stop someone trying
    >>>>>to book a course where there are no places left.
    >>>>>
    >>>>>So far so good. BUT... If 2 or more users look at the list of
    >>>>>courses and 2 or more of them click to book a course with only 1
    >>>>>place left, I need only the first insert to course_bookings to
    >>>>>succeed and the rest should fail because there are now no places
    >>>>>left. Effectively I need to:
    >>>>>
    >>>>>INSERT INTO `course_bookings` VALUES (thiscourse,myid,null)
    >>>>>WHERE
    >>>>>(SELECT places FROM `courses` WHERE id = thiscourse) >
    >>>>>(SELECT COUNT(course_id ) FROM `course_bookings`)
    >>>>>
    >>>>>I have to admit that this one has got me well stumped. Anyone got
    >>>>>any ideas? TIA
    >>>>>
    >>>>>
    >>>>
    >>>>Just a slightly different approach:
    >>>>
    >>>>You first want to set AUTOCOMMIT to 0 to keep an implicit COMMIT
    >>>>from prematurely unlocking the tables.
    >>>>
    >>>>Lock all the tables you're going to use in your queries, and again
    >>>>check for free seats in your course.
    >>>>
    >>>>If there are free seats, update the bookings table with the new seat
    >>>>and COMMIT the transaction.
    >>>>
    >>>>If there are no free seats, issue a ROLLBACK and present an error
    >>>>message to the user (you could also COMMIT - no difference since you
    >>>>made no changes).
    >>>>
    >>>>Either COMMIT or ROLLBACK will unlock the tables.
    >>>>
    >>>>Does this help?
    >>>
    >>>
    >>>As I understand it, in order to use COMMIT & ROLLBACK I need to
    >>>create the table using InnoDB rather than MyISAM. Whenever I create
    >>>a table with ENGINE=InnoDB it seems to have worked, but when I go
    >>>into Operations on or Export from phpMyAdmin, it seems to suggest
    >>>that the table has been created using MyISAM.
    >>>
    >>>This leads me to think that the InnoDB engine is not available, or
    >>>is it posible that phpMyAdmin is fooloing me?
    >>>
    >>>
    >>
    >>The default is ISAM. If InnoDB is installed, you need to specify it
    >>when you create your table.
    >
    > As I said, I specified ENGINE=InnoDB and phpMyAdmin said "Your SQL-query has
    > been executed successfully". But when I went into Operations or Exported the
    > table, phpMyAdmin was showing the ENGINE as MyISAM.
    >
    >
    >>And ISAM doesn't support transactions. But COMMIT and ROLLBACK also
    >>unlock the table - which is why I recommended them. They won't affect
    >>transactions (since you don't have any on ISAM), but they shouldn't
    >>cause an error.
    >
    > So are you saying that I can use LOCK TABLES and COMMIT and ROLLBACK with
    > MyISAM tables?
    >
    >
    >
    Yes, you can still lock the tables. Commit and Rollback should still
    release the lock.

    Or you can just unlock the tables.

    --
    ==================
    Remove the "x" from my email address
    Jerry Stuckle
    JDS Computer Training Corp.
    [email]jstucklexattglobal.net[/email]
    ==================
    Jerry Stuckle Guest

  13. #13

    Default Re: Conditional INSERT

    Paul Lautman wrote:
    > Jerry Stuckle wrote:
    >
    >>>>Just a slightly different approach:
    >>>>
    >>>>You first want to set AUTOCOMMIT to 0 to keep an implicit COMMIT
    >>>>from prematurely unlocking the tables.
    >
    > The manual says:
    > If you are using a transaction-safe storage engine (such as InnoDB, BDB, or
    > NDB Cluster), you can disable autocommit mode with the following statement:
    >
    > SET AUTOCOMMIT=0;
    > But since I will be using MyISAM, do I need to do the SET AUTOCOMMIT?
    >
    >
    No, you don't HAVE to - but it's a good idea in case you later change to
    InnoDB tables.

    --
    ==================
    Remove the "x" from my email address
    Jerry Stuckle
    JDS Computer Training Corp.
    [email]jstucklexattglobal.net[/email]
    ==================
    Jerry Stuckle Guest

  14. #14

    Default Re: Conditional INSERT

    Paul Lautman wrote:
    > Jerry Stuckle wrote:
    >
    >>Paul Lautman wrote:
    >>
    >>>Jerry Stuckle wrote:
    >>>
    >>>
    >>>>Paul Lautman wrote:
    >>>>
    >>>>
    >>>>>I have a pair of tables.
    >>>>>
    >>>>>The first one contains information about courses and 2 of its
    >>>>>columns are `id` (the id of the course) and `places` (the total
    >>>>>number of places available for the course).
    >>>>>
    >>>>>The second one contains one row per booking. Its columns are
    >>>>>`course_id`, `user_id`, `booking_date`.
    >>>>>
    >>>>>To display to users the list of courses and how many places are
    >>>>>left, I use a query like:
    >>>>>
    >>>>>SELECT SQL_CALC_FOUND_ROWS mybookings.course_id booked,
    >>>>>courses.id, (courses.places - COUNT( coursebookings.course_id ))
    >>>>>places_remaining
    >>>>>FROM `courses` courses
    >>>>>LEFT JOIN `course_bookings` coursebookings ON courses.id =
    >>>>>coursebookings.course_id
    >>>>>LEFT JOIN `course_bookings` mybookings ON mybookings.user_id = 63
    >>>>>AND mybookings.course_id = courses.id
    >>>>>GROUP BY courses.id
    >>>>>
    >>>>>The column "booked" tells me if this user has a particular course
    >>>>>booked and the "63" is changed to match the user making the enquiry
    >>>>>on the web site. `places_remaining` allows me to show how many
    >>>>>places are left and lets me use javascript to stop someone trying
    >>>>>to book a course where there are no places left.
    >>>>>
    >>>>>So far so good. BUT... If 2 or more users look at the list of
    >>>>>courses and 2 or more of them click to book a course with only 1
    >>>>>place left, I need only the first insert to course_bookings to
    >>>>>succeed and the rest should fail because there are now no places
    >>>>>left. Effectively I need to:
    >>>>>
    >>>>>INSERT INTO `course_bookings` VALUES (thiscourse,myid,null)
    >>>>>WHERE
    >>>>>(SELECT places FROM `courses` WHERE id = thiscourse) >
    >>>>>(SELECT COUNT(course_id ) FROM `course_bookings`)
    >>>>>
    >>>>>I have to admit that this one has got me well stumped. Anyone got
    >>>>>any ideas? TIA
    >>>>>
    >>>>>
    >>>>
    >>>>Just a slightly different approach:
    >>>>
    >>>>You first want to set AUTOCOMMIT to 0 to keep an implicit COMMIT
    >>>>from prematurely unlocking the tables.
    >>>>
    >>>>Lock all the tables you're going to use in your queries, and again
    >>>>check for free seats in your course.
    >>>>
    >>>>If there are free seats, update the bookings table with the new seat
    >>>>and COMMIT the transaction.
    >>>>
    >>>>If there are no free seats, issue a ROLLBACK and present an error
    >>>>message to the user (you could also COMMIT - no difference since you
    >>>>made no changes).
    >>>>
    >>>>Either COMMIT or ROLLBACK will unlock the tables.
    >>>>
    >>>>Does this help?
    >>>
    >>>
    >>>As I understand it, in order to use COMMIT & ROLLBACK I need to
    >>>create the table using InnoDB rather than MyISAM. Whenever I create
    >>>a table with ENGINE=InnoDB it seems to have worked, but when I go
    >>>into Operations on or Export from phpMyAdmin, it seems to suggest
    >>>that the table has been created using MyISAM.
    >>>
    >>>This leads me to think that the InnoDB engine is not available, or
    >>>is it posible that phpMyAdmin is fooloing me?
    >>>
    >>>
    >>
    >>The default is ISAM. If InnoDB is installed, you need to specify it
    >>when you create your table.
    >>
    >>And ISAM doesn't support transactions. But COMMIT and ROLLBACK also
    >>unlock the table - which is why I recommended them. They won't affect
    >>transactions (since you don't have any on ISAM), but they shouldn't
    >>cause an error.
    >
    >
    > I tried "set autocommit=0;lock table atest write" in phpMyAdmin and then on
    > a form on a web page submitting
    > UPDATE `atest` SET b = b + 1
    > and the update succeeded even though a lock should have been on?
    >
    >
    No, the lock was released when phpMyAdmin closed the connection and
    returned the results to you.

    Everything must be done all together.


    --
    ==================
    Remove the "x" from my email address
    Jerry Stuckle
    JDS Computer Training Corp.
    [email]jstucklexattglobal.net[/email]
    ==================
    Jerry Stuckle Guest

  15. #15

    Default Re: Conditional INSERT

    Giuseppe Maxia wrote:
    > Jerry Stuckle wrote:
    > [SNIP]
    >
    >>The default is ISAM. If InnoDB is installed, you need to specify it
    >>when you create your table.
    >>
    >>And ISAM doesn't support transactions. But COMMIT and ROLLBACK also
    >>unlock the table - which is why I recommended them. They won't affect
    >>transactions (since you don't have any on ISAM), but they shouldn't
    >>cause an error.
    >>
    >
    >
    > COMMIT and ROLLBACK don't release an explicit lock on MyISAM tables.
    > BEGIN WORK does, though. (And, of course, UNLOCK TABLES or quitting the session)
    >
    > [url]http://dev.mysql.com/doc/refman/5.1/en/lock-tables.html[/url]
    >
    > ciao
    > gmax
    >
    My mistake - thanks for the correction!

    --
    ==================
    Remove the "x" from my email address
    Jerry Stuckle
    JDS Computer Training Corp.
    [email]jstucklexattglobal.net[/email]
    ==================
    Jerry Stuckle Guest

  16. #16

    Default Re: Conditional INSERT


    Jerry Stuckle wrote:
    > Giuseppe Maxia wrote:
    > > COMMIT and ROLLBACK don't release an explicit lock on MyISAM tables.
    > > BEGIN WORK does, though. (And, of course, UNLOCK TABLES or quitting the session)
    > >
    > > [url]http://dev.mysql.com/doc/refman/5.1/en/lock-tables.html[/url]
    > >
    > > ciao
    > > gmax
    > >
    >
    > My mistake - thanks for the correction!
    Thanks Jerry, Giuseppe, Peter.

    I assume that BEGIN WORK is no good in this case as unlike LOCK TABLES
    it does require InnoDB or the like??

    paul_lautman@yahoo.com Guest

  17. #17

    Default Conditional INSERT


    Hello,

    I'm trying to perform an INSERT .. but only if no rows in the table
    have a specific field that equals zero.

    For example:

    Nodes has the fields NodeID and ParentNodeID (there are more than
    that, but this is simplified)

    NodeID will be unique no matter what, since it is incremental.

    I want to
    INSERT INTO Nodes
    VALUES(NULL, 0)

    only if
    // There are no Nodes that have a ParentNodeID of 0
    SELECT COUNT(NodeID)
    FROM Nodes
    WHERE ParentNodeID = 0 is less than 1

    Thank you

    evanpeck@gmail.com Guest

  18. #18

    Default Re: Conditional INSERT

    On Jun 25, 2:19 pm, com wrote: 

    if not exists (select 1 from nodes where ParentNodeID = 0) begin
    insert into Nodes
    values(null, 0);
    end if;

    ZeldorBlat Guest

  19. #19

    Default Re: Conditional INSERT

    Unfortunately, that doesn't seem to be working for me.

    I get a sytax error near

    "if not exists (select 1 from nodes where ParentNodeID = 0) be"

    evanpeck@gmail.com Guest

Similar Threads

  1. Question Insert from ASP to SQL using recordset data as values in insert statement
    By JasonM in forum Microsoft SQL / MS SQL Server
    Replies: 0
    Last Post: June 13th, 05:54 PM
  2. What's faster - loop for insert or insert...select.
    By mr. modus in forum Coldfusion Database Access
    Replies: 2
    Last Post: December 10th, 01:57 AM
  3. Multiple Insert or Looping Insert
    By payado in forum Dreamweaver AppDev
    Replies: 1
    Last Post: May 25th, 09:33 PM
  4. Conditional sum?
    By Fred in forum FileMaker
    Replies: 2
    Last Post: October 28th, 04:16 PM
  5. Replies: 3
    Last Post: September 30th, 09:24 PM

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
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139