PHP - Table Totals

Currently we have a Microsoft Access database which we use to display a report, we are rolling over to PHP/SQL online for greater control e.t.c.

So far we have the report pulling all the information from the database however we need to group this information by Day followed by Employee, sorted by time and then totaled for each Employee for each day.

Below is the code we have so far which only prints columns how do we go about displaying it like the access screenshot. Attached is an example of the Microsoft Access report we have.

Code: [Select]
<?php
 $username='admin';
 $password='password';
 $database='planner';

 mysql_connect(localhost,$username,$password);
 @mysql_select_db($database) or die( "Unable to select database");
 $query="SELECT * FROM reservations ORDER BY start_date, machid, starttime";
 $result=mysql_query($query);

 $num=mysql_numrows($result);

 mysql_close();

 ?>

 <?php
 $i=0;
 while ($i < $num) {
 $f0=mysql_result($result,$i,"machid");
 $f1=mysql_result($result,$i,"start_date");
 $f2=mysql_result($result,$i,"end_date");
 $f3=mysql_result($result,$i,"starttime");
 $f4=mysql_result($result,$i,"endtime");
 $f5=mysql_result($result,$i,"first_name");
 $f6=mysql_result($result,$i,"last_name");
 $f7=mysql_result($result,$i,"company");
 $f8=mysql_result($result,$i,"phone_number");
 $f9=mysql_result($result,$i,"site_address");
 $f10=mysql_result($result,$i,"site_suburb");
 $f11=mysql_result($result,$i,"site_postcode");
 $f12=mysql_result($result,$i,"postal_address");
 $f13=mysql_result($result,$i,"postal_suburb");
 $f14=mysql_result($result,$i,"postal_postcode");
 $f15=mysql_result($result,$i,"job_type");
 $f16=mysql_result($result,$i,"payment_type");
 $f17=mysql_result($result,$i,"payment_status");
 $f18=mysql_result($result,$i,"quote_outcome");
 $f19=mysql_result($result,$i,"quote_amount");
 $f20=mysql_result($result,$i,"quote_employee");
 $f21=mysql_result($result,$i,"job_income");
 $f22=mysql_result($result,$i,"job_materials");
 $f23=mysql_result($result,$i,"invoice_number");
 $f24=mysql_result($result,$i,"job_notes");

/* START TIME */
$stime = $f3;

$shour = floor($stime/60);
$smin = str_pad($stime % 60,2,0,STR_PAD_LEFT);

//echo $hour.':'.$min;
//echo '<br><br>';

// For AM/PM
$shalf = 'am';
if( $shour > 12 ) {
$shalf = 'pm';
$shour -= 12;
}

$stime = $shour.':'.$smin.' '.$shalf;

/* END TIME */
$etime = $f4;

$ehour = floor($etime/60);
$emin = str_pad($etime % 60,2,0,STR_PAD_LEFT);

//echo $hour.':'.$min;
//echo '<br><br>';

// For AM/PM
$ehalf = 'am';
if( $ehour > 12 ) {
$ehalf = 'pm';
$ehour -= 12;
}

$etime = $ehour.':'.$emin.' '.$ehalf;

 ?>
 <table width="3500px" border="0" cellspacing="0" cellpadding="0">
 <tr>
 <td width="125px"><?php echo $f0; ?></td>
 <td width="80px"><?php echo date('d-m-y', "$f1"); ?></td>
 <td width="50px" style="text-align:right;"><?php echo $stime; ?></td>
 <td width="50px" style="text-align:right; padding-right:20px;"><?php echo $etime; ?></td>
 <td width="100px"><?php echo $f5; ?></td>
 <td width="100px"><?php echo $f6; ?></td>
 <td width="100px"><?php echo $f7; ?></td>
 <td width="100px"><?php echo $f8; ?></td>
 <td width="100px"><?php echo $f9; ?></td>
 <td width="100px"><?php echo $f10; ?></td>
 <td width="100px"><?php echo $f11; ?></td>
 <td width="100px"><?php echo $f12; ?></td>
 <td width="100px"><?php echo $f13; ?></td>
 <td width="100px"><?php echo $f14; ?></td>
 <td width="100px"><?php echo $f15; ?></td>
 <td width="100px"><?php echo $f16; ?></td>
 <td width="100px"><?php echo $f17; ?></td>
 <td width="100px"><?php echo $f18; ?></td>
 <td width="100px"><?php echo $f19; ?></td>
 <td width="100px"><?php echo $f20; ?></td>
 <td width="100px"><?php echo $f21; ?></td>
 <td width="100px"><?php echo $f22; ?></td>
 <td width="100px"><?php echo $f23; ?></td>
 <td width="100px"><?php echo $f24; ?></td>
 <?php
 $i++;
 }
 ?>
Once again thanks everyone at phpfreaks!

ok, so I can't seem to figure out why when I take this code:
Code: [Select]
<?php
// Query member data from the database and ready it for display
$sql = mysql_query("SELECT SUM(amount) AS total FROM payment where cart_id='$cid'");
while($row = mysql_fetch_array($sql)){
$amount123 = $row["total"];

?>

<?php

$left = $total-$amount123;
}
?><?php echo $left; ?>I echo $left and instead of subtracting $amount123 from $total, it subtracts it from zero, but when I refresh the page, it works properly.  can anyone see what the issue is?

I have a pipe delimited file with this sample content:

INV8004199|01/26/2012|Next door|648.00
SPT803487|01/25/2012|This and That|3360.00
INV8004193|01/25/2012|Where is it|756.00
INV8004133|01/05/2012|snowy winter|6750.00

