Layout mysql query in separate tables based on value in column

[lspiehler]

To accomplish the layout I was looking for, I used the following code:

 

for ($i=1; $i<=7; $i++)
  	{

//set day of week for display as title of columns
$dow=date("l", strtotime($year.'W'."$weekno"."$i"));

echo "<td class=\"wvcolumn\" valign=\"top\">
<table cellpadding=\"0\" cellspacing=\"0\" border=\"0\" align=\"center\" width=\"$daycolumn\">
<tr><th class=\"wvtitle\" valign=\"bottom\" align=\"center\" width=\"$daycolumn\"><a class=\"titledate\" href=\"createticket.php?action=o&month=".date("m", strtotime($year.'W'."$weekno"."$i"))."&date=".date("d", strtotime($year.'W'."$weekno"."$i"))."&year=".date("Y", strtotime($year.'W'."$weekno"."$i"))."\">$dow, ".date("F d", strtotime($year.'W'.$weekno."$i"))."</a> <a onClick=\"window.open('http://www.batchgeo.com')\" title=\"Map $dow's Appointments\" href=\"mapday.php?week=$weekno&year=$year&day=$i\"><img width=\"20\" style=\"border-style: none\" src=\"../images/icon_globe.png\"></a></th></tr>";

if(date("Y-m-d")==date("Y-m-d", strtotime($year.'W'."$weekno"."$i"))) {
	$query = "SELECT fieldtickets.ticketnumber, fieldtickets.business, title, apptdatetime, DATE_FORMAT(apptdatetime,'%h:%i %p') AS fapptdatetime, status, billstatus, type, assigntech, clients.business FROM fieldtickets LEFT JOIN clients ON fieldtickets.business = clients.id WHERE status='Open' AND (type = 'Field' OR type = 'Phone') AND apptdatetime BETWEEN '".date("Y-m-d", strtotime($year.'W'."$weekno"."$i"))." 00:00:00' AND '".date("Y-m-d", strtotime($year.'W'."$weekno"."$i"))." 23:59:59' UNION SELECT fieldtickets.ticketnumber, fieldtickets.business, title, apptdatetime, DATE_FORMAT(apptdatetime,'%h:%i %p') AS fapptdatetime, status, billstatus, type, assigntech, clients.business FROM fieldtickets LEFT JOIN clients ON fieldtickets.business = clients.id WHERE status = 'Pending' ORDER BY status ASC, apptdatetime ASC";
	} else { $query = "SELECT fieldtickets.ticketnumber, fieldtickets.business, title, apptdatetime, DATE_FORMAT(apptdatetime,'%h:%i %p') AS fapptdatetime, status, billstatus, type, assigntech, clients.business FROM fieldtickets LEFT JOIN clients ON fieldtickets.business = clients.id WHERE status='Open' AND (type = 'Field' OR type = 'Phone') AND apptdatetime BETWEEN '".date("Y-m-d", strtotime($year.'W'.$weekno."$i"))." 00:00:00' AND '".date("Y-m-d", strtotime($year.'W'.$weekno."$i"))." 23:59:59' ORDER BY apptdatetime ASC"; }

$result = mysql_query($query) or die(mysql_error());

while($row = mysql_fetch_array($result)){
	//if ticket is open show appt time and business, if ticket pending display DELIVERY and business name
	if($row[status]=="Open") { $href="createticket.php?action=e&ticket=".$row[ticketnumber]; $time=$row[fapptdatetime]; $delivery=""; } elseif($row[status]=="Pending") { $href="createticket.php?action=c&ticket=".$row['ticketnumber'].""; $time=""; $delivery="DELIVER"; }
	echo "<tr><td valign=\"top\"><a title=\"".$row[ticketnumber]." - ".$row[title]."\" class=\"".$row[status]."\" href=\"$href\"><span>$delivery$time ".substr($row['business'], 0, $displaychars)."</span></a></td></tr>\n"; }

echo "</table>";

}

echo "</td></tr>
</table>";

 

Output is similar to this, and any existing "deliveries" are displayed under the current day.

