I-AM-OBODO

January 30, 2017

#1

you say moving on. So your current script is going to send multiple emails to a customer if he/she has multiple bills.

#2 It would be nice to see the error checking code

#3 & #4 Since you have not made a cron job yet, I wanted to see the whole script to be sure it is correct. If you don't want to show us that as I asked, then I guess we're done here.

PS - if as you say that the username column is a name then how can you use it as an email address?

I really do not understand why you dont understand me. I said the script is to send just a mail with the bills due in a table. All the bills should be tabulated.

I tried the script manually but its sending multiple mails i.e if a customer has 4 bills, it sends 4 different mail but that is not what i want, all i want is a single mail to be sent to the customer detailing the bill details in a table. If you can help, please do help and lets get it over with. Below is my whole script:

 
$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: $username</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();

}

January 27, 2017

#1

you say moving on. So your current script is going to send multiple emails to a customer if he/she has multiple bills.

#2 It would be nice to see the error checking code

#3 & #4 Since you have not made a cron job yet, I wanted to see the whole script to be sure it is correct. If you don't want to show us that as I asked, then I guess we're done here.

PS - if as you say that the username column is a name then how can you use it as an email address?

1. yes. the script is suppose to send all the bills a user has

2. this is where i have my error reporting

3. this is all the code there is or is it incomplete?

4. i said the username is the email and not name

 
error_reporting(0);
$database='dbname';
$user='username';
$password='password.';
$dsn="mysql:host=localhost;dbname=$database";

try {
$pdo = new PDO($dsn,$user,$password);
} catch(PDOException $e) {
die ('Failed to connect');

}

thanks

January 27, 2017

1 - While your message refers to multiple bills you do realize that you are sending ONE email for EACH record(aka 'bill') that you retrieve? Moving on...

       (the intention though is to send bills to user, bills are sent based on the numbers of bills each user has and not just one)

2 - where do you turn on php error checking?

      (my error checking is where i have my db credentials)

3 - where is the rest of the script? It is important to see it for a cron job examination.

       (there's no other script. or maybe i don't get what you are trying to say)

4 - have you ever done a cron job before?

     (working cron? nope.)

PS - is the column 'username' really an email address as you later imply?

        (yes its the username)

January 26, 2017

This is the Exact scrpt

$stmt=$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 xbp_bills.bill_status = 'OVERDUE'");
$stmt->execute();

//$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 = 'password';                           // SMTP password
$mail->SMTPSecure = 'ssl';                            // Enable TLS encryption, `ssl` also accepted
$mail->Port = 465;                                    // TCP port to connect to
$mail->From = 'no_reply@noreply';
$mail->FromName = 'Bill Sender';

$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 = 'Notification on Bill Overdue for Payment';

while($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
    $user_email = $row['username'];
    $trans_ref = $row['trans_ref'];;
    $due_date = $row['due_date'];
    $days_diff = $row['days_diff'];
    $service_provider = ucwords($row['service_provider']);
    $service_type = ucwords($row['service_type']);
    $amount_paid = number_format($row['amount_paid'],2);
    $bill_status = $row['bill_status'];    

$message = "
<div class='messages'>
<h3><img src='cid:my_logo'> </h3>
<br>
<div style='font-size:15px;'>Email Notification on Bill Overdue for Payment </h4>
<p>Dear: $user_email</p>
<p>
The following bills are overdue for payment:
</p>
<table width='80%' border='0' cellspacing='0' cellpadding='0'>
  <tr style='font-weight:bold;'>
    <td>Service Provider</td>
    <td>Services</td>
    <td>Amount Due</td>
    <td>Due Date</td>
    <td>Overdue Days</td>
    <td> </td>
  </tr>
  <tr>
    <td>$service_provider</td>
    <td>$service_type</td>
    <td>$amount_paid</td>
    <td>$due_date</td>
    <td>$days_diff</td>
    <td>Click to Pay</td>
  </tr>
</table>

<h3>Yours<br>
Team Mate</h3>
";

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

}

January 25, 2017

Turn on php error checking (see my signature) and run this NOT as a cron job. Put some debugging lines in there to ensure that things are happening correctly and work on it THAT way until it works as desired. Then set it up as a cron job and test it by sending yourself the email still.

i have ran the script and it worked fine but wont run as a cron.

January 22, 2017

Is this your final script? You want to notify the user via email, no? Because you are not sending an email here, or outputting anything.

Check in the phpMyAdmin SQL console your query to make sure it is correct.

