automatic php email

Barand · June 22, 2015

A couple of things would help it to work better

1) Mysqli_real_escape_string requires the connection as the first parmeter

$visitor_name = mysqli_real_escape_string($conn, $_POST['visitor_name']);

or

$visitor_name = $conn->real_escape_string($_POST['visitor_name']);

2 ) The item table would be set up once and forgotten. No need to update it when processing the form

3 ) As I have already said 2 or 3 times already, you would get the new insert_id AFTER you have inserted the visitor record.

$sql = "INSERT INTO visitors (visitor_name, visitor_email, visitor_firstline, visitor_secondline,visitor_town, visitor_county, visitor_postcode, visitor_tel, visitor_mobile, visitor_model, visitor_plate) VALUES
('$visitor_name', '$visitor_email', '$visitor_firstline', '$visitor_secondline', '$visitor_town', '$visitor_county', '$visitor_postcode', '$visitor_tel', '$visitor_mobile', '$visitor_model', '$visitor_plate')";

$conn->query($sql);

$visitor_id = $conn->insert_id;

$sql = "INSERT INTO renewal (visitor_id, item_id, renewal_date) VALUES
($visitor_id, 1, '$insurance_date'),
($visitor_id, 2, '$mot_date'),
($visitor_id, 3, '$tax_date')";

$conn->query($sql);

edit: PS The index errors are because your form field names are visitor_mot, visitor_tax and visitor_insurance.

Edited June 22, 2015 by Barand

ianhaney50 · June 22, 2015

Hi Barand

Sorry being a pain I know

It is adding into the database now and the visitor info if being added ok along with the data in the renewal table but in the renewal table, it is adding 3 rows to it and the renewal date column is being displayed as 0000-00-00 and the data notified column is posting the current time an date?

see the screenshot attached

Edited June 22, 2015 by ianhaney50

Barand · June 22, 2015

The date notified is a TIMESTAMP, therefore updates automatically when the record is updated. If you only want to update when a notification is sent then define as

date_notified DATETIME DEFAULT NULL

and, when you send a notification, SET date_notified = NOW()

As for those 0 dates, what is the value originally sent to the page in $_POST['visitor_insurance'] ?

ianhaney50 · June 22, 2015

Ok cool have changed the database to date_notified DATETIME DEFAULT NULL and the coding in the UPDATE sql query to SET date_notified = NOW()

The dates I am entering is 23-06-2015 for the tax, 24-06-2015 for the mot and 25-06-2015 for the insurance

The coding for them bits is below

$insurance_date = mysqli_real_escape_string($conn, $_POST['renewal_date']);
$mot_date = mysqli_real_escape_string($conn, $_POST['renewal_date']);
$tax_date = mysqli_real_escape_string($conn, $_POST['renewal_date']);

AND

$sql = "INSERT INTO renewal (visitor_id, item_id, renewal_date) VALUES
($visitor_id, 1, '$insurance_date'),
($visitor_id, 2, '$mot_date'),
($visitor_id, 3, '$tax_date')";

the coding for them bits in the form is below

<label>Car Tax Renewal Date :</label>
<input type="text" id="datepicker" name="renewal_date" required="required" placeholder="Please Enter your Car Tax Renewal Date"/>
<br><br>
<label>MOT Expiry Date :</label>
<input type="text" id="datepicker2" name="renewal_date" required="required" placeholder="Please Enter your MOT Expiry Date"/>
<br><br>
<label>Insurance Expiry Date :</label>
<input type="text" id="datepicker3" name="renewal_date" required="required" placeholder="Please Enter your Insurance Expiry Date"/>

Edited June 22, 2015 by ianhaney50

Barand · June 22, 2015

A couple of problems

1. You do not have a form field with the name of "renewal_date". As I told you in reply #76 they are "visitor_mot", "visitor_tax" and "visitor_insurance".

2. Dates should always be stored in YYYY-mm-dd format.

Change

$insurance_date = mysqli_real_escape_string($conn, $_POST['renewal_date']);
$mot_date = mysqli_real_escape_string($conn, $_POST['renewal_date']);
$tax_date = mysqli_real_escape_string($conn, $_POST['renewal_date']);

to

$insurance_date = date('Y-m-d', strtotime($_POST['visitor_insurance']));
$mot_date = date('Y-m-d', strtotime($_POST['visitor_mot']));
$tax_date = date('Y-m-d', strtotime($_POST['visitor_tax']));

EDIT: Just noticed you gave all three dates in the form the same name. Why would you want to do that?

Edited June 22, 2015 by Barand

ianhaney50 · June 22, 2015

Ok done that by changing the coding and added a new visitor and is still storing it as 0000-00-00 in the renewal_date column in the renewal table

