Retrieving and comparing data from arrays

[assgar]

Hi

I am php newbie and I don't know if I am approaching this properly.
I need to creat a scheduler that list the appointment time on the left
and client for a specific appointment time on the left.

NOTE: The appointments are stored in a Mysql database

This is what I am trying to acomplish:

Time | client Name
-------------------------------------------
09:00 AM |
10:00 AM | John Smith
11:00 AM |
to
09:00 PM | Mike Smith



The problem I am having is getting the appointments on the
right to match up with the time on the left.


This is what I am getting with the code below:


Time | client Name
------------------------------------------------
09:00 AM | John Smith
10:00 AM | Mike Smith
11:00 AM |
to
09:00 PM |



[php]
<?
/**--------------------------time interval--------------------**/
//array with time interval listing found on the left side of the page
$time_60min_array = array(
'06:00 AM', '07:00 AM', '08:00 AM', '09:00 AM', '10:00 AM', '11:00 AM',
'12:00 PM', '01:00 PM', '02:00 PM', '03:00 PM', '04:00 PM', '05:00 PM',
'06:00 PM', '07:00 PM', '08:00 PM', '09:00 PM');

/**-------------select appointment from database-----------------**/
// select by date to match to time interval listing
$query = "SELECT distinct(event_id), event_date, event_time, event_am_pm,
first_name, last_name,
FROM cal_appointment
WHERE event_date = '$event_date'
ORDER BY event_date, event_am_pm, event_time";

$result = mysqli_query($mysqli, $query) or die('Error, query failed');

/**-------------------------------title--------------------------**/
echo "<div id=\"Layer4\" style=\"position:absolute; margin: 4px; width:100%; height:20px; z-index:4; left: 4px; top: 430px;\">\n";
echo "<table width=\"98.5%\" margin=\"\" left =\"4\" align=\"center\">
<tr>
<td width=\"30%\" height=\"12\" align=\"center\"
bgcolor=\"#eeeee0 \"> <span class=\"style20\"> <strong>Time</strong></span></td>
<td width=\"68%\" height=\"12\" align=\"center\" bgcolor=\"#eeeee0\"><span class=\"style20\"><strong>Patient</strong></span></td>
</tr>\n";
echo "</table>\n";
echo "</div>\n";

//search area display area layer and table
echo "<table width=\"99%\" border=\"0\">
<tr align=\"center\" bgcolor=\"#FFFFFF\" height=\"\">
<td width=\"100%\" >
<div id=\"Layer2\" style=\"position:absolute; width:100%; height:500px;
z-index:2; left: 4px; top: 465px;\">
<div id=\"scroll-box2\" style=\"overflow: auto; float: left; width: 100%;
height: 480px; margin: 5px; \">\n";


//table begins
echo "<table width=\"100%\" height=\"332\" left =\"4\" align = \"\" border=\"0\" font face =\"arial\">\n";


/**--------------------------display------------------------**/
$result = mysqli_query($mysqli, $query) or die('Error, query failed');

for($i=0; $i < count($time_60min_array ); $i++)
{
$row = mysqli_fetch_array($result);

list($event_id, $event_date, $event_time, $event_am_pm,$first, $last) = $row;


//format time
$appoint_time = $hour.":".$minutes." ".$am_pm;

//match appointment time on right to time listing on left
if(in_array($appoint_time, $time_60min_array))
{
$s_event_id = $event_id;
$client_name = $last.", ".$first;
}

//display list
echo"<tr height=\"10\">
<td width=30% height=\"10\" bgcolor=\"$bgcolor\"
align=\"center\">$time_interval[$i]</td>
<td width=\"70%\" height=\"10\" bgcolor=\"$bgcolor\"><span
class=\"style20\">$client_name</td>\n";
echo"</tr>\n";
}
?>
[/php]

[assgar]

I forgot to add the end of the table structure

