help with date code

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
gcapp
Forum Newbie
Posts: 14
Joined: Thu Jun 01, 2006 8:59 am

help with date code

Post by gcapp »

hello - I am new to php coding. Have been doing asp for years.

I tried using a asp to php conversion tool for my issue, but it did not resolve it.
I have the following asp code:

sql="select ID, Deadline, Job_Title, Rate, Job_Location, Job_Function, Qualifications, Start_Date, End_Date from Job_Postings where End_Date >= #thedate# and Start_Date <= #stopdate# order by Start_Date asc"
sql=replace(sql,"thedate",currentdate)
sql=replace(sql,"stopdate",enddate)


I am trying to convert to php and the conversion tool gave me this:

$sql="select ID, Deadline, Job_Title, Rate, Job_Location, Job_Function, Qualifications, Start_Date, End_Date from Job_Postings where End_Date >= #thedate# and Start_Date <= #stopdate# order by Start_Date asc";


$sql=str_replace("thedate",$currentdate,$sql);
$sql=str_replace("stopdate",$enddate,$sql);

But this is not working correctly.
Can someone help?? Again, I am brand new to php, so feel free to dumb it down if need be!!

Thnaks in advance
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Re: help with date code

Post by Christopher »

Not really a PHP thing, but MySQL wants dates in quotes (usually single in MySQL). So:

Code: Select all

$sql="select ID, Deadline, Job_Title, Rate, Job_Location, Job_Function, Qualifications, Start_Date, End_Date from Job_Postings where End_Date >= #thedate# and Start_Date <= #stopdate# order by Start_Date asc"; 
$sql=str_replace("#thedate#","'$currentdate'",$sql);
$sql=str_replace("#stopdate#","'$enddate'",$sql);
Or more PHP style would be:

Code: Select all

