pulling a date field from sql table table makes query return no results

General PHP Help
#1

New to php. I am able to connect to my database and I can pull results from a table but when a date field is added in the mix then nothing is returned. It has something to do with trying to display the date but I am baffled as to the cause.

php

$serverName = “STISD-WEB01\STBOBCATS”;

$connectionInfo = array( “Database”=>“STBOBCATS”, “UID”=>“××××”, “PWD”=>“××××××”);

$conn = sqlsrv_connect( $serverName, $connectionInfo);if ($conn === false) die("

".print_r(sqlsrv_errors(), true));echo “Successfully connected!”;

$sql = "SELECT TOP (5) EDate, Time, Event


FROM         dbo.Events


WHERE     (EDate >= GETDATE()) AND (IncludeOnHome = 1)


ORDER BY EDate";


$query = sqlsrv_query($conn, $sql);


if ($query === false)


{  exit("
".print_r(sqlsrv_errors(), true));}

while ($row = sqlsrv_fetch_array($query))


{
    $date = strtotime($row[Edate]);

                            echo date("F j Y", $date), "<p> > $row[Event] </p>" ;
}



sqlsrv_free_stmt($query);


Everthing connects.  If I run echo date(“F j Y”, $date), "
 > $row[Event] 
"   I get an incorrect date 12/31/1969 and the events

If I run      echo "
 > $row[Event] 
"  I get the events

If I run      echo "
 $row [EDate] > $row[Event] 
"  I get no results

Played around with variations but can’t get the dates in the table to display.
#2

You should be having the database do all your date formatting work. Always use the DB when it can do something.

[php]SELECT
DATE_FORMAT(your_date_column, ‘%m-%d-%Y’) AS mydate
FROM
mytable[/php]

#3

What format is it currently in and what is the columns type? The different RDBMS’s all have their idiosyncrasies, but this information is needed to formulate the proper query.

#4

I’m thinking you probably want something like this:

[php]$sql = <<<SQL
SELECT
TOP (5) EDate,
Time,
Event
FROM
dbo.Events
WHERE
EDate >= CONVERT(Date, GETDATE())
AND
IncludeOnHome = 1
ORDER BY EDate;
SQL;[/php]

#5

Chris

astonecipher,

I set EDate to the date data type in dbo.Events and it is storing the dates as yyyy-m-dd (2015-9-1). I am running SQL Express as well if that helps. So is there something specific to PHP that will not let you run an echo command on a date field from SQL to just display what is in the database? I can tell that my query is using [EDate] to filter the results properly because it returns the correct [Events] so I was surprised that when I added in an echo for EDate that it returned nothing. Just trying to understand the logic as well. I will try some of your other suggestions as well. Thanks!

#6

So, the query works? It is just not displaying the date and that is the issue?

#7

For one thing, do a print_r( $row ) to see what the table is actually returning.

Your issue with the data format is that date() expects an integer (unix timestamp) of which you are passing a string to.

This may be what you are looking for in that case:

[php]$date = DateTime::createFromFormat(“F j Y”, $row[‘EDate’]);
echo $date->format(‘Y-m-d’);[/php]

#8

Yes if I only echo [Events] on the page I get the correct Events listed from the table and I verified my results by running the query in SQL. Once I try to echo [EDate] on the screen with echo [Events] I get neither the date or the event listed it is just blank, no errors or anything. I thought it might be some kind of format issue with PHP not knowing how to handle a date data type from SQL that I wasn’t aware of which is why I was playing around with the strtotime function. If I could just get the results to display that would be a major victory and I can work on formatting the date properly after that.

#9

That would be where this comes in:

[php]while ($row = sqlsrv_fetch_array($query))
{
echo “

”;

print_r( $row );

echo “
”;
}[/php]

That will show what is actually being returned from the query. From there you will also know what the date field is being associated with.

#10

Thanks I will give that a shot and see what I discover.

#11

Jumping into the thread…

I had a lot of trouble with dates a long time ago. I just couldn’t get it to save the way I wanted to for a
site in Australia I was helping with. I finally found out that the database field formatted with datetime was
saving it in this format: date(“Y-m-d H:i:s”) … So, once I saved it in this format and for displays used the
correct version for their area : date(“d-m-Y H:i:s”) all was fine. Seems the MySQL database always saved
it in the same manor and I kept getting a 1969 date…

Just thought I should mention that. It might help in the long run…

#12

Ok so MSSQL is returning and array for the Date field so my results look like this

Volleyball Tourney @ Banquete TBA

Array
(
[0] => DateTime Object
(
[date] => 2015-09-05 00:00:00
[timezone_type] => 3
[timezone] => America/Tegucigalpa
)

[Edate] => DateTime Object
    (
        [date] => 2015-09-05 00:00:00
        [timezone_type] => 3
        [timezone] => America/Tegucigalpa
    )

[1] => Volleyball Tourney @ Banquete TBA
[Event] => Volleyball Tourney @ Banquete TBA

)

That makes more sense on why when I echo row$[Edate] that it craps out on me. It ended up that I need to format Edate and capture it in a variable so I could display it correctly. So I made this change.

while ($row = sqlsrv_fetch_array($query) and $date = date_format($row[Edate], ‘M-d-Y’))
{
echo "

$date > $row[Event]

";
}
sqlsrv_free_stmt($query);

and now I get the desired results.

Sep-04-2015 > Varsity Football VS Woodsboro 7:30 Woodsboro

Sep-05-2015 > Volleyball Tourney @ Banquete TBA

Sep-07-2015 > Student/Staff Holiday

Thanks for all of your help!

#13

Great! Good for you!

Remember the print_r($somearray) for future debugging…

One further note: Also remember that the server’s time is where the server is based.
Therefore, you need to set the time so it adjusts for the timezone you want to display in…

Sponsor our Newsletter | Privacy Policy | Terms of Service