Jump to content

automatic php email


ianhaney50

Recommended Posts

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.

Link to comment
Share on other sites

  • Replies 157
  • Created
  • Last Reply

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

post-173104-0-42930700-1434969807_thumb.jpg

Link to comment
Share on other sites

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'] ?

Link to comment
Share on other sites

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"/>
Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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"/>
Link to comment
Share on other sites

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    |                |
+---------------+----------+------+-----+---------+----------------+
Link to comment
Share on other sites

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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);
?>
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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'

Link to comment
Share on other sites

Archived

This topic is now archived and is closed to further replies.


×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.