[esial]

I' m writing code to subtract two dates. It is for a contract type thingy, where user gets to see the number of days left for his contract to complete. Something like start_date_time="today" and end_date_time= y where the value of y is retrieved from the database (DATETIME type). It is in the mysql datetime format (yyyy-mm-dd HH:mm:ss).

<?php
include_once '../include/connections.php';
$id =$_REQUEST['uid'];
$result= mysql_query("SELECT * FROM data WHERE uid  = '$id'");
$test = mysql_fetch_array($result);
echo $test[14];
echo "<br /><br />";
$today=time();
$enddate=strtotime('$test[14]');
$timediff = $enddate - $today;
$days=intval($timediff/86400);
$remaining=$timediff%86400;
$hours=intval($remaining/3600);
$remaining=$remaining%3600;
$mins=intval($remaining/60);
$secs=$remaining%60;
echo "<br>".$days.' days '.$hours.' hours '.$mins.' minutes and '.$secs.' seconds.';
?>

When i echo $test[14]; i get the date and time as stored in the database which is (2012-09-26 00:00:00) When i echo $today then i get it in this format 1348381896. Now how do i convert this format to the one retrieved from the db so that i can subtract the 2 dates and get the number of days and time left.

[Atli]

Hey.

You don't have to manually calculate the remaining days in PHP. MySQL can do that for you. The DATEDIFF() function is, in fact, specially made to do this. You could just issue a SQL query like this, and MySQL would give you the value you are trying to calculate.

SELECT DATEDIFF(end_date_time, NOW()) AS days_remaining
FROM data WHERE uid = '$id'

(This assumes that the column in the database that stores the end date is called 'end_date_time'. Update this accordingly if that is not the case.)


Also, here are a couple of things to consider:

• About your SQL query. You are doing SELECT * ..., which tells MySQL to return all the columns for that row (which, judging by your code, are quite a few), when in fact you only need one of them. You should always try to be exact when requesting data from MySQL, and tell it exactly which columns you need. Anything else is a waste of resources.
  
  
• Please note the difference between single-quotes and double-quotes. Double-quoted strings will parse variables within them and replace them with their respective values. Single-quoted strings will not do that. So if you do: '$test[14]', it will print just that. It won't print the 14'th element of the $test variable; only that string, character for character. If you want it to print the value of the element, either use double quotes: "$test[14]", or just don't quote it at all.

[esial]

Thanks for that. rep added.
I tried this in mysql, it works well. i dont know for some reason it does'nt work in php. This is the code that i wrote in php

<?php
include_once '../include/connections.php';

$id =$_REQUEST['uid'];
$result= mysql_query("SELECT DATEDIFF(datetime, now()) AS days_remaining FROM `data` WHERE uid = '$id'");     (datetime is the name of the column to store date and time)
echo $result;

i get output as " resource id #9 "

Please help,

[CTphpnwb]

You're using deprecated, insecure MySQL functions. Don't do that.
Read and study this.

[Atli]

The docs for the mysql_query() function would explain why it does that.

php.net said:

For SELECT, SHOW, DESCRIBE, EXPLAIN and other statements returning resultset, mysql_query() returns a resource on success, or FALSE on error.

For other type of SQL statements, INSERT, UPDATE, DELETE, DROP, etc, mysql_query() returns TRUE on success or FALSE on error.

The returned result resource should be passed to mysql_fetch_array(), and other functions for dealing with result tables, to access the returned data.

And, of course, I agree with CTphpnwb about the MySQL functions. The PDO extension he linked to can look intimidating if your not used to OOP, but if that is the case you can switch to the Improved MySQL extension (mysqli) instead, which has a procedural interface that looks much the same as the old MySQL functions. It'll just be easier for you to use more modern (and secure!) methods when using mysqli.

[esial]

<?php

include_once '../include/connections.php';

$id =$_REQUEST['uid'];
$result=mysql_query("SELECT DATEDIFF(datetime, now()) FROM data WHERE uid = '$id'"); 
$test = mysql_fetch_array($result);
echo mysql_result($test);
?>

is there anything wrong with this code, i get nothing as output.

Another problem?
if i enter "SELECT *" then i get the output, as shown below:

<?php
include_once '../include/connections.php';

$id =$_REQUEST['uid'];
$result=mysql_query("SELECT * FROM data where uid='$id'");
$row = mysql_fetch_array($result);
echo $row[14];
?>

output is my date and time 2012-09-24 00:00:00.
If i remove the star (*) and choose the column like this below i get no output

<?php
include_once '../include/connections.php';

$id =$_REQUEST['uid'];
$result=mysql_query("SELECT datetime FROM data where uid='$id'"); //datetime is column name with           DATETIME type
$row = mysql_fetch_array($result);
echo $row[14];
?>

[Atli]

esial said:

is there anything wrong with this code, i get nothing as output.

What were you expecting the mysql_result() call to do? You are using it incorrectly there.

Your call to mysql_fetch_array() will return the first row of the result set as an array, with the DATEDIFF value as the first element. All you have to do to print the value is echo that first element.

echo $test[0];

esial said:

If i remove the star (*) and choose the column like this below i get no output

When you remove the star, you are no longer getting the entire row, only the single column you requested. Which means that each row of the result set will only have a single field. Your code is trying to print the 14'th element of an array containing only one element.


On a side-note, the fact that you get no output there means that you have error_reporting turned off. If you turn that on, PHP will print error messages and notices that can help you debug your code. You can do that either by altering the display_errors and error_reporting values in the PHP config, or by adding this to the very top of your pages. (To be removed when the code goes "live"!)

ini_set("display_errors", true);
error_reporting(E_ALL);

[esial]

thanks

I did this and i get an error saying "undefined offset 15 on line 14". i have created a column called datediff and it is on the 15th row (starting from 0)

<?php
include_once '../include/connections.php';
ini_set("display_errors", true);

error_reporting(E_ALL);

$id =$_REQUEST['uid'];

$result=mysql_query("SELECT DATEDIFF(datetime, now()) FROM data WHERE uid = '$id'"); 
$test = mysql_fetch_array($result);
echo $test[15]; //line 14
?>

[Atli]

The index you should put into $test[x] is not the index of the column in the actual table, but rather the index of the field in the result set.

If you were to execute your query in a MySQL command line window, or Workbench, or whatever, you would get something like:

> SELECT DATEDIFF(datetime, now()) FROM data WHERE uid = '1';
+---------------------------+
| DATEDIFF(datetime, now()) |
+---------------------------+
|                         5 |
+---------------------------+        

It doesn't matter what the data table looks like, only what MySQL is returning to PHP. The above result set is what PHP is getting, and mysql_fetch_array() would convert it into an array that looks like this (excluding the associative indexes):

array (
    [0] => 5
)

So in order to get your result, you would have to do: $test[0].

[esial]

omg!

your awesome. thank you so much.

[Dormilich]

question, isn’t datetime a reserved MySQL keyword?

[Atli]

It's a keyword, but not a reserved one. MySQL allows certain keywords to be used as identifiers because of how much they are actually used as identifiers. Other good examples of this are TEXT and ACTION.

It's mentioned at the bottom of the Reserved Keywords list in the manual.

This post has been edited by Atli: 24 September 2012 - 12:07 AM