I open the file to read it:
Code: [Select]
     $CPfile = "sample.DEL";

      $ft = fopen( $CPfile, "r");
$content1 = fread($ft, filesize($CPfile));
fclose($ft);



Then I loop thru it:

Code: [Select]
$out = explode("\n",  $content1);
$total = 0;
   
   foreach($out as $var) {

$tmp = explode("|", $var);

for($i=0;$i<count($tmp);$i++){
                            echo $tmp[$i] . "<BR>";
               }
   }


What I would like to get is the total of all prices (last column) in all lines of the file.  Can someone help please?. Thanks!

Hi, I'm having issues trying to add up the totals of each product type in a while loop- I'm sure it's straightforward to fix but for some reason I can't find a way to get it to add up all the totals of the various product types and list them so we have say 5 of product 1, 11 of product 2, 8 of product 3 and so on.

So firstly the user searches between two different dates / times to get the orders made during that time period: (I'm using separare date and time fields as the datetime-local input doesn't work in a lot of browsers)

if($_POST['search'])
{
    $searchdate1 = mysqli_real_escape_string($conn, $_POST['_Date1']);
    $searchdate2 = mysqli_real_escape_string($conn, $_POST['_Date2']);
    
    $searchtime1 = mysqli_real_escape_string($conn, $_POST['_Time1']);
    $searchtime2 = mysqli_real_escape_string($conn, $_POST['_Time2']);
    
    $datetime1 = $searchdate1 . ' ' . $searchtime1;
    $datetime2 = $searchdate2 . ' ' . $searchtime2;
    
    $nicedate1 = date("l F j, Y", strtotime($searchdate1) ); //Just used for readability in the report
    $nicedate2 = date("l F j, Y", strtotime($searchdate2) );

//Now get the orders from the db

$getorders = $oc_conn->query("SELECT * FROM oc_order WHERE date_added >= '$datetime1' AND date_added <= '$datetime2' AND order_status_id != 0 ORDER BY order_id DESC");

//Then generate start of a table in HTML, the while loop that follows will populate the rows

while ($row = $getorders->fetch_assoc()) { //Get the order data

          $firstname = $row["firstname"];
          $lastname = $row["lastname"];
          $fullname = $firstname . ' ' . $lastname;
        $orderid = $row["order_id"];
        $orderdate = $row["date_added"];    
          $email = $row["email"];
        $total = $row['total'];
          $total = number_format($total, 2);
            
        $niceorderdate = date('F j, Y H:i', strtotime($orderdate));    
            
             echo '<tr><td>'.$fullname.'</td><td>'. $orderid . '</td><td>';
    
            $getproducts = $oc_conn->query('SELECT * FROM oc_order_product WHERE order_id = '.$orderid); //Product names and details are held in a separate table
        
                while ($row = $getproducts->fetch_assoc()) {
                    
                    $orderproductid = $row["order_product_id"];
                    $productname = $row["name"];            
                    $quantity = $row["quantity"];
                    echo $quantity . ' x '.$productname.' <br>'; //This works for each individual order, but what I need is the total $quantity of each $productname, across ALL orders in the time range
                
                }
            echo '</td><td>'. $niceorderdate.'</td></tr>';           
        }
    echo '</tbody></table>';
}
?>

[/PHP]

As mentioned in the comment above, what I need is the total $quantity of each $productname, across ALL orders in the time range, so that I can see how many of each product ($productname) were ordered in total.

I tried using SUM for quantity in the mySQL, and COUNT for adding up the different product names but for some reason it didn't work. Can someone let me know what I need to do? Thanks :)

 

 

Ok, so I have a bit of code but it's not working as it should, would one of you guys have a look at it and let me know where I'm going wrong?

Basically I need to be able to input several words or terms into the text area, each on a separate line and have the results displayed something like:

hello: 23,000.000
hello world: 19,000,000
world: 278,000,000

I also see that the script is not searching the words / terms inside quotes (EG. "hello world")

TIA

Code: [Select]
<html>
<body>
<?php
function my_fetch($url,$user_agent='Mozilla/4.0 (compatible; MSIE 5.01; Windows NT 5.0)')
{
$ch = curl_init();
curl_setopt ($ch, CURLOPT_URL, $url);
curl_setopt ($ch, CURLOPT_USERAGENT, $user_agent);
curl_setopt ($ch, CURLOPT_HEADER, 0);
curl_setopt ($ch, CURLOPT_RETURNTRANSFER, 1);
curl_setopt ($ch, CURLOPT_REFERER, 'http://www.google.com/');
$result = curl_exec ($ch);
curl_close ($ch);
return $result;
}

$s = $_GET['s'];
if (isset($s))
{
echo "<p><i>Search for $s</i></p>";
    $s = urlencode($s);
    $data = my_fetch("http://www.google.com/search?hl=en&q=" . $s . "&btnG=Google+Search");

    $data = strip_tags($data);

    $find = 'Results 1 - 10 of about ';
    $find2 = ' for';

    $data = strstr($data, $find);

    $pos = strpos($data, $find2);


$search_number=substr($data,strlen($find), $pos-strlen($find));

echo "Total Results: $search_number";
}
else
{
?>

<form name="form1" id="form1" method="get" action="">
<div align="left">
<p>  <textarea name="s" type="text" id="s" rows="8" style="width:60%" /></textarea><br />
<input type="submit" name="Submit" value="Results" /></p>
Put "" around the string: <input type="checkbox" checked name="apos" value="true" /><br />
</div>
</form>
<p>
<?php
}
?>
</p>
</body>
</html>