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

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?

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

sorry typo, date 3-1-07 & 3-2-07

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

well, the way i would do it, would be something like thiseach time a user registers some seats for a particular day, you decrement the number of remaining seats

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

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

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...

yes, that is an example of an insert

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

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

no, easier... much much easier

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);