php email and mysql issue

ianhaney · Jun 19, 2015

Hi

I am trying to make a website that sends out automatic php emails when users car tax is near it's expiry date, ideally I would the automatic emails to be sent out 2 weeks before the expiry date

I have got the info storing in a mysql database

Below is the coding I have, I am using phpmailer but I am not receiving any email?

<?php
ini_set('display_startup_errors',1);
ini_set('display_errors',1);
error_reporting(-1);
?>

<html>
<title>Automatic Email</title>
<body>

<?php
$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);
}

// get the 'id' value from the URL (if it exists), making sure that it is valid (checing that it is numeric/larger than 0)
 if (isset($_GET['visitor_id']) && ($visitor_id = (int)$_GET['visitor_id']) > 0)
 {
 // query db
 $result = mysql_query("SELECT * FROM visitors WHERE visitor_id = ".$visitor_id) or die(mysql_error());

 // Using assoc is better in my opinion.  Yes array gives you the same info, but it also gives double the info in numeric indexes, which typically are not needed. 
	$row = mysql_fetch_assoc($result);

// check that the 'id' matches up with a row in the databse
if($row)
{

$sql = "SELECT * FROM visitors WHERE (visitor_tax AND visitor_mot AND visitor_insurance > DATE_SUB(CURDATE(), INTERVAL 2 WEEK)";

{

if($sql){
$to = $row['visitor_email'];

require("class.phpmailer.php");

$mail = new PHPMailer();

$mail->IsSMTP();  // telling the class to use SMTP
$mail->Host     = "mail.broadwaymediadesigns.co.uk"; // SMTP server

$mail->From     = "noreply@broadwaymediadesigns.co.uk";
$mail->AddAddress("ianhaney@broadwaymediadesigns.co.uk");

$mail->Subject  = "TEST EMAIL";
$mail->Body     = "Hi! \n\n This is my test e-mail sent through PHPMailer.";
$mail->WordWrap = 50;

if(!$mail->Send()) {
  echo 'Message was not sent.';
  echo 'Mailer error: ' . $mail->ErrorInfo;
} else {
  echo 'Message has been sent.';
}

}
}
 }
 }
?>
</body>
</html>

cluelessPHP · Jun 20, 2015

Where did you get this example? It uses a lot of depcricated

Weedpacket · Jun 20, 2015

You certainly can't mix MySQLi and MySQL functions; the latter should be replaced by the former.

ianhaney · Jun 20, 2015

Hi

I got that coding from another forum post who got it working

I have changed it slightly but still a blank white page, could it be that I am mixing mysqli with mysql be causing it to be a blank white page?

The new coding I have is below

<?php
ini_set('display_startup_errors',1);
ini_set('display_errors',1);
error_reporting(-1);
?>

<html>
<title>Automatic Email</title>
<body>

<?php
$username = "";
$password = "";
$hostname = "";

//connection to the database
$dbhandle = mysql_connect($hostname, $username, $password)
or die("Unable to connect to MySQL");

//select a database to work with
$selected = mysql_select_db("",$dbhandle)
or die("Could not select examples");

//execute the SQL query and return records
/$result = mysql_query("SELECT FROM visitors WHERE visitor_mot = DATE_ADD(curdate(), INTERVAL 2 DAY)");/
$query = 'SELECT FROM visitors WHERE visitor_mot = DATE_ADD(curdate(), INTERVAL 2 DAY)';

/$result = mysql_query("SELECT FROM visitors WHERE TO_DAYS(date)=To_DAYS(NOW())-3");*/
$result = mysql_query($query);
//fetch tha data from the database
while ($row = mysql_fetch_array($result)) {
mail("ianhaney@irhwebsites.co.uk", "Subject", "body");
}
//close the connection
mysql_close($dbhandle);
?>

</body>
</html>

ianhaney · Jun 20, 2015

I've updated the coding from mysql to mysqli, does it look right?

<?php
ini_set('display_startup_errors',1);
ini_set('display_errors',1);
error_reporting(-1);
?>

<html>
<title>Automatic Email</title>
<body>

<?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));

$query = "SELECT * FROM visitors WHERE visitor_mot = DATE_ADD(curdate(), INTERVAL 2 DAY)";

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

//fetch tha data from the database
while ($row = mysqli_fetch_array($result)) {
mail("ianhaney@irhwebsites.co.uk", "Subject", "body");
}

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

