Best way to select records by year and month??

[ehask]

Hey All,

I am stumped. I am setting up a page to display all of my transactions broken down into types. I need to get the total dollar amount for each Trans Type and total amount of each transtype. by month and by day

  if(isset($_POST['month'])){
      $qmonth = $_POST['month'];
  } else { $qmonth = date(n);}
  if(isset($_POST['year'])) {
     $qyear = $_POST['year'];
  }
$daysinmnth = date(t);

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

		$sql1 = mysql_query("select count(id) as TotAut, sum(m_amount) as TotAAut,m_txn_subtd FROM txn_cust_info where m_status='AUTH' and MONTH(m_txn_subtd) = '".$qmonth."' and YEAR(m_txn_subtd) = '".$qyear."'  and DAY(m_txn_subtd) = '".$i."' and m2020_id = '".$_SESSION['m2020_id']."'");

    $sql2 = mysql_query("select count(id) as TotCap, sum(m_amount) as TotACap,m_txn_subtd FROM txn_cust_info where m_status='CAPT' and MONTH(m_txn_subtd) = '".$qmonth."' and YEAR(m_txn_subtd) = '".$qyear."'  and DAY(m_txn_subtd) = '".$i."' and m2020_id = '".$_SESSION['m2020_id']."'");

	$sql3 = mysql_query("select count(id) as TotDec, sum(m_amount) as TotADec,m_txn_subtd FROM txn_cust_info where m_status='DECL' and MONTH(m_txn_subtd) = '".$qmonth."' and YEAR(m_txn_subtd) = '".$qyear."'  and DAY(m_txn_subtd) = '".$i."' and m2020_id = '".$_SESSION['m2020_id']."'");

	$sql4 = mysql_query("select count(id) as TotRev, sum(m_amount) as TotARev,m_txn_subtd FROM txn_cust_info where m_status='REVR' and MONTH(m_txn_subtd) = '".$qmonth."' and YEAR(m_txn_subtd) = '".$qyear."'  and DAY(m_txn_subtd) = '".$i."' and m2020_id = '".$_SESSION['m2020_id']."'");

	$sql5 = mysql_query("select count(id) as TotChb, sum(m_amount) as TotAChb,m_txn_subtd FROM txn_cust_info where m_status='REVR' and MONTH(m_txn_subtd) = '".$qmonth."' and YEAR(m_txn_subtd) = '".$qyear."'  and DAY(m_txn_subtd) = '".$i."' and m2020_id = '".$_SESSION['m2020_id']."'");

	$auth_arr = mysql_fetch_array($sql1); 
	$cap_arr = mysql_fetch_array($sql2);
	$dec_arr = mysql_fetch_array($sql3);
	$rev_arr = mysql_fetch_array($sql4);
	$chb_arr = mysql_fetch_array($sql5);	
 }
?>

I am getting Invalid MySQL_resource. If I chop all the date stuff off it actually works but for all the data in the database not daily totals or monthly totals.

Is there a better way to do this?? and my main issue is just extracting data for each day of a specific month as I look at back months alot.

thx for any pointers

Eric

[ehask]

I managed to get it to work for daily totals but I can seem to do the monthly totals

