PHP MYSQL appointment system NEED HELP!!

[danieliser]

Ok here are the basics..

 

I can have 16 customers per day.

1 Customer can only fill 1 spot per day.

If the customer is already registered that day it should automatically go to the next.

 

i have everything worked out until i introduce a quantity purchase. right now it goes like this:

 

<?php
/////////////////////////////////////////////
//Get The Last Underlord Date Already Taken//
/////////////////////////////////////////////
$sql="SELECT * FROM underlord ORDER by underlord_date DESC LIMIT 1";
$result = mysql_query($sql);
$myrow = mysql_fetch_array($result);
$last = $myrow['underlord_date'];
$phpdate = strtotime($last);
if($phpdate < strtotime(date("Y-n-d")))
{
	$phpdate = strtotime(date("Y-n-d"));
	$next_available = $phpdate + 86400;
	$underlord_next = date('Y-m-d',$next_available);
}
elseif($phpdate >= strtotime(date("Y-n-d")))
{
	///////////////////////////////////
	//Get The Last Date Already Taken//
	///////////////////////////////////
	$sql="SELECT * FROM underlord ORDER by underlord_date DESC LIMIT 1";
	$result = mysql_query($sql);
	$myrow = mysql_fetch_array($result);
	$last = $myrow['underlord_date'];
	////////////////////////////////////////
	//Get The Row Count From The Last Date//
	////////////////////////////////////////  
	$sql2="SELECT * FROM underlord WHERE underlord_date='$last'";
	$result2 = mysql_query($sql2);
	/////////////////////////////////////////////
	//Determine If Its The Same Day Or The Next//
	/////////////////////////////////////////////
	if (mysql_num_rows($result2)>=16)
		{
			$phpdate = strtotime($last);
			$next_available = $phpdate + 86400;
			$underlord_next = date('Y-m-d',$next_available);
		}
	elseif (mysql_num_rows($result2)<16)
		{
			$underlord_next = $last;
		}
	else;
}
else;
?>

 

My issue is that if i introduce a quantity ability in checkout then heres what happens..

 

customer purchases 3

register customer on the last date available

register customer on the next day

register custrmer on the 3rd day

 

so that part works the problem is that now when another customer orders they will not get listed on day one even if there is an opening because the sql statement that gets the last date in the database is sorted descending and the customer that purchased 3 days is days ahead now causes the other days to be skipped by any other order.. i hope i explained it right.. i will basically lose any more sales on days 1 and 2. and if somebody purchased 20 that would be drastic. i hope somebody can help.. thanks in advance

 

this is what i worked out before i realized this would happen

 

$sql3="SELECT * FROM overlord WHERE overlord_date='$last' AND userid='$userid'";
$result3 = mysql_query($sql3);
if (mysql_num_rows($result2)>=2 && mysql_num_rows($result3)==0)

it would replace the if statements above.. but i can see that will not be sufficient. thanks all

[danieliser]

Also i should not that this script is included in a loop based on quantity as follows and that it is getting variables from Paypal IPN if that makes a difference.. The table has 3 fields.. underlord_number(auto_inc), userid(), and underlord_date()

 

<?php
elseif ($item_name == 'underlord')
{
	for ($i = $quantity; $i > 0;)
		{
			require('includes/underlord_next.php');
			$sql="INSERT INTO `underlord` VALUES ('', '$userid', '$underlord_next')";
			$result = mysql_query($sql);
			$i = ($i-1);
		}
}?>

[gizmola]

Oso that part works the problem is that now when another customer orders they will not get listed on day one even if there is an opening because the sql statement that gets the last date in the database is sorted descending and the customer that purchased 3 days is days ahead now causes the other days to be skipped by any other order.. i hope i explained it

 

We don't have any idea what your database looks like, but the first piece of information we don't have, is what determines these order dates?  Seems to me, that your algorithm as describes simply schedules someone for consecutive slots starting from Day X.  We don't have any other information about the criteria to determine what the starting day should be, but it seems that getting the "last date in the database" is an erroneous approach.  Why would it not be the first day > CURDATE() that does not already have 16 reservations?  That's really what you're asking for aren't you?

[danieliser]

yes basically that is a simpler version of my question.. yes i guess i need to figure out how to better select my initial date.. it worked perfectly until i decided i wanted them to be able to purchase more than 1 day at a time..

[danieliser]

any chance i can get some direction on how to rework that? i mean i know what i want it to do i just cant get my head around it for some reason.. even tried writing the process on paper in the order it should logically go.. and im stuck..

[gizmola]

Again, we need the structure of your table including datatypes.  Do a describe tablename and paste it in here in a code block.

[danieliser]

Will do in the morning. I'm thinking something like this would do the trick.

 

For ($searchdate=date('Y-m-d');$row=16;$date=$searchdate)

{

$SQL = "select * where date=$searchdate";

$result=(ysql_query($SQL);

 

If (mysql_number_rows($result)>=16)

{

 

$searchdate="$searchdate +1";

$rows =16;

}

Elseif (mysql_number_rows($result)<16)

{

 

$rows = 1;

}

Else;

}

[gizmola]

That code most certainly won't work, and you're really off track.  for loops are designed for simple iteration, and don't have any intelligence about dates.  I think you're going about this the wrong way --  make your mysql database do the heavy lifting.  Figure out the queries you need, which can be tested without php code.  This is why we need the structure.  Often you can do in a single query what it might take you blocks of code and multiple nested (and inefficient queries) to do when you are trying to approach things too procedureally.

[danieliser]

thanks for your help.. my table is extremeley simple which has worked so far but maybe thats what needs redoing.

 

here is the create for my table.

 

 

-- Table "underlord" DDL

 

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;

 

[danieliser]

anybody? everything else is ready to go live.. and im stumped.. any help?

[gizmola]

I haven't forgotten about you, but I'm really busy with work right now.  When I get a free moment, I'll give you some queries.  You can establish the initial date by doing a GROUP BY on underlord_date HAVING COUNT(*) CURDATE ORDER BY underlord_date.  That's not the exact sql but basically what you need.  The first row in your result set which you can get via LIMIT, will be the date you should begin with your scheduling.

 

From there, my opinion is that you should query each day, as your scheduling run is going, so you can insure that there is still an available slot AND that the person isn't already scheduled for that day. 

 

Since you have the initial day established, doing this in a loop isn't difficult.  The way you were doing it, was bound in my opinion to have a problem, due to the assumptions inherent in your initial approach.

[danieliser]

Thank you.. I appreciate any help i can get. ive never used the GROUP BY or HAVING COUNT().. im reading up on them now.. but they seem like they could be very useful..  as for what you said in the second paragraph i should be taking the results from that query then doing my if userid exists statements and a verify the date is open?

[danieliser]

I think i got your concept and here is what i came up with

 

SELECT underlord_date, COUNT(*) AS Total 
FROM underlord
WHERE underlord_date>CURDATE() 
GROUP BY underlord_date 
HAVING Total<16
ORDER BY underlord_date

 

it works... might need to tweak it.. just need to rework my php now to accomidate that.. mainly in the area of checking for userid already existing..

[gizmola]

Looks good. Seems like you got the gist of things --- Kuddos for reading up on the examples.  Looks like you're on your way.    To check for the userid, just add in

 

WHERE underlord_date > CURDATE() AND userid != $userid 

 

So obviously the $userid is a variable your script needs to supply.

[danieliser]

Thank you so much for your help.. I will have time later tonight to actually try and peice it all together haha.. ive been coding so much on several sites im working on lately that i have coding nightmares haha.. where im always stuck in an endless loop haha.. sort of like video game dreams if you ever had those.. play a game to much and you will be stuck in it all night lol..

[danieliser]

Ok ive been working on this for about an hour now and its not operating the way it should.. It works until i add in the

AND userid != $userid

 

Ive also tried using

& userid != $userid
and
&&userid != $userid

 

The & statement did return the only date that didnt have that user.. but if there is no date that the user isnt on it returns no results.. so i guess now that im writing this im thinking a if (!$result) then i query for the last day and go to the day.. thats all i can think of.. that would work right? would there be a simpler way?

 

One more thing i just thought of.. is there any way to do a verify that no day is skipped? because in my test if i has user 1 on day 1 2 3, then all those days dont show up and it returns null.. but if user 1 was on 1 2 and 4 for some reason.. i cant think of any reason that should happen the way the code is.. but if it per haps did.. then the result still returns null because day 3 isnt a group in the table because nobody is there yet..

[danieliser]

ok i think i might have to use a seperate query to check for user being on that date.. it doesnt work properly and i have tried it several ways..

[danieliser]

Ok i just noticed what its doing.. it is not excluding days if the user is on them.. just excluding them from the count. if there are 5 people on a day and the user is on that day the result will show that day but with the user not being counted.. i need to exclude those days from the group by..

[danieliser]

Im thinking its either gotta be filtered in php or i just saw something about sub query.. query within a query.. maybe tomorow.. sigh.. so close but so far.. haha..

[danieliser]

Does this look right.. after much much tinkering i think i got it.. tests so far have been correct.. although i need to test it much further.. any critique would be much appreciated.

 

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

[danieliser]

here is the php i came up with.. does it look right? i mean its not gonna skip any days.. whether or not they are already in there.. i have a ton of testing to do in the morning.. but im done for tonight.. night all..

 

ipn.php

<?php
////////////////////////////////
// GET USERID FROM USER TABLE //
////////////////////////////////
$sql="SELECT * FROM user WHERE email='$payer_email'";
$result = mysql_query($sql);
$myrow = mysql_fetch_array($result);
$userid = $myrow['userid'];
////////////////////////////
// ASSIGN PURCHASED DATES //
////////////////////////////
if ($item_name == 'dracula')
{
	for ($i = $quantity; $i > 0;)
		{
			require('includes/dracula_next.php');
			$sql="INSERT INTO `dracula` VALUES ('', '$userid', '$dracula_next')";
			$result = mysql_query($sql);
			$i = ($i-1);
		}
}
elseif ($item_name == 'overlord')
{
	for ($i = $quantity; $i > 0;)
		{
			require('includes/overlord_next.php');
			$sql="INSERT INTO `overlord` VALUES ('', '$userid', '$overlord_next')";
			$result = mysql_query($sql);
			$i = ($i-1);
		}
}
elseif ($item_name == 'underlord')
{
	for ($i = $quantity; $i > 0;)
		{
			require('includes/underlord_next.php');
			$sql="INSERT INTO `underlord` VALUES ('', '$userid', '$underlord_next')";
			$result = mysql_query($sql);
			$i = ($i-1);
		}
}
else;
?>

 

overlord_next.php

<?php
$sql="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 LIMIT 1";
$result = mysql_query($sql);
$myrow = mysql_fetch_array($result);
$underlord_available = $myrow['underlord_date'];
if (!$result)
  {
  $sql="SELECT * FROM underlord ORDER by overlord_date DESC LIMIT 1";
  $result = mysql_query($sql);
  $myrow = mysql_fetch_array($result);
  $last = $myrow['overlord_date'];
  $phpdate = strtotime($last);
  $phpdate = strtotime(date("Y-n-d"));
  $next_available = $phpdate + 86400;
  $underlord_next = date('Y-m-d',$next_available);
  }	  
else;
?>

[gizmola]

Ok, so the query I gave you, does work.

 

SELECT underlord_date, COUNT(*) AS Total 
FROM underlord 
WHERE underlord_date>CURDATE() AND userid != $userid
GROUP BY underlord_date HAVING TotalORDER BY underlord_date

 

The problem is -- and this is not a problem with the query, but rather, a basic SQL fundamental -- that if you execute a query and there are no results, you get an empty set.  So when you start out, and your table is empty, no matter what query is executed, you will get an empty result set.  You will also get an empty result set in this case, if you have no rows inserted for a particular day, and this then becomes a chicken and egg issue.

 

So, I considered a few different approaches.  This is the simplest one I could come up with.

 

Break the thinking into 2 different queries:

 

1. Establish dates that you CAN NOT schedule this person for (it's EITHER full, OR the user is already scheduled on that day)

 

SELECT underlord_date

FROM underlord

WHERE userid = $userid

UNION 

SELECT underlord_date

FROM underlord

WHERE underlord_date > CURDATE()

GROUP BY underlord_date

HAVING COUNT(*) > 15

ORDER BY underlord_date

 

2. Get the next schedule date, and using #1, look for the first date you can find, that isn't blocked by the results of query #1 above.

 

select DATE_ADD(CURDATE(), INTERVAL +1 DAY) as nextday;

 

 

So in the 2nd query will get you the first date that COULD be valid.  Using PHP you can cycle through dates using something like this:

 

echo date('Y-m-d', strtotime("+1 day"));

 

I'll leave it to you to research how to convert back and forth between PHP date and mysql dates.  The basic pseudocode however would be:

 

1. Get the First available date either from mysql or PHP

2. Query for dates that are unavailable for this USER (SEE UNION query above)

3. Starting with php date, check list of unavailable dates, and return the first available date -- INSERT NEW ROW.  Repeat with subsequent days.

 

 

One thing this strategy also protects against, is that if for some reason, a person got scheduled for some future date, the will not be scheduled 2x, simply because you were looping through a range of dates, which didn't take into account the potential for gaps.  I don't know if this was likely, but it was always a possibility with the original algorithm.

[danieliser]

Did you see this query i came up with several posts up.. it will effectively check for all dates the user is listed on and then group all the groups of days excluding those in the results of the other subquery

 

Here it is again..

 

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

 

 

That works perfect... im having trouble now rebuilding my application using those results though.. im sure its something simple so i am currently adding a or die(custom error) for every line to see what is going on..

[gizmola]

Yes I did see that query.  Unfortunately, it suffers from the same problem I was just describing.  It does work if there's a row, but then again so does mine, and it doesn't require the correllated subquery.  They are in essence the same, only the original one is more efficient. 

 

If you feel that query is getting you close to what you need, go with it.  Do however test it out for yourself.  For example, if your table is empty, do you get any results? 

[danieliser]

No i dont get a result.. but im trying to correct that with php..

 

here is what ive got so far..

 

<?php
/////////////////////////////////////////////
//Get The Last Underlord Date Already Taken//
/////////////////////////////////////////////
$sql = "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 LIMIT 1";
$result_1 = mysql_query($sql);

if (mysql_num_rows($result_1) == 0)
{echo 'IF 1 OPTION 1<br />';
$sql = "SELECT underlord_date FROM underlord WHERE underlord_date>CURDATE() ORDER BY underlord_date DESC LIMIT 1";
$result_2 = mysql_query($sql);

if (mysql_num_rows($result_2) == 0) //Set Date to Day after Today
{echo 'IF 2 OPTION 1<br />';
	$phpdate = strtotime(date('Y-m-d'));
	$next_available = date('Y-m-d',$phpdate + 86400);
	$underlord_next = $next_available;
}
elseif (mysql_num_rows($result_2) != 0) //Set Date to Day after Last Day in Table
{echo 'IF 2 OPTION 2<br />';
	$myrow = mysql_fetch_array ($result_2);
	$last_date = $myrow['underlord_date'];
	$phpdate = strtotime($last_date);
	$next_available = date('Y-m-d',$phpdate + 86400);
	$underlord_next = $next_available;
}
else;
}
elseif (mysql_num_rows($result_1) != 0)
{echo 'IF 1 OPTION 2<br />';
$myrow = mysql_fetch_array ($result_1);
$underlord_next = $myrow['underlord_date'];
}
else;
echo $underlord_next,'<br />';

?>

 

So far it works perfect.. im trying to break it though.. any chance you wanna break it for me? i know it lacks in 2 ways.. 1 a verification that it had the right day, 2 PHP/MYSQL Security.. i need to read up on that part.. any good guides? need to protect my investment.. mostly time but w/e.. dont want anybody injecting freebies.. by the way i loop this for the quantity funciton i was trying to achieve..  im gonna use Paypal IPN.. so you need to insert the variables manually for $userid and $quantity.. my ipn script gets that and passes it on..here is the loop..

 

for ($i = $quantity; $i > 0;)
   {
       require('./includes/underlord_next.php');
       $sql="INSERT INTO `underlord` VALUES ('', '$userid', '$underlord_next')";
       $insert = mysql_query($sql);
       $i = ($i-1);
    }