[SOLVED] query by year only

[eagleweb]

I am trying to only pull rows from the db where the 'tour_date' year = the present year. Here is my query which does not work:

$queryres = mysql_query("SELECT * FROM reservations WHERE ".date('Y', tour_date)." = '".date('Y')."' ORDER by tour_date DESC", $mysql_connect);

Can someone tell me why this simple code is not pulling any results? By the way, tour_date is inserted from a form and was set up like this (if it matters)

	$day = $_POST['d'];
	$month = $_POST['m'];
	$year = $_POST['y'];

	$timeStamp = mktime(0,0,0, $month, $day, $year);

 

Thanks in advance for any help.

[jd2007]

ok, if the tour_date type is mysql's date/datetime type, do this....

 

$year=gmstrftime("%Y");
$year.="-";
$queryres = mysql_query("SELECT * FROM reservations WHERE tour_date='%$year%' ORDER by tour_date DESC", $mysql_connect);

 

its better for you to do your querying this way :

 

$query= "SELECT * FROM reservations WHERE tour_date='%$year%' ORDER by tour_date DESC";
$res=mysql_query($query, $mysql_connect);

 

try and see if it works...then, tell us.

[eagleweb]

It does not work. I get a '0' for mysql_num_rows($queryres)

I see that you are taking the mysql year string and seeing if it exists in the tour_date string.

What does the

 $year.="-";

do?

 

 

[jd2007]

remove $year.="-";....what happens ?

[eagleweb]

Same thing.

I did and echo on the $year and get 2007-, therefore all the$year.="-"; does is add a dash after the year.

tour_date is a timestamp. It looks like this in the db: 1199336400

$year will not find a match.

 

By the way, why do you prefer to place the db connect 'mysql_connect' on a seperate line? I have always done it the way you prefer, but recently changed to the other way. Just curious why it is better.

 

Thanks

[jaimitoc30]

Hi, do you have an example of a registry of the tour_date field so I can see the format it has and then I can provide you with a query that may help you.

 

Regards

[MmmVomit]

$queryres = mysql_query("SELECT * FROM reservations WHERE YEAR(tour_date) = $var_containing_year ORDER by tour_date DESC", $mysql_connect);

 

 

[eagleweb]

<?php
$year = date('Y');
$queryres = mysql_query("SELECT * FROM reservations WHERE YEAR(tour_date) = '".$year."' ORDER by tour_date DESC", $mysql_connect);
$rowres = mysql_fetch_assoc($queryres);
$totalRowsres = mysql_num_rows($queryres);

if($totalRowsres > 0) { 
  echo $totalRowsres; 
}
?>

I get 0 results when I know I have approx 7 with this year in it.

 

I had no clue that trying to match the tour_date to the present year was going to give me a difficult time. Maybe if I would have used Y-m-d for the tour_date instead...

 

Any more ideas?

[BlueSkyIS]

i'd back up and take a look at the data we're comparing:

 

SELECT YEAR(tour_date) FROM reservations

 

do you see proper years here? if so, then echo the $sql so we can see that too. oh, it's wrapped up in mysql_query. pull it out into it's own variable and echo:

 

$sql = "your sql statement";

$queryres = mysql_query($sql) or die(mysql_error());

echo $sql;

[MmmVomit]

Try getting rid of the single quotes around $year

[eagleweb]

I removed the single quotes but still the same. It makes no sense.

Since the tour_date is a timestamp, doesn't it need to be told to strtotime or something before it will recognize that there is a year in there? something like WHERE YEAR(strtotime(0, tour_date("Y")))    or    YEAR(date('Y', tour_date))?

 

 

I did what BlueSkyIS wrote:

SELECT * FROM reservations WHERE YEAR(tour_date) = 2007 ORDER by tour_date DESC

 

 

 

 

[BlueSkyIS]

Try getting rid of the single quotes around $year

 

this will have no effect. there is no reason to NOT use single quotes around every SQL value. imo, it's best to consistently ALWAYS use quotes than to worry about what does or doesn't get quoted.

[BlueSkyIS]

i'd back up and take a look at the data we're comparing:

 

SELECT YEAR(tour_date) FROM reservations

 

do you see proper years here? if so, then echo the $sql so we can see that too. oh, it's wrapped up in mysql_query. pull it out into it's own variable and echo:

 

$sql = "your sql statement";

$queryres = mysql_query($sql) or die(mysql_error());

echo $sql;

[eagleweb]

BlueSkyIS

I did that. This was the echo $sql

SELECT * FROM reservations WHERE YEAR(tour_date) = 2007 ORDER by tour_date DESC

 

 

[BlueSkyIS]

i'd back up and take a look at the data we're comparing:

 

SELECT YEAR(tour_date) FROM reservations

 

do you see proper years here?

 

[eagleweb]

Not in the SELECT YEAR(tour_date)

but the = '".$year."' does show 2007

 

So, how do we pull the year out of the timestamp tour_date so that our query works?

 

I have tried multiple things such as

WHERE ".date('Y', tour_date)." = '".$year."'

but it does not seem to work.

 

 

[MmmVomit]

i'd back up and take a look at the data we're comparing:

 

SELECT YEAR(tour_date) FROM reservations

 

do you see proper years here?

 

^^^ run this query and post the output ^^^

[eagleweb]

<?php
$query= "SELECT YEAR(tour_date) FROM reservations";
$res=mysql_query($query, $mysql_connect);

echo "RESULTS:<br>";
do {
echo $res;
echo "<br>";
} while ($res = mysql_fetch_assoc($query));
?>

 

RESULTS:

Resource id #7

 

[BlueSkyIS]

we'd like to see the data output, not the resource identifier. you were close, but what we want is in here:

 

while ($res = mysql_fetch_assoc($query)) {
     print_r($res); // We want to see the data returned....
}

 

eh, rough but it'll do..

[Barand]

If you are storing as 1199336400 then it needs converting to datetime format first

 

... WHERE YEAR(FROM_UNIXTIME(mydatecolumn)) = '2007'

[eagleweb]

Barand,

You hit it on the head!!!

Were you just sitting back watching and laughing at me; waiting for the right time to pop in? LOL

 

Thanks alot.

 

Thanks to BlueSkyIS and MmmVomit also for your time.

 

AWESOME!

[Barand]

I saw this topc with 18 replies for what looked like a simple problem. Just thought I'd jump in before the thread filled the server.