This is not the final script. My final script is:

$stmt = $pdo->query("SELECT *, DATEDIFF(due_date, NOW()) AS days_diff FROM xbp_bills WHERE bill_status = '$bill_status' AND CURDATE() BETWEEN due_date - INTERVAL reminder DAY AND due_date ");

$stmt->execute();

$mail = new PHPMailer;

//$mail->SMTPDebug = 3;

$mail->isSMTP();

$mail->Host = 'localhost';

$mail->SMTPAuth = true;

$mail->Username = 'username@user.com';

$mail->Password = 'password.';

$mail->SMTPSecure = 'ssl';

$mail->Port = 465;

$mail->From = 'no_reply@noreply.com';

$mail->FromName = 'Coy Sender';

$mail->WordWrap = 587;

$mail->AddEmbeddedImage("../img/logo.png", "my_logo");

$mail->isHTML(true);

$mail->Subject = 'Notification';

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

$email= $row['email'];

$due_date = $row['due_date'];

$service_provider = ucwords($row['service_provider']);

$service_type = ucwords($row['service_type']);

$bill_status = $row['bill_status'];

$message = "

This is a reminder

";

$mail->addAddress($email);

$mail->Body = $message;

$mail->send();

$mail->ClearAddresses();

}

January 14, 2017

hello

this is what i ended up with. The problem now is that the cron wont send?

$stmt = $pdo->query("SELECT *, DATEDIFF(due_date, NOW()) AS days_diff FROM xbp_bills WHERE bill_status = '$bill_status' AND CURDATE() BETWEEN due_date - INTERVAL reminder DAY AND due_date ");
$stmt->execute();
 
while($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
    $due_date = $row['due_date'];
    $service_provider = ucwords($row['service_provider']);
    $service_type = ucwords($row['service_type']);
    $bill_status = $row['bill_status'];  
 
}

December 31, 2016

Use a cron job to run daily. Query your event table for those events
SELECT ... WHERE CURDATE() BETWEEN eventdate - INTERVAL 10 DAY AND eventdate

... INTERVAL 10 DAY AND eventdate? Is it complete?

December 31, 2016

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

December 13, 2016

For a start, too many "UNIONS".
SELECT MONTH(due_date) as month
, SUM(amount_paid) as total
FROM (

    SELECT due_date, amount_paid FROM table1 
	UNION ALL
    SELECT due_date, amount_paid FROM table2 
	
    )x 
GROUP BY month
If you are having to do that, it looks like your table1 and table2 should be a single table with an additional identifier column.

I think i agree with you. I have changed the table to a singe table with an identifier. I created two tables cos of an issue but i have been able to sail pass that now.

thanks

December 13, 2016

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

December 13, 2016

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

January 20, 2016

Thanks all.

UNION did the magic. Credit to r937 of devshed. i dunno if posting the link to the solved issue is prohibited cos i don't want go against the laws of the forum.

January 19, 2016

your current database design will only work, using a JOIN query, if each username only has one row in the ca_processed (invoice) table, which would be useless in a real application. in this case, the username does become the value that relates any row(s) in the ca_my_payments table to the one correct row in the ca_processed table. you would however not use SUM(p.total_payment) to come up with the total_invoice amount as that would repeatedly add the amount for each JOINed row between the two tables.

if you have more than one row for any username in the ca_processed table, you can use a UNION query to sum up all the invoice amounts and the payment amounts for each username, but i doubt that's your goal.

Thanks. if you look at table A and table B, i used a JOIN to sum up the values and GROUP them based on the user name and it worked fine. i just want to have the SUMs in one table (table C) instead of two tables (A & B). maybe UNION could do it, can u pls come up with an example on UNION?

Thanks

January 18, 2016

a JOIN is used when you have related data between the tables.

an example would be a user table and your ca_processed table. a query to retrieve user information and his related row(s) from the ca_processed table would use a (LEFT) JOIN query. the user_id, defined in the user table, would be used in the ca_processed table to relate the user's rows in the ca_processed table to the user they belong to.

your two tables don't have any defined relationship between them. if you have multiple rows in ca_processed for any username and multiple rows in ca_my_payments for the same username, you will get the result of every row for that username in the first table joined to every row for that username in the second table.

if you had an invoice table (which i guess is what your ca_processed table is), that assigned an invoice_id to the invoiced amount and you stored row(s) in a payment table, related to the corresponding invoice using the invoice_id, you would use a (LEFT) JOIN query to get the amount of the invoice and any amounts paid toward that invoice because you now have a defined relationship between the data in the two tables.

