jetski423 Posted July 22, 2011 Share Posted July 22, 2011 Since we upgraded it seems I've lost some reporting functionality... I'm not sure if it's PHP or a bad MySQL statement but everything pulls data except this line: $todays_reservations = $reservations[$day][$reservationist["username"]] Is my syntax incorrect to the new standard? It should be looking up how many days a reservationist has booked for each day. This all worked fine on a PHP4/MySQL 3.2.3 setup. EX.: Day 1 2 3 4 Jeff 5 2 5 8 $SQLreservationist = "Select username from admin_users"; $qreservationist = mysql_query($SQLreservationist); //pre-process the list to skip anybody who didn't have any reservations this month foreach($reservations as $subarray) { foreach($subarray as $user_name => $res_count) { $reservationist_total[$user_name] += $res_count; } } while($reservationist = mysql_fetch_array($qreservationist)) { if($reservationist_total[$reservationist["username"]] == 0) { continue; } $MTDreservations = 0; ?> <td><?=$reservationist["username"] ?> </td> <? for($i="01"; $i <= $lastday; $i++) { $day = sprintf("%04d%02d%02d", $year, $month, $i); $todays_reservations = $reservations[$day][$reservationist["username"]]; $MTDreservations += $todays_reservations; $daily["$day"] += $todays_reservations; ?> <td align=right><?=$todays_reservations ?></td> Quote Link to comment Share on other sites More sharing options...
gristoi Posted July 22, 2011 Share Posted July 22, 2011 turn on your error reporting and see what error it throws: <?php ini_set('display_errors',1); error_reporting(E_ALL); ?> put that at the top of your script, just below the opening php tag. post the errors here Quote Link to comment Share on other sites More sharing options...
jetski423 Posted July 22, 2011 Author Share Posted July 22, 2011 Mainly just notices. I just tried to run this against my old SQL 3.23 and it worked. Maybe my query statements are now outdated a little bit? Notice: Use of undefined constant d - assumed 'd' in /home/cfv/public_html/admin/resvprod.php on line 6 Notice: Use of undefined constant m - assumed 'm' in /home/cfv/public_html/admin/resvprod.php on line 8 Notice: Use of undefined constant Y - assumed 'Y' in /home/cfv/public_html/admin/resvprod.php on line 10 Notice: Undefined variable: SQLnewreservation in /home/cfv/public_html/admin/resvprod.php on line 76 Notice: Undefined variable: reservations in /home/cfv/public_html/admin/resvprod.php on line 81 Notice: Undefined index: 2011-07- in /home/cfv/public_html/admin/resvprod.php on line 81 Notice: Undefined index: Online Reservation in /home/cfv/public_html/admin/resvprod.php on line 81 Notice: Undefined index: scott in /home/cfv/public_html/admin/resvprod.php on line 81 Notice: Undefined index: lindsey in /home/cfv/public_html/admin/resvprod.php on line 81 Notice: Undefined index: shaun in /home/cfv/public_html/admin/resvprod.php on line 81 Notice: Undefined index: jamie in /home/cfv/public_html/admin/resvprod.php on line 81 Notice: Undefined index: james in /home/cfv/public_html/admin/resvprod.php on line 81 Notice: Undefined index: debbie in /home/cfv/public_html/admin/resvprod.php on line 81 Notice: Undefined index: kenny in /home/cfv/public_html/admin/resvprod.php on line 81 Notice: Undefined index: lee in /home/cfv/public_html/admin/resvprod.php on line 81 Notice: Undefined index: courtney in /home/cfv/public_html/admin/resvprod.php on line 81 Notice: Undefined index: amanda in /home/cfv/public_html/admin/resvprod.php on line 81 Notice: Undefined index: Melena in /home/cfv/public_html/admin/resvprod.php on line 81 Notice: Undefined index: Alan in /home/cfv/public_html/admin/resvprod.php on line 81 Notice: Undefined index: Online Reservation in /home/cfv/public_html/admin/resvprod.php on line 92 Notice: Undefined index: scott in /home/cfv/public_html/admin/resvprod.php on line 92 Notice: Undefined index: lindsey in /home/cfv/public_html/admin/resvprod.php on line 92 Notice: Undefined index: shaun in /home/cfv/public_html/admin/resvprod.php on line 92 Notice: Undefined index: jamie in /home/cfv/public_html/admin/resvprod.php on line 92 Notice: Undefined index: james in /home/cfv/public_html/admin/resvprod.php on line 92 Notice: Undefined index: debbie in /home/cfv/public_html/admin/resvprod.php on line 92 Notice: Undefined index: kenny in /home/cfv/public_html/admin/resvprod.php on line 92 Notice: Undefined index: lee in /home/cfv/public_html/admin/resvprod.php on line 92 Notice: Undefined index: courtney in /home/cfv/public_html/admin/resvprod.php on line 92 Notice: Undefined index: amanda in /home/cfv/public_html/admin/resvprod.php on line 92 Notice: Undefined index: Melena in /home/cfv/public_html/admin/resvprod.php on line 92 Notice: Undefined index: Alan in /home/cfv/public_html/admin/resvprod.php on line 92 Notice: Undefined index: 20110701 in /home/cfv/public_html/admin/resvprod.php on line 116 Notice: Undefined index: 20110702 in /home/cfv/public_html/admin/resvprod.php on line 116 Notice: Undefined index: 20110703 in /home/cfv/public_html/admin/resvprod.php on line 116 Notice: Undefined index: 20110704 in /home/cfv/public_html/admin/resvprod.php on line 116 Notice: Undefined index: 20110705 in /home/cfv/public_html/admin/resvprod.php on line 116 Notice: Undefined index: 20110706 in /home/cfv/public_html/admin/resvprod.php on line 116 Notice: Undefined index: 20110707 in /home/cfv/public_html/admin/resvprod.php on line 116 Notice: Undefined index: 20110708 in /home/cfv/public_html/admin/resvprod.php on line 116 Notice: Undefined index: 20110709 in /home/cfv/public_html/admin/resvprod.php on line 116 Notice: Undefined index: 20110710 in /home/cfv/public_html/admin/resvprod.php on line 116 Notice: Undefined index: 20110711 in /home/cfv/public_html/admin/resvprod.php on line 116 Notice: Undefined index: 20110712 in /home/cfv/public_html/admin/resvprod.php on line 116 Notice: Undefined index: 20110713 in /home/cfv/public_html/admin/resvprod.php on line 116 Notice: Undefined index: 20110714 in /home/cfv/public_html/admin/resvprod.php on line 116 Notice: Undefined index: 20110715 in /home/cfv/public_html/admin/resvprod.php on line 116 Notice: Undefined index: 20110716 in /home/cfv/public_html/admin/resvprod.php on line 116 Notice: Undefined index: 20110717 in /home/cfv/public_html/admin/resvprod.php on line 116 Notice: Undefined index: 20110718 in /home/cfv/public_html/admin/resvprod.php on line 116 Notice: Undefined index: 20110719 in /home/cfv/public_html/admin/resvprod.php on line 116 Notice: Undefined index: 20110720 in /home/cfv/public_html/admin/resvprod.php on line 116 Notice: Undefined index: 20110721 in /home/cfv/public_html/admin/resvprod.php on line 116 Notice: Undefined index: 20110722 in /home/cfv/public_html/admin/resvprod.php on line 116 Notice: Undefined index: 20110723 in /home/cfv/public_html/admin/resvprod.php on line 116 Notice: Undefined index: 20110724 in /home/cfv/public_html/admin/resvprod.php on line 116 Notice: Undefined index: 20110725 in /home/cfv/public_html/admin/resvprod.php on line 116 Notice: Undefined index: 20110726 in /home/cfv/public_html/admin/resvprod.php on line 116 Notice: Undefined index: 20110727 in /home/cfv/public_html/admin/resvprod.php on line 116 Notice: Undefined index: 20110728 in /home/cfv/public_html/admin/resvprod.php on line 116 Notice: Undefined index: 20110729 in /home/cfv/public_html/admin/resvprod.php on line 116 Notice: Undefined index: 20110730 in /home/cfv/public_html/admin/resvprod.php on line 116 Notice: Undefined index: 20110731 in /home/cfv/public_html/admin/resvprod.php on line 116 Notice: Undefined variable: MTD_total in /home/cfv/public_html/admin/resvprod.php on line 127 Notice: Undefined variable: Rev_total in /home/cfv/public_html/admin/resvprod.php on line 128 Notice: Undefined variable: Pot_total in /home/cfv/public_html/admin/resvprod.php on line 129 Full code: <? error_reporting(E_ALL); ?> <? include("header.php"); $day = date(d); if(!isset($month)) $month= date(m); if(!isset($year)) $year=date(Y); $firstday = "01"; //$firstday="18"; $lastday = 31; while(!checkdate($month,$lastday,$year)) { $lastday--; } $timestart =mktime(0, 0, 0, $month, $firstday, $year); $timeend = mktime(23, 59, 59, $month, $lastday, $year); ?> <h1> Reservations Productivity Report </h1> <form method="get" action="resvprod.php"> <select name=month> <? for($i = 1; $i <= 12; $i++) { ?> <option value="<?=$i?>" <?=($i==$month)?"SELECTED":""?>><?=strftime("%B", mktime(0,0,0,$i,1,2004));?></option> <? } ?> </select> <select name=year> <? for($i = 2001; $i <= date(Y); $i++) { ?> <option value="<?=$i?>" <?=($i==$year)?"SELECTED":""?>><?=$i?></option> <? } ?> </select> <input type=submit value="Go"> </form> <table border=0> <tr> <td><b>Reservationist </b></td> <? for($i=1; $i <= $lastday; $i++) { ?> <td style="width:12px; font-weight: bold;" align=right><?=$i ?> </td> <? } ?> <td><b>MTD </b></td> <td><b>Potential Revenue </b></td> <td><b>Actual Revenue </b></td> <td><b>Percentage</b></td> </tr> <tr> <? $tstamp_start = strftime("%Y%m%d000000", $timestart); $tstamp_end = strftime("%Y%m%d235959", $timeend); $SQLdiscount = "Select username, sum(value) as discount from reservation, res_log, adjustments, admin_users where res_log.reservationist_id=admin_users.id and reservation.id=res_log.reservation_id and reservation.id=adjustments.reservation_id and res_log.type='1' and reservation.type='Guest' and res_log.timestamp >='$tstamp_start' and res_log.timestamp <='$tstamp_end' GROUP BY res_log.reservationist_id"; $qdiscount= mysql_query($SQLdiscount); while($discount = mysql_fetch_array($qdiscount)) { $adjustment[$discount["username"]] = $discount["discount"]; } $SQLamount = "Select username, sum(rate) as rate from reservation, res_log, reservation_rates, admin_users where res_log.reservationist_id=admin_users.id and reservation.id=res_log.reservation_id and reservation.id=reservation_rates.reservation_id and res_log.type='1' and reservation.type='Guest' and res_log.timestamp >='$tstamp_start' and res_log.timestamp <='$tstamp_end' GROUP BY res_log.reservationist_id"; $qamount = mysql_query($SQLamount); while($amount = mysql_fetch_array($qamount)) { $rate[$amount["username"]] = $amount["rate"]; } $SQLnumreserv = "Select username, COUNT(res_log.reservation_id) as numresv, LEFT(res_log.timestamp, as tstamp from res_log, admin_users, reservation where reservation.id=reservation_id and reservationist_id=admin_users.id and res_log.timestamp >='$tstamp_start' and res_log.timestamp<='$tstamp_end' and res_log.type='1' and reservation.type='Guest' GROUP BY LEFT(res_log.timestamp,, username ORDER BY res_log.reservationist_id"; $newreservation= mysql_query($SQLnewreservation); $numresrv= mysql_query($SQLnumreserv); while($reserv = mysql_fetch_array($numresrv)){ $reservations[$reserv["tstamp"]][$reserv["username"]] += $reserv["numresv"]; } $SQLreservationist = "Select username from admin_users"; $qreservationist = mysql_query($SQLreservationist); //pre-process the list to skip anybody who didn't have any reservations this month foreach($reservations as $subarray) { foreach($subarray as $user_name => $res_count) { $reservationist_total[$user_name] += $res_count; } } $colors = Array("#CCCCCC", "#EEEEEE"); $j =0; while($reservationist = mysql_fetch_array($qreservationist)) { if((isset($reservationist_total[$reservationist["username"]])) == 0) { continue; } $MTDreservations = 0; $j %= count($colors); ?> <tr style="background-color:<?=$colors[$j]?>; border-left:1px solid black"> <? $j++; ?> <td><?=$reservationist["username"] ?> </td> <? for($i="01"; $i <= $lastday; $i++) { $day = sprintf("%04d%02d%02d", $year, $month, $i); $todays_reservations = (isset($reservations[$day][$reservationist["username"]])); $MTDreservations += $todays_reservations; $daily["$day"] += $todays_reservations; ?> <td align=right><?=$todays_reservations ?></td> <? } $user = $reservationist["username"]; $Revenue = $rate[$user]; $Potrev = $rate[$user] + $adjustment[$user]; $Percentage = ($Revenue > 0) ? ($Potrev/$Revenue) : 0; $MTD_total += $MTDreservations; $Rev_total += $Revenue; $Pot_total += $Potrev; ?> <td align=right><?= ($MTDreservations) ? $MTDreservations : "-" ?> </td> <td align=right><?= ($Revenue) ? number_format($Revenue, 2) : "-" ?> </td> <td align=right><?= ($Potrev) ? number_format($Potrev,2) : "-" ?> </td> <td align=right><?= ($Percentage) ? sprintf("%.2f%%", $Percentage * 100.0) : "-"?> </td> </tr> <? } ?> <tr> <td><b>Daily Total </b></td> <? for($i="01"; $i <= $lastday; $i++) { $day = sprintf("%04d%02d%02d", $year, $month, $i); ?> <td><b><?=isset($daily["$day"]) ?></b></td> <? } ?> <td><b><?= $MTD_total ?></b></td> <td align=right><b><?= ($Rev_total) ? number_format($Rev_total, 2) : "-" ?></b></td> <td align=right><b><?= ($Pot_total) ? number_format($Pot_total, 2) : "-" ?></b> </td> <td align=right><b><?= ($Rev_total) ? sprintf("%.2f%%", ($Pot_total/$Rev_total)*100.0) : "-"?></td> </tr> </table> Quote Link to comment Share on other sites More sharing options...
PFMaBiSmAd Posted July 22, 2011 Share Posted July 22, 2011 Did your mysql get upgraded too? You didn't exactly state so. It's more likely that you either don't have any matching data (or no data at all) in your database or that the conditions being produced by php (the strftime() statements) going into the query statements are not what you think. Have you echoed out the sql query statement(s) to see what they actually are and used print_r or var_dump on the data to check if it is what you expect? Quote Link to comment Share on other sites More sharing options...
jetski423 Posted July 22, 2011 Author Share Posted July 22, 2011 I dumped the DB from 3.23 and imported into 5.0. Out of over 100 pages, this is the only one that has a major issue. I can see the correct reservationists being pulled and printed and even their earnings.... total reservations listed by each day, however, do not work. Any recommendations on which query or variable to print_r on? <?=$todays_reservations ?> - This doesn't produce anything, but the others work fine :-\ Quote Link to comment Share on other sites More sharing options...
PFMaBiSmAd Posted July 22, 2011 Share Posted July 22, 2011 echo '<pre>'; var_dump($reservations); echo '</pre>'; Quote Link to comment Share on other sites More sharing options...
jetski423 Posted July 22, 2011 Author Share Posted July 22, 2011 var_dump($reservations); = array(1) { ["2011-07-"]=> array(13) { ["Online Reservation"]=> int(194) ["scott"]=> int(1) ["lindsey"]=> int(33) ["shaun"]=> int(1) ["jamie"]=> int(11) ["james"]=> int(32) ["debbie"]=> int(44) ["kenny"]=> int(24) ["lee"]=> int(34) ["courtney"]=> int(11) ["amanda"]=> int(32) ["Melena"]=> int(31) ["Alan"]=> int(24) } } Quote Link to comment Share on other sites More sharing options...
PFMaBiSmAd Posted July 22, 2011 Share Posted July 22, 2011 Seriously, did you look at YOUR data that was displayed to see what was wrong with it? The date index is only "2011-07-". How is that going to match the $day variable which would have values like "2011-07-01". The LEFT(res_log.timestamp, as tstamp term in your query is only giving "2011-07-". Since I don't know if/how/or what mysql 3 returned for a DATE/DATETIME/TIMESTAMP data type, you would need to change any usage of LEFT(..., to LEFT(...,10) or you can just use the mysql DATE() function to get the date part of a DATETIME/TIMESTAMP data type. Quote Link to comment Share on other sites More sharing options...
jetski423 Posted July 22, 2011 Author Share Posted July 22, 2011 '$day' - prints out "string( "20110731"" $reservations - prints out a good looking: array(22) { ["2011-07-06"]=> array(7) { ["Online Reservation"]=> int(10) ["lindsey"]=> int(1) ["jamie"]=> int(1) ["james"]=> int(1) ["debbie"]=> int(2) ["kenny"]=> int(2) ["amanda"]=> int(4) } ["2011-07-22"]=> array(5) { ["Online Reservation"]=> int(2) ["jamie"]=> int(2) ["debbie"]=> int(2) ["lee"]=> int(1) ["Melena"]=> int(2) } ["2011-07-01"]=> array(9) { ["Online Reservation"]=> int(10) ["lindsey"]=> int(1) ["james"]=> int(4) ["debbie"]=> int(4) ["kenny"]=> int(1) ["courtney"]=> int(1) ["amanda"]=> int(3) ["Melena"]=> int(2) ["Alan"]=> int(2) } ["2011-07-17"]=> array(4) { ["Online Reservation"]=> int(14) ["james"]=> int(1) ["Melena"]=> int(3) ["Alan"]=> int(2) } ["2011-07-12"]=> array(7) { ["Online Reservation"]=> int( ["lindsey"]=> int(5) ["kenny"]=> int(2) ["lee"]=> int(3) ["amanda"]=> int(3) ["Melena"]=> int(2) ["Alan"]=> int(1) } ["2011-07-07"]=> array(6) { ["Online Reservation"]=> int(11) ["lindsey"]=> int(2) ["jamie"]=> int(1) ["james"]=> int(6) ["debbie"]=> int(3) ["courtney"]=> int(3) } ["2011-07-02"]=> array(5) { ["Online Reservation"]=> int( ["james"]=> int(3) ["lee"]=> int(3) ["courtney"]=> int(2) ["Alan"]=> int(2) } ["2011-07-18"]=> array(7) { ["Online Reservation"]=> int( ["james"]=> int(1) ["debbie"]=> int(3) ["lee"]=> int(3) ["courtney"]=> int(1) ["amanda"]=> int(2) ["Alan"]=> int(3) } ["2011-07-13"]=> array(7) { ["Online Reservation"]=> int(10) ["lindsey"]=> int(2) ["james"]=> int(1) ["debbie"]=> int(5) ["lee"]=> int(2) ["courtney"]=> int(1) ["Melena"]=> int(4) } ["2011-07-08"]=> array(5) { ["Online Reservation"]=> int(10) ["lindsey"]=> int(3) ["debbie"]=> int(1) ["kenny"]=> int(5) ["Melena"]=> int( } ["2011-07-03"]=> array(2) { ["Online Reservation"]=> int(4) ["amanda"]=> int(1) } ["2011-07-19"]=> array( { ["Online Reservation"]=> int(9) ["lindsey"]=> int(2) ["jamie"]=> int(2) ["james"]=> int(2) ["debbie"]=> int(2) ["kenny"]=> int(2) ["lee"]=> int(4) ["Melena"]=> int(1) } ["2011-07-14"]=> array(7) { ["Online Reservation"]=> int(10) ["lindsey"]=> int(1) ["shaun"]=> int(1) ["james"]=> int(2) ["debbie"]=> int(4) ["lee"]=> int(3) ["Alan"]=> int(1) } ["2011-07-09"]=> array(5) { ["Online Reservation"]=> int(11) ["james"]=> int(5) ["lee"]=> int(5) ["amanda"]=> int(4) ["Alan"]=> int(1) } ["2011-07-04"]=> array(7) { ["Online Reservation"]=> int( ["debbie"]=> int(4) ["kenny"]=> int(2) ["courtney"]=> int(1) ["amanda"]=> int(2) ["Melena"]=> int(2) ["Alan"]=> int(1) } ["2011-07-20"]=> array(9) { ["Online Reservation"]=> int(3) ["scott"]=> int(1) ["lindsey"]=> int(2) ["jamie"]=> int(1) ["james"]=> int(2) ["debbie"]=> int(1) ["kenny"]=> int(4) ["amanda"]=> int(1) ["Melena"]=> int(1) } ["2011-07-15"]=> array(6) { ["Online Reservation"]=> int( ["debbie"]=> int(5) ["lee"]=> int(4) ["courtney"]=> int(2) ["amanda"]=> int(3) ["Alan"]=> int(2) } ["2011-07-10"]=> array(5) { ["Online Reservation"]=> int(13) ["james"]=> int(4) ["lee"]=> int(1) ["Melena"]=> int(3) ["Alan"]=> int(1) } ["2011-07-05"]=> array(5) { ["Online Reservation"]=> int( ["lindsey"]=> int(1) ["jamie"]=> int(1) ["debbie"]=> int(1) ["amanda"]=> int(4) } ["2011-07-21"]=> array(7) { ["Online Reservation"]=> int( ["lindsey"]=> int(5) ["jamie"]=> int(3) ["debbie"]=> int(4) ["kenny"]=> int(3) ["lee"]=> int(5) ["Alan"]=> int(2) } ["2011-07-16"]=> array(5) { ["Online Reservation"]=> int(14) ["lindsey"]=> int(2) ["amanda"]=> int(3) ["Melena"]=> int(3) ["Alan"]=> int(4) } ["2011-07-11"]=> array(6) { ["Online Reservation"]=> int(7) ["lindsey"]=> int(6) ["debbie"]=> int(3) ["kenny"]=> int(3) ["amanda"]=> int(2) ["Alan"]=> int(2) } } But I print: $todays_reservations, I get a bool(false)... Quote Link to comment Share on other sites More sharing options...
jetski423 Posted July 22, 2011 Author Share Posted July 22, 2011 Forgot to mention, I changed the LEFT(..., to LEFT(...,10). Looks like it's getting somewhere! I also modified the sprint to: sprintf("%04d-%02d-%02d", $year, $month, $i); Still stumped as to why this is returning bool(false): $todays_reservations = $reservations[$day][$reservationist["username"]] Quote Link to comment Share on other sites More sharing options...
jetski423 Posted July 22, 2011 Author Share Posted July 22, 2011 I think it's almost working... it's only printing out "1" reservation made per person is the only problem now. It prints 1 corresponding to the day they made it, even if they made 5 reservations.. Edit: turned off isset and it works!! Quote Link to comment 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.