SELECT SUBSTRING(time,4) AS year

Donovan · Client Joined: Oct 07, 2003 Posts: 691 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");

Gremmie · Posted: Mon Jan 28, 2008 10:21 am

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

Donovan · Client Joined: Oct 07, 2003 Posts: 691 Location: Ohio

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