I got the following

$insurance_date = date('Y-m-d', strtotime($_POST['visitor_insurance']));
$mot_date = date('Y-m-d', strtotime($_POST['visitor_mot']));
$tax_date = date('Y-m-d', strtotime($_POST['visitor_tax']));

AND

<label>Car Tax Renewal Date :</label>
<input type="text" id="datepicker" name="visitor_tax" required="required" placeholder="Please Enter your Car Tax Renewal Date"/>
<br><br>
<label>MOT Expiry Date :</label>
<input type="text" id="datepicker2" name="visitor_mot" required="required" placeholder="Please Enter your MOT Expiry Date"/>
<br><br>
<label>Insurance Expiry Date :</label>
<input type="text" id="datepicker3" name="visitor_insurance" required="required" placeholder="Please Enter your Insurance Expiry Date"/>

Barand · June 22, 2015

After this code

$sql = "INSERT INTO renewal (visitor_id, item_id, renewal_date) VALUES
($visitor_id, 1, '$insurance_date'),
($visitor_id, 2, '$mot_date'),
($visitor_id, 3, '$tax_date')";

can you

echo "<pre>$sql</pre>";

and post the result

ianhaney50 · June 22, 2015

No worries, done that and has added to the database and on the php page has outputted the following

INSERT INTO renewal (visitor_id, item_id, renewal_date) VALUES(22, 1, '2015-06-25'),(22, 2, '2015-06-24'),(22, 3, '2015-06-23')

Barand · June 22, 2015

So it is working then?

ianhaney50 · June 22, 2015

Sorry yeah is outputting correctly on the page but in the database it is making the date appear as 0000-00-00 as can see in the screenshot

Barand · June 22, 2015

I notice the renewal_id contains all 0. Should be defined as "INT NOT NULL AUTO_INCREMENT" and should be the PRIMARY KEY

When I ran it

mysql> INSERT INTO renewal (visitor_id, item_id, renewal_date) VALUES
    -> (22, 1, '2015-06-25'),
    -> (22, 2, '2015-06-24'),
    -> (22, 3, '2015-06-23');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM renewal;
+------------+------------+---------+--------------+---------------+
| renewal_id | visitor_id | item_id | renewal_date | date_notified |
+------------+------------+---------+--------------+---------------+
|          1 |         22 |       1 | 2015-06-25   | NULL          |
|          2 |         22 |       2 | 2015-06-24   | NULL          |
|          3 |         22 |       3 | 2015-06-23   | NULL          |
+------------+------------+---------+--------------+---------------+

And my table

mysql> describe renewal;
+---------------+----------+------+-----+---------+----------------+
| Field         | Type     | Null | Key | Default | Extra          |
+---------------+----------+------+-----+---------+----------------+
| renewal_id    | int(11)  | NO   | PRI | NULL    | auto_increment |
| visitor_id    | int(11)  | YES  |     | NULL    |                |
| item_id       | int(11)  | YES  |     | NULL    |                |
| renewal_date  | date     | YES  |     | NULL    |                |
| date_notified | datetime | YES  |     | NULL    |                |
+---------------+----------+------+-----+---------+----------------+

Edited June 22, 2015 by Barand

ianhaney50 · June 22, 2015

I wonder if that is why in the renewal table records are duplicated twice

I tried making it AI and it comes up with incorrect table defination and only one column can be AUTO and it needs to be defined as a key and does not let me change it and then try and make it primary and says duplicate entry 0 for key primary

ianhaney50 · June 22, 2015

Sorry realised cause it had data in it so removed the data and is ok now, just going to do a test one again

ianhaney50 · June 22, 2015

Is it right that it is duplicating the records in the renewal table, thought it would just do 3 rows for the visitor_id and not 6, I have attached a screen shot of what it is doing

Barand · June 22, 2015

That should only happen if you are executing the query twice

ianhaney50 · June 22, 2015

Is what I thought

the query is only being executed once

$sql = "INSERT INTO visitors (visitor_name, visitor_email, visitor_firstline, visitor_secondline,visitor_town, visitor_county, visitor_postcode, visitor_tel, visitor_mobile, visitor_model, visitor_plate) VALUES
('$visitor_name', '$visitor_email', '$visitor_firstline', '$visitor_secondline', '$visitor_town', '$visitor_county', '$visitor_postcode', '$visitor_tel', '$visitor_mobile', '$visitor_model', '$visitor_plate')";

$conn->query($sql); - would these lines affect it as is called here then bit further below

$visitor_id = $conn->insert_id;

$insurance_date = date('Y-m-d', strtotime($_POST['visitor_insurance']));
$mot_date = date('Y-m-d', strtotime($_POST['visitor_mot']));
$tax_date = date('Y-m-d', strtotime($_POST['visitor_tax']));