_______________________________________________________________

|    Monday    |    Tuesday    |    Wednesday    |    Thursday    |      Friday      |

|appointments |appointments |appointments    |appointments  |appointments

 

This works great, but requires 5 separate queries. My database is very small for now, so its not a big deal, but I know this can be done much more efficiently.

 

How can I query the whole week and put appointments in their corresponding tables (days)? Thank you for your help!

[kickstart]

Hi

 

Should be possible based on something like this (not tested so excuse any typos):-

 

SELECT fieldtickets.ticketnumber, fieldtickets.business, title, apptdatetime, DATE_FORMAT(apptdatetime,'%h:%i %p') AS fapptdatetime, status, billstatus, type, assigntech, clients.business, DAYOFWEEK(apptdatetime)
FROM fieldtickets 
LEFT JOIN clients ON fieldtickets.business = clients.id 
WHERE status='Open' 
AND (type = 'Field' OR type = 'Phone') 
AND YEARWEEK(apptdatetime) = '".date('YW')."'
ORDER BY apptdatetime ASC"

 

This should get you all the records which are the same year and week as the current year and week.

 

It returns the day of the week as one field. You should be able to bring those all back and output them onto the screen. Possibly sort by the day of the week to make it easier.

 

All the best

 

Keith

[lspiehler]

Thank you, but I understand how to get the entire week from mysql. In my attempts to accomplish my goal, I've even created an additional column so each appointment would tell me which day of the week it was on (and ordered by this starting with monday), but i couldn't figure how to get all this to lay out the way I described above.

[kickstart]

Hi

 

Doing it as a table will be clunky (although possible).

 

Using DIVs would be easier. Something like this.

 

$DivArray = array(1=>'Sunday<br />',2=>'Monday<br />',3=>'Tuesday<br />',4=>'Wednesday<br />',5=>'Thursday<br />',6=>'Friday<br />',7=>'Saturday<br />');
$result = mysql_query($query) or die(mysql_error());
while($row = mysql_fetch_array($result))
{
$DivArray[$row['DayOfWeek']] .= $row['ticketnumber'].'<br />';
}

foreach($DivArray AS $ThisDiv)
{
echo "<div>$ThisDiv</div>";
}

 

Style the Divs to float left and you should have columns.

 

Trying to think of a clean way to do it using tables.

 

$DivArray = array(1=>array(),2=>array(),3=>array(),4=>array(),5=>array(),6=>array(),7=>array());
$result = mysql_query($query) or die(mysql_error());
while($row = mysql_fetch_array($result))
{
$DivArray[$row['DayOfWeek']][] .= $row['ticketnumber'];
}

$rownum = 0;

