Jump to content


Mr-Chidi

Member Since 20 May 2008
Offline Last Active May 12 2017 09:39 AM

Topics I've Started

Send multiple rows to one email

30 January 2017 - 08:44 PM

Hi all

Please, i have a script that send email to customers. The problem with the script is that it sends multiple mails but all i want is for it to send one mail containing details of multiple rows. i.e all the bill details should be in a table.

below is my script

Thanks

 

$bill_status = "OVERDUE";

$st = $pdo->prepare("SELECT *, DATEDIFF(NOW(), due_date) AS days_diff FROM bills
LEFT JOIN login_details ON bills.user_id = login_details.user_id
WHERE bills.bill_status = '$bill_status'");
$st->execute();
$row_sent = $st->rowCount();


//$message = htmlspecialchars($mess);
$mail = new PHPMailer;

//$mail->SMTPDebug = 3;                               // Enable verbose debug output

$mail->isSMTP();                                      // Set mailer to use SMTP
$mail->Host = 'localhost';  // Specify main and backup SMTP servers
$mail->SMTPAuth = true;                               // Enable SMTP authentication
$mail->Username = 'mails@mail.com';                 // SMTP username
$mail->Password = 'pasword';                           // SMTP password
$mail->SMTPSecure = 'ssl';                            // Enable TLS encryption, `ssl` also accepted
$mail->Port = 465;                                    // TCP port to connect to
$mail->From = 'no_reply@noreply.com';
$mail->FromName = 'Coy Name';

$mail->WordWrap = 587;                                 // Set word wrap to 50 characters
$mail->AddEmbeddedImage("../img/logo.png", "logo");
$mail->isHTML(true);                                  // Set email format to HTML
$mail->Subject = 'Notice';


while($rows = $st->fetch(PDO::FETCH_ASSOC)){
    $username = $rows['username'];
    
$message= "
<div class='messages'>
<h3><img src='cid:my_logo'> </h3>
<br>
<div style='font-size:15px;'>Email Notification on Bill Due for Payment </h4>
<p>Dear: Customer</p>
<p>
The following bills are due for payment:
</p>
<table width='80%' border='0' cellspacing='0' cellpadding='0'>
  <tr style='font-weight:bold;'>
    <td>Trans Ref</td>
                <td>Due Date</td>
                <td>Days Overdue</td>
                <td>Service Provider</td>
                <td>Service Type</td>
                <td>Amount Paid</td>
                <td>Bill Status</td>
                <td>Recurring</td>
  </tr>

<h3>Yours<br>
Coy Name</h3>
";    

$message .= "        <tr>";
$message .= "            <td>".$rows['trans_ref']."</td>";
$message .= "            <td>".$rows['due_date']."</td>";
$message .= "            <td>".$rows['days_diff']."</td>";
$message .= "            <td>".$rows['service_provider']."</td>";
$message .= "            <td>".$rows['service_type']."</td>";
$message .= "            <td>".$rows['amount_paid']."</td>";
$message .= "            <td>".$rows['bill_status']."</td>";
$message .= "            <td>".$rows['recurring']."</td>";
$message .= "        </tr>";

$mail->addAddress($username);     // Add a recipient
$mail->Body    = $message;
$mail->send();
$mail->ClearAddresses();

}

Send a reminder based on chosen date

31 December 2016 - 09:17 AM

Hi,
I have an application where during registration a user will choose when they want to be reminded of an event. For instance the event lets say will start on 2017-02-02 and i scheduled it on 2016-12-30 and i want to be reminded everyday from 10days to the event date(2017-02-02). How do i go about it? I need idea as to know how to go about it. I dont know if cron will be good.
Thanks


Process the value of a searched result

13 December 2016 - 01:12 PM

Hi guys,

How can i process the value of a search result.

this is what i've tried so far:

//searche result page
 
 
if(isset($_POST['submit'])){

    

    $_SESSION['from'] = $_POST['from'];

    $_SESSION['to'] = $_POST['to'];

    

    $sql = ("SELECT * FROM $tbl_name WHERE date_order BETWEEN '$_SESSION[from]' AND '$_SESSION[to]'");



    //$stmt = $pdo->prepare("SELECT * FROM ca_processed");

    $stmt=$pdo->query($sql);

    $stmt->execute();

    $num_rows = $stmt->rowCount();

    #print "<p>$num_rows Record(s) Found.</p>";

    if($stmt->rowCount() < 1){

    

    echo '<div class="alert alert-warning text-center">NO RECORD FOUND</div>';

    

}else{

print "<p>$num_rows Record(s) Found.</p>";
 
<form action="ReconcileAccounts" method="post">  

<table width="100%" class='table-responsive table-condensed table-striped'>



<tr>

<td bgcolor="#444444"><font color='#fff'></font></td>

<td  bgcolor="#444444"><font color='#fff'><strong>#</strong></font></td>

<td  bgcolor="#444444"><font color='#fff'>Trans Ref</font></td>

<td  bgcolor="#444444"><font color='#fff'>Service Provider</font></td>

<td  bgcolor="#444444"><font color='#fff'>Service Type</font></td>

<td  bgcolor="#444444"><font color='#fff'><strong>($) Amount</strong></font></td>

<td  bgcolor="#444444"><font color='#fff'><strong>Date Paid</strong></font></td>

<td bgcolor="#444444"><font color='#fff'><strong>Reconcile Status</strong></font></td>

</tr>

<?php

$i = 1;

while($row = $stmt->fetch(PDO::FETCH_ASSOC)) {

    $trans_ref = $row['trans_ref'];

    $service_provider = $row['service_provider'];

    $service_type = $row['service_type'];

    $amount_paid = number_format($row['amount_paid'],2);

    $date_paid = $row['date_paid'];

    $reconcile_status = $row['reconcile_status'];

    if($reconcile_status == 0){

        $reconcile_status = "<strong>NOT RECONCILED</strong>";

    }elseif($reconcile_status == 1){

         $reconcile_status = "<strong>RECONCILED</strong>";

    }

    

$reconcile_info = [

'trans_ref' => $trans_ref,

'service_provider' => $service_provider,

'service_type' => $service_type,

'amount_paid' => $amount_paid,

'date_paid' => $date_paid,

'reconcile_status' => $reconcile_status

];

$_SESSION['reconcile_info'] = $reconcile_info;



?>

<tr>

<td align="center"><input name="check_list[]" type="checkbox" value="<?php echo $row['id']; ?>" ></td>

<td><?php echo $i++; ?></td>

<td><?php echo  $trans_ref; ?></td>

<td><?php echo  $service_provider; ?></td>

<td><?php echo  $service_type; ?></td>

<td><?php echo  $amount_paid; ?></td>

<td><?php echo  $date_paid; ?></td>

<td><?php echo $reconcile_status; ?></td>

</tr>

<?php

}

?>

</table>

<input name="reconcile" type="submit" class="btn btn-primary btn-margin" id="reconciled" value="RECONCILE SELECTED">

</form>
}
 
}
 
//ReconcileAccounts
$tbl_name="xbp_paid_bills";        //your table name

$tbl_name2="xbp_registration_info";



if(isset($_POST['reconcile'])){



    if(!empty($_POST['check_list'])){

        foreach($_POST['check_list'] as $selected){



$stmt = $pdo->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_WARNING );

$stmt = $pdo->prepare("UPDATE xbp_paid_bills SET reconcile_status =1 WHERE trans_id='$selected'");

$stmt->execute();

$count = $stmt->rowCount();

}

if($count){



    echo "<div class='bg-success alert alert-success text-center'>RECORD(S) RECONCILED</div>";

    $url = "ReconcileAccount";

    echo '<meta http-equiv="refresh" content="3;URL=' . $url . '">';

    

}else{

    echo "<div class='bg-warning alert alert-warning text-center'>A PROBLEM OCCURED WHILE RECONCILING RECORD</div>";

    echo "<br>";

    

print_r($stmt->errorInfo());



}

    }



}