the reason i mentioned a user table in the example of a join query, is because you shouldn't have usernames/emails duplicated in these two tables. you should have the user information stored in only one place, then use the user_id in any tables holding information that's related to the user.

thanks for the tips. my problem is just table c. i guess what they all have in common is the username and so far all the joins work. the database is been designed already like this but with time will make changes. the case on ground now is to have them together.

thanks

January 18, 2016

Does that last query give the correct results that should be inserted into tableC?

No it doesnt. The result is different from what is expected.

January 18, 2016

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>";

January 11, 2016

If you're not pulling in data from any other tables, and all you need from the transaction table is the date, then use MAX with a GROUP BY on the user.
SELECT c.firstname, c.surname, c.regDate, MAX(t.lastTrans) AS lastTrans
FROM confirmed c
LEFT JOIN transaction t ON c.user_id = t.user_id
WHERE c.status = 'confirmed'
GROUP BY c.user_id
ORDER BY MAX(t.lastTrans)

Thanks a zillion dozen times.

January 11, 2016

Hello all.
I dont know how to go about this. I have a table (Transactions) that contains transactions of users. Another table (Confirmed) contains details of every confirmed user.

I want to do a select statement that will display all the confirmed user with only the last of their transaction.
But so far all it does is replicate the user and their date of transaction and that is not what i want.

My intention is to get something like:

Firstname   Surname       Date Registered       Last Transaction
andrews       john       12-12-2014       10-10-2015
doe       andy       12-12-2010       12-12-2014

But i'm getting something like:

Firstname   Surname       Date Registered       Last Transaction
andrews       john       12-12-2014       10-10-2015
andrews       john       12-12-2014       10-11-2015
doe       andy       12-12-2010       12-12-2014
doe       andy       12-12-2010       01-12-2014
doe       andy       12-12-2010       12-12-2013

Thanks

$stm=$pdo->query("select * from confirmed left join transaction on confirmed.user_id = transaction.user_id where confirmed.status='confirmed' order by date");

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

echo $row['firstname'];
echo $row['surname'];
echo $row['regDate'];
echo $row['lastTrans'];

}

October 15, 2015

After setting my email to get the cron response, this is the error i got from the cron with my html messages:

Status: 302 Moved Temporarily
X-Powered-By: PHP/5.4.43
Set-Cookie: PHPSESSID=9aa0e1459caeb93365729438b3b12f54; path=/
Expires: Thu, 19 Nov 1981 08:52:00 GMT
Cache-Control: no-store, no-cache, must-revalidate, post-check=0, pre-check=0
Pragma: no-cache
location:./index.php
Content-type: text/html

October 13, 2015

Hi all.

I don't know why this is happening.
I have a scrip that backs up database. It works fine on database1 but when i use it on database2, it throws an error?
Fatal error: Call to a member function fetch_row() on a non-object in $tableshema = $shema->fetch_row() ;
both databases are on same domain.

ps: even with another backup script still throws an error in database2

thanks

 
#####################
//CONFIGURATIONS
#####################
// Define the name of the backup directory
define('BACKUP_DIR', 'cashBackup' ) ;

// Define  Database Credentials
define('HOST', 'localhost' ) ;
define('USER', 'username' ) ;
define('PASSWORD', 'password' ) ;
define('DB_NAME', 'database2' ) ;
/*
Define the filename for the Archive
If you plan to upload the  file to Amazon's S3 service , use only lower-case letters .
Watever follows the "&" character should be kept as is , it designates a timestamp , which will be used by the script .
*/
$archiveName = 'mysqlbackup--' . date('d-m-Y') . '@'.date('h.i.s').'&'.microtime(true) . '.sql' ;
// Set execution time limit
if(function_exists('max_execution_time')) {
if( ini_get('max_execution_time') > 0 )  set_time_limit(0) ;
}
 
//END  OF  CONFIGURATIONS
 