while (array_key_exists($DivArray[1][$rownum]) OR array_key_exists($DivArray[2][$rownum]) OR array_key_exists($DivArray[3][$rownum]) OR array_key_exists($DivArray[4][$rownum]) OR array_key_exists($DivArray[5][$rownum]) OR array_key_exists($DivArray[6][$rownum]) OR array_key_exists($DivArray[7][$rownum])
{
echo '<tr><td>'.((array_key_exists($DivArray[1][$rownum])) ? $DivArray[1][$rownum] : ' ' ).'</td>';
echo '<td>'.((array_key_exists($DivArray[2][$rownum])) ? $DivArray[2][$rownum] : ' ' ).'</td>';
echo '<td>'.((array_key_exists($DivArray[3][$rownum])) ? $DivArray[3][$rownum] : ' ' ).'</td>';
echo '<td>'.((array_key_exists($DivArray[4][$rownum])) ? $DivArray[4][$rownum] : ' ' ).'</td>';
echo '<td>'.((array_key_exists($DivArray[5][$rownum])) ? $DivArray[5][$rownum] : ' ' ).'</td>';
echo '<td>'.((array_key_exists($DivArray[6][$rownum])) ? $DivArray[6][$rownum] : ' ' ).'</td>';
echo '<td>'.((array_key_exists($DivArray[7][$rownum])) ? $DivArray[7][$rownum] : ' ' ).'</td></tr>';
if (!array_key_exists($DivArray[1][$rownum]) AND !array_key_exists($DivArray[2][$rownum]) AND !array_key_exists($DivArray[3][$rownum]) AND !array_key_exists($DivArray[4][$rownum]) AND !array_key_exists($DivArray[5][$rownum]) AND !array_key_exists($DivArray[6][$rownum]) AND !array_key_exists($DivArray[7][$rownum])
$rownum++;
}

 

Something like that maybe (not tested), but it is fairly clunky.

 

All the best

 

Keith

[lspiehler]

I'm making progress as I understand this code. I'll will post my final "production" code when I'm finished. Thank you so much. I've learned a lot from this!

[lspiehler]

Finished up using tables. I plan to learn more about CSS before using the DIVs. Again, lots of thanks to Keith. Here's the final code:

 

$numdays=5;
echo "<table align=\"center\" width=\"$workweekwidth\"><tr><td align=\"left\"><a href=\"workweek.php?week=$previousweek$previousyear\">Previous</a></td><td align=\"right\"><a href=\"workweek.php?week=$nextweek$nextyear\">Next</a></td></tr></table>
<table height=\"200\" border=\"0\" cellpadding=\"0\" cellspacing=\"0\" align=\"center\" width=\"$workweekwidth\">
<tr>";

$query = "SELECT fieldtickets.ticketnumber, fieldtickets.business, title, apptdatetime, DATE_FORMAT(apptdatetime,'%w') AS wapptdatetime, DATE_FORMAT(apptdatetime,'%h:%i %p') AS fapptdatetime, status, billstatus, type, assigntech, clients.business FROM fieldtickets LEFT JOIN clients ON fieldtickets.business = clients.id WHERE status='Open' AND (type = 'Field' OR type = 'Phone') AND apptdatetime BETWEEN '".date("Y-m-d", strtotime($year.'W'.$weekno."1"))." 00:00:00' AND '".date("Y-m-d", strtotime($year.'W'.$weekno."5"))." 23:59:59' ORDER BY wapptdatetime, apptdatetime ASC";

//build initial, multidimensional array for layout while setting title for each column, start array with 1, not 0
for ($i=1; $i<=$numdays; $i++)
  	{
$dow=date("l", strtotime($year.'W'."$weekno"."$i"));

$DivArray[$i] = "<tr><th class=\"wvtitle\" valign=\"bottom\" align=\"center\" width=\"$daycolumn\"><a class=\"titledate\" href=\"createticket.php?action=o&month=".date("m", strtotime($year.'W'."$weekno"."$i"))."&date=".date("d", strtotime($year.'W'."$weekno"."$i"))."&year=".date("Y", strtotime($year.'W'."$weekno"."$i"))."\">$dow, ".date("F d", strtotime($year.'W'.$weekno."$i"))."</a> <a onClick=\"window.open('http://www.batchgeo.com')\" title=\"Map $dow's Appointments\" href=\"mapday.php?week=$weekno&year=$year&day=$i\"><img width=\"20\" style=\"border-style: none\" src=\"../images/icon_globe.png\"></a></th></tr>";
}

$result = mysql_query($query) or die(mysql_error());

while($row = mysql_fetch_array($result)){

//fill each array with information for relevant column
for ($i=1; $i<=$numdays; $i++)
  	{
if($row[wapptdatetime]==$i) {
$DivArray[$row['wapptdatetime']] .= "<tr><td valign=\"top\"><a title=\"".$row[ticketnumber]." - ".$row[title]."\" class=\"".$row[status]."\" href=\"createticket.php?action=e&ticket=".$row[ticketnumber]."\"><span>".$row[fapptdatetime]." ".substr($row['business'], 0, $displaychars)."</span></a></td></tr>\n";
}
}

}

foreach($DivArray AS $ThisDiv)
{
echo "<td class=\"wvcolumn\" valign=\"top\">
<table cellpadding=\"0\" cellspacing=\"0\" border=\"0\" align=\"center\" width=\"$daycolumn\">$ThisDiv</table>";
}

echo "</tr></table><br />";