$sql = "select ID, Deadline, Job_Title, Rate, Job_Location, Job_Function, Qualifications, Start_Date, End_Date from Job_Postings where End_Date >= '{thedate}' and Start_Date <= '{stopdate}' order by Start_Date asc"; 
$sql = str_replace('{thedate}', $currentdate, $sql);
$sql = str_replace('{stopdate}', $enddate, $sql);
(#10850)
gcapp
Forum Newbie
Posts: 14
Joined: Thu Jun 01, 2006 8:59 am

Re: help with date code

Post by gcapp »

I appreciate the help but it did not work. On my webpage it produces this:

= '(thedate)' and Start_Date <= '(stopdate)' order by Start_Date asc"; $sql=str_replace('(thedate)',$currentdate,$sql); $sql=str_replace('(stopdate)',$enddate,$sql); $rsUpcomingEvent.$Open $sql, $JobEvents; if ($rsUpcomingEvent.$EOF==true) { echo "There are no job postings at this time."; } else { do while $rsUpcomingEvent.$EOF=false; if ($rsUpcomingEvent.$Fields["Start_Date"] <= date ()) { if ($rsUpcomingEvent["Deadline"] != "") { echo "

Any ideas?
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Re: help with date code

Post by Christopher »

Does your page start with a <?php tag? That is just showing the source to the page. Is PHP working?
(#10850)
gcapp
Forum Newbie
Posts: 14
Joined: Thu Jun 01, 2006 8:59 am

Re: help with date code

Post by gcapp »

If anyone can help, I would appreciate it.

Here is the entire code:

Code: Select all

<?php
$JobEvents;
$oConn, $oRs;
$qry, $connectstr;
$db_name, $db_username, $db_userpassword;
$db_server;
 
// Intentionally obfuscated
$db_server = "127.0.0.1:3306";
$db_name = "database";
$db_username = "user";
$db_userpassword = "pass";
 
$connectstr = "Driver={MySQL ODBC 3.51 Driver};SERVER=".$db_server.";DATABASE=".$db_name.";UID=".$db_username.";PWD=".$db_userpassword;
 
Set $oConn = Server.CreateObject("ADODB.Connection");
$oConn.$Open $connectstr;
 
$rsUpcomingEvent;     
set $rsUpcomingEvent= server.CreateObject("adodb.recordset");
 
//display calendar starts here
$sql;
 
$currentdate, $enddate;
$currentdate = date(); 
$enddate= DateAdd("d", 7, date());      //enddate returns actual date + 7 days. 
 
$sql="select ID, Deadline, Job_Title, Rate, Job_Location, Job_Function, Qualifications, Start_Date, End_Date from Job_Postings where End_Date >= '{thedate}' and Start_Date <= '{stopdate}' order by Start_Date asc"; 
	
$sql=str_replace{'(thedate}','$currentdate',$sql);
$sql=str_replace('{stopdate}','$enddate',$sql);
 
	
$rsUpcomingEvent.$Open $sql, $JobEvents;
 
	
if ($rsUpcomingEvent.$EOF==true) {   
	echo "<font color='red'><b>There are no job postings at this time.</b></font>";
	
} else {
 
	 do while $rsUpcomingEvent.$EOF=false;
	 if ($rsUpcomingEvent.$Fields["Start_Date"] <= date ()) {
 
	
	  if ($rsUpcomingEvent["Deadline"] != "") {
	  echo "<p class=redtext><b>"."Deadline: "."".$rsUpcomingEvent["Deadline"]."</b></p>";
		}
	 if ($rsUpcomingEvent["Job_Title"] != "") { 
	 echo "<p class=blacksmalltext><b>"."Job Title: "."</b>".$rsUpcomingEvent["Job_Title"];
	 }
	if ($rsUpcomingEvent["Rate"] != "") {
	echo "<br><b>"."Rate: "."</b>".$rsUpcomingEvent["Rate"];
	}
	if ($rsUpcomingEvent["Job_Location"] != "") {
	echo "<br><b>"."Location: "."</b>".$rsUpcomingEvent["Job_Location"];
	}
	echo "<p class=blacksmalltext><b><u>"."Basic Function:"."</b></u>";
	if ($rsUpcomingEvent["Job_Function"] != "") {
	echo "<br>".$rsUpcomingEvent["Job_Function"];
	}
	echo "<p class=blacksmalltext><b><u>"."Qualifications:"."</b></u>";
	if ($rsUpcomingEvent["Qualifications"] != "") {
	echo "<br>".$rsUpcomingEvent["Qualifications"];
	}
	echo "<hr noshade size='1'>";
} else {
echo "No Listings";
 
	echo "</font>";
}
		$rsUpcomingEvent.$MoveNext;
	loop;
			
}
 
$rsUpcomingEvent.Close;
set $rsUpcomingEvent=nothing;
 
set $oConn = nothing;
</p>
?php>
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: help with date code

Post by Celauran »

I noticed a number of syntax issues in there stemming, I imagine, from the differences between ASP and PHP. I have made some small corrections and left comments explaining them. Hope that helps.

Code: Select all

<?php

// Obfuscated intentionally
$db_server = "127.0.0.1:3306";
$db_name = "database";
$db_username = "user";
$db_userpassword = "password";
 
// Unless you otherwise need an ODBC connection, we can use PHP's MySQL drivers
// $connectstr = "Driver={MySQL ODBC 3.51 Driver};SERVER=".$db_server.";DATABASE=".$db_name.";UID=".$db_username.";PWD=".$db_userpassword;
 
$db = new PDO("mysql:host={$db_server};dbname={$db_name}", $db_username, $db_userpassword);

/**
 * Set/set are not valid keywords in PHP.
 * Moreover, the PDO object we instantiated above established the connection.
 * Finally, PHP does not use dot notation, using instead $object->property
 * or $object->method() for instantiated classes and FQCN::$property or
 * FQCN::method() for static calls
 */
// Set $oConn = Server.CreateObject("ADODB.Connection");
// $oConn.$Open $connectstr;

// $rsUpcomingEvent;    
// set $rsUpcomingEvent= server.CreateObject("adodb.recordset");
 
//display calendar starts here
// $sql;
 
// http://php.net/manual/en/function.date.php
$currentdate = date('Y-m-d H:i:s');
// http://php.net/manual/en/function.strtotime.php
$enddate = date('Y-m-d H:i:s', strtotime('+7 days'));

// $currentdate = date();
// $enddate= DateAdd("d", 7, date());      //enddate returns actual date + 7 days.
 
// This is mostly good but we're going to use prepared statements here instead
// $sql="select ID, Deadline, Job_Title, Rate, Job_Location, Job_Function, Qualifications, Start_Date, End_Date from Job_Postings where End_Date >= '{thedate}' and Start_Date <= '{stopdate}' order by Start_Date asc";

$sql = "SELECT ID, Deadline, Job_Title, Rate, Job_Location, Job_Function, Qualifications, Start_Date, End_Date FROM Job_Postings WHERE End_Date >= :current AND Start_Date <= :stopdate ORDER BY Start_Date ASC";

// Prepare the query. We can swap in values for placeholders when executing the query
// http://php.net/manual/en/pdo.prepare.php
$stmt = $db->prepare($sql);

// $sql=str_replace{'(thedate}','$currentdate',$sql);
// $sql=str_replace('{stopdate}','$enddate',$sql);
 

// $rsUpcomingEvent.$Open $sql, $JobEvents;
 
// Execute the query
// http://php.net/manual/en/pdostatement.execute.php
$exec = $stmt->execute(['current' => $currentdate, 'stopdate' => $enddate]);

// Fetch the results if query succeeded
$results = [];
if ($exec) {
    $results = $stmt->fetchAll(PDO::FETCH_ASSOC);
}

// We have already fetched the result set. Rather than checking for EOF, we can
// check if said array contains values
// if ($rsUpcomingEvent.$EOF==true) {  
if (empty($results)) {
    echo "<font color='red'><b>There are no job postings at this time.</b></font>";
} else {
 
    /**
     * Looping constructs have a slightly different syntax in PHP
     * Rather than do { } while we're going to use a foreach here
     * to iterate over the result set
     */
    // do while $rsUpcomingEvent.$EOF=false;
    foreach ($results as $rsUpcomingEvent) {
        // We've already got a variable containing date(), so we'll use that
        // if ($rsUpcomingEvent.$Fields["Start_Date"] <= date ()) {
        // Shouldn't occur anyway since we set these constraints in the query
        if ($rsUpcomingEvent['Start_Date'] <= $currentdate) { // Kept existing logic, but this looks backwards
            if ($rsUpcomingEvent["Deadline"] != "") {
                echo "<p class=redtext><b>"."Deadline: "."".$rsUpcomingEvent["Deadline"]."</b></p>";
            }
            if ($rsUpcomingEvent["Job_Title"] != "") {
                echo "<p class=blacksmalltext><b>"."Job Title: "."</b>".$rsUpcomingEvent["Job_Title"];
            }
            if ($rsUpcomingEvent["Rate"] != "") {
                echo "<br><b>"."Rate: "."</b>".$rsUpcomingEvent["Rate"];
            }
            if ($rsUpcomingEvent["Job_Location"] != "") {
                echo "<br><b>"."Location: "."</b>".$rsUpcomingEvent["Job_Location"];
            }
            echo "<p class=blacksmalltext><b><u>"."Basic Function:"."</b></u>";
            if ($rsUpcomingEvent["Job_Function"] != "") {
                echo "<br>".$rsUpcomingEvent["Job_Function"];
            }
            echo "<p class=blacksmalltext><b><u>"."Qualifications:"."</b></u>";
            if ($rsUpcomingEvent["Qualifications"] != "") {
                echo "<br>".$rsUpcomingEvent["Qualifications"];
            }
            echo "<hr noshade size='1'>";
        } else {
            echo "No Listings";

            echo "</font>";
        }
    }
    // $rsUpcomingEvent.$MoveNext;
    // loop;

}
 
// $rsUpcomingEvent.Close;
// set $rsUpcomingEvent=nothing;
//  
// set $oConn = nothing;
// </p>
// ?php>
?>
gcapp
Forum Newbie
Posts: 14
Joined: Thu Jun 01, 2006 8:59 am

Re: help with date code

Post by gcapp »

Celauran,

I truly appreciate the help. I am new to PHP and the coding is quite different than ASP. I took your code and pasted it into my page and this is the result that shows on my webpage:

property * or $object->method() for instantiated classes and FQCN::$property or * FQCN::method() for static calls */ // Set $oConn = Server.CreateObject("ADODB.Connection"); // $oConn.$Open $connectstr; // $rsUpcomingEvent; // set $rsUpcomingEvent= server.CreateObject("adodb.recordset"); //display calendar starts here // $sql; // http://php.net/manual/en/function.date.php $currentdate = date('Y-m-d H:i:s'); // http://php.net/manual/en/function.strtotime.php $enddate = date('Y-m-d H:i:s', strtotime('+7 days')); // $currentdate = date(); // $enddate= DateAdd("d", 7, date()); //enddate returns actual date + 7 days. // This is mostly good but we're going to use prepared statements here instead // $sql="select ID, Deadline, Job_Title, Rate, Job_Location, Job_Function, Qualifications, Start_Date, End_Date from Job_Postings where End_Date >= '{thedate}' and Start_Date <= '{stopdate}' order by Start_Date asc"; $sql = "SELECT ID, Deadline, Job_Title, Rate, Job_Location, Job_Function, Qualifications, Start_Date, End_Date FROM Job_Postings WHERE End_Date >= :current AND Start_Date <= :stopdate ORDER BY Start_Date ASC"; // Prepare the query. We can swap in values for placeholders when executing the query // http://php.net/manual/en/pdo.prepare.php $stmt = $db->prepare($sql); // $sql=str_replace{'(thedate}','$currentdate',$sql); // $sql=str_replace('{stopdate}','$enddate',$sql); // $rsUpcomingEvent.$Open $sql, $JobEvents; // Execute the query // http://php.net/manual/en/pdostatement.execute.php $exec = $stmt->execute(['current' => $currentdate, 'stopdate' => $enddate]); // Fetch the results if query succeeded $results = []; if ($exec) { $results = $stmt->fetchAll(PDO::FETCH_ASSOC); } // We have already fetched the result set. Rather than checking for EOF, we can // check if said array contains values // if ($rsUpcomingEvent.$EOF==true) { if (empty($results)) { echo "There are no job postings at this time."; } else { /** * Looping constructs have a slightly different syntax in PHP * Rather than do { } while we're going to use a foreach here * to iterate over the result set */ // do while $rsUpcomingEvent.$EOF=false; foreach ($results as $rsUpcomingEvent) { // We've already got a variable containing date(), so we'll use that // if ($rsUpcomingEvent.$Fields["Start_Date"] <= date ()) { // Shouldn't occur anyway since we set these constraints in the query if ($rsUpcomingEvent['Start_Date'] <= $currentdate) { // Kept existing logic, but this looks backwards if ($rsUpcomingEvent["Deadline"] != "") { echo "

"."Deadline: "."".$rsUpcomingEvent["Deadline"]."
"; } if ($rsUpcomingEvent["Job_Title"] != "") { echo "

"."Job Title: "."".$rsUpcomingEvent["Job_Title"]; } if ($rsUpcomingEvent["Rate"] != "") { echo "
"."Rate: "."".$rsUpcomingEvent["Rate"]; } if ($rsUpcomingEvent["Job_Location"] != "") { echo "
"."Location: "."".$rsUpcomingEvent["Job_Location"]; } echo "

"."Basic Function:".""; if ($rsUpcomingEvent["Job_Function"] != "") { echo "
".$rsUpcomingEvent["Job_Function"]; } echo "

"."Qualifications:".""; if ($rsUpcomingEvent["Qualifications"] != "") { echo "
".$rsUpcomingEvent["Qualifications"]; } echo "
"; } else { echo "No Listings"; echo ""; } } // $rsUpcomingEvent.$MoveNext; // loop; } // $rsUpcomingEvent.Close; // set $rsUpcomingEvent=nothing; // // set $oConn = nothing; //

// ?php>

Again if anyone can help - I would appreciate it.
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: help with date code

Post by Celauran »

There's definitely something wrong with your PHP install.
gcapp
Forum Newbie
Posts: 14
Joined: Thu Jun 01, 2006 8:59 am

Re: help with date code

Post by gcapp »

What do you mean my PHP install? I assume you mean my code??
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: help with date code

Post by Celauran »

gcapp wrote:What do you mean my PHP install? I assume you mean my code??
If you copy/pasted what I posted above, I don't think it's the code. This is the second time you've posted where the contents of your PHP file are being displayed in the browser at some seemingly random spot. That is not normal behaviour. What is your setup like? Have you checked error logs? Does a simple phpinfo() page work?
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Re: help with date code

Post by Christopher »

gcapp wrote:What do you mean my PHP install? I assume you mean my code??
No, PHP has to be installed correctly and the name of the script needs to end with .php. Since the webserver is just displaying the source it means there is some problem causing the webserver not to pass the page to the PHP interpreter.
(#10850)
gcapp
Forum Newbie
Posts: 14
Joined: Thu Jun 01, 2006 8:59 am

Re: help with date code

Post by gcapp »

Ok I figured out why my web page was showing all that code.

But I am still having an issue with the date portion of the code. Because of the date part not working right, my database info won't display.

So I have this code:

Code: Select all

$currentdate = date('Y-m-d H:i:s');
$enddate = date('Y-m-d H:i:s', strtotime('+7 days'));

$sql = "SELECT ID, Deadline, Job_Title, Rate, Job_Location, Job_Function, Qualifications, Start_Date, End_Date FROM Job_Postings WHERE End_Date >= :current AND Start_Date <= :stopdate ORDER BY Start_Date ASC";


$stmt = $db->prepare($sql);
$exec = $stmt->execute(['current' => $currentdate, 'stopdate' => $enddate]);
$results = [];
if ($exec) {
    $results = $stmt->fetchAll(PDO::FETCH_ASSOC);
}
if (empty($results)) {
    echo "<font color='red'><b>There are no job postings at this time.</b></font>";
} else {

        if ($rsUpcomingEvent['Start_Date'] <= $currentdate) { // Kept existing logic, but this looks backwards
            if ($rsUpcomingEvent["Deadline"] != "") {
                echo "<p class=redtext><b>"."Deadline: "."".$rsUpcomingEvent["Deadline"]."</b></p>";
            }
            if ($rsUpcomingEvent["Job_Title"] != "") {
                echo "<p class=blacksmalltext><b>"."Job Title: "."</b>".$rsUpcomingEvent["Job_Title"];
            }
            if ($rsUpcomingEvent["Rate"] != "") {
                echo "<br><b>"."Rate: "."</b>".$rsUpcomingEvent["Rate"];
            }
            if ($rsUpcomingEvent["Job_Location"] != "") {
                echo "<br><b>"."Location: "."</b>".$rsUpcomingEvent["Job_Location"];
            }
            echo "<p class=blacksmalltext><b><u>"."Basic Function:"."</b></u>";
            if ($rsUpcomingEvent["Job_Function"] != "") {
                echo "<br>".$rsUpcomingEvent["Job_Function"];
            }
            echo "<p class=blacksmalltext><b><u>"."Qualifications:"."</b></u>";
            if ($rsUpcomingEvent["Qualifications"] != "") {
                echo "<br>".$rsUpcomingEvent["Qualifications"];
            }
            echo "<hr noshade size='1'>";
        } else {
            echo "No Listings";

            echo "</font>";
        }
    }
Something is not right with the date part of that code. I am a beginner to PHP, so I am lost.

Basically what I am trying to do is have data from my MySQL database display based on a start date and end date. So the data should show when the start date is a certain date and then disappear after the end date is a certain date.

If anyone can help me, it would be greatly appreciated.
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: help with date code

Post by Celauran »

When you say it's not working, what is happening? Are any errors being displayed? Have you checked the error log? Is the query completing successfully ($exec should be true)? Does it contain any results (try var_dump($results))?
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Re: help with date code

Post by Christopher »

Yes, you should should add logic to check for errors and properly display and/or log error messages. That will show you if you have a SQL error from the prepare, such as not quotes around the dates, etc.
(#10850)
gcapp
Forum Newbie
Posts: 14
Joined: Thu Jun 01, 2006 8:59 am

Re: help with date code

Post by gcapp »

the only part of the code that is showing is:

echo "<p class=blacksmalltext><b><u>"."Basic Function:"."</b></u>";
echo "<p class=blacksmalltext><b><u>"."Qualifications:"."</b></u>";

There is nothing else coming in. No errors, but obviously, there is an issue.

I put in the dump code and this is what it spits out:

array(1) { [0]=> array(9) { ["ID"]=> string(1) "0" ["Deadline"]=> string(26) "Friday, September 23, 2016" ["Job_Title"]=> string(4) "Test" ["Rate"]=> string(6) "$12.00" ["Job_Location"]=> string(4) "test" ["Job_Function"]=> string(5) "wdgwd" ["Qualifications"]=> string(5) "dsgbv" ["Start_Date"]=> string(10) "2016-09-26" ["End_Date"]=> string(10) "2016-10-02" } }

So it is seeing the data in my table but it is not spitting it out.
Again, I am a beginner, so I am guessing - but I think it goes back to the date part of the code.

Any idea??
Post Reply