Include an update Query

Assuming you've already created the field for NextVisit in the database...

<?php
header("Location: ./");
?>

<?
$hostname="xxxxxxxxxx";
$database="xxxxxxxx";
$username="xxxxxxxxxxxx";
$password="xxxxxxxxxxxxx";
$con = mysql_connect($hostname,$username,$password);
 if (!$con)
   {
   die('Could not connect: ' . mysql_error());
   }
else { 
mysql_select_db($database, $con);
}

//retrieve post data
$id = $_POST["ID"];
$Title = $_POST["Title"];
$FirstName = $_POST["FirstName"];
$LastName = $_POST["LastName"];
$HouseName = $_POST["HouseName"];
$HouseNumber = $_POST["HouseNumber"];
$Street = $_POST["Street"];
$Town = $_POST["Town"];
$PhoneNumber = $_POST["PhoneNumber"];
$Notes = $_POST["Notes"];
$Frequency = $_POST["Frequency"];
$LastVisit = $_POST["LastVisit"];
$NextVisit = ($Frequency * 30) + $LastVisit
$VisitBy = $_POST["VisitBy"];

if ($id != '') {
$query = "UPDATE tblpeople SET Title='" . $Title . "',FirstName='" . $FirstName . "',LastName='" . $LastName . "',
HouseName='" . $HouseName . "',HouseNumber='" . $HouseNumber . "',Street='" . $Street . "',Town='" . $Town . "',
PhoneNumber='" . $PhoneNumber . "',Notes='" . $Notes . "',Frequency='" . $Frequency . "',
LastVisit='" . $LastVisit . "',
NextVisit='" . $NextVisit . "',VisitBy='" . $VisitBy . "' WHERE ID=" . $id;
}
else {
$query = "INSERT INTO tblpeople(Title, FirstName, LastName, HouseName, HouseNumber, Street, Town, PhoneNumber, Notes, Frequency, LastVisit, NextVisit, VisitBy)
VALUES
(
'" . $Title . "',
'" . $FirstName . "',
'" . $LastName . "',
'" . $HouseName . "',
'" . $HouseNumber . "',
'" . $Street . "',
'" . $Town . "',
'" . $PhoneNumber . "',
'" . $Notes . "',
'" . $Frequency . "',
'" . $LastVisit . "',
'" . $NextVisit . "',
'" . $VisitBy . "'
)";
}
$result = mysql_query ($query) or die("SQL Error: " . mysql_error());
mysql_close();
?>

Select allOpen in new window

micropc thanks i got this to work but it is putting the next visit date as 0000-00-00 in the database, any ideas on how to make this a proper date?

Try this:

$NextVisit = strtotime($LastVisit . "+ " . $Frequency * 30 . "days")

Ha yes - for some reason I was thinking it was an integer. What dqmq said should do it.

You could also try converting $NextVisit to a DateTime and use the Add method like shown here: http://www.php.net/manual/en/datetime.add.php

This article tells the ways of handling DATETIME values in PHP and MySQL.  Please read it over and see if it helps answer your question.  If you're still not getting the results you want after you read the article, please post back and I will try to help run it to ground.
https://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/A_201-Handling-date-and-time-in-PHP-and-MySQL.html

best regards, ~Ray

thanks for getting back to me, i tried dqmq's suggestion but it is still showing up as 000-00-00 and not an actual date. Not sure about using a date/time also as i dont want the time in the field only a date.

Ray, I did look through your post and tried using the date_add function but could not get that to work either.

Apologies but I am new to this and I am really struggling. I have checked micropc and the data type for $LastVisit is "Date" in the database?

I was referring to the $LastVisit variable from $_POST - not the database field. The datatype of the LastVisit database field is date, but the datatype of the $LastVisit $_POST variable is String - that needs to be converted to a php DateType to add time to it. You can then insert the modified $LastVisit DateType variable into the database date field. Hope that makes since...

Regarding the $LastVisit variable from $_POST you can handle it this way.

$x = strtotime($_POST["LastVisit"]);
if (!$x) { /* BOGUS DATA IN THE POST ARRAY */ }
$datetime_string = date('c', $x);
/* NOW IT IS OK TO USE $datetime_string IN A QUERY */

Thanks Ray, I input the code but now neither the last visit or next visit now has dates they are both 0000-00-00. I have copied the code below now. I made some changes to the $NextVisit throughout to vhange it to $x - was this right to do that?

