PHP math with MySQL date format
PHP math with MySQL date format
am 14.10.2007 12:36:13 von NerdRevenge
(Don't froth at the mouth Jerry, this IS a PHP question - sort of
)
I have a SQL column with date format. In my PHP script I need to
do simple math on it, eg how many days past the date in the SQL
record are we now.
Do I need to convert it to a timestamp in order to do the math,
or is there an easier way ?
bill
Re: PHP math with MySQL date format
am 14.10.2007 12:44:48 von luiheidsgoeroe
On Sun, 14 Oct 2007 12:36:13 +0200, bill wrote:
> (Don't froth at the mouth Jerry, this IS a PHP question - sort of )
>
> I have a SQL column with date format. In my PHP script I need to do
> simple math on it, eg how many days past the date in the SQL record are
> we now.
Well, it could be solved quite easily in MySQL....
SELECT
something,
datecolumn,
DATEDIFF(datecolumn,CURDATE())
FROM table
> Do I need to convert it to a timestamp in order to do the math, or is
> there an easier way ?
Otherwise it's just a string, because PHP has no 'date' type. So either
split it into components or indeed convert it to a timestamp (which would
be easiest).
--
Rik Wasmus
Re: PHP math with MySQL date format
am 14.10.2007 12:59:21 von NerdRevenge
Rik Wasmus wrote:
> On Sun, 14 Oct 2007 12:36:13 +0200, bill wrote:
>
>> (Don't froth at the mouth Jerry, this IS a PHP question - sort of )
>>
>> I have a SQL column with date format. In my PHP script I need to do
>> simple math on it, eg how many days past the date in the SQL record
>> are we now.
>
> Well, it could be solved quite easily in MySQL....
>
> SELECT
> something,
> datecolumn,
> DATEDIFF(datecolumn,CURDATE())
> FROM table
>
>
>> Do I need to convert it to a timestamp in order to do the math, or is
>> there an easier way ?
>
> Otherwise it's just a string, because PHP has no 'date' type. So either
> split it into components or indeed convert it to a timestamp (which
> would be easiest).
>
WOW !
Thanks Rik
bill
Re: PHP math with MySQL date format
am 14.10.2007 13:07:24 von NerdRevenge
Rik Wasmus wrote:
> On Sun, 14 Oct 2007 12:36:13 +0200, bill wrote:
>
>> (Don't froth at the mouth Jerry, this IS a PHP question - sort of )
>>
>> I have a SQL column with date format. In my PHP script I need to do
>> simple math on it, eg how many days past the date in the SQL record
>> are we now.
>
> Well, it could be solved quite easily in MySQL....
>
> SELECT
> something,
> datecolumn,
> DATEDIFF(datecolumn,CURDATE())
> FROM table
>
>
>> Do I need to convert it to a timestamp in order to do the math, or is
>> there an easier way ?
>
> Otherwise it's just a string, because PHP has no 'date' type. So either
> split it into components or indeed convert it to a timestamp (which
> would be easiest).
>
now back to PHP...
If I do the select as you noted above, after I have gotten the
row, what is the index..
e.g: $row['?????']
bill
(The SQL docs are of no use on this)
Re: PHP math with MySQL date format
am 14.10.2007 14:35:18 von colin.mckinnon
On 14 Oct, 12:07, bill wrote:
> Rik Wasmus wrote:
> > On Sun, 14 Oct 2007 12:36:13 +0200, bill wrote:
>
> >> (Don't froth at the mouth Jerry, this IS a PHP question - sort of )
>
> >> I have a SQL column with date format. In my PHP script I need to do
> >> simple math on it, eg how many days past the date in the SQL record
> >> are we now.
>
> > Well, it could be solved quite easily in MySQL....
>
> > SELECT
> > something,
> > datecolumn,
> > DATEDIFF(datecolumn,CURDATE())
> > FROM table
>
> >> Do I need to convert it to a timestamp in order to do the math, or is
> >> there an easier way ?
>
> > Otherwise it's just a string, because PHP has no 'date' type. So either
> > split it into components or indeed convert it to a timestamp (which
> > would be easiest).
>
> now back to PHP...
> If I do the select as you noted above, after I have gotten the
> row, what is the index..
> e.g: $row['?????']
>
> bill
> (The SQL docs are of no use on this)
They're numbered from the left, but its much, MUCH more sensible to
always use associative arrays (use var_dump to see what it currently
shows).
For a proper solution use aliases in the query:
SELECT
something,
datecolumn,
DATEDIFF(datecolumn,CURDATE()) AS days_diff
FROM table
C.
Re: PHP math with MySQL date format
am 14.10.2007 19:16:18 von NerdRevenge
C. (http://symcbean.blogspot.com/) wrote:
> On 14 Oct, 12:07, bill wrote:
>> Rik Wasmus wrote:
>>> On Sun, 14 Oct 2007 12:36:13 +0200, bill wrote:
>>>> (Don't froth at the mouth Jerry, this IS a PHP question - sort of )
>>>> I have a SQL column with date format. In my PHP script I need to do
>>>> simple math on it, eg how many days past the date in the SQL record
>>>> are we now.
>>> Well, it could be solved quite easily in MySQL....
>>> SELECT
>>> something,
>>> datecolumn,
>>> DATEDIFF(datecolumn,CURDATE())
>>> FROM table
>>>> Do I need to convert it to a timestamp in order to do the math, or is
>>>> there an easier way ?
>>> Otherwise it's just a string, because PHP has no 'date' type. So either
>>> split it into components or indeed convert it to a timestamp (which
>>> would be easiest).
>> now back to PHP...
>> If I do the select as you noted above, after I have gotten the
>> row, what is the index..
>> e.g: $row['?????']
>>
>> bill
>> (The SQL docs are of no use on this)
>
> They're numbered from the left, but its much, MUCH more sensible to
> always use associative arrays (use var_dump to see what it currently
> shows).
>
> For a proper solution use aliases in the query:
>
> SELECT
> something,
> datecolumn,
> DATEDIFF(datecolumn,CURDATE()) AS days_diff
> FROM table
>
>
> C.
>
better,
Thanks
bill
Re: PHP math with MySQL date format
am 23.12.2007 15:24:34 von unknown
Post removed (X-No-Archive: yes)
Re: PHP math with MySQL date format
am 23.12.2007 17:18:40 von pritaeas
"Puzzled" wrote in message
news:6drsm3l1m7sn9gd24p76g9p2r5lnblbshl@4ax.com...
> On Sun, 14 Oct 2007 07:07:24 -0400,
> bill wrote:
>
>>> SELECT
>>> something,
>>> datecolumn,
>>> DATEDIFF(datecolumn,CURDATE())
>>> FROM table
>>>
>>>
>>>> Do I need to convert it to a timestamp in order to do the math, or is
>>>> there an easier way ?
>>>
>>> Otherwise it's just a string, because PHP has no 'date' type. So either
>>> split it into components or indeed convert it to a timestamp (which
>>> would be easiest).
>>>
>>
>>now back to PHP...
>>If I do the select as you noted above, after I have gotten the
>>row, what is the index..
>>e.g: $row['?????']
>
> Let's say the name of your date field is 'LastUpdate', and your
> table is 'purchases' (for this example, you're trying to find out
> how long ago someone made a purchase). So to do it, you go
>
> $dataset = mysql_query( 'SELECT Name, DATEDIFF( LastUpdate,
> CURDATE()) FROM purchases' ) ;
>
> This presumably yields as many values as you have customers.
> Since you specified Name, then DATEDIFF, those values are in the
> [0] and [1] elements respectively, so:
>
> while ( $row = mysql_fetch_row($dataset) )
> {
> echo $row[1].' last bought something '.
> $row[0].' days ago
' ; // print out all the deltas
> }
>
> HTH
$dataset = mysql_query( 'SELECT Name, DATEDIFF( LastUpdate, CURDATE()) AS
column_datediff FROM purchases' ) ;
Now you can access
$row["column_datediff"];