automatic php email

[Barand]

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.

[ianhaney50]

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

[Barand]

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]

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

[Barand]

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?

[ianhaney50]

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]

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]

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]

So it is working then?

[ianhaney50]

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]

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    |                |
+---------------+----------+------+-----+---------+----------------+

[ianhaney50]

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]

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]

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]

That should only happen if you are executing the query twice

[ianhaney50]

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]

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]

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]

 

$conn->query($sql);

 

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

 

You call it twice.

[ianhaney50]

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]

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


if ($result !== false) {

[ianhaney50]

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 = "[email protected]";
    $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);
?>

[Barand]

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    | [email protected]     | Ford Ka       | AB12CDE       | Car insurance | 25 June 2015 |
|          1 | Visitor A    | [email protected]     | Ford Ka       | AB12CDE       | Car tax       | 27 June 2015 |
|          1 | Visitor A    | [email protected]     | Ford Ka       | AB12CDE       | Car MOT       | 5 July 2015  |
|          2 | Visitor B    | [email protected]     | Mercedes C300 | MN45XYZ       | Car MOT       | 24 June 2015 |
|          2 | Visitor B    | [email protected]     | Mercedes C300 | MN45XYZ       | Car insurance | 25 June 2015 |
+------------+--------------+---------------+---------------+---------------+---------------+--------------+

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

[ianhaney50]

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]

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?