jimlawrnc Posted November 12, 2007 Share Posted November 12, 2007 I have a report that pulls data from a mysql table and generates a html table with the data. I need to sum up the columns into 2 separate fields I think I need to access the result from the query but do not know how. Here is my result.php script <?php session_start(); if(!session_is_registered('member_ID')) : header('Location: index.php?msg=requires_login'); endif; // Include database information and connectivity include ('config/opendb.php'); // We store all our functions in one file include ('config/functions.php'); $tech = htmlspecialchars($_POST['technician']); $startdate = htmlspecialchars($_POST['datBegin']); $enddate = htmlspecialchars($_POST['datBegin1']); //build the query $query = sprintf("SELECT * FROM tblhours WHERE technician = '$tech' AND servicedate >= '$startdate' AND servicedate <= '$enddate' ORDER BY servicedate ASC "); // print $query $result = mysql_query($query); if (mysql_num_rows($result)) { print "<center><TABLE BORDER=\"3\"><TR STYLE=\"font-weight: bold;\"><TD>ID</TD><TD>Technician</TD><TD>Client</TD><TD>Hours</TD><TD>Miles</TD><TD>Billable</TD><TD>Not Billable</TD><TD>Remote/Bench</TD><TD>Date of Service</TD></TR>"; while($row = mysql_fetch_assoc($result)) { extract($row); print "<TR><TD>$ID</TD><TD>$tech</TD><TD>$client</TD><TD>$hours</TD><TD>$mileage</TD><TD>$billable</TD><TD>$nobillable</TD><TD>$remote_bench</TD><TD>$servicedate</TD></TR>"; } print "</TABLE></center>"; } ?> Quote Link to comment https://forums.phpfreaks.com/topic/77063-total-table-columns/ Share on other sites More sharing options...
jimlawrnc Posted November 12, 2007 Author Share Posted November 12, 2007 I should add the table structure... - Table structure for table `tblhours` -- CREATE TABLE IF NOT EXISTS `tblhours` ( `ID` int(10) NOT NULL auto_increment, `technician` char(30) NOT NULL, `client` char(50) NOT NULL, `hours` float NOT NULL, `mileage` tinyint(10) NOT NULL, `billable` varchar(10) NOT NULL, `nobillable` varchar(10) NOT NULL, `remote_bench` varchar(10) NOT NULL, `servicedate` date default NULL, PRIMARY KEY (`ID`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ; Quote Link to comment https://forums.phpfreaks.com/topic/77063-total-table-columns/#findComment-390265 Share on other sites More sharing options...
gin Posted November 13, 2007 Share Posted November 13, 2007 It'd be great if you could explain what you mean by "sum up the columns into 2 separate fields". Also, please cut out all non-relevant code. Quote Link to comment https://forums.phpfreaks.com/topic/77063-total-table-columns/#findComment-390515 Share on other sites More sharing options...
jimlawrnc Posted November 13, 2007 Author Share Posted November 13, 2007 while($row = mysql_fetch_assoc($result)) { extract($row); print "<TR><TD>$ID</TD><TD>$tech</TD><TD>$client</TD><TD>$hours</TD><TD>$mileage</TD><TD>$billable</TD><TD>$nobillable</TD><TD>$remote_bench</TD><TD>$servicedate</TD></TR>"; I need to add the $hours column & $mileage column the $hours column needs to be summed by the following total hours where $billable = Yes and $remote_bench = no (or Null) total hours where $billable = Yes and $remote_bench = Yes total hours where $billable = No (or Null) and $remote_bench = no (or Null) total hours where $billable = No (or Null) and $remote_bench = Yes No (or Null) is what is in the table Yes is given if on the input form a checkbox is checked. I tried to have the field a bool type but data would not save to the table. Quote Link to comment https://forums.phpfreaks.com/topic/77063-total-table-columns/#findComment-390526 Share on other sites More sharing options...
sasa Posted November 13, 2007 Share Posted November 13, 2007 try SELECT sum( hours ) hr, sum( mileage ) ml, IF ( billable = '', 'no', billable )bill, IF ( remote_bench = '', 'no', remote_bench )rb FROM `tblhours` WHERE 1 GROUP BY bill, rb Quote Link to comment https://forums.phpfreaks.com/topic/77063-total-table-columns/#findComment-390740 Share on other sites More sharing options...
jimlawrnc Posted November 14, 2007 Author Share Posted November 14, 2007 This can in incorporated into the query i have? Quote Link to comment https://forums.phpfreaks.com/topic/77063-total-table-columns/#findComment-391338 Share on other sites More sharing options...
jimlawrnc Posted November 14, 2007 Author Share Posted November 14, 2007 Not sure how to do this Quote Link to comment https://forums.phpfreaks.com/topic/77063-total-table-columns/#findComment-391783 Share on other sites More sharing options...
jimlawrnc Posted November 16, 2007 Author Share Posted November 16, 2007 bump Quote Link to comment https://forums.phpfreaks.com/topic/77063-total-table-columns/#findComment-392794 Share on other sites More sharing options...
pkSML Posted November 16, 2007 Share Posted November 16, 2007 I need to add the $hours column & $mileage column the $hours column needs to be summed by the following total hours where $billable = Yes and $remote_bench = no (or Null) total hours where $billable = Yes and $remote_bench = Yes total hours where $billable = No (or Null) and $remote_bench = no (or Null) total hours where $billable = No (or Null) and $remote_bench = Yes No (or Null) is what is in the table Yes is given if on the input form a checkbox is checked. I tried to have the field a bool type but data would not save to the table. This may not be the prettiest way to do it, but just have several complex if statements inside your while loop. Use global variables to keep track of the running totals. Quote Link to comment https://forums.phpfreaks.com/topic/77063-total-table-columns/#findComment-392839 Share on other sites More sharing options...
jimlawrnc Posted November 16, 2007 Author Share Posted November 16, 2007 Yes but i do not know how to do this... the code given above run in phpmyadmin displays the results but i cannot get that to work in the result.php page Quote Link to comment https://forums.phpfreaks.com/topic/77063-total-table-columns/#findComment-393078 Share on other sites More sharing options...
jimlawrnc Posted November 20, 2007 Author Share Posted November 20, 2007 Bumpy bump I was able to do the following but the table layout looks horrible <?php session_start(); if(!session_is_registered('member_ID')) : header('Location: index.php?msg=requires_login'); endif; // Include database information and connectivity include ('config/opendb.php'); // We store all our functions in one file include ('config/functions.php'); $tech = htmlspecialchars($_POST['technician']); $startdate = htmlspecialchars($_POST['datBegin']); $enddate = htmlspecialchars($_POST['datBegin1']); $sql = "SELECT sum( hours ) hr, sum( mileage ) ml, IF( billable = '', 'no', billable ) bill, IF( remote_bench = '', 'no', remote_bench ) rb FROM `tblhours` WHERE technician = '$tech' AND servicedate >= '$startdate' AND servicedate <= '$enddate' GROUP BY bill, rb"; $result = mysql_query($sql); print $result; if (mysql_num_rows($result)) { print "<center><TABLE> <face='verdana, arial, helvetica' size='2' align='center'><TR><TD>Hours Report For: $tech From: $startdate - $enddate </TD></TR></table></center><center><TABLE BORDER=\"3\"><TR STYLE=\"font-weight: bold;\"><TD>Total Hours</TD><TD>Mileage</TD><TD>Billable</TD><TD>Remote/Bench</TD></TR>"; while($row = mysql_fetch_assoc($result)) { extract($row); print "<TR><TD>$hr</TD><TD>$ml</TD><TD>$rb</TD></TR>"; } print "</TABLE></center>"; } //print $result; ?> [attachment deleted by admin] Quote Link to comment https://forums.phpfreaks.com/topic/77063-total-table-columns/#findComment-394872 Share on other sites More sharing options...
pkSML Posted November 21, 2007 Share Posted November 21, 2007 I should add the table structure... - Table structure for table `tblhours` -- CREATE TABLE IF NOT EXISTS `tblhours` ( `ID` int(10) NOT NULL auto_increment, `technician` char(30) NOT NULL, `client` char(50) NOT NULL, `hours` float NOT NULL, `mileage` tinyint(10) NOT NULL, `billable` varchar(10) NOT NULL, `nobillable` varchar(10) NOT NULL, `remote_bench` varchar(10) NOT NULL, `servicedate` date default NULL, PRIMARY KEY (`ID`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ; We'll start with the basics and then this will be a lot easier. For each yes/no entry in your database, use the boolean data type. Change billable and remote_bench to BOOL NULL rather than varchar. You shouldn't need the column "nobillable", right? And when you're inputting all this data with your form, make billable and remote bench a checkbox. This will put the data in the db properly. Edit: Is remote_bench supposed to be either 'remote' or 'bench'? Quote Link to comment https://forums.phpfreaks.com/topic/77063-total-table-columns/#findComment-395789 Share on other sites More sharing options...
jimlawrnc Posted November 30, 2007 Author Share Posted November 30, 2007 I cant change the field type on phpmyadmin ALTER TABLE `tblhours` CHANGE `billable` `billable` BOOL( 1 ) NULL DEFAULT NULL MySQL said: Documentation #1064 - 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) NULL DEFAULT NULL' at line 1 if you would like to see it in action send me a message and i'll give you a login this is the form i use webtime.php <?php // Start a session session_start(); // Sends the user to the login-page if not logged in if(!session_is_registered('member_ID')) : header('Location: login.php?msg=requires_login'); endif; // Include database information and connectivity include ('config/opendb.php'); // We store all our functions in one file include ('config/functions.php'); //Links include 'links.php'; ?> <html> <title>Technician Hours Submission Form</title> <head> <link rel=StyleSheet href="stylesheet.css" type="text/css"> <script language="javascript" src="choosedate.js"></script> <link type="text/css" rel="stylesheet" href="calendar.css"> </head> <body> <!--- style="background-color: silver;">---> <p>Welcome <?php print user_info('username'); ?></p> <form name="frm" method="post" action="submittime.php"> <div style="text-align: center;"><span style="font-family: Verdana;">Technician Hours Entry Form<br> </span> <hr style="width: 100%; height: 2px;"><span style="font-family: Verdana;"><br style="font-family: Verdana;"> </span></div> <br> <table> <tbody> <input type="hidden" name="tech" value="$username"> <tr id="0"> <td><input type="hidden" name="technician"></td> <td>Client:</td> <td> <input size="20" name="Client"></td> <td>Hours:</td> <td><input size="5" name="Hours"></td> <td>Mileage:</td> <td><input size="5" name="Mileage"> </td> <td>Billable:</td> <td> <input name="Billable" value="Yes" type="checkbox"></td> <td>Non-Billable:</td> <td> <input name="NoBillable" value="Yes" type="checkbox"> </td> <td>Remote/Bench:</td> <td> <input name="remotebench" value="Yes" type="checkbox"> </td> <td>Date:</td> <td><input type="text" name="datBegin" size="7"> <script language=javascript> var basicCal = new calendar("FIELD:document.frm.datBegin"); basicCal.writeCalendar(); </script></td> </tr> <!--- Add a few more rows here ---> <tr id="1"> <td><input type="hidden" name="technician1"></td> <td>Client:</td> <td> <input size="20" name="Client1"></td> <td>Hours:</td> <td><input size="5" name="Hours1"></td> <td>Mileage:</td> <td><input size="5" name="Mileage1"> </td> <td>Billable:</td> <td> <input name="Billable1" value="Yes" type="checkbox"></td> <td>Non-Billable:</td> <td> <input name="NoBillable1" value="Yes" type="checkbox"> </td> <td>Remote/Bench:</td> <td> <input name="remotebench1" value="Yes" type="checkbox"> </td> <td>Date:</td> <td><input type="text" name="datBegin1" size="7"> <script language=javascript> var basicCal = new calendar("FIELD:document.frm.datBegin1"); basicCal.writeCalendar(); </script></td></tr> <tr id="2"> <td><input type="hidden" name="technician2"></td> <td>Client:</td> <td> <input size="20" name="Client2"></td> <td>Hours:</td> <td><input size="5" name="Hours2"></td> <td>Mileage:</td> <td><input size="5" name="Mileage2"> </td> <td>Billable:</td> <td> <input name="Billable2" value="Yes" type="checkbox"></td> <td>Non-Billable:</td> <td> <input name="NoBillable2" value="Yes" type="checkbox"> </td> <td>Remote/Bench:</td> <td> <input name="remotebench2" value="Yes" type="checkbox"> </td> <td>Date:</td> <td><input type="text" name="datBegin2" size="7"> <script language=javascript> var basicCal = new calendar("FIELD:document.frm.datBegin2"); basicCal.writeCalendar(); </script></td></tr> <tr id="3"> <td><input type="hidden" name="technician3"></td> <td>Client:</td> <td> <input size="20" name="Client3"></td> <td>Hours:</td> <td><input size="5" name="Hours3"></td> <td>Mileage:</td> <td><input size="5" name="Mileage3"> </td> <td>Billable:</td> <td> <input name="Billable3" value="Yes" type="checkbox"></td> <td>Non-Billable:</td> <td> <input name="NoBillable3" value="Yes" type="checkbox"> </td> <td>Remote/Bench:</td> <td> <input name="remotebench3" value="Yes" type="checkbox"> </td> <td>Date:</td> <td><input type="text" name="datBegin3" size="7"> <script language=javascript> var basicCal = new calendar("FIELD:document.frm.datBegin3"); basicCal.writeCalendar(); </script></td> </tr> <tr id="4"> <td><input type="hidden" name="technician4"></td> <td>Client:</td> <td> <input size="20" name="Client4"></td> <td>Hours:</td> <td><input size="5" name="Hours4"></td> <td>Mileage:</td> <td><input size="5" name="Mileage4"> </td> <td>Billable:</td> <td> <input name="Billable4" value="Yes" type="checkbox"></td> <td>Non-Billable:</td> <td> <input name="NoBillable4" value="Yes" type="checkbox"> </td> <td>Remote/Bench:</td> <td> <input name="remotebench4" value="Yes" type="checkbox"> </td> <td>Date:</td> <td><input type="text" name="datBegin4" size="7"> <script language=javascript> var basicCal = new calendar("FIELD:document.frm.datBegin4"); basicCal.writeCalendar(); </script></td> </tr> </tbody> </table> <p style="text-align: right;"><input name="submit" value="Submit" type="submit"></p> <p></p> </form> </body> </html> This is the form that submits the entries to the DB submittime.php <?php // Start a session session_start(); include 'config/config.php'; include 'config/opendb.php'; include 'config/functions.php'; include 'links.php'; // Sends the user to the login-page if not logged in if(!session_is_registered('member_ID')) : header('Location: login.php?msg=requires_login'); endif; //echo user_info('username'); $tech = user_info('username'); $client = htmlspecialchars($_POST['Client']); $Hours = htmlspecialchars($_POST['Hours']); $Mileage = htmlspecialchars($_POST['Mileage']); $Billable = htmlspecialchars($_POST['Billable']); $NoBillable = htmlspecialchars($_POST['NoBillable']); $RemoteBench = htmlspecialchars($_POST['remotebench']); $serviceDate = htmlspecialchars($_POST['datBegin']); //Build sql insert command $addTimequery = "INSERT into tblhours (technician, client, hours, mileage, billable, nobillable, remote_bench, serviceDate) VALUES('$tech', '$client', '$Hours', '$Mileage', '$Billable', '$NoBillable', '$RemoteBench', '$serviceDate')"; //second row $tech = user_info('username'); $client = htmlspecialchars($_POST['Client1']); $Hours = htmlspecialchars($_POST['Hours1']); $Mileage = htmlspecialchars($_POST['Mileage1']); $Billable = htmlspecialchars($_POST['Billable1']); $NoBillable = htmlspecialchars($_POST['NoBillable1']); $RemoteBench = htmlspecialchars($_POST['remotebench1']); $serviceDate = htmlspecialchars($_POST['datBegin1']); //Build sql insert command $addTimequery1 = "INSERT into tblhours (technician, client, hours, mileage, billable, nobillable, remote_bench, serviceDate) VALUES('$tech', '$client', '$Hours', '$Mileage', '$Billable', '$NoBillable', '$RemoteBench', '$serviceDate')"; //Third row $tech = user_info('username'); $client2 = htmlspecialchars($_POST['Client2']); $Hours2 = htmlspecialchars($_POST['Hours2']); $Mileage2 = htmlspecialchars($_POST['Mileage2']); $Billable2 = htmlspecialchars($_POST['Billable2']); $NoBillable2 = htmlspecialchars($_POST['NoBillable2']); $RemoteBench2 = htmlspecialchars($_POST['remotebench2']); $serviceDate2 = htmlspecialchars($_POST['datBegin2']); //Build sql insert command $addTimequery2 = "INSERT into tblhours (technician, client, hours, mileage, billable, nobillable, remote_bench, serviceDate) VALUES('$tech', '$client2', '$Hours2', '$Mileage2', '$Billable2', '$NoBillable2', '$RemoteBench2', '$serviceDate2')"; //Forth row $tech = user_info('username'); $client = htmlspecialchars($_POST['Client3']); $Hours = htmlspecialchars($_POST['Hours3']); $Mileage = htmlspecialchars($_POST['Mileage3']); $Billable = htmlspecialchars($_POST['Billable3']); $NoBillable = htmlspecialchars($_POST['NoBillable3']); $RemoteBench = htmlspecialchars($_POST['remotebench3']); $serviceDate = htmlspecialchars($_POST['datBegin3']); //Build sql insert command $addTimequery3 = "INSERT into tblhours (technician, client, hours, mileage, billable, nobillable, remote_bench, serviceDate) VALUES('$tech', '$client', '$Hours', '$Mileage', '$Billable', '$NoBillable', '$RemoteBench', '$serviceDate')"; //Fifth row $tech = user_info('username'); $client = htmlspecialchars($_POST['Client4']); $Hours = htmlspecialchars($_POST['Hours4']); $Mileage = htmlspecialchars($_POST['Mileage4']); $Billable = htmlspecialchars($_POST['Billable4']); $NoBillable = htmlspecialchars($_POST['NoBillable4']); $RemoteBench = htmlspecialchars($_POST['remotebench4']); $serviceDate = htmlspecialchars($_POST['datBegin4']); //Build sql insert command $addTimequery4 = "INSERT into tblhours (technician, client, hours, mileage, billable, nobillable, remote_bench, serviceDate) VALUES('$tech', '$client', '$Hours', '$Mileage', '$Billable', '$NoBillable', '$RemoteBench', '$serviceDate')"; //execute sql query mysql_query($addTimequery) or die (mysql_error()); mysql_query($addTimequery1) or die (mysql_error()); mysql_query($addTimequery2) or die (mysql_error()); // mysql_query($addTimequery3) or die (mysql_error()); // mysql_query($addTimequery4) or die (mysql_error()); include 'closedb.php'; echo "Your Time was added sucessfully"; ?> <html> <head> <link rel=StyleSheet href="stylesheet.css" type="text/css"> </head> <body> </body> </html> what im trying to accomplish is to combind 2 results into 1 page e.g i have a report page that asks for name start date and end date i need it to spit out the results in a table format and total the colums the code posted by sasa works by its self but i cannot get the 2 results on one page formatted nicely. Quote Link to comment https://forums.phpfreaks.com/topic/77063-total-table-columns/#findComment-402825 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.