Need help with query for the first date with openings..

[danieliser]

ok.. i am in need to be able to check for the earliest day that has less than 16 bookings..

 

each day can have 16 people booked.. days dont have to be user selectable, just need it to find the soonest day after today that has <16 people registered.

 

i was using a simpler method of just registering them in order until the row count for a day was 16.. but i want to be able to allow them to buy more than one day at a time and there cant be a duplicate on any day..

 

my problem now is that if somebody orders 3 then their name just gets put into 3 of the 16 slots rather than 1 slot today 1 slot tomorow and so on.. where that makes a problem is i was using

"SELECT * FROM overlord ORDER by overlord_date DESC LIMIT 1"

which would mean that after that guy registered for 3 days nobody else would get put onto those days they would get whatever day was last in the db.

 

the table is simple

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

-- Table structure for underlord

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

CREATE TABLE `underlord` (

  `underlord_number` int(11) NOT NULL auto_increment,

  `userid` varchar(12) NOT NULL,

  `underlord_date` date NOT NULL,

  PRIMARY KEY  (`underlord_number`)

)

 

Any help would be great.. thanks

[fenway]

Why not just count how many are on each day, and assign N orders chronologically?

[danieliser]

Sorry.. maybe thats why i cant get any help haha.. thanks for the link.. here goes again..

 

•MySQL server version - 5.0.67 Community

 

Code in question!

SELECT * FROM underlord WHERE underlord_date='2009-04-17'

 

Table Structure

CREATE TABLE `underlord` (
  `underlord_number` int(11) NOT NULL auto_increment,
  `userid` varchar(12) NOT NULL,
  `underlord_date` date NOT NULL,
  PRIMARY KEY  (`underlord_number`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

 

Description of issue -  I need to be able to select the first date in the database after today that has less than 16 people. also some direction on how to best work the sql with php if possible, such as should i use Ifs or while statments to repeatedly run the script for each day till it finds the opening.

 

Decsription of what its doing now -  the site works if i leave out the ability to purchase multiple days. but if i have somebody purchase 3 days my php will add them to today tomorow and so on.. but using my old method this would mean that the person after them would get put on day 3 even if there were 15 openeings lefton day 1.

 

•a brief listing of the types of things you've attempted so far

I have tried several methods in php including ifs and for statements and it really comes down to getting this part right. I can easily do an if user is listed on day try next statement..

[fenway]

Did you even consider my suggestion?

[danieliser]

Why not just count how many are on each day, and assign N orders chronologically?

 

lol actually didnt see it.. saw your sig and reposted the info.. do you mean using

if(mysql_num_rows() < 16)

if so i have been using that and my php will insert a person into the database 3 days in a row.. but my initial get date sql statement is insufficient in that once they are in 3 days in a row it will use the last date in the table to start the next order rather than the 1st day with an opening.

 

if thats not what you mean then im a bit confused..

[fenway]

That's not what I intended.... find out how many less-than-16 dates are available, and insert one record into each of that dates N times.

[danieliser]

Ive been hacking away and digging the internet for hours.. ive come up with this.. it works so far that ive tested and i intend to test more.. just wanting to know if this would be trumped by something else? or any critiquing

 

SELECT underlord_date, COUNT(*) AS Total
FROM underlord
WHERE underlord_date>CURDATE() AND underlord_date NOT IN (SELECT underlord_date FROM underlord WHERE userid='$userid')
GROUP BY underlord_date 
HAVING Total<16 
ORDER BY underlord_date DESC

[fenway]

Looks good to me... though I suppose you could replace NOT IN with a LEFT JOIN...