<?php
header("Location: ./");
?>

<?
$hostname-"xxxxxxxxx";
$database="xxxxxxxxx";
$username="xxxxxxxxx";
$password="xxxxxxxxxx";
$con = mysql_connect($hostname,$username,$password);
 if (!$con)
   {
   die('Could not connect: ' . mysql_error());
   }
else {
mysql_select_db($database, $con);
}

//retrieve post data
$id = $_POST["ID"];
$Title = $_POST["Title"];
$FirstName = $_POST["FirstName"];
$LastName = $_POST["LastName"];
$HouseName = $_POST["HouseName"];
$HouseNumber = $_POST["HouseNumber"];
$Street = $_POST["Street"];
$Town = $_POST["Town"];
$PhoneNumber = $_POST["PhoneNumber"];
$Notes = $_POST["Notes"];
$Frequency = $_POST["Frequency"];
$x = strtotime($_POST["LastVisit"]);
if (!$x) { /* BOGUS DATA IN THE POST ARRAY */ }
$datetime_string = date('c', $x);
/* NOW IT IS OK TO USE $datetime_string IN A QUERY */
$NextVisit = ($Frequency * 30) + $x;
$VisitBy = $_POST["VisitBy"];


if ($id != '') {
$query = "UPDATE tblpeople SET Title='" . $Title . "',FirstName='" . $FirstName . "',LastName='" . $LastName . "',
HouseName='" . $HouseName . "',HouseNumber='" . $HouseNumber . "',Street='" . $Street . "',Town='" . $Town . "',
PhoneNumber='" . $PhoneNumber . "',Notes='" . $Notes . "',Frequency='" . $Frequency . "',LastVisit='" . $x . "',NextVisit='" . $NextVisit ."',VisitBy='" . $VisitBy . "' WHERE ID=" . $id;
}
else {
$query = "INSERT INTO tblpeople(Title, FirstName, LastName, HouseName, HouseNumber, Street, Town, PhoneNumber, Notes, Frequency, LastVisit, ,NextVisit, VisitBy)
VALUES
(
'" . $Title . "',
'" . $FirstName . "',
'" . $LastName . "',
'" . $HouseName . "',
'" . $HouseNumber . "',
'" . $Street . "',
'" . $Town . "',
'" . $PhoneNumber . "',
'" . $Notes . "',
'" . $Frequency . "',
'" . $x . "',
'" . $NextVisit . "',
'" . $VisitBy . "'
)";
}
$result = mysql_query ($query) or die("SQL Error: " . mysql_error());
mysql_close();
?>

Suggest you buy this book and give yourself a little while to work through the lessons and examples.  It builds the ideas in a structured way, step-by-step and will be a much faster learning experience than trying to read code and guess at the thought processes that led the author to write one thing or another.  Very readable with great examples, and now in its fourth printing, it has been a permanent part of my professional library since Edition One.
http://www.sitepoint.com/books/phpmysql4/

There are a lot of things that can go wrong with the script posted above, and so I am going to suggest that you step back from it and just concentrate on one thing at a time.  In the instant case we need to see how to convert a string from a human-readable date into a computer-readable date.  Here is a little test script that will illustrate the concept.  You can experiment with it on my server here: http://www.laprbass.com/RAY_strtotime.php

<?php // RAY_strtotime.php
error_reporting(E_ALL);

// PHP 5.1+  SEE http://php.net/manual/en/function.date-default-timezone-set.php
date_default_timezone_set('America/Chicago');