</body>
</html>

I am getting a blank white page still

ianhaney · Jun 20, 2015

I got the test email script and put that in and worked perfect so I put the database mysqli coding back in and the email don't work, is just a blank white page

<?php
ini_set('display_startup_errors',1);
ini_set('display_errors',1);
error_reporting(-1);
?>

<html>
<title>Automatic Email</title>
<body>

<?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));

$query = "SELECT * FROM visitors WHERE visitor_mot = DATE_ADD(curdate(), INTERVAL 1 DAY)";

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

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

ini_set( 'display_errors', 1 );
error_reporting( E_ALL );
$from = "noreply@irhwebsites.co.uk";
$to = "ianhaney@irhwebsites.co.uk";
$subject = "PHP Mail Test script";
$message = "This is a test to check the PHP Mail functionality";
$headers = "From:" . $from;
mail($to,$subject,$message, $headers);
echo "Test email sent";

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

</body>
</html>

I was just thinking if the php script stops running before it gets to the email script part?

ianhaney · Jun 20, 2015

Hi

Sorry I trying to debug my PHP bit by bit and have put in a connection success or fail message as well as echo $query and all it says is success so I know it connecting to the database ok as when I change the database info it comes up with fail check parameters message so am thinking is there something wrong with my $query line, I have commented out the email part for now and do one bit at a time, below is the coding I currently have

<?php
ini_set('display_startup_errors',1);
ini_set('display_errors',1);
error_reporting(-1);
?>

<html>
<title>Automatic Email</title>
<body>

<?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 {
	echo 'success';
}

$query = "SELECT * FROM visitors WHERE visitor_mot = DATE_ADD(curdate(), INTERVAL 1 DAY)";  

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

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

echo $query;

/*ini_set( 'display_errors', 1 );
error_reporting( E_ALL );
$from = "noreply@irhwebsites.co.uk";
$to = "ianhaney@irhwebsites.co.uk";
$subject = "PHP Mail Test script";
$message = "This is a test to check the PHP Mail functionality";
$headers = "From:" . $from;
mail($to,$subject,$message, $headers);
echo "Test email sent";*/
}
//close the connection
mysqli_close($db);
?>

</body>
</html>

ianhaney · Jun 20, 2015

Hi, sorry got a update

I am getting there I think, I put in echo $sqlcommand and echoed that out all ok so then put in the echo $row results so it displayed the results from the mysql database table and has done that ok as I put WHERE clause in and defined what WHERE clause values and outputted all ok so think the issue was with the following line

$query = "SELECT * FROM visitors WHERE visitor_mot = DATE_ADD(curdate(), INTERVAL 1 DAY)";

So what would be the correct coding to be where the results are displayed if the date from visitor_mot is tomorrow for example so want it to display if the date is 1 day away, hope that makes sense

My current coding is below

<?php
ini_set('display_startup_errors',1);
ini_set('display_errors',1);
error_reporting(-1);
?>

<html>
<title>Automatic Email</title>
<body>

<?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 {
        echo 'success';
}

$sqlCommand = "SELECT * FROM visitors WHERE visitor_county='Essex' AND visitor_id='3' "; // no limit

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

/*$query = "SELECT * FROM visitors WHERE visitor_mot = DATE_ADD(curdate(), INTERVAL 1 DAY)";  

$result = mysqli_query($db, $query) or die(mysqli_error($db));*/

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

echo "<br><br>";
echo $row['visitor_id'];
echo "<br><br>";
echo $row ['visitor_name'];
  echo "<br />";

    /*echo $sqlCommand;*/

/*ini_set( 'display_errors', 1 );
error_reporting( E_ALL );
$from = "noreply@irhwebsites.co.uk";
$to = "ianhaney@irhwebsites.co.uk";
$subject = "PHP Mail Test script";
$message = "This is a test to check the PHP Mail functionality";
$headers = "From:" . $from;
mail($to,$subject,$message, $headers);
echo "Test email sent";*/
}

// Free the results  

mysqli_free_result($query);

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

</body>
</html>

Feel like I am getting closer with it slowly

php email and mysql issue

Iianhaney

CcluelessPHP

Weedpacket

Iianhaney

Iianhaney

Iianhaney

Iianhaney

Iianhaney