[Resolved] Need to display data by year

dotty

Hi,

I need to display records from a db for this year only.
This is what I've been trying:

$thisyear = date("Y");
SELECT * FROM table WHERE date("Y",strtotime(update)) = $thisyear

does anyone know if this is the correct way?

Drakla

if update is a date field, and you want stuff for this year you could do

SELECT * FROM table WHERE YEAR(update) = YEAR(NOW());

dotty

Thanks Drakla - this works well.

🙂

Drakla

Ahhhh, being doing some heavy query benchmarking today to see how int fields with unix timestamps compare to using the datetime type, and if you've got an index on your update field then my previous solution would throw it right out of the window. To keep the index intact you're better off constructing a query that is equivalent of

SELECT * FROM table WHERE update >= '2005-01-01' AND update < '2006-01-01';

Or use BETWEEN if you can be bothered, e.g.

SELECT * FROM table WHERE update BETWEEN '2005-01-01' AND '2005-12-31 23:59:59';

On a half million row table with an indexed datetime column the difference is from 0.39 from the last two, to 15 seconds and all rows for my initial suggestion.

Peredy

Hey thats great. I used that too and it got to display just the year 2004. Now here's what I have been looking for. In the script, what would I add so that I have 2 diffferent tables. IE:

2005
(here is a list of all the info for 2005)

2004
(here is a list of all the info for 2004)

PROBLEM: The page of upcoming shows and past is all in one php file. It has a switch. I dont know much php, but I have a feeling that this could be right in front of my face and dont know it.

To see the page, go to http://www.blyster.com/bschedule/index.php
The past concerts are what I want to seperate in the years.

Here is the page all together....

<body link="#CCCC00" vlink="#CCCC00" alink="#FFFFFF" text="#CCCCCC">
<style type="text/css"> body {background-color: transparent} </style>
<?php
/*******************************************************************************************/
/ Band Schedule Version: 1.2 /
/ Band Schedule Auther: Peredy; /
/ Band Schedule 1.2 License: Band Schedule 1.2 is licensed under the GNU license agreement /
/ © Copyright: Peredy, All Rights Reserved /
/ /
/ /
/ /
/ /
/ /
/ /
/*******************************************************************************************/

include("header.inc");

function DisplaySchedule() {
$getswitch = $_GET['switch'];
$db = mysql_connect("localhost","blysterc_b21","test");
mysql_select_db('blysterc_bschedule');

switch($getswitch) {
case 1: {

$query = "select * FROM schedule where bsdate >= '2004-01-01' AND bsdate < '2004-12-31' order by bsdate desc";

break;
}
default:

$query = "select * FROM schedule where bsdate >= now() order by bsdate asc";

break;

}
$result = mysql_query($query) or die("No query".mysql_error());
echo "<table border=0 cellpadding=0 cellspacing=0 class=\"DisPalyTab\">";
echo "<tr><td colspan=4 class=\"bsNavHeader\" ><a href=\"index.php?switch=1\">Past Concerts</a> | <a href=\"index.php\">UpComming Concerts</a> | <a href=\"http://www.blyster.com/flyers.htm\" target=\"_blank\">Flyers</a> | <a href=\"http://www.blyster.com/dir.htm\">Directions</a></td></tr>";

$i = 0;
while($rows = mysql_fetch_array($result)) {
$i++;
$timestamp = strtotime($rows['bsdate']);
$new_time_format = date ("F jS ", $timestamp);
$newtime = str_replace(" ", '<br>',$new_time_format);

echo "<tr>";
echo "<td align=\"center\" class=\"bsDate\">".$newtime."</td>";
echo "<td align=\"center\" class=\"bsVenue_City\">".$rows['bsvenue']."<br>".$rows['bslocation']."</td>";
echo "<td align=\"center\" class=\"bsBand_Listings\">".$rows['bsbandlistings']."</td>";
echo "<td align=\"center\" class=\"bsDetails\">".$rows['bsdetails']."</td>";
echo "</tr>";
echo"<div id=list".$i." class=\"bsBListMenu\"> ".$rows['bsbandlistings']."</div>";

}

echo "</table>";
mysql_close($db);
include("footer.inc");

}
DisplaySchedule();
?>

Much appreciated for any help.

Peredy