0

What should i use if i need to make a scheduled email let us say every 12 midnight with query finding data that represents as a request for each approver. im using sql server and php

King Jherold
  • 129
  • 3
  • 14
  • 1
    You will need to write a php page that does the processing that you wish to be done. Then you will need to set up a cron job on your server to trigger the php page when you want it to run. – Joseph_J Mar 20 '18 at 06:02
  • @Joseph_J will it be the same with scheduled task on sql server? i think cron job is for linux/unix only (not sure) – King Jherold Mar 20 '18 at 06:29
  • I do not know. I use linux. But I did a quick search and found this. Looks like there are lots of resources to get you going. https://stackoverflow.com/questions/2348863/how-to-run-a-stored-procedure-in-sql-server-every-hour – Joseph_J Mar 20 '18 at 06:33

2 Answers2

1

This task involves couple of technologies:

  1. cron or other scheduler to setup the execution of the job
  2. PHP script that will read the data from the database and if found any relevant data send it to given email address or more
  3. the database with stored data and probably the list of email recipients
  4. you may want to log when were the emails sent and to whom etc.

Shall you have any difficulties? That's best for learning.

ino
  • 2,345
  • 1
  • 15
  • 27
1

The wording of your question seems a little different than what is being asked in the title. If you're looking to conditionally send emails based on data being available or not in the database, the solution is pretty simple.

Setup a cron job that sends your email out every 12 midnight. Before running, the cron job should check a boolean flag that gets toggled true or false based on data being available in the database. To toggle the flag, make a GET request to your mail server (or whatever the METHOD and Endpoint your API offers for returning email count data as a response) using a function.

Using Node, you could write a very small module that takes care of this for you. 

*index.js*

// Easy to use, *promise based*, NPM module for HTTP requests.
import axios from 'axios'

const domain = `your-mail-server.com`
const endpoint = `route/to/mail/api/`
const query = `list=mail`

const url = `https://${domain}/${endpoint}?${query1}`

const fetchMail = (bool) => {
    return axios.get(url)
        .then(res => res.body)
        .then(data => data.mail.count)
        .then(count => count !== 0 ? bool = true : bool = false)
        .catch(err => new Error(err));
}

const checkIfDataExistsInDatabase = () => {
    let mailExists
    return fetchMail(mailExists) ? {{ RUN_CRON_JOB }} : null
}

checkIfDataExistsInDatabase()

Just run index.js using node via the console. 

node ./path/to/project/index.js

Make sure to setup a working package.json first by running:

npm init

or

yarn

Follow the basic setup prompts. Default values will work out of the box, so need pressure on having to change anything you may not understand right away.

Then include axios as a project dependency by running:

npm install axios

or

yarn add axios

Set the start command found in scripts to

"start": "node ./index.js"

Go back to your terminal and run:

npm run i

or

yarn

Then finally, run:

npm run start

or

yarn run start

Your app will run, running your cron job only if mail exists in the database.

You may want to add an additional Cron Job that runs this node app for you every midnight. Also, error logging and some kind of success/failure output would be a nice touch.

Cheers!

DaneTheory
  • 282
  • 3
  • 16