/*
 Create backupDir (if it's not yet created ) , with proper permissions .
 Create a ".htaccess" file to restrict web-access
*/
if (!file_exists(BACKUP_DIR)) mkdir(BACKUP_DIR , 0700) ;
if (!is_writable(BACKUP_DIR)) chmod(BACKUP_DIR , 0700) ;
// Create an ".htaccess" file , it will restrict direct access to the backup-directory .
$content = 'deny from all' ;
$file = new SplFileObject(BACKUP_DIR . '/.htaccess', "w") ;
$written = $file->fwrite($content) ;
// Verify that ".htaccess" is written , if not , die the script
if($written <13) die("Could not create a \".htaccess\" file , Backup task canceled")  ;
// Check timestamp of the latest Archive . If older than 24Hour , Create a new Archive
$lastArchive = getNameOfLastArchieve(BACKUP_DIR)  ;
$timestampOfLatestArchive =  substr(ltrim((stristr($lastArchive , '&')) , '&') , 0 , -  ;
if (allowCreateNewArchive($timestampOfLatestArchive)) {
// Create a new Archive
createNewArchive($archiveName) ;
} else {
echo '<p>'.'Sorry the latest Backup is not older than 24Hours , try a few hours later' .'</p>' ;
}
 
###########################
// DEFINING  THE FOUR  FUNCTIONS
// 1) createNewArchive : Creates an archive of a Mysql database
// 2) getFileSizeUnit  : gets an integer value and returns a proper Unit (Bytes , KB , MB)
// 3) getNameOfLastArchieve : Scans the "BackupDir" and returns the name of last created Archive
// 4) allowCreateNewArchive : Compares two timestamps (Yesterday , lastArchive) . Returns "TRUE" , If the latest Archive is onlder than 24Hours .
###########################
// Function createNewArchive
function createNewArchive($archiveName){
$mysqli = new mysqli(HOST , USER , PASSWORD , DB_NAME) ;
if (mysqli_connect_errno())
{
   printf("Connect failed: %s", mysqli_connect_error());
   exit();
}
 // Introduction information
 
$return = "--\n";
$return .= "-- A Mysql Backup System \n";
$return .= "--\n";
$return .= '-- Export created: ' . date("Y/m/d") . ' on ' . date("h:i") . "\n\n\n";
$return .= "--\n";
$return .= "-- Database : " . DB_NAME . "\n";
$return .= "--\n";
$return .= "-- --------------------------------------------------\n";
$return .= "-- ---------------------------------------------------\n";
$return .= 'SET AUTOCOMMIT = 0 ;' ."\n" ;
$return .= 'SET FOREIGN_KEY_CHECKS=0 ;' ."\n" ;
$tables = array() ;
// Exploring what tables this database has
$result = $mysqli->query('SHOW TABLES' ) ;
// Cycle through "$result" and put content into an array
while ($row = $result->fetch_row())
{
$tables[] = $row[0] ;
}
// Cycle through each  table
 foreach($tables as $table)
 {
// Get content of each table
$result = $mysqli->query('SELECT * FROM '. $table) ;
// Get number of fields (columns) of each table
$num_fields = $mysqli->field_count  ;
// Add table information
$return .= "--\n" ;
$return .= '-- Tabel structure for table `' . $table . '`' . "\n" ;
$return .= "--\n" ;
$return.= 'DROP TABLE  IF EXISTS `'.$table.'`;' . "\n" ;
// Get the table-shema
$shema = $mysqli->query('SHOW CREATE TABLE '.$table) ;
// Extract table shema
$tableshema = $shema->fetch_row() ;
// Append table-shema into code
$return.= $tableshema[1].";" . "\n\n" ;
// Cycle through each table-row
while($rowdata = $result->fetch_row())
{
// Prepare code that will insert data into table
$return .= 'INSERT INTO `'.$table .'`  VALUES ( '  ;
// Extract data of each row
for($i=0; $i<$num_fields; $i++)
{
$return .= '"'.$rowdata[$i] . "\"," ;
 }
 // Let's remove the last comma
 $return = substr("$return", 0, -1) ;
 $return .= ");" ."\n" ;
 }
 $return .= "\n\n" ;
}
// Close the connection
$mysqli->close() ;
$return .= 'SET FOREIGN_KEY_CHECKS = 1 ; '  . "\n" ;
$return .= 'COMMIT ; '  . "\n" ;
$return .= 'SET AUTOCOMMIT = 1 ; ' . "\n"  ;
//$file = file_put_contents($archiveName , $return) ;
$zip = new ZipArchive() ;
$resOpen = $zip->open(BACKUP_DIR . '/' .$archiveName.".zip" , ZIPARCHIVE::CREATE) ;
if( $resOpen ){
$zip->addFromString( $archiveName , "$return" ) ;
    }
$zip->close() ;
$fileSize = getFileSizeUnit(filesize(BACKUP_DIR . "/". $archiveName . '.zip')) ;
$message = <<<msg
  <h4>BACKUP  completed ,</h4>
  <p>
  The backup file has the name of  : <strong>  $archiveName  </strong> and it's file-size is : $fileSize.
  </p>
  <p>
 This zip archive can't be accessed via a web browser , as it's stored into a protected directory.<br>
  It's highly recomended to transfer this backup to another filesystem , use your favorite FTP client to download the file .
  </p>
msg;
echo $message ;
} // End of function creatNewArchive
 
// Function to append proper Unit after a file-size .
function getFileSizeUnit($file_size){
switch (true) {
    case ($file_size/1024 < 1) :
        return intval($file_size ) ." Bytes" ;
        break;
    case ($file_size/1024 >= 1 && $file_size/(1024*1024) < 1)  :
        return round(($file_size/1024) , 2) ." KB" ;
        break;
    default:
    return round($file_size/(1024*1024) , 2) ." MB" ;
}
} // End of Function getFileSizeUnit
 
// Funciton getNameOfLastArchieve
function getNameOfLastArchieve($backupDir) {
$allArchieves = array()  ;
$iterator = new DirectoryIterator($backupDir) ;
foreach ($iterator as $fileInfo) {
   if (!$fileInfo->isDir() && $fileInfo->getExtension() === 'zip') {
        $allArchieves[] = $fileInfo->getFilename() ;
 
    }
}
    return  end($allArchieves) ;
} // End of Function getNameOfLastArchieve
 
// Function allowCreateNewArchive
function allowCreateNewArchive($timestampOfLatestArchive , $timestamp = 24) {
    $yesterday =  time() - $timestamp*3600 ;
    return ($yesterday >= $timestampOfLatestArchive) ? true : false ;
} // End of Function allowCreateNewArchive

October 10, 2015

You can log output like so:
/usr/local/bin/php -q /home/username/directory/directory/file-name.php >> /log/file/path.log
Make the path something that the CRON owner has write permissions to. Make sure that error reporting is turned on in the script.

the cron owner has write permissions. was wondering maybe cron does not understand/cannot read sub-directory structure?

October 8, 2015

What does the script do? Are there any errors logged? Is cron set up to email you the results?

The script is suppose to send a reminder to members. I don't think there's any error logged cos i didn't get any. Yes the cron is set up to send a mail to me but i got no mail as well.

Thanks

October 8, 2015

Hi all.

I have a script that i want to run in a cronjob. but i dont know why it's not running.

i am using godaddy cpanel

ps: when i visit the url of the file, it works as expected, and also when i run it via a form button, it works also.

what could be the problem?

below is the command i used:

/usr/local/bin/php -q /home/username/directory/directory/file-name.php

thanks

Posted September 30, 2015 · September 30, 2015

Really all you have to do is put the mail sending logic into the loop rather than after it. Right now, you just keep overwriting the same set of variables in your loop, and then you send a single mail with the data that happens to come last.

However, you still need to fix some things:

You should send the e-mails in a controlled manner with an upper limit per script execution (e. g. at most 100 mails). Clearing your entire database table at once is not a good idea, because it may overload your server or even be abused for DoS attacks.

Never insert raw strings into an HTML context. This applies to both websites and e-mails. While most e-mail clients won't execute any injected scripts, this is still very unprofessional and may be flagged as malicious content. Use HTML-escaping with htmlspecialchars().

Thanks. I modified the code but

i know sending all the mails at once will curse flooding, was thinking i'd find a way around that later but thanks for reminding. but how will the script know the first hundred mails it has sent? or does it mean i will have to create another table so that after sending a 100 it marks them as sent/1 as the case maybe it updates the table column and loop again for those not sent.

Sign In

I-AM-OBODO

Posts

Joined

Last visited

Content Type

Profiles

Forums

Posts posted by I-AM-OBODO

Send a reminder based on chosen date

Send a reminder based on chosen date

Send a reminder based on chosen date

Send a reminder based on chosen date

Send a reminder based on chosen date

Send a reminder based on chosen date

Send a reminder based on chosen date

Send a reminder based on chosen date

Send a reminder based on chosen date

Sum row in UNION

Process the value of a searched result

Sum row in UNION

Getting wron result merging tables

Getting wron result merging tables

Getting wron result merging tables

Getting wron result merging tables

Getting wron result merging tables

Getting only last date of transaction with join

Getting only last date of transaction with join

cronjob wont send

What could be the reason

cronjob wont send

cronjob wont send

cronjob wont send

sending mails to different users with different values from database

Browse

Activity

Important Information