Solved

Include an update Query

Posted on 2012-03-09
17
227 Views
Last Modified: 2012-04-20
I found the following code on ferdy christants pages. I am new to this but have changed this round slightly to suit me. One thing I am struggling with now is that I have a field called "NextVisit" (date type)  and what i need is that after the record is saved then a calcualtion is carried out 30 * Frequency added onto the LastVisit. Can you point me in the right direction with this?

<?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"];
$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 . "',VisitBy='" . $VisitBy . "' WHERE ID=" . $id;
}
else {
$query = "INSERT INTO tblpeople(Title, FirstName, LastName, HouseName, HouseNumber, Street, Town, PhoneNumber, Notes, Frequency, LastVisit, VisitBy)
VALUES
(
'" . $Title . "',
'" . $FirstName . "',
'" . $LastName . "',
'" . $HouseName . "',
'" . $HouseNumber . "',
'" . $Street . "',
'" . $Town . "',
'" . $PhoneNumber . "',
'" . $Notes . "',
'" . $Frequency . "',
'" . $LastVisit . "',
'" . $VisitBy . "'
)";
}
$result = mysql_query ($query) or die("SQL Error: " . mysql_error());
mysql_close();
?>
0
Comment
Question by:bryanscott53
  • 6
  • 6
  • 4
  • +1
17 Comments
 
LVL 7

Expert Comment

by:micropc1
ID: 37704133
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();
?>

Open in new window

0
 

Author Comment

by:bryanscott53
ID: 37704413
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?
0
 
LVL 42

Expert Comment

by:dqmq
ID: 37705095
Try this:

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

Expert Comment

by:micropc1
ID: 37705116
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
0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 37705487
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.
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/A_201-Handling-date-and-time-in-PHP-and-MySQL.html

best regards, ~Ray
0
 

Author Comment

by:bryanscott53
ID: 37705533
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.
0
 
LVL 7

Assisted Solution

by:micropc1
micropc1 earned 250 total points
ID: 37705655
I believe the problem is that you have $LastVisit, which is a string datatype containing something like "01/01/2012".  Since you can't add to a string you need to convert the string to a DateTime datatype. Then you can use the Add method to add to that value.

So something like this may work (i haven't tested this)...
$NextVisit = new DateValue($LastVisit)
$NextVisit->add(new DateInterval("P" . 30*$Frequency . "S"))

To output $NextVisit you would use $NextVisit->format('format string')
0
 

Author Comment

by:bryanscott53
ID: 37705751
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?
0
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
LVL 7

Expert Comment

by:micropc1
ID: 37705848
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...
0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 37705958
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 */
0
 

Author Comment

by:bryanscott53
ID: 37708623
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();
?>
0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 37710037
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>

Open in new window

0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 37710059
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";

Open in new window

0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 37710079
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
0
 

Author Comment

by:bryanscott53
ID: 37791673
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
0
 
LVL 108

Accepted Solution

by:
Ray Paseur earned 250 total points
ID: 37792962
As you may have guessed by now... there are a lot of moving parts to this question.  And if you believe as I do that time is money, you might want to consider setting the project aside and hiring a professional developer to help you get the answers.  It will not cost you much money and you will get the results much faster than if you try to learn all of what you need to know by yourself.  Best of luck with it, ~Ray
0
 

Author Closing Comment

by:bryanscott53
ID: 37874444
although my question was not answered i appreciate the effort and help.
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Generating table dynamically is the most common issue faced by php developers.... So it seems there is a need of an article that explains the basic concept of generating tables dynamically. It just requires a basic knowledge of html and little maths…
Nothing in an HTTP request can be trusted, including HTTP headers and form data.  A form token is a tool that can be used to guard against request forgeries (CSRF).  This article shows an improved approach to form tokens, making it more difficult to…
The viewer will learn how to count occurrences of each item in an array.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now