SELECT SUBSTRING(time,4) AS year

Client Joined: Oct 07, 2003 Posts: 735 Location: Ohio

I need to capture the year from a field such as:

Quote:

2008-01-17 13:02:07

Then use this value in a query to view only those records for that year.

I have a list box such as

Code:




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();

So I can choose last year and use that in a query to view just those records.

Code:




$result = $db->sql_query("SELECT SUBSTRING(cm.time,4) 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");

Posted: Mon Jan 28, 2008 10:21 am

I would think the YEAR() function would be a better choice than SUBSTRING().

Posted: Mon Jan 28, 2008 10:55 am

Gremmie wrote:

I would think the YEAR() function would be a better choice than SUBSTRING().

Your right. And since it is DATETIME I can use that instead.

I still have to figure out the proper structure of the query to read the value of the list box.

Code:

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>";   





$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");