danieliser Posted April 16, 2009 Share Posted April 16, 2009 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 Quote Link to comment https://forums.phpfreaks.com/topic/154290-need-help-with-query-for-the-first-date-with-openings/ Share on other sites More sharing options...
fenway Posted April 16, 2009 Share Posted April 16, 2009 Why not just count how many are on each day, and assign N orders chronologically? Quote Link to comment https://forums.phpfreaks.com/topic/154290-need-help-with-query-for-the-first-date-with-openings/#findComment-811473 Share on other sites More sharing options...
danieliser Posted April 16, 2009 Author Share Posted April 16, 2009 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.. Quote Link to comment https://forums.phpfreaks.com/topic/154290-need-help-with-query-for-the-first-date-with-openings/#findComment-811827 Share on other sites More sharing options...
fenway Posted April 17, 2009 Share Posted April 17, 2009 Did you even consider my suggestion? Quote Link to comment https://forums.phpfreaks.com/topic/154290-need-help-with-query-for-the-first-date-with-openings/#findComment-812068 Share on other sites More sharing options...
danieliser Posted April 17, 2009 Author Share Posted April 17, 2009 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.. Quote Link to comment https://forums.phpfreaks.com/topic/154290-need-help-with-query-for-the-first-date-with-openings/#findComment-812073 Share on other sites More sharing options...
fenway Posted April 17, 2009 Share Posted April 17, 2009 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. Quote Link to comment https://forums.phpfreaks.com/topic/154290-need-help-with-query-for-the-first-date-with-openings/#findComment-812286 Share on other sites More sharing options...
danieliser Posted April 18, 2009 Author Share Posted April 18, 2009 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 Quote Link to comment https://forums.phpfreaks.com/topic/154290-need-help-with-query-for-the-first-date-with-openings/#findComment-812999 Share on other sites More sharing options...
fenway Posted April 20, 2009 Share Posted April 20, 2009 Looks good to me... though I suppose you could replace NOT IN with a LEFT JOIN... Quote Link to comment https://forums.phpfreaks.com/topic/154290-need-help-with-query-for-the-first-date-with-openings/#findComment-814566 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.