lspiehler

Thanks for the education on functions. I've got what I need, and thanks for always being so fast. You guys are great! -Lyas

Why won't $assemblelist take the output of function this()? As you can see below, in my actual function, I need to be able to remove the last comma. <?php $list = array('apples', 'pears', 'bananas', 'oranges'); function this($array) { foreach($array as $fruits) { print "I like to eat ".$fruits.","; } } $assemblelist = this($list); $formattedlist = substr('$assemblelist', 0, -1); ?> Thank you for any help! -Lyas

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 />";

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!

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.

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!

I've got a table with 5 columns that shows appointments for Monday through Friday, but I'm using 5 separate queries, one for each column, for each corresponding day because i cannot figure out how to query the whole week and only put the appointments in the column they belong. Thank you for any help.

Trashed rollup and did all calculations with PHP. Here's the modified code: echo "<p align=\"center\"><font size=\"6\">Productivity Report</font></p> $types=array( 'Field', 'Phone', 'Office' ); foreach ($types as $type) { if($type=="Field") { $hourly=$configrow[fieldhourrate]; } elseif($type=="Phone") { $hourly=$configrow[phonehourrate]; } elseif($type=="Office") { $hourly=$configrow[officehourrate]; } setlocale(LC_MONETARY, 'en_US'); $query = "SELECT COUNT(fieldtickets.ticketnumber) AS numtickets, fieldtickets.type, SUM(fieldtickets.hours) AS totalhours , SUM( fieldtickets.hours * $hourly ) AS totalamt , fieldtickets.type, CONCAT( fname, ' ', lname ) AS fullname FROM fieldtickets RIGHT JOIN users ON fieldtickets.assigntech = users.id WHERE type = '$type' AND (status = 'Closed' OR status = 'Pending') $sqltech $sqldate GROUP BY fullname"; //$includerollup $result = mysql_query($query) or die(mysql_error()); echo "<table width=\"600\" align=\"center\"> <tr class=\"row\"><td align=\"center\" colspan=\"5\"><font size=\"5\"><b>$type - \$ $hourly/hr</b></font></td></tr> <tr><td width=\"195\"><b>Name</b></td><td width=\"135\"><b>Tickets</b></td><td width=\"135\"><b>Hours</b></td><td width=\"135\"><b>Total</b></td></tr>\n"; while($row = mysql_fetch_array($result)){ echo "<tr><td>".$row[fullname]."</td><td>".$row[numtickets]."</td><td>".$row[totalhours]."</td><td>".money_format('%(#8n', $row[totalamt])."</td></tr>\n"; $ticket_{$type}[] = $row[numtickets]; $hours_{$type}[] = $row[totalhours]; $total_{$type}[] = $row[totalamt]; } echo "<tr><td> </td><td><b>".array_sum($ticket_{$type})."</b></td><td><b>".array_sum($hours_{$type})."</b></td><td><b>".money_format('%(#8n', array_sum($total_{$type}))."</b></td></tr>\n </table><p> </p>\n"; } } else { echo "<p align=\"center\">This report generates the total tickets, hours, and billed amount for each type<br /> of ticket for the specified technician(s) within a given range.</p>"; } $totaltickets = array_sum($ticket_{Phone})+array_sum($ticket_{Office})+array_sum($ticket_{Field}); $totalhours = array_sum($hours_{Phone})+array_sum($hours_{Office})+array_sum($hours_{Field}); $totaltotal = array_sum($total_{Phone})+array_sum($total_{Office})+array_sum($total_{Field}); echo "<hr width=\"600\" /> <table width=\"600\" align=\"center\"> <tr><td width=\"195\"> </td><td width=\"135\"><font size=\"5\"><b>$totaltickets</b></font></td><td width=\"135\"><font size=\"5\"><b>$totalhours</b></font></td><td width=\"135\"><font size=\"5\"<b>".money_format('%(#8n', $totaltotal)."</b></font></td></tr> </table>";

I separated these queries for the sake of the layout I was trying to achieve, and I need to calculate a sub total for each of my 3 categories, plus a grand total which is just the sum of the three categories. I used "with rollup" to get my sub totals for each category, but I'm not sure how to go about getting the grand total. I was trying to get the subtotals into an array, that I could just sum up later. I was attempting to return the last value of my fetch array (which should have been the rollup value) into a new array to do array_sum afterward, but I couldn't even find a way to echo the value after the while loop. Resetting the array didn't work. Anyway here's the code: echo "<p align=\"center\"><font size=\"6\">Productivity Report</font></p>"; $types=array( 'Field', 'Phone', 'Office' ); foreach ($types as $type) { if($type=="Field") { $hourly=$configrow[fieldhourrate]; } elseif($type=="Phone") { $hourly=$configrow[phonehourrate]; } elseif($type=="Office") { $hourly=$configrow[officehourrate]; } setlocale(LC_MONETARY, 'en_US'); $query = "SELECT COUNT(fieldtickets.ticketnumber) AS numtickets, fieldtickets.type, SUM(fieldtickets.hours) AS totalhours , SUM( fieldtickets.hours * $hourly ) AS totalamt , fieldtickets.type, CONCAT( fname, ' ', lname ) AS fullname FROM fieldtickets RIGHT JOIN users ON fieldtickets.assigntech = users.id WHERE type = '$type' AND (status = 'Closed' OR status = 'Pending') GROUP BY fullname WITH ROLLUP"; $result = mysql_query($query) or die(mysql_error()); echo "<table width=\"600\" align=\"center\"> <tr class=\"row\"><td align=\"center\" colspan=\"5\"><font size=\"5\"><b>$type</b></font></td></tr> <tr><td><b>Name</b></td><td><b>Tickets</b></td><td><b>Hours</b></td><td><b>Rate</b></td><td><b>Total</b></td></tr>\n"; while($row = mysql_fetch_array($result)){ echo "<tr><td>".$row[fullname]."</td><td>".$row[numtickets]."</td><td>".$row[totalhours]."</td><td>".money_format('%(#10n', $hourly)."/hr</td><td>".money_format('%(#10n', $row[totalamt])."</td></tr>\n"; } echo "</table><br />\n"; Which outputs: Productivity Report Field Name Tickets Hours Rate Total Keith Dean 11 7.00 $ 95.00/hr $ 665.00 Lyas Spiehler 10 10.50 $ 95.00/hr $ 997.50 Melissa Burk 1 0.00 $ 95.00/hr $ 0.00 Mike Bieller 11 10.00 $ 95.00/hr $ 950.00 33 27.50 $ 95.00/hr $ 2,612.50 Phone Name Tickets Hours Rate Total Lyas Spiehler 2 2.00 $ 35.00/hr $ 70.00 Melissa Burk 1 1.00 $ 35.00/hr $ 35.00 3 3.00 $ 35.00/hr $ 105.00 Office Name Tickets Hours Rate Total Lyas Spiehler 1 1.00 $ 65.00/hr $ 65.00 Melissa Burk 2 2.00 $ 65.00/hr $ 130.00 Mike Bieller 1 1.00 $ 65.00/hr $ 65.00 4 4.00 $ 65.00/hr $ 260.00 It's a lot prettier in html... Any help is greatly appreciated. Thanks a lot!

I've got for ($i=1; $i<=5; $i++) { if(isset($_POST['partsusedqty'.$i]) && $_POST['partsusedqty'.$i] != "" && $_POST['partsusedqty'.$i] != "0.00") { mysql_query("INSERT INTO partsused (ptnumber, partqty, partdesc, partprice) VALUES ($ticket, '$partsusedqty'.$i, '$partsuseddesc'.$i, '$partsusedprice'.$i)") or die(mysql_error()); } } I need to know the correct formatting to put these variable variables as values in the mysql query. With this particular code, I get the error "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '.1, ''.1, ''.1)' at line 2" I've tried formatting this an endless number of ways, but I used this particular example because its the one I really thought should work. Everything I've tried that doesn't throw an error put the $partsusedqty in the partqty, partdesc, and partprice fields. Thanks for any help!

Forgive me. I seem to have completely forgot to mention my "special characters" in my original post, but DavidAM was right! After having checked my function, I realized I'd gone back earlier and escaped the hyphen and just never re-checked it. Certainly not a waste because now I understand. Thanks for all the help!

I understand that, but I'm not sure which one of us is misunderstanding the other. Those special characters that I need to allow, are not alphanumeric. So a string containing the characters I need to allow, using your validation, wouldn't allow me to use them. What I'm looking for is something that let's me use hyphens, single quotes, spaces and alphanumeric characters, which is what my function does, but my problem is that it also allows the "&" character for some reason, which is unacceptable. I feel like your not understanding me, but i am new at this, so maybe I'm just not grasping your concepts.

but will that allow my special characters? (- ')

I'm trying to make a function to validate business names, allowing numbers and letters only 3-40 characters, but It is still allowing the "&" symbol which will definitely cause problems. Here's the function: function fnValidateBusiness($business){ #alphabet only allowed. Minimum 3 characters, maximum 40. return preg_match('/^[a-zA-Z0-9 -\']{3,40}$/i', $business); } Any ideas? Thanks a lot!

I've set up a ticket\work order system that I am trying to create a form to be able to search for tickets with varying attributes. The form includes a field to enter the clients name, and dropdowns to select whether the ticket has been billed, closed, who took the call, and who was dispatched to the job. There are a lot of variables involved, but not all of them will always have values specified. If my query looks like this: $query = "SELECT * FROM fieldtickets WHERE (clientname = '$clientname' AND openorclosed = '$openorclosed' AND billstatus = '$billstatus' AND secretary = '$secretary' AND technician = '$technician' ORDER BY apptdatetime"; This query works fine if every section of the field is filled out, but if you only want to see all open tickets, essentially marking all other fields as "all". It doesn't work. I've tried finding a wildcard to use in the case that a field is set to all, but I did not see that one exists without using LIKE. I know I can make it work by making the whole query conditional, based on which fields are set, but that would become a huge amount of conditions depending on how many search options I have. How should I go about doing this? Thanks for any help!