thanks


Sum row in UNION

13 December 2016 - 01:06 PM

Hello guys,

I'm try to sum rows in a UNION but having a hard time about it

$stmt = $pdo->prepare("SELECT due_date, SUM(amount_paid) FROM (

    SELECT due_date, amount_paid FROM table1 union all

    SELECT due_date, amount_paid FROM table2 UNION ALL

)x GROUP BY MONTH");

$stmt->execute();
 
while($row = $stmt->fetch(PDO::FETCH_ASSOC)) {  
echo $row['x'];

thanks


Getting wron result merging tables

18 January 2016 - 11:51 AM

Hello guys.

I’m having trouble understanding JOIN and GROUP.

I have two tables that I want to merge into one but when I try it, the result is not what is expected and I don’t know where I’m wrong. I want to merge Table A and B to get Table C as shown below:

 

Table A

Client Name        Username        Amount Deposited

John  Doe        joh@doe.com        2500

Julian Cram        jul@cram.com        2000

Peter Stalone        pet@sta.com        1200

Creig Davies        creg@davies.com        3000


Table B

Client Name        Username        Invoice Amount

John  Doe        joh@doe.com        1000

Julian Cram        jul@cram.com        500

Peter Stalone        pet@sta.com        4500

Creig Davies        creg@davies.com        1500


Table C

Client Name        Username        Invoice Amount        Amount Deposited

John  Doe        joh@doe.com        1000            2500

Julian Cram        jul@cram.com        500            2000

Peter Stalone        pet@sta.com        4500            1200

Creig Davies        creg@davies.com        1500            3000

 
echo "<table width='100%' class='table table-striped tbl'>";
echo "<tr>
<th bgcolor='#444444' align='center'><font color='#fff'>Client's Name</font></th>
<th bgcolor='#444444' align='center'><font color='#fff'>Username</font></th>
<th bgcolor='#444444' align='center'><font color='#fff'>Amount Deposited</font></th>
</tr>";
 
$stmt = $pdo->query("
SELECT d.firstname, d.surname, d.username, SUM(d.amt_deposited) AS sum_deposited
FROM ca_my_payments d GROUP BY d.username
");
while($row = $stmt->fetch(PDO::FETCH_ASSOC)){
echo "<tr><td>";
$client = ucwords($row['firstname'] . " " .$row['surname']);
echo $client;
echo "</td><td>";
echo $row['username'];
echo "</td><td>";
echo $row['sum_deposited'];
echo "</tr></td>";
}
echo "</table>";
 
echo "<br><br>";
 
echo "<table width='100%' class='table table-striped tbl'>";
echo "<tr>
<th bgcolor='#444444' align='center'><font color='#fff'>Client's Name</font></th>
<th bgcolor='#444444' align='center'><font color='#fff'>Username</font></th>
<th bgcolor='#444444' align='center'><font color='#fff'>Invoice</font></th>
</tr>";
               
$stmt = $pdo->query("
SELECT p.payee, p.username, SUM(p.total_payment) AS total_invoice
FROM ca_processed p GROUP BY p.username
");
while($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
echo "<tr><td>";
echo $row['payee'];
echo "</td><td>";
echo $row['username'];
echo "</td><td>";
echo number_format($row['total_invoice'],2);
echo "</tr></td>";
}
echo "</table>";
echo "<br><br>";
 
echo "<table width='100%' class='table table-striped tbl'>";
echo "<tr>
<th bgcolor='#444444' align='center'><font color='#fff'>Client's Name</font></th>
<th bgcolor='#444444' align='center'><font color='#fff'>Username</font></th>
<th bgcolor='#444444' align='center'><font color='#fff'>Invoice</font></th>
<th bgcolor='#444444' align='center'><font color='#fff'>Amount Deposited</font></th>
<th bgcolor='#444444' align='center'><font color='#fff'>Current Balance</font></th>
</tr>";
                               
$stmt = $pdo->query("
SELECT a.payee, a.username, b.username, SUM(a.total_payment) AS total_invoice, SUM(b.amt_deposited) AS sum_deposited
FROM ca_processed a
LEFT JOIN ca_my_payments b ON a.username = b.username
GROUP BY a.username
");
 
while($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
echo "<tr><td>";
echo $row['payee'];
echo "</td><td>";
echo $row['username'];
echo "</td><td>";
echo number_format($row['total_invoice'],2);
echo "</td><td>";
echo number_format($row['sum_deposited'],2);
echo "</tr></td>";
}
echo "</table>";