$sql = "INSERT INTO renewal (visitor_id, item_id, renewal_date) VALUES
($visitor_id, 1, '$insurance_date'),
($visitor_id, 2, '$mot_date'),
($visitor_id, 3, '$tax_date')";

echo "<pre>$sql</pre>";

$conn->query($sql); - same as the line bit further above

Is weird cause the visitor table is ok and not being duplicated, is only the renewal table data being duplicated

Barand · June 22, 2015

The first one executes the sql to insert the visitor record, the second executes the renewal insert.

Are you sure there isn't a call to mysqli_query lower down the page?

ianhaney50 · June 22, 2015

That's it, is what I thought

na just double checked

Below is the php coding I have

<?php
if(isset($_POST["submit"])){
$servername = "";
$username = "";
$password = "";
$dbname = "";
 
// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}

// escape variables for security
$visitor_name = mysqli_real_escape_string($conn, $_POST['visitor_name']);
$visitor_email = mysqli_real_escape_string($conn, $_POST['visitor_email']);
$visitor_firstline = mysqli_real_escape_string($conn, $_POST['visitor_firstline']);
$visitor_secondline = mysqli_real_escape_string($conn, $_POST['visitor_secondline']);
$visitor_town = mysqli_real_escape_string($conn, $_POST['visitor_town']);
$visitor_county = mysqli_real_escape_string($conn, $_POST['visitor_county']);
$visitor_postcode = mysqli_real_escape_string($conn, $_POST['visitor_postcode']);
$visitor_tel = mysqli_real_escape_string($conn, $_POST['visitor_tel']);
$visitor_mobile = mysqli_real_escape_string($conn, $_POST['visitor_mobile']);
$visitor_model = mysqli_real_escape_string($conn, $_POST['visitor_model']);
$visitor_plate = mysqli_real_escape_string($conn, $_POST['visitor_plate']);

$sql = "INSERT INTO visitors (visitor_name, visitor_email, visitor_firstline, visitor_secondline,visitor_town, visitor_county, visitor_postcode, visitor_tel, visitor_mobile, visitor_model, visitor_plate) VALUES
('$visitor_name', '$visitor_email', '$visitor_firstline', '$visitor_secondline', '$visitor_town', '$visitor_county', '$visitor_postcode', '$visitor_tel', '$visitor_mobile', '$visitor_model', '$visitor_plate')";

$conn->query($sql);

$visitor_id = $conn->insert_id;

$insurance_date = date('Y-m-d', strtotime($_POST['visitor_insurance']));
$mot_date = date('Y-m-d', strtotime($_POST['visitor_mot']));
$tax_date = date('Y-m-d', strtotime($_POST['visitor_tax']));

$sql = "INSERT INTO renewal (visitor_id, item_id, renewal_date) VALUES
($visitor_id, 1, '$insurance_date'),
($visitor_id, 2, '$mot_date'),
($visitor_id, 3, '$tax_date')";

echo "<pre>$sql</pre>";

$conn->query($sql);
 
if ($conn->query($sql) === TRUE) {
echo "<script type= 'text/javascript'>alert('Your Information has been added successfully to our database');</script>";
} else {
echo "<script type= 'text/javascript'>alert('Error: " . $sql . "<br>" . $conn->error."');</script>";
}
 
$conn->close();
}
?>

I even checked my footer.php just to make sure

Barand · June 22, 2015

$conn->query($sql);

