Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 3 of 3
  1. #1
    New Coder
    Join Date
    Jul 2010
    Posts
    47
    Thanks
    0
    Thanked 0 Times in 0 Posts

    emailing all and account transactions by date range, from database

    I am trying to email account from a MYsql Database. payment is to made by Ecommerce website
    to Music Royalty Creditors

    #################################################################################################### #
    ( PRODUCERS : financing recording
    ,WRITER :writer of album, or song;
    DISTRIBUTOR :seller of music
    ARTIST
    MUSICIAN

    #################################################################################################### #

    the statement to be emailed is based on a date range as well as the option to email all transactions.
    A form is provided to enter year ,month and day from a starting to an ending date. When the form is submitted the year month and day is stored in
    session variables.
    To prevent repeatedly entering dates when testing program I assigned some values to the session variables.
    Need to setup a date range to test email I Don't know if this date is formatted is correctly. I did not include
    the email function , just the account.



    An error occurred on statement line AND TRNSDAT BETWEEN '$FROMDT' AND '$TODATE'"
    THE OPENING BALANCE ON THE ROYALTY CREDITOR WILL BE SHOWN LATER.

    DO YOU THINK THIS PROGRAM CAN WORK.


    PHP Code:
    <?php
                        $hostname     
    "localhost";
                        
    $password     "";
                        
    $username     "root";  
                        
    $CON          =mysql_connect $hostname,$username$password );
                        
    $SQLACCOUNT   " SELECT * FROM ROYALTYACCOUNT ";
                        
    $FROYALTYACCOUNT  mysql_query$SQLACCOUNT$CON );
                        while 
    $ACCOUNT   mysql_fetch_object($FROYALTYACCOUNT)){
                                   
    ROYALTY_CODE $ACCOUNT ->CREDITORCOD
                                $ROYALTYCREDITOR 
    $ACCOUNT->ROYALTYCREDITOR
                                
    if ( $ROYALTYCREDITOR $_SESSION['ROYALTYCREDITOR']); 
                                    if 
    $_SESSION['ROYALTYCREDITOR'] == "PRODUCER"){                            
                         if   ( 
    ROYALTYCREDITOR == "ALL" ){
                                
    $SQLPRODUCER " SELECT * FROM PRODUCER ";
                        }
                        else {
                                
    $SQLPRODUCER " SELECT * FROM PRODUCER ";
                                
    $SQLPRODUCER.= " WHERE   COUNTRYCOD =$_SESSION['COUNTRYCOD'];
                                $SQLPRODUCER.= " 
    AND        STATECOD=$_SESSION['STATECOD'];
                                
    $SQLPRODUCER.= " AND         CITYCOD=$_SESSION['CITYCOD']; 
                         }                            
                                $FPRODUCER   =mysql_query($SQLPRODUCER , $CON );
                                 while ($CREDITOR = mysql_fetch_object(FPRODUCER)){
                   ##########################################################################################                    
                                        $ACCOUNTNO    = CREDITOR->ACCOUNT_NUMBER;
                                        $ACCOUNT_NAME = CREDITOR->ACCOUNT_NAME; 
                                        $EMAIL        = CREDITOR->EMAIL;               
               ################################################################################################

                        if ( $CALENDAR  == "
    ALL" ){
                                        $SQLLEDGER    =" 
    SELECT FROM ROYALTYLEDGER ";
                                        $SQLLEDGER   .=" 
    WHERE  ACCOUNT_NUMBER=" . $ACCOUNTNO .
                                         $FROYALTYLEDGER = mysql_query( $SQLLEDGER, $CON );
                                        ######################################################################                    
                                        while ( $LEDGER = mysql_fetch_object( $FROYALTYLEDGER )){
                                               $TRNSNO   = $LEDGER->TRNSNO;
                                               $TRNSTYPE = $LEDGER->TRNSTYPE;
                                               $TRNSDAT  = $LEDGER->TRNSDAT;
                                               $AMOUNT   = $LEDGER->AMOUNT; 
                                               if ( $TRNSTYPE == "
    I" ){ 
                                                        $PARTICULAR       = "
    INVOICEE";
                                                        $DEBIT           = $AMOUNT;
                                                        $CREDIT          =  0;
                                                        $CURRENT_BALANCE = $CURRENT_BALANCE + $AMOUNT;
                                                }
                                                 if ( $TRNSTYPE == "
    R" ){ 
                                                        $PARTICULAR       = "
    RECEIPT";
                                                        $DEBIT           = 0;
                                                        $CREDIT          =  $AMOUNT;
                                                        $CURRENT_BALANCE = $CURRENT_BALANCE - $AMOUNT;
                                                  }
                                                  $BODY="
    <TR>";
                                                  $BODY.="
    <TD><?php echo($TRNSNO )          ?></TD>";
                                                  $BODY.="<TD><?php echo($TRNSDAT)          ?></TD>";
                                                  $BODY.="<TD><?php echo($PARTICULAR)       ?></TD>";
                                                  $BODY.="<TD><?php echo($DEBIT )           ?></TD>";
                                                  $BODY.="<TD><?php echo($CREDIT)           ?></TD>";
                                                  $BODY.="<TD><?php echo($CURRENT_BALANCE ?></TD>";
                                                          $BODY.="</TR>";              
                                        }
                                         mail($EMAIL , "ROYALTY CREDITOR STATEMENT", $BODY );
                        }
                        else {
                                $_SESSION['FROMYEAR']=2012;
                                $_SESSION['FROMMTH'] = 3;
                                $_SESSION['FROMDAY'] = 25;
                                $_SESSION['TOYEAR']  = 2013;
                                $_SESSION['TOMTH']   = 4;
                                $_SESSION['TODAY']   = 30;
                                 $FROMYEAR=$_SESSION['FROMYEAR'];
                                $FROMMTH =$_SESSION['FROMMTH'] ;
                                $FROMDAY =$_SESSION['FROMDAY'] ;
                                $TOYEAR  =$_SESSION['TOYEAR']  ; 
                                $TOMTH   =$_SESSION['TOMTH']   ;
                                $TODAY     =$_SESSION['TODAY']   ;  
                                 $FROMDT  =$FROMYEAR."\\".$FROMMTH."\\".$FROMDAY    ;
                                $TODATE  =$TOYEAR."\\".$TOMTH."\\".$TODAY;
                                $STARTDT = $FROMDT."\\". $FROMMTH ."\\". "01";
                   ##########################################################################################                                 
                                        $SQLLEDGER    =" SELECT * FROM ROYALTYLEDGER ";
                                        $SQLLEDGER   .=" WHERE  ACCOUNT_NUMBER=" . $ACCOUNTNO .
                                        $SQLLEDGER   .=" AND  TRNSDAT BETWEEN 'STARTDT' AND '$FROMDT'" 
                                        $FROYALTYLEDGER = mysql_query( $SQLLEDGER, $CON );
                                        
                                        
                                                OPENING BALANCE
                                            
                    ########################################################################################### 
                    
                                while (  $LEDGER    = mysql_fetch_object($FROYALTYLEDGER)){            
                                        $TRNSTYPE  = $LEDGER->TRNSTYPE;
                                        $AMOUNT    = $LEDGER->AMOUNT;
                                        if ( $TRNSTYPE =="I"){                               
                                                  $CURRENT_BALANCE = $CURRENT-BALANCE   +  $AMOUNT;        
                                        
                                        }        
                                        if  ( $TRNSTYPE == "R" ){

                                                   $CURRENT_BALANCE = $CURRENT_BALANCE - $AMOUNT;
                                        }
                                }        
                    ################################################################################################
                                        $SQLLEDGER    =" SELECT * FROM ROYALTYLEDGER ";
                                        $SQLLEDGER   .=" WHERE  ACCOUNT_NUMBER=" . $ACCOUNTNO .
                                        $SQLLEDGER   .=" AND  TRNSDAT BETWEEN '$FROMDT' AND '$TODATE'" 
                                        $FROYALTYLEDGER = mysql_query( $SQLLEDGER, $CON );
                     ##############################################################################################                      
                                        while ( $LEDGER = mysql_fetch_object( $FROYALTYLEDGER )){
                                               $TRNSNO   = $LEDGER->TRNSNO;
                                               $TRNSTYPE = $LEDGER->TRNSTYPE;
                                               $TRNSDAT  = $LEDGER->TRNSDAT;
                                               $AMOUNT   = $LEDGER->AMOUNT; 
                                               if ( $TRNSTYPE == "I" ){ 
                                                        $PARTICULAR       = "INVOICE";
                                                        $DEBIT           = $AMOUNT;
                                                        $CREDIT          =  0;
                                                        $CURRENT_BALANCE = $CURRENT_BALANCE + $AMOUNT;
                                                }
                                                 if ( $TRNSTYPE == "R" ){ 
                                                        $PARTICULAR       = "RECEIPT";
                                                        $DEBIT           = 0;
                                                        $CREDIT          =  $AMOUNT;
                                                        $CURRENT_BALANCE = $CURRENT_BALANCE - $AMOUNT;
                                                  }
                                                  $BODY="<TR>";
                                                  $BODY.="<TD><?php echo($TRNSNO )          ?></TD>";
                                                  $BODY.="<TD><?php echo($TRNSDAT)          ?></TD>";
                                                  $BODY.="<TD><?php echo($PARTICULAR)       ?></TD>";
                                                  $BODY.="<TD><?php echo($DEBIT )           ?></TD>";
                                                  $BODY.="<TD><?php echo($CREDIT)           ?></TD>";
                                                  $BODY.="<TD><?php echo($CURRENT_BALANCE ?></TD>";
                                                   $body.="</TR>"   ;
                                        }
                                        mail ($EMAIL, "ROYALTY CREDITOR STATEMENT", $BODY );
                        }
        #################################################################################################################
    Last edited by Fou-Lu; 08-15-2013 at 12:48 AM.

  • #2
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,994
    Thanks
    4
    Thanked 2,662 Times in 2,631 Posts
    Do I think it'll work? No, it has at least two errors, one syntactical and one logical within it; the syntactical one may not be a syntax error as I cannot tell if it intends to indicate another script or not. Make sure you're wrapping this in [php][/php] as well, after 40+ posts you should know to do that.
    Dates are not written as year\month\day. They are written as YYYY-MM-DD. If you have the format of year\month\day, that indicates that you have (incorrectly) used a string and not a date for your data. That means you cannot search using a BETWEEN clause, as well as being unreliable for any type of comparisons since they are strings. The only way to deal with that is to assemble the stored data into a timestamp or cast to a datetime and compare it then. Problem with this is you are now no longer searching for data, instead you are comparing *every* record to see it it matches a criteria instead of relying on the searchable indexes of the db (assuming the date was indexed).
    So SQL wise, you'll either have an incorrect datatype which should result in a syntax error at worst or simply no results at best (I cannot recall what mysql does with an invalid date).

    Edit:
    And as you can see when I wrapped that in PHP tags, you ended up with more syntax errors near the top there.
    PHP Code:
    header('HTTP/1.1 420 Enhance Your Calm'); 
    Been gone for a few months, and haven't programmed in that long of a time. Meh, I'll wing it ;)

  • #3
    New Coder
    Join Date
    Jul 2010
    Posts
    47
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I don't know how to wrap code with <php></php> . will I have to wrap each line of
    code separately.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •