posting a custom end date for a record in mysql using php

General PHP Help
#1

Hello,

I’m working on a classifieds page for a client and they want a specific custom end date for the ads. Specifically, they want all ads to be disabled every two weeks on tuesday at 12pm. I’ve made a form and a php file to post the data into the db. So far everything works and no errors but I’m not able to get my enddate table to post the correct date. I tried to echo the code [php]$startdate = strtotime(“Tuesday”);
$enddate = strtotime("+2 weeks", $startdate);[/php] and it worked fine. However, the dates in my db are not correct and show as: “0000-00-00 00:00:00”

here is my code with the sensitive info scrubbed:
[php]

Untitled Document <?php mysql_pconnect("localhost","username","password") or die("Unable to connect to SQL server"); mysql_select_db("database") or die("Unable to select database");

//for testing & debugging purposes
//echo $_POST[‘name’];
//echo $_POST[‘email’];
//echo $_POST[‘phone’];
//echo $_POST[‘title’];
//echo $_POST[‘category’];
//echo $_POST[‘price’];
//echo $_POST[‘details’];’
//print_r($_POST);

$startdate = strtotime(“Tuesday”);
$enddate = strtotime("+2 weeks", $startdate);

$query = “INSERT INTO wp_awpcp_ads
(
ad_contact_name,
ad_contact_email,
ad_contact_phone,
ad_title,
ad_category_id,
ad_item_price,
ad_details,
disabled,
disabled_date,
ad_postdate,
ad_startdate,
ad_last_updated,
ad_enddate
)
VALUES
(
‘$_POST[name]’,
‘$_POST[email]’,
‘$_POST[phone]’,
‘$_POST[title]’,
‘$_POST[category]’,
‘$_POST[price]’,
‘$_POST[details]’,
1,
now(),
now(),
now(),
now(),
$enddate
)”;
if (mysql_query($query))
{
echo “New record created successfully”;
//header(‘Location: index.php’);
}
else {die(“Unable to post data”);}
?>

[/php]

thanks in advance for any assistance :slight_smile:

enddate_help.png
enddate_help.png892×269 5.11 KB

#2

First, stop using MySQL_ functions. They are old and should not be used anymore.

Second, you need some functions to handle the conversions. What Tuesday?

[php]$startDate = new DateTime();
echo “

Today is: {$startDate->format( ‘l m/d/Y’ )}

”;

// N gives a numeric day to the days of the week
// 2 means Tuesday
while ( $startDate->format( ‘N’ ) != 2 )
$startDate->modify( ‘+1 days’ );

echo “

Start date is: {$startDate->format(‘l m/d/Y’)}

”;
[/php]
#3

Astonecipher,

Thanks for your quick reply. This is for an ads page online and the client needs to ads to be disabled precisely after two weeks on tuesday at 12pm MST/MDT.

So far I changed my code to use sqli procedural and added the code you supplied. that solves a portion of my issue. I’ve added a new object called $endDate and set it to exactly 2 weeks ahead at 12pm Denver local time (I think).

So now I need to be able to insert the $endDate object into my db which uses a DateTime object. So far I’ve tried several different attempts.

[php]

Untitled Document <?php $servername = "localhost"; $username = "username"; $password = "password"; $dbname = "db";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}

date_default_timezone_set(“America/Denver”);
$startDate = new DateTime();
$endDate = new DateTime();

echo “

Today is: {$startDate->format( ‘l m/d/Y H:i:s’ )}

”;

// N gives a numeric day to the days of the week
// 2 means Tuesday
while ( $startDate->format( ‘N’ ) != 2 )

$startDate->modify( ‘+1 days’ );

echo “

Start date is: {$startDate->format(‘l Y/m/d H:i:s’)}

”;

//Before Modifications
echo “

End date is: {$endDate->format(‘l Y/m/d H:i:s’)}

”;

//Set endDate at Tuesday
$endDate = $startDate;
$endDate ->modify("+2 Weeks");
$endDate ->modify(“12:00pm”);
echo “

End date is: {$endDate->format(‘l Y/m/d H:i:sa’)}

”;

$sql = “INSERT INTO wp_awpcp_ads (ad_enddate)
VALUES ($endDate)”;
//fails with msg: Object of class DateTime could not be converted to string

if ($conn->query($sql) === TRUE) {
echo “New record created successfully”;
} else {
echo "Error: " . $sql . “
” . $conn->error;
}

$conn->close();
?>

[/php]
#4

Updated, this also uses midnight (00:00:00) for the start and endtimes.

[php]
$startDate = new DateTime(‘00:00:00’);
echo “

Today is: {$startDate->format( ‘l m/d/Y’ )}

”;

// N gives a numeric day to the days of the week
// 2 means Tuesday
while ( $startDate->format( ‘N’ ) != 2 )

$startDate->modify( ‘+1 days’ );

echo “

Start date is: {$startDate->format(‘l m/d/Y H:i:s’)}

”;

$endDate = $startDate->modify(’+2 weeks’);

echo “

End date set at: {$endDate->format(‘l m/d/Y H:i:s’)}

”; [/php]
#5

astonecipher,

thanks. That simplifies things a bit. I modified my code from yours (time set at 12pm, easier for client).

now how can I post the $endDate php object into mysql db as a datetime object?

I am now getting the error:

Today is: Tuesday 2016-04-26 12:00:00

Start date is: Tuesday 2016-04-26 12:00:00

End date is: Tuesday 2016/05/10 12:00:00pm

Catchable fatal error: Object of class DateTime could not be converted to string in [site directory]/test12.php on line 39

#6

You need it formatted. And FYI, 12:00:00 is noon not midnight.

->format(‘Y-m-d H:i:s’)

#7

Thanks, that cleared out the errors.

[php]$sql = “INSERT INTO wp_awpcp_ads (ad_enddate)
VALUES ($endDate->format(‘Y-m-d H:i:s’))”;[/php]

However, when I look at the msql db I get a ad_enddate value of ‘0000/00/00 00:00:00’ (see attached image). so for some odd reason it’s not posting the date/time

enddate_help.png

#8

prepared statements. Currently you are concatenating the query, which not only leads to security issues, but requires more work in the long run. I am a fan, as are most members here, of pdo for database operations.

#9

I probably forgot to mention, I’m new to php and have been copying and pasting from websites. So here’s my new code:
[php]

test13.php <?php $servername = "server"; $username = "uername"; $password = "password"; $dbname = "dbname";

//Setup for endDate value
date_default_timezone_set(“America/Denver”);
$startDate = new DateTime(‘12:00:00’);

echo “

Today is: {$startDate->format( ‘l Y-m-d H:i:s’ )}

”;

// N gives a numeric day to the days of the week
// 2 means Tuesday
while ( $startDate->format( ‘N’ ) != 2 )

$startDate->modify( ‘+1 days’ );

echo “

Start date is: {$startDate->format(‘l Y-m-d H:i:s’)}

”;

//Set endDate at Tuesday
$endDate = $startDate ->modify("+2 Weeks");
echo “

End date is: {$endDate->format(‘l Y/m/d H:i:sa’)}

”;

try {
$conn = new PDO(“mysql:host=$servername;dbname=$dbname”, $username, $password);
// set the PDO error mode to exception
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$sql = "INSERT INTO wp_awpcp_ads (ad_enddate)
	VALUES ($endDate->format('Y-m-d H:i:s'))";
	// use exec() because no results are returned
$conn->exec($sql);
echo "New record created successfully";
}

catch(PDOException $e)
{
echo $sql . “
” . $e->getMessage();
}

$conn = null;
?>

[/php]

still no errors and still the value is not setting.

#10

If you are actually wanting to learn, Rubio is find of this tutorial, https://phpdelusions.net/pdo

#11

Astonecipher,

Again I want to thank you for your assistance. I rewrote my code and was able to solve the remaining issues. The most important issue being how to insert a “named value”. The correct format is [php]VALUES
(?,?,?,?,?,?,1,now(),now(),now(),’".$tuesday."’)");[/php]. Here’s my final insert code for anyone else having the same issue:
[php]// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}

//----------------------------------------------------
//today is Tuesday
if (2 == date(‘N’)){
$tuesday = time(‘12:00:00’);
}else{
$tuesday = strtotime(‘last Tuesday’);
}

$tuesday = strtotime(’+2 week’, $tuesday);
//echo date(‘d-m-Y’, $tuesday) . ‘
’;
$tuesday = date(“Y-m-d H:i:s”, $tuesday);
//----------------------------------------------------

if(isset($_REQUEST[‘submit_ad’]))
{

// prepare and bind
$stmt = $conn->prepare(“INSERT INTO wp_awpcp_ads
(
ad_contact_name,
ad_contact_email,
ad_contact_phone,
ad_category_id,
ad_title,
ad_details,
disabled,
disabled_date,
ad_postdate,
ad_startdate,
ad_enddate
)
VALUES
(?,?,?,?,?,?,1,now(),now(),now(),’”.$tuesday."’)");

$stmt->bind_param(“ssssss”, $name,$phone,$email,$category,$adtitle,$addetails);

$name = $_POST[‘name’];
$email = $_POST[‘email’];
$phone = $_POST[‘phone’];
$category = $_POST[‘category’];
$adtitle = $_POST[‘adtitle’];
$addetails = $_POST[‘addetails’];

$stmt->execute();
echo “New ads created successfully”;

}
$conn->close();
?>[/php]

#12

Is this really working? You are binding the variables to the query before initializing them. The bind_param line should throw an “Undefined variable” notice and you shouldn’t get anything but NULL inserted.

[php] $stmt->bind_param(“ssssss”, $name,$phone,$email,$category,$adtitle,$addetails);

$name = $_POST[‘name’];
$email = $_POST[‘email’];
$phone = $_POST[‘phone’];
$category = $_POST[‘category’];
$adtitle = $_POST[‘adtitle’];
$addetails = $_POST[‘addetails’];[/php]

[hr]

Please also change this, it’s just comforting to see all params bound:

[php] // prepare and bind
$stmt = $conn->prepare(“INSERT INTO wp_awpcp_ads
(
ad_contact_name,
ad_contact_email,
ad_contact_phone,
ad_category_id,
ad_title,
ad_details,
disabled,
disabled_date,
ad_postdate,
ad_startdate,
ad_enddate
)
VALUES
(?,?,?,?,?,?,1,now(),now(),now(),’”.$tuesday."’)");

$stmt->bind_param(“ssssss”, $name,$phone,$email,$category,$adtitle,$addetails);

$name = $_POST[‘name’];
$email = $_POST[‘email’];
$phone = $_POST[‘phone’];
$category = $_POST[‘category’];
$adtitle = $_POST[‘adtitle’];
$addetails = $_POST[‘addetails’];[/php]

To

[php] // prepare and bind
$stmt = $conn->prepare(“INSERT INTO wp_awpcp_ads
(
ad_contact_name,
ad_contact_email,
ad_contact_phone,
ad_category_id,
ad_title,
ad_details,
disabled,
disabled_date,
ad_postdate,
ad_startdate,
ad_enddate
)
VALUES
(?,?,?,?,?,?,1,now(),now(),now(),?)”);

// setting these aren’t really necessary, could just bind directly from $_POST
$name = $_POST[‘name’];
$email = $_POST[‘email’];
$phone = $_POST[‘phone’];
$category = $_POST[‘category’];
$adtitle = $_POST[‘adtitle’];
$addetails = $_POST[‘addetails’];

$stmt->bind_param(“ssssss”, $name,$phone,$email,$category,$adtitle,$addetails, $tuesday);[/php]

#13

meh, yeah i rechecked. the working code is from before I switched to prepared statements which caused issues. so how do I initialize the variables first? here’s my full code:
[php]<?php
?>

<title>First Form</title>

Add Your Listing

Name *

Email
*

Phone
*

Category

<?php require_once 'philobytes_login.php'; //servername //dbname //username //password // Create connection $conn = new mysqli($servername, $username, $password, $dbname); // Check connection if ($conn->connect_error) { die("Connection failed: " . $conn->connect_error); } error_reporting(E_ALL); ini_set('display_errors', 1); $query = "SELECT ad_category_id, ad_category_parent_id, b.category_name from wp_awpcp_ads a INNER JOIN wp_awpcp_categories b where ad_category_id = category_id"; $select_category = mysqli_query($conn, $query); echo ""; echo "-Select-"; while ($row = mysqli_fetch_array($select_category)) { echo "" .$row['b.category_name'].""; } $conn->close(); echo ""; ?>


Please add a 2 word Title for the printed ad


Ad Title

Ad Details

(18 words left)

<?php /** * This function can be used to check the sanity of variables * * [member=5926]access[/member] private * * @param string $type The type of variable can be bool, float, numeric, string, array, or object * @param string $string The variable name you would like to check * @param string $length The maximum length of the variable * * return bool */ function sanityCheck($string, $type, $length){ // assign the type $type = 'is_'.$type; if(!$type($string)) { return FALSE; } // now we see if there is anything in the string elseif(empty($string)) { return FALSE; } // then we check how long the string is elseif(strlen($string) > $length) { return FALSE; } else { // if all is well, we return TRUE return TRUE; } } /** * This function if the $_POST vars are set * * [member=5926]access[/member] private * * return bool */ function checkSet(){ return isset($_POST['name'], $_POST['phone'], $_POST['email']); } /** * This function checks a number is greater than zero * and exactly $length digits. returns TRUE on success. * * [member=5926]access[/member] private * * @param int $num The number to check * @param int $length The number of digits in the number * * return bool */ function checkNumber($num, $length){ if($num > 0 && strlen($num) == $length) { return TRUE; } } /** * This function checks if an email address in a valid format * * [member=5926]access[/member] private * * @param string $email The email address to check * * return bool */ function checkEmail($email){ return preg_match('/^\S+@[\w\d.-]{2,}\.[\w]{2,6}$/iU', $email) ? TRUE : FALSE; } // check all our variables are set if(checkSet() != FALSE) { // check the POST variable userName is sane, and is not empty if(empty($_POST['name'])==FALSE && sanityCheck($_POST['name'], 'string', 50) != FALSE) { $name = $_POST['name']; } else { echo 'Name is not set'; exit(); } // check number is greater than 0 and $length digits long if(sanityCheck($_POST['phone'], 'numeric', 7) != FALSE && checkNumber($_POST['phone'], 10) == TRUE) { $phone = preg_replace("|(^[0-9]|", "", $phone); $phone = $_POST['phone']; } else { $phone=''; } // if the checks are ok for the email we assign the email address to a variable if(sanityCheck($_POST['email'], 'string', 50) != FALSE && checkEmail($_POST['email']) != FALSE) { $email = $_POST['email']; } else // if all is not well we echo an error message echo 'Invalid Email Address Supplied'; // and exit the script exit(); } else { // this will be the default message if the form accessed without POSTing echo '

Please fill in the form above

'; } require_once 'philobytes_login.php'; //servername //dbname //username //password // Create connection $conn = new mysqli($servername, $username, $password, $dbname); // Check connection if ($conn->connect_error) { die("Connection failed: " . $conn->connect_error); } //---------------------------------------------------- //today is Tuesday if (2 == date('N')){ $tuesday = time('12:00:00'); }else{ $tuesday = strtotime('last Tuesday'); } $tuesday = strtotime('+2 week', $tuesday); //echo date('d-m-Y', $tuesday) . '
'; $tuesday = date("Y-m-d H:i:s", $tuesday); //---------------------------------------------------- if(isset($_REQUEST['submit_ad'])) { // prepare and bind $stmt = $conn->prepare("INSERT INTO wp_awpcp_ads ( ad_contact_name, ad_contact_email, ad_contact_phone, ad_category_id, ad_title, ad_details, disabled, disabled_date, ad_postdate, ad_startdate, ad_enddate ) VALUES (?,?,?,?,?,?,1,now(),now(),now(),?)"); // setting these aren't really necessary, could just bind directly from $_POST $name = $_POST['name']; $email = $_POST['email']; $phone = $_POST['phone']; $category = $_POST['category']; $adtitle = $_POST['adtitle']; $addetails = $_POST['addetails']; $stmt->bind_param("ssssss", $name,$phone,$email,$category,$adtitle,$addetails, $tuesday); $stmt->execute(); echo "New ads created successfully"; } $conn->close(); ?> [/php]
#14

The new code you posted sets the variables befpre you pass them to the bind_param functiom so it should be ok

Sponsor our Newsletter | Privacy Policy | Terms of Service