// IF WE HAVE INPUT FROM THE URL QUERY STRING
if (!empty($_GET["s"]))
{
    // USE strtotime() FUNCTION TO MAKE A TIMESTAMP
    // MAN PAGE: http://php.net/manual/en/function.strtotime.php
    $unix_timestamp = strtotime($_GET["s"]);

    // TEST FOR SUCCESS OR FAILURE
    if ($unix_timestamp === FALSE)
    {
        echo "<strong>HONK!</strong><br /> <u>{$_GET["s"]}</u> NOT USEFUL WITH strtotime() <br/><br/><br/><br/>";
    }

    // ON SUCCESS, PRINT THE RESULTS
    else
    {
        echo "<strong>BINGO</strong><br /> <strong><u>{$_GET["s"]}</u></strong> WORKS WITH strtotime() <br/>";
        echo "THE INTEGER TIMESTAMP VALUE IS ";
        echo number_format($unix_timestamp) . "<br />";

        // FORMAT ISO AND HUMAN-READABLE DATES
        // MAN PAGE: http://php.net/manual/en/function.date.php
        $y = date('c', $unix_timestamp);
        echo "THE ISO8601 DATETIME STRING IS $y<br />";
        $z = date('l dS \o\f F Y g:i:s A', $unix_timestamp);
        echo "THE TEXTUAL DATE IS $z<br />";
    }

    echo PHP_EOL;
} // END OF PROCESSING INPUT
// PUT UP THE FORM
?>
<html>
<head>
<title>TEST THE PHP FUNCTION strtotime()</title>
</head>
<body onload="document.f.s.focus()">
<form name="f" method="get">
<br />TO TEST A STRING FOR A VALID DATE/TIME, TYPE IT HERE:<input name="s" />
<input type="submit" value="GO" />
</form>

<br/><strong>Note: Your local time may vary.  This server is in <?php echo date_default_timezone_get(); ?></strong>
<br/><?php echo 'Current PHP version is: ' . phpversion(); ?>
<br/>TRY TESTING SOME OF THESE STRINGS (CLICK THE LINK, OR COPY AND PASTE INTO THE FORM):


<?php function t($str)
{
    echo "<br/><a href=\"http://www.laprbass.com/RAY_strtotime.php?s=" . urlencode($str) . "\">$str</a>\n";
}
t('- 3 hours');
t('tomorrow');
t('tomorrow 3:15:25 pm');
t('March 15, 1986');
t('yesterday');
t('yesterday + 1 week');
t('next year');
t('now');
t('now + 627 hours 15 minutes');
t('tomorrow midnight');
t('tomorrow 1:35pm');
t('last Tuesday');
t('three days ago');
t('- 3 days');
t('A TIME');
t('A BOGUS TIME');
t('s time');
t('t time');
t('u time');
t("Avogadro's Constant");
t("Wednesday November, 10 2010 1:01pm");
t("Wednesday, November 10 2010 1:01pm");
t("First Tuesday 2005"); echo " works, but not the way you might think";
t("Last day of January + 1 Month"); echo " works, but not the way you might think";
t("January 31 + 1 Month"); echo " works, but not the way you might think";

t('-1000000000 seconds'); echo " one billion";
t('+1000000000 seconds');
t('42'); echo " and thanks for all the fish!";
?>

</body>
</html>

Select allOpen in new window

Some of the other things that can go wrong include the use of the MySQL data base to store information.  MySQL is not a black box -- it can and will fail for reasons that are not completely under your control, and your scripts need to be able to detect and report these conditions.  You need to see the inputs you sent to MySQL along with the responses that MySQL sent back.  This teaching example shows some of the basics of MySQL, along with ways you can detect and report error conditions.

<?php // RAY_mysql_example.php
error_reporting(E_ALL);


// THE ABSOLUTE MINIMUM YOU MUST UNDERSTAND TO USE PHP AND MYSQL
// MAN PAGE: http://php.net/manual/en/ref.mysql.php
// MAN PAGE: http://php.net/manual/en/mysql.installation.php
// MAN PAGE: http://php.net/manual/en/function.mysql-connect.php
// MAN PAGE: http://php.net/manual/en/function.mysql-select-db.php
// MAN PAGE: http://php.net/manual/en/function.mysql-real-escape-string.php
// MAN PAGE: http://php.net/manual/en/function.mysql-query.php
// MAN PAGE: http://php.net/manual/en/function.mysql-errno.php
// MAN PAGE: http://php.net/manual/en/function.mysql-error.php
// MAN PAGE: http://php.net/manual/en/function.mysql-num-rows.php
// MAN PAGE: http://php.net/manual/en/function.mysql-fetch-assoc.php
// MAN PAGE: http://php.net/manual/en/function.mysql-fetch-array.php
// MAN PAGE: http://php.net/manual/en/function.mysql-insert-id.php



// CONNECTION AND SELECTION VARIABLES FOR THE DATABASE
$db_host = "localhost"; // PROBABLY THIS IS OK
$db_name = "??";        // GET THESE FROM YOUR HOSTING COMPANY
$db_user = "??";
$db_word = "??";