if ($conn->query($sql) === TRUE) {

You call it twice.

ianhaney50 · June 22, 2015

Ahh ok

if I take that out, is is still possible to have the alert box appear to let the visitor know their info has been added to the database?

Barand · June 22, 2015



$result = $conn->query($sql);


if ($result !== false) {

ianhaney50 · June 22, 2015

Thank you so much, is perfect now

just thought would test the email to make sure that sends still and have not received anything yet, I know the other day it was delayed a bit but thought it would come through by now, is it ok to quickly glance over the coding and make sure is all ok, it looks all ok from what I can see

<?php

$db = mysqli_connect("" , "", "") or die("Check connection parameters!"); 
// Optionally skip select_db and use: mysqli_connect(host,user,pass,dbname)  
mysqli_select_db($db,"") or die(mysqli_error($db));

if (mysqli_connect_error()) {
    die ('Failed to connect to MySQL');
} else {
	/*SUCCESS MSG*/
	echo '';
}

$sqlCommand = "SELECT 
v.visitor_id
, visitor_name
, visitor_email
, visitor_model
, visitor_plate
, item.description
, DATE_FORMAT(renewal_date, '%e %M %Y') as datedue
FROM visitors v
    INNER JOIN renewal USING (visitor_id)
    INNER JOIN item USING (item_id)
WHERE renewal_date BETWEEN CURDATE() AND CURDATE()+INTERVAL 14 DAY
    AND date_notified < CURDATE()-INTERVAL 14 DAY;";

$query = mysqli_query($db, $sqlCommand) or die (mysqli_error($db)); 

/*$count = 1;*/

//fetch tha data from the database 
while ($row = mysqli_fetch_array($query)) {

	/*echo $sqlCommand;*/

    ini_set( 'display_errors', 1 );
    error_reporting( E_ALL );
    $from = "noreply@irhwebsites.co.uk";
    $to = "{$row['visitor_email']}";
    $subject = "{$row['visitor_name']} Expiry Date(s)";
	$message = "Name: {$row['visitor_name']} \n Car Tax Expiry Date: {$row['taxdate']} \n Car MOT Expiry Date: {$row['motdate']} \n Insurance Expiry Date: {$row['vidate']}";
	$headers = "From:" . $from;
    $sendmail = mail($to,$subject,$message, $headers);
	
	/*if ($count % 5 == 0) {
      sleep(5); // this will wait 5 secs every 5 emails sent, and then continue the while loop
    }
    $count++;*/
	
	if ($sendmail) {
    echo "Email Successfully Sent";
} else { 
    echo "Error in Sending of Email";
}
	
	if($sendmail){
           $sqlCommand2 = "UPDATE visitors SET date_notified = NOW() WHERE visitor_id = " . $row['visitor_id'];
            
			$query2 = mysqli_query($db, $sqlCommand2) or die (mysqli_error($db));
        } else {
		echo "do nothing";
		}
	
}

// Free the results  
mysqli_free_result($query);

//close the connection
mysqli_close($db);
?>

Edited June 22, 2015 by ianhaney50

Barand · June 22, 2015

Now you have NULL dates I had to modify the query slightly

SELECT 
v.visitor_id
, visitor_name
, visitor_email
, visitor_model
, visitor_plate
, item.description
, DATE_FORMAT(renewal_date, '%e %M %Y') as datedue
FROM visitor v
    INNER JOIN renewal USING (visitor_id)
    INNER JOIN item USING (item_id)
WHERE renewal_date BETWEEN CURDATE() AND CURDATE()+INTERVAL 14 DAY
    AND IFNULL(date_notified, '1901-01-01') < CURDATE()-INTERVAL 14 DAY
ORDER BY v.visitor_id, renewal_date;

Note that the query results will now look like this, a row for each due date

+------------+--------------+---------------+---------------+---------------+---------------+--------------+
| visitor_id | visitor_name | visitor_email | visitor_model | visitor_plate | description   | datedue      |
+------------+--------------+---------------+---------------+---------------+---------------+--------------+
|          1 | Visitor A    | A@aaa.com     | Ford Ka       | AB12CDE       | Car insurance | 25 June 2015 |
|          1 | Visitor A    | A@aaa.com     | Ford Ka       | AB12CDE       | Car tax       | 27 June 2015 |
|          1 | Visitor A    | A@aaa.com     | Ford Ka       | AB12CDE       | Car MOT       | 5 July 2015  |
|          2 | Visitor B    | B@bbb.com     | Mercedes C300 | MN45XYZ       | Car MOT       | 24 June 2015 |
|          2 | Visitor B    | B@bbb.com     | Mercedes C300 | MN45XYZ       | Car insurance | 25 June 2015 |
+------------+--------------+---------------+---------------+---------------+---------------+--------------+

so your processing will need changing for multiple rows per visitor.

ianhaney50 · June 22, 2015

Oh right cool what do you mean the processing, sorry, do you mean the output within the email?

The php page is displaying the following

29

Ian Haney
Notice: Undefined index: taxdate in /home/sites/broadwaymediadesigns.co.uk/public_html/sites/the-tax-elephants/auto-email.php on line 59

Notice: Undefined index: motdate in /home/sites/broadwaymediadesigns.co.uk/public_html/sites/the-tax-elephants/auto-email.php on line 59

Notice: Undefined index: vidate in /home/sites/broadwaymediadesigns.co.uk/public_html/sites/the-tax-elephants/auto-email.php on line 59

Email Successfully Sent

Unknown column 'date_notified' in 'field list'

Barand · June 22, 2015

You don't have columns "taxdate", "motdate", "vidate" in the query results (see my prev reply ^ )

As for date_notified, what have you called the column in the renewal table?

Sign In

automatic php email

Recommended Posts

Link to comment

Share on other sites

Top Posters In This Topic

Popular Days

Top Posters In This Topic

Popular Days

Popular Posts

Barand

Posted Images

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information