SELECT substr("time",4) AS year

[Donovan]

Is this possible?  I need to be able to filter the year in a select clause.

 

I have a value in a field called time...

 

2008-01-17 13:02:07

 

I need to capture the year, which is the first 4 characters.  I will do this by a list box and filter what questions are displayed.

 

Here is a snippet:

Opentable();
echo"<tr><center>Use drop down box to view prior years questions.</center></tr>";
echo"<tr><center>Select Year <select name='year' size=\"1\" onchange=\"reload(this.form)\">";
echo"<option value=\"2007\" selected>2007</option>"
."<option value=\"2008\">2008</option>"
."<option value=\"2009\">2009</option>"
."<option value=\"2010\">2010</option>"	
."</select></center></tr>"
."</form>";	
Closetable();

$result = $db->sql_query("SELECT substr("time",4) AS year, * FROM comments cm 
JOIN course cs ON (cm.course_id = cs.course_id) 
JOIN reply r ON (cm.cid = r.cid) 
WHERE cm.course_id = '$course_id' 
ORDER BY time DESC");	
while ($row = $db->sql_fetchrow($result))	{	

[toplay]

If your column is of DATETIME, then you can use YEAR().

 

If it's a made up with a char/varchar (not good), then use LEFT(), or SUBSTRING().

 

Click on those functions to see the corresponding manual pages.

 

 

[Donovan]

It's DATETIME. 

 

I'm thinking I need a separate SELECT statement to get the value of the listbox.

 

Opentable();
echo"<tr><center>Use drop down box to view prior years questions.</center></tr>";
echo"<tr><center>Select Year <select name=\"year\" size=\"1\" onchange=\"document.forms[0].submit();\">";
echo"<option value=\"2007\">2007</option>"
."<option value=\"2008\"selected>2008</option>"
."<option value=\"2009\">2009</option>"
."<option value=\"2010\">2010</option>"	
."</select></center></tr>"
."</form>";	
Closetable();

$result = $db->sql_query("SELECT YEAR(cm.time) AS qyear, * FROM comments cm 
JOIN course cs ON (cm.course_id = cs.course_id) 
JOIN reply r ON (cm.cid = r.cid) 
WHERE cm.course_id = '$course_id' 
AND qyear = '$year'
ORDER BY cm.time DESC");