// OPEN A CONNECTION TO THE DATA BASE SERVER
if (!$db_connection = mysql_connect("$db_host", "$db_user", "$db_word"))
{
    $errmsg = mysql_errno() . ' ' . mysql_error();
    echo "<br/>NO DB CONNECTION: ";
    echo "<br/> $errmsg <br/>";
}

// SELECT THE MYSQL DATA BASE
if (!$db_sel = mysql_select_db($db_name, $db_connection))
{
    $errmsg = mysql_errno() . ' ' . mysql_error();
    echo "<br/>NO DB SELECTION: ";
    echo "<br/> $errmsg <br/>";
    die('NO DATA BASE');
}
// IF THE SCRIPT GETS THIS FAR IT CAN DO QUERIES




// ESCAPE AN EXTERNAL DATA FIELD FOR USE IN MYSQL QUERIES
$safe_username = mysql_real_escape_string($_POST["username"]);




// CREATE AND SEND A SELECT QUERY AND TEST THE RESULTS
$sql = "SELECT id FROM my_table WHERE username='$safe_username'";
$res = mysql_query($sql);

// IF mysql_query() RETURNS FALSE, SHOW THE ERROR
if (!$res)
{
    $errmsg = mysql_errno() . ' ' . mysql_error();
    echo "<br/>QUERY FAIL: ";
    echo "<br/>$sql <br/>";
    die($errmsg);
}
// IF WE GET THIS FAR, THE QUERY SUCCEEDED AND WE HAVE A RESOURCE-ID IN $res SO WE CAN NOW USE $res IN OTHER MYSQL FUNCTIONS




// DETERMINE HOW MANY ROWS OF RESULTS WE GOT
$num = mysql_num_rows($res);
if (!$num)
{
    echo "<br/>QUERY FOUND NO DATA: ";
    echo "<br/>$sql <br/>";
}
else
{
    echo "<br/>QUERY FOUND $num ROWS OF DATA ";
    echo "<br/>$sql <br/>";
}




// ITERATE OVER THE RESULTS SET TO SHOW WHAT WE FOUND
while ($row = mysql_fetch_assoc($res))
{
    var_dump($row);
}




// A WAY OF DETERMINING HOW MANY ROWS WE HAVE IN A TABLE
$sql = "SELECT COUNT(*) FROM my_table";
$res = mysql_query($sql);

// IF mysql_query() RETURNS FALSE, GET THE ERROR REASONS
if (!$res)
{
    $errmsg = mysql_errno() . ' ' . mysql_error();
    echo "<br/>QUERY FAIL: ";
    echo "<br/>$sql <br/>";
    die($errmsg);
}
// GET THE RESULTS SET ROW IN AN ARRAY WITH A NUMERIC INDEX - POSITION ZERO IS THE COUNT
$row = mysql_fetch_array($res, MYSQL_NUM);
$num = $row[0];
$fmt = number_format($num);
echo "<br/>THERE ARE $fmt ROWS IN THE TABLE";




// MAKING AN INSERT QUERY AND TESTING THE RESULTS
$sql = "INSERT INTO my_table (username) VALUES ('$safe_username')";
$res = mysql_query($sql);

// IF mysql_query() RETURNS FALSE, GET THE ERROR REASONS
if (!$res)
{
    $errmsg = mysql_errno() . ' ' . mysql_error();
    echo "<br/>QUERY FAIL: ";
    echo "<br/>$sql <br/>";
    die($errmsg);
}
// GET THE AUTO_INCREMENT ID OF THE RECORD JUST INSERTED - PER THE DB CONNECTION
$id  = mysql_insert_id($db_connection);
echo "<br/>YOU JUST INSERTED A RECORD WITH AUTO_INCREMENT ID = $id";

Select allOpen in new window

One of the other things that you need to understand is the relationship between HTML forms and the PHP "superglobal" request variables.  
http://php.net/manual/en/reserved.variables.get.php
http://php.net/manual/en/reserved.variables.post.php
http://php.net/manual/en/reserved.variables.request.php

This segment of the PHP Introductory Tutorial discusses how to deal with external data.
http://php.net/manual/en/tutorial.forms.php

Hope that helps, ~Ray

Ray, thanks for all these. I have looked through them and tried everything. I purchased the book too but no use I still cant get the NextVisit to calculate

although my question was not answered i appreciate the effort and help.