here is revised script

         for($i=1;$i<=$daysinmnth;$i++) 
        { 
		  $imod = $i;
		  if ($imod == 1) { $imod = '01'; }
		  if ($imod == 2) { $imod = '02'; } 
		  if ($imod == 3) { $imod = '03'; } 
		  if ($imod == 4) { $imod = '04'; } 
		  if ($imod == 5) { $imod = '05'; } 
		  if ($imod == 6) { $imod = '06'; } 
		  if ($imod == 7) { $imod = '07'; } 
		  if ($imod == 8) { $imod = '08'; } 
		  if ($imod == 9) { $imod = '09'; } 

	$sql1 = mysql_query("select count(id) as TotAut, sum(m_amount) as TotAAut FROM txn_cust_info where m_status='AUTH' and m_txnmod_date = '".$qyear."-".$qmonth."-".$imod."' and m2020_id = '".$_SESSION['m2020_id']."'");
    $sql2 = mysql_query("select count(id) as TotCap, sum(m_amount) as TotACap FROM txn_cust_info where m_status='CAPT' and m_txnmod_date = '".$qyear."-".$qmonth."-".$imod."' and m2020_id = '".$_SESSION['m2020_id']."'");
	$sql3 = mysql_query("select count(id) as TotDec, sum(m_amount) as TotADec FROM txn_cust_info where m_status='DECL' and m_txnmod_date = '".$qyear."-".$qmonth."-".$imod."' and m2020_id = '".$_SESSION['m2020_id']."'");
	$sql4 = mysql_query("select count(id) as TotRev, sum(m_amount) as TotARev FROM txn_cust_info where m_status='REVR' and m_txnmod_date = '".$qyear."-".$qmonth."-".$imod."' and m2020_id = '".$_SESSION['m2020_id']."'");
	$sql5 = mysql_query("select count(id) as TotChb, sum(m_amount) as TotAChb FROM txn_cust_info where m_status='REVR' and m_txnmod_date = '".$qyear."-".$qmonth."-".$imod."' and m2020_id = '".$_SESSION['m2020_id']."'"); 

	$auth_arr = mysql_fetch_array($sql1); 
	$cap_arr = mysql_fetch_array($sql2);
	$dec_arr = mysql_fetch_array($sql3);
	$rev_arr = mysql_fetch_array($sql4);
	$chb_arr = mysql_fetch_array($sql5);		
	if ($bgcolor == "#E0E0E0"){ 
        $bgcolor = "#FFFFFF"; 
    }else{ 
        $bgcolor = "#E0E0E0"; 
    } 

[tekky]

part of your problem could easily lie here

if(isset($_POST['month'])){ 
      $qmonth = $_POST['month']; 
  } else { $qmonth = date(n);} 
  if(isset($_POST['year'])) { 
     $qyear = $_POST['year']; 
  } 

i asumed you reused this since you still used the $daysinmonth var also...

you need to do

$daysinmonth = date($month . '-01-' . date('Y'), 't');

otherwise you will ALWAYS pull the days for the current month (never the month they selected)

[ehask]

Tekky,

I did have the problem with the days not changing.

when I tried to use your code it would only show 12 days so I echo $daysofthemnth and found it was putting up

12-01-2004

$daysinmnth = date($qmonth . '-01-' . date('Y'), 't');

php.net doesnt have great examples of this on their site either LOL

thx

[ehask]

I have managed to get all the totals to display correctly now just trying to get the date() command to adjust when a different month is selected.

Here is the final code I used

<?php

$daysinmnth = date('t');
//$daysinmnth = date($qmonth . '-01-' . date('Y'), 't');
//echo $daysinmnth;
$bgcolor == "#E0E0E0";

  for($i=1;$i<=$daysinmnth;$i++) 
      { 
	  $imod = $i;
	  if ($imod == 1) { $imod = '01'; }
	  if ($imod == 2) { $imod = '02'; } 
	  if ($imod == 3) { $imod = '03'; } 
	  if ($imod == 4) { $imod = '04'; } 
	  if ($imod == 5) { $imod = '05'; } 
	  if ($imod == 6) { $imod = '06'; } 
	  if ($imod == 7) { $imod = '07'; } 
	  if ($imod == 8) { $imod = '08'; } 
	  if ($imod == 9) { $imod = '09'; } 

	$sql1 = mysql_query("select count(id) as TotAut, sum(m_amount) as TotAAut FROM txn_cust_info where m_status='AUTH' and m_txnmod_date = '".$qyear."-".$qmonth."-".$imod."' and m2020_id = '".$_SESSION['m2020_id']."'");
    $sql2 = mysql_query("select count(id) as TotCap, sum(m_amount) as TotACap FROM txn_cust_info where m_status='CAPT' and m_txnmod_date = '".$qyear."-".$qmonth."-".$imod."' and m2020_id = '".$_SESSION['m2020_id']."'");
	$sql3 = mysql_query("select count(id) as TotDec, sum(m_amount) as TotADec FROM txn_cust_info where m_status='DECL' and m_txnmod_date = '".$qyear."-".$qmonth."-".$imod."' and m2020_id = '".$_SESSION['m2020_id']."'");
	$sql4 = mysql_query("select count(id) as TotRev, sum(m_amount) as TotARev FROM txn_cust_info where m_status='REVR' and m_txnmod_date = '".$qyear."-".$qmonth."-".$imod."' and m2020_id = '".$_SESSION['m2020_id']."'");
	$sql5 = mysql_query("select count(id) as TotChb, sum(m_amount) as TotAChb FROM txn_cust_info where m_status='REVR' and m_txnmod_date = '".$qyear."-".$qmonth."-".$imod."' and m2020_id = '".$_SESSION['m2020_id']."'"); 

	$auth_arr = mysql_fetch_array($sql1); 
	$cap_arr = mysql_fetch_array($sql2);
	$dec_arr = mysql_fetch_array($sql3);
	$rev_arr = mysql_fetch_array($sql4);
	$chb_arr = mysql_fetch_array($sql5);		
	if ($bgcolor == "#E0E0E0"){ 
        $bgcolor = "#FFFFFF"; 
    }else{ 
        $bgcolor = "#E0E0E0"; 
    } 
	?>
	<tr align="center" valign="top" bgcolor="<?php echo $bgcolor; ?>"> 
      <td valign="middle" class="head"><b><?php echo $i; ?></b></td>
      <td>
	  Auth<?php echo ?><br>
	  Capt<?php echo ?><br>
	  REV<?php echo ?><br></td>
      <td >
	  $<?php if($auth_arr['TotAAut'] != "") {echo $auth_arr['TotAAut'];} else { echo 0;}?><br>
	  $<?php if($cap_arr['TotACap'] != "") {echo $cap_arr['TotACap'];} else { echo 0;}?><br>
	  $<?php if($rev_arr['TotARev'] != "") {echo $rev_arr['TotARev'];} else { echo 0;}?><br></td>
      <td>
	  <?php echo $auth_arr['TotAut'];?><br>
	  <?php echo $cap_arr['TotCap'];?><br>
	  <?php echo $rev_arr['TotRev'];?><br>
	  </td>
      <td><?php echo ?><br>
	  &nbsp;</td>
      <td><?php echo ?><br>
	  &nbsp;</td>
    </tr>
	<?php
	}
	?>
    <tr><td class="head" >&nbsp;</td>
	 <td colspan="3" class="head"><?php
	$sql6 = mysql_query("select count(id) as TotAut, sum(m_amount) as TotAAut FROM txn_cust_info where m_status='AUTH' and Year(m_txnmod_date) = '".$qyear."' and Month(m_txnmod_date) = '".$qmonth."' and m2020_id = '".$_SESSION['m2020_id']."'");
    $sql7 = mysql_query("select count(id) as TotCap, sum(m_amount) as TotACap FROM txn_cust_info where m_status='CAPT' and Year(m_txnmod_date) = '".$qyear."' and Month(m_txnmod_date) = '".$qmonth."' and m2020_id = '".$_SESSION['m2020_id']."'");
	$sql8 = mysql_query("select count(id) as TotDec, sum(m_amount) as TotADec FROM txn_cust_info where m_status='DECL' and Year(m_txnmod_date) = '".$qyear."' and Month(m_txnmod_date) = '".$qmonth."' and m2020_id = '".$_SESSION['m2020_id']."'");
	$sql9 = mysql_query("select count(id) as TotRev, sum(m_amount) as TotARev FROM txn_cust_info where m_status='REVR' and Year(m_txnmod_date) = '".$qyear."' and Month(m_txnmod_date) = '".$qmonth."' and m2020_id = '".$_SESSION['m2020_id']."'");
	$sql10 = mysql_query("select count(id) as TotChb, sum(m_amount) as TotAChb FROM txn_cust_info where m_status='CHRB' and Year(m_txnmod_date) = '".$qyear."' and Month(m_txnmod_date) = '".$qmonth."' and m2020_id = '".$_SESSION['m2020_id']."'"); 

	$authmt_arr = mysql_fetch_array($sql6); 
	$capmt_arr = mysql_fetch_array($sql7);
	$decmt_arr = mysql_fetch_array($sql8);
	$revmt_arr = mysql_fetch_array($sql9);
	$chbmt_arr = mysql_fetch_array($sql10);
	echo '<b>'.$authmt_arr['TotAut'].' Authorizations totaling $'.$authmt_arr['TotAAut'].'<b><br>';
	echo '<b>'.$capmt_arr['TotCap'].' Captures totaling $'.$capmt_arr['TotACap'].'<b><br>';
	echo '<b>'.$decmt_arr['TotDec'].' Declines totaling $'.$decmt_arr['TotADec'].'<b><br>';
	echo '<b>'.$revmt_arr['TotRev'].' Reversals totaling $'.$revmt_arr['TotARev'].'<b><br>';
	echo '<b>'.$chbmt_arr['TotChb'].' Chargebacks totaling $'.$chbmt_arr['TotAChb'].'<b><br>';
	mysql_close();
	 ?></td><td colspan="2">&nbsp;</td>

thx

[Roger_Ramjet]

Quick point

change

for($i=1;$i<=$daysinmnth;$i++) 
          { 
          $imod = $i;
          if ($imod == 1) { $imod = '01'; }
          if ($imod == 2) { $imod = '02'; } 
          if ($imod == 3) { $imod = '03'; } 
          if ($imod == 4) { $imod = '04'; } 
          if ($imod == 5) { $imod = '05'; } 
          if ($imod == 6) { $imod = '06'; } 
          if ($imod == 7) { $imod = '07'; } 
          if ($imod == 8) { $imod = '08'; } 
          if ($imod == 9) { $imod = '09'; }  


//FOR

for($i=1;$i<=$daysinmnth;$i++) 
          { 
          $imod = $i;
          if ($imod < 10) { $imod = '0'.$imod; }

Only evaluate 1 if not 9.

[tekky]

duh, the reason my suggestion didnt work...
date() using timestamps....

should have been
$daysinmnth = date(strtotime('01-' . $month . '-2004'),'t');

fix the -2004 to be dynamic also 😉

and good fix on the if statements 😛