Crone Job Setup using Mysql PHP

Question

I have a following table called vehicle master

vhcl_no
department
vhcl_type
vhcl_make
fuel_type
chassis_no
engine_no
manufacture_on
cubic_capacity
rcbook_no
rcbook_valid_till
insurance_no
insurance_company
insurance_from
insurance_to
vhcl_puc
puc_validity
remarks
created_by
updated_by
created_on

I want to send Reminder mail to the vehicle users whose

           1) RC book vailidity before 1 month    
           2) Insurance vailidity before 1 month
           3) PUC vailidity before 1 week

How to create a cron job which can fullfill above 3 conditions using same table?

I have tried something but stuck what to do ahead

$query1 = mysqli_query($con1, "select * from vehicle_master");

$count = mysqli_num_rows($query1);

if ($count >= 1)
{
    while($row = mysqli_fetch_array($query1))
        {

                $vhcl_no           =    $row['vhcl_no'];
                $department        =    $row['department'];
                $vhcl_type         =    $row['vhcl_type'];
                $vhcl_make         =    $row['vhcl_make'];
                $fuel_type         =    $row['fuel_type'];
                $chassis_no        =    $row['chassis_no'];
                $engine_no         =    $row['engine_no'];
                $manufacture_on    =    $row['manufacture_on'];
                $cubic_capacity    =    $row['cubic_capacity'];
                $rcbook_no         =    $row['rcbook_no'];
                $rcbook_valid_till =    $row['rcbook_valid_till'];
                $insurance_no      =    $row['insurance_no'];
                $insurance_company =    $row['insurance_company'];
                $insurance_from    =    $row['insurance_from'];
                $insurance_to      =    $row['insurance_to'];
                $vhcl_puc          =    $row['vhcl_puc'];
                $puc_validity      =    $row['puc_validity'];
                $remarks           =    $row['remarks'];
                $created_by        =    $row['created_by'];
                $updated_by        =    $row['updated_by'];
                $created_on        =    $row['created_on'];

            $search_output .= "<ul>
                <li>
                    <h4>".$vhcl_no."</h4>
                    <p><b>Department: ".$department."</b></p>
                    <p>Vehicle Type : ".$vhcl_type."</p>
                    <p>Vehicle Make: ".$vhcl_make."</p>
                    <p>Engine No: ".$engine_no."</p>
                    <p>Manufacture On: ".$manufacture_on."</p>
                    <p>Rcbook No: ".$rcbook_no."</p>
                    <p>Rcbook Valid Till: ".$rcbook_valid_till."</p>        
                    <p>Insurance No: ".$insurance_no."</p>    
                    <p>Insurance Company: ".$insurance_company."</p>
                    <p>Insurance From: ".$insurance_from."</p>    
                    <p>Insurance To: ".$insurance_to."</p>
                    <p>Vehicle PUC: ".$vhcl_puc."</p>    
                    <p>Vehicle PUC Validity: ".$puc_validity."</p>
                    <p>Vehicle Remaorks: ".$remarks."</p>      
                </li><br/>                  
            </ul>";    

          //  var_dump($search_output); die;

                $sendMail = true;



        }
}

I want to develop a query inwhich i can perform all 3 condtionds together to send a reminder mail

score 1 · Answer 1 · edited May 23 '17 at 11:44

1

Use conditionals to compare dates, Compare given date with today

Here is crontab reference: http://www.adminschoice.com/crontab-quick-reference

If your fields are timestamps, SQL you might want could be:

$monthFromNow = strtotime("+1 month");
$sql = "select * from vehicle_master where rcbook_valid_till < " . time() - ($monthFromNow) . " and insurance_to < " . time() - ($monthFromNow) . ";";

add the fields you want in the query with [condition and condition and ..]

http://php.net/manual/en/function.date.php https://en.wikipedia.org/wiki/Unix_time

edited May 23 '17 at 11:44

Community

1
1

answered Aug 02 '16 at 09:05

visualex

736
12
17

thanks for answering.@visualex... I have a different fileds for dates as you can see in vehicle_master.. I want to develop a query inwhich i can perform all 3 condtionds together to send a reminder mail . – vijay nathji Aug 02 '16 at 09:09

score 1 · Answer 2 · answered Aug 02 '16 at 11:50

I'm sorry that this isnt the cronjob what you asked for but this is what you need for the SQL query to be. Sql query answers for this.

1) RC book vailidity before 1 month    
2) Insurance vailidity before 1 month
3) PUC vailidity before 1 week

Just put this SQL query inside mysqli_query($con1, "HERE");

SELECT * FROM vehicle_master
WHERE (rcbook_valid_till) <= (NOW() + INTERVAL 1 MONTH) AND (rcbook_valid_till) >= (NOW()) OR 
WHERE (insurance_to) <= (NOW() + INTERVAL 1 MONTH) AND (insurance_to) >=(NOW()) OR
WHERE (puc_validity) <= (NOW() + INTERVAL 1 WEEK) AND (puc_validity) >=(NOW())

It will get all data for those who have their vailiditys inside your said time limits. Even if one of them is met.

Crone Job Setup using Mysql PHP

2 Answers2