[php]
<?
echo"</div>\n";
echo "</div>\n";
echo "</td>\n";
echo "</tr>\n";
echo "</table>\n";
echo "</table>\n";
?>
[/php]

[pbmods]

Heya, assgar.

I forgot to add the end of the table structure

I think it's safe to say that that part is not the problem ~_^

[EDIT: Also not directly related, but you might be interested. DISTINCT is not a function in MySQL. So the parenthesis in your query aren't actually doing anything.

Your query gets evaluated like this (note GROUP BY clause):

SELECT DISTINCT event_id, event_date, event_time, event_am_pm, first_name, last_name FROM cal_appointment WHERE event_date = '$event_date' GROUP BY event_id, event_date, event_time, event_am_pm, first_name, last_name ORDER BY event_date, event_am_pm, event_time

]

[assgar]

Heya, assgar.


I think it's safe to say that that part is not the problem ~_^

[EDIT: Also not directly related, but you might be interested. DISTINCT is not a function in MySQL. So the parenthesis in your query aren't actually doing anything.

Your query gets evaluated like this (note GROUP BY clause):

SELECT DISTINCT event_id, event_date, event_time, event_am_pm, first_name, last_name FROM cal_appointment WHERE event_date = '$event_date' GROUP BY event_id, event_date, event_time, event_am_pm, first_name, last_name ORDER BY event_date, event_am_pm, event_time

]

Thanks for the info on the select statement I changed it. This though is not a solution to matching the schedule time eg 07:00 AM to the client name with an appointment for 07:00AM.

[assgar]

Hello
Thanks for the info its was the best way to make this work.

This is the solution, this final code allows you to double book appointments.


[php]
<?

/**---------------------------appointment interval--------------------------**/
SWITCH($time_interval)
{
case 10:
$add_time = 600; //10 min appointment
break;

case 15:
$add_time = 900; //15 min appointment
break;

case 20:
$add_time = 1200; //20 min appointment
break;

case 30:
$add_time = 1800; //30 min appointment
break;

case 60:
$add_time = 3600; //60 min appointment
break;

default:
$add_time = 900; //15 min appointment
break;
}
/**-------------------------------search by date-----------------------------------**/
$query = "SELECT event_id, event_date, event_time,
first_name, last_name
FROM cal_appointment
WHERE a.event_date = '$event'
GROUP BY a.event_id, a.event_date, a.event_time,
first_name, last_name
ORDER BY event_time, last_name, first_name
";

$result = mysqli_query($mysqli, $query) or die('Error, query failed');


/************************** this section displays events********************************/

/**-Note: <table> tags etc. go here**/

//declare array
$events = array();

//Storing the rows rather than outputting them immediately
while($row = mysqli_fetch_array($result))
{
$events[] = $row;
}

//Loop over the hours from 9AM to 6PM
for ($time = $start_time; $time <= $end_time; $time += $add_time)
{
//format 24 hour time interval for passing via url
$interval_24hr = date("H:i:s", $time);


echo "<tr>";
//Output the time interval label
echo"<td width=\"10%\" height=\"15\" bgcolor=\"#e6e8fa\" align=\"center\">".date("h:i A", $time)."</td>";


//start of next cell
foreach ($events as $event)
{
list($event_hr,$event_min,$event_sec) = split(":",$event['event_time']);

//convert event time for comparison
$event_time = mktime($event_hr, $event_min, $event_sec);

//Event falls into this hour
if($event_time >= $time && $event_time < ($time + $add_time))
{
//format client name
$client_name = $event['last_name'].", ".$event['first_name'];

//looping data diaplayed
echo "<td width=\"20%\" height=\"10\" bgcolor=\"$bgcolor\"><span class=\"style20\"><div style = \"margin-left:10;\">$client_name</td>";
}//end if

}//end foreach

echo "</tr>";
}//end for

/**Note: end of </table> etc. goes here**/
}
?>
[/php]