php mysql distinct year custom timestamp
I have a set of records in mysql that have a timestamp that looks like this
2010/04/14
How can I select the distinct year of all these records using a mysql query?
2010/04/14
How can I select the distinct year of all these records using a mysql query?
ASKER
Ideally, how can this be done in a while loop? I've got records for many years
$year = array(
'2000',
'2001',
'2002',
'2003',
'2004',
'2005',
);
foreach($year as $value)
{
$sql = "SELECT * FROM tablename WHERE fieldname LIKE '$value%'";
$result = mysql_query($sql);
while($data = mysql_fetch_array($result)
echo "HERE DO YOUR HTML OF HOW YOU WANT TO DISPLAY THE INFO";
}
}
to display the information use the format "".$data['dbfieldname'].""
'2000',
'2001',
'2002',
'2003',
'2004',
'2005',
);
foreach($year as $value)
{
$sql = "SELECT * FROM tablename WHERE fieldname LIKE '$value%'";
$result = mysql_query($sql);
while($data = mysql_fetch_array($result)
echo "HERE DO YOUR HTML OF HOW YOU WANT TO DISPLAY THE INFO";
}
}
to display the information use the format "".$data['dbfieldname'].""
I think what you are asking for is:
SELECT DISTINCT Year(`yourDateField`) FROM yourTable
SELECT DISTINCT Year(`yourDateField`) FROM yourTable
ASKER
hielo: I get an empty array from that
SELECT DISTINCT (YEAR('date')) FROM payments
while($timeperiod = mysql_fetch_array($get_earnings)) {
print_r($timeperiod);
shows:
Array ( [0] => [(YEAR('date'))] => )
ASKER
gamebits: that was a structure i was using originally. the reason for the question was to automate the process. i wouldn't want to manually update that array.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
>>hielo: I get an empty array from that
>>SELECT DISTINCT (YEAR('date')) FROM payments
That's NOT what I gave you. I surrounded the field with backticks (usually, it is the character on the same key as the ~ character). Instead of backticks, YOU used apostrophes. In MySQL VALUES are sorrounded with apostrophes, but
database, tables, and/or field names are surrounded with backticks.
SELECT DISTINCT YEAR(`date`) FROM payments
>>SELECT DISTINCT (YEAR('date')) FROM payments
That's NOT what I gave you. I surrounded the field with backticks (usually, it is the character on the same key as the ~ character). Instead of backticks, YOU used apostrophes. In MySQL VALUES are sorrounded with apostrophes, but
database, tables, and/or field names are surrounded with backticks.
SELECT DISTINCT YEAR(`date`) FROM payments
SELECT * FROM tablename WHERE fieldname LIKE "$year%";