Results 1 to 14 of 14
  1. #1
    Join Date
    Mar 2007
    Posts
    26

    Unanswered: database basic setup & calculation question

    Hi to all,
    I had a simple question, I was wanted to know what would be the best way to setup a simple table(s) to calculate guests/dates & openings avail. Meaning
    if I had 4 different dates say 3-1-07, 3-2-07, 3-5-07, 3-3-07. and each one had only 50 spots avail, and when a participant registered to attend one of those dates they could choose to bring a max of 4 guests, ultimately reducing the 50 spots either by 1 or 5, whats the best way to setup a table to know when they have been filled up?...and how would one calculate this?
    Kind regards, John

    ps email is
    maliciousdata@yahoo.com

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    are the spots numbered, i.e. individually identifiable? does it matter who sits in which spot? or does it matter only that you don't oversell the number of tickets?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Mar 2007
    Posts
    26
    Hi rudy.ca,
    thanks for your reply....it only matters that the seats requested are not over the limit of seats avail. so if i have 50 spots for date 3-1-07, and 50 for 3-1-07... I need to be able to know or count whats being inserted where? and then when submitting I guess checking to see if that dates spots have been all taken. The crux is that, 1 person can bring up to 1-4 guests... so I have to account for the possibiltiy of this deduction in each date as well.
    Kind regards, John

  4. #4
    Join Date
    Mar 2007
    Posts
    26
    sorry typo, date 3-1-07 & 3-2-07

  5. #5
    Join Date
    Mar 2007
    Posts
    26

    your site

    hi, checked out your site, really cool... alot of information.
    Im in NY...
    Best, John

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    well, the way i would do it, would be something like this
    Code:
    create table day_seats
    ( theday  date     not null primary key
    , seats   smallint not null
    );
    insert into day_seats values
     ( '2007-03-01', 50 )
    ,( '2007-03-02', 46 )
    ,( '2007-03-03', 50 )
    ,( '2007-03-04', 50 )
    ;
    create table user_registrations
    ( userid  integer  not null primary key auto_increment
    ( theday  date     not null 
    , seats   smallint not null
    );
    insert into user_registrations values
     (  24, '2007-03-02', 4 )
    each time a user registers some seats for a particular day, you decrement the number of remaining seats
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Mar 2007
    Posts
    26

    decrementing

    I only have been using database for few months, how would I go about drementing the value? use SQL command? when doing an insert?
    can you provide an example?.
    really appreciate you help and your time!, John

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you will need some sort of scripting language here, no?

    - receive user request
    - check available total for the date requested (SELECT query)
    - if greater than or equal to the number user is requesting, insert user request (INSERT statement), and decrement the available (UPDATE statement)

    all the above needs to be inside a transaction block

    the UPDATE statement willl look something like
    Code:
    UPDATE day_seats
       SET seats = seats = $req
     WHERE theday = '$date'
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Mar 2007
    Posts
    26

    this script

    can I just import this SQL? into a database I want to create?, the inserts are for adding values? right?..also the SQL part:

    insert into user_registrations values
    ( 24, '2007-03-02', 4 )
    is an example of how I would do insert?

    sorry, really a beginner...

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    yes, that is an example of an insert

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Mar 2007
    Posts
    26

    Cool i think i got it...

    I wil try i it out today, hey thanks so much for your help!
    stay in touch.
    John

  12. #12
    Join Date
    Mar 2007
    Posts
    26

    getting there / derementing

    Hi Rudy.ca,
    I think I am making progress thanks to you, I can query the DB and get the amounts of seats avail for each day, also can update the DB.
    But I am having trouble decrementing the seats to reflect the changes.
    Also I put another field called reqID in the day_seats table so I can know what day I am looking it. Is my thinking correct?... Moreover, how can I do insert based upon user data?, use posted variables right?...like for php $_POST["dates"]; etc...

    I am a ok programmer but when comes to databases I seem to get confused...do you think databases are a harder concept than programming?
    John

  13. #13
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by john_cmd
    do you think databases are a harder concept than programming?
    no, easier... much much easier
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  14. #14
    Join Date
    Mar 2007
    Posts
    26

    databases

    strange, maybe I just have more programming behind me...thats why i feel comfortable..anyways, this is the basic code I am using and the SQL:

    I just made very basic for testing, but I still having issues decrementing the values once a user does insert...can you take a look?
    Thanks much, john

    /* test from url*/
    $req = $_GET["dates"];

    //return the current amount of seats avail
    $sql = "SELECT seats FROM day_seats WHERE reqID='$req'";
    $results = mysql_query("$sql");

    if(!$results){
    echo "error" . mysql_error();
    exit();
    }

    while($row = mysql_fetch_array($results)){

    for($j=0; $j<count($row); $j++){
    $data .= $row[$j];
    }
    echo $data . "<BR>";
    }

    //if seat amt still at 50 insert a new record & send email & decrement seats,
    if($data <> 0 && $req == 2){

    //echo "yes";
    //add the users date requested plus amount of guests

    $insert_seat = "INSERT INTO user_registrations VALUES(10, '2007-03-01', 4)";
    mysql_query($insert_seat);

    $update = "UPDATE day_seats SET seats = seats = $req WHERE reqID = '$req'";
    mysql_query($update);
    }


    ******* SQL I am using starts here ******


    -- Database: `cooking_event`
    --

    -- --------------------------------------------------------

    --
    -- Table structure for table `day_seats`
    --

    CREATE TABLE `day_seats` (
    `theday` date NOT NULL,
    `seats` smallint(15) NOT NULL,
    `reqID` smallint(15) NOT NULL,
    PRIMARY KEY (`theday`)
    ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

    --
    -- Dumping data for table `day_seats`
    --

    INSERT INTO `day_seats` VALUES ('2007-03-01', 34, 1);
    INSERT INTO `day_seats` VALUES ('2007-03-02', 50, 2);

    -- --------------------------------------------------------

    --
    -- Table structure for table `user_registrations`
    --

    CREATE TABLE `user_registrations` (
    `userid` int(15) NOT NULL auto_increment,
    `theday` date NOT NULL,
    `seats` smallint(15) NOT NULL,
    PRIMARY KEY (`userid`)
    ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=40 ;

    --
    -- Dumping data for table `user_registrations`
    --

    INSERT INTO `user_registrations` VALUES (32, '2007-03-01', 4);
    INSERT INTO `user_registrations` VALUES (31, '2007-03-01', 4);
    INSERT INTO `user_registrations` VALUES (24, '2007-03-02', 4);
    INSERT INTO `user_registrations` VALUES (12, '2007-03-02', 4);
    INSERT INTO `user_registrations` VALUES (21, '2007-03-01', 4);
    INSERT INTO `user_registrations` VALUES (20, '2007-03-01', 4);
    INSERT INTO `user_registrations` VALUES (15, '2007-03-01', 4);
    INSERT INTO `user_registrations` VALUES (18, '2007-03-01', 4);
    INSERT INTO `user_registrations` VALUES (10, '2007-03-01', 4);

Posting Permissions

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