[SOLVED] strings

[chocopi]

ok so in my db i have dates stored like dd/mm/yyyy. how can i split the string up to get date = dd month = mm and year = yy.

 

This is so i can then get an array on the month so it can show dd [month name] yyyy

 

Thanks,

 

~ Chocopi

[Barand]

If you stored your dates correctly as yyyy-mm-dd in a DATE field you could either

 

use Mysql to get month name

SELECT MONTHNAME(datecol) as mname ...

 

or

 

use PHP to get the month name

$mname = date('F', strtotime($datecol))

[chocopi]

Thanks, i shall change it to yyyy-mm-dd

[Barand]

In addition, you will now be able to sort by date and compare dates, to see which is later.

 

You can't do either with dd/mm/yyyy.

[chocopi]

Cheers thanks,

 

but, what will the whole Mysql line look like ?

 

~ Chocopi

[Barand]

you can either

<?php
$sql = "SELECT DATE_FORMAT(datecol, '%d %M %Y') as date FROM mytable";
$res = mysql_query($sql) or die (mysql_error()."<p>$sql</p>");
while ($row = mysql_fetch_assoc($res))
{
    echo $row['date'], '<br>';
} 
?>

 

or, formatting the date with php

<?php 
$sql = "SELECT datecol FROM mytable";
$res = mysql_query($sql) or die (mysql_error()."<p>$sql</p>");
while ($row = mysql_fetch_assoc($res))
{
    echo date('d F Y', strtotime($row['datecol'])), '<br>';
} 

?>

[chocopi]

Cheers Barand, im using the top one and tis super.

 

Thanks,

 

~ Chocopi

[chocopi]

Just one last point, how do you minus dates

 

for example:

reg_date = 2007-05-17
last_login = 2007-05-20

last login - reg_date = 3

You have been registered for 3 days

 

something like that anyways.

 

Thanks,

 

~ Chocopi

[Barand]

SELECT DATEDIFF(last_login, reg_date) as daysreg

[chocopi]

ive added it like this but it doesnt work:

<?php
	$age = "SELECT DATEDIFF(last_login, reg_date) as daysreg";

			while ($row = mysql_fetch_assoc($age))
			{
				echo $row['daysreg'];
			}
?>

[chigley]

<?php
	$age = mysql_query("SELECT DATEDIFF(last_login, reg_date) as daysreg");

			while ($row = mysql_fetch_assoc($age))
			{
				echo $row['daysreg'];
			}
?>

 

[chocopi]

cheers, but its still not working

[chigley]

<?php
	$age = mysql_query("SELECT DATEDIFF(last_login, reg_date) as daysreg FROM tablename");

			while ($row = mysql_fetch_assoc($age))
			{
				echo $row['daysreg'];
			}
?>

[chocopi]

cheers, but it is still coming up blank

[chigley]

<?php
	$age = mysql_query("SELECT DATEDIFF(last_login, reg_date) as daysreg FROM tablename") or die(mysql_error());

			while ($row = mysql_fetch_assoc($age))
			{
				print_r($row);
			}
?>

 

What does that output?

[chocopi]

it outputs:

 

You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near '(last_login, reg_date) as daysreg FROM Mark' at line 1

[Barand]

DATEDIFF requires MySQL 4.1.1 or later.

 

If you are using an earlier version try

 

SELECT (TO_DAYS(last_login) - TO_DAYS(reg_date)) as daysreg FROM Mark

[chocopi]

Cheers Barand,

 

thats solved it

 

THANKS

 

~ Chocopi