Help getting This Week from query.

jefferis · January 9th, 2006, 12:14 PM

Dear Folks and Friends,
I screwed up somewhere and had this code working before, but now, everything has fallen apart. I have a simple MySql database which has the calendar week's first date starting on monday, with columns for restaurants, and the format is as follows
Calweek_date Restaurant1 Restaurant2 etc...
2006-01-09 menu1 menu2 etc...

I'm trying to pull this week's menu from the current week's date which begins on monday.

This may be a problem of simple syntax and it was working before Christmas break, but not now.

Quote:

quote:<?php
mysql_select_db($database_utMenuCal, $utMenuCal);
$query_thisweek = "SELECT * FROM utCalMenu WHERE DATE_FORMAT(utCalMenu.Calweek_date,'%v') = '".date("W")."' ";
$thisweek = mysql_query($query_thisweek, $utMenuCal) or die(mysql_error());
$row_thisweek = mysql_fetch_assoc($thisweek);
$totalRows_thisweek = mysql_num_rows($thisweek);
?>

<?php echo $row_thisweek; ?> returns no value for this week. And the query returns no data at all.
If I use this query: SELECT * FROM utCalMenu WHERE DATE_FORMAT(utCalMenu.Calweek_date,'%v') all data in all rows are returned, not just the current week. So it is working to a point, but something is breaking down in the conversion to the current week...

Any help DEEPLY appreciated.

~~~~~~~~~~~~~
http://www.petersonsales.net

Mantis · January 9th, 2006, 10:48 PM

you've done 1/2 the job already:
the issue is in:
'".date("W")."' ";
I think you should try a test to see if you're getting a simple result out;
Date("W") return the week number so I would suggest to try:
$this_week=3;
$query_thisweek = "SELECT * FROM utCalMenu WHERE DATE_FORMAT(utCalMenu.Calweek_date,'%v') LIKE '$this_week'";

I don't use = i use LIKE might make a diffrence there (???)
if that works then just assign the variable $this_week=date("W"); and sorted.

jefferis · January 10th, 2006, 12:32 AM

I am getting the proper week from the query as week 2
SELECT * FROM utCalMenu WHERE DATE_FORMAT(utCalMenu.Calweek_date,'%v') = '2'
http://www.utoledo.avifoodweb.com/phptest2.php

However, is 2006-01-09 considered the 2nd week or third week when using date ("W") since the first was a Sunday?
I don't have data for the previous week...

~~~~~~~~~~~~~
http://www.petersonsales.net

jefferis · January 10th, 2006, 12:39 AM

On a different note, would this be a safer query to use? I am getting the proper row returned for this week with it:

Code:

SELECT * FROM utCalMenu WHERE NOW() >= (utCalMenu.Calweek_date)  and NOW() <= (ADDDATE(utCalMenu.Calweek_date, 6) )

~~~~~~~~~~~~~
http://www.petersonsales.net

Mantis · January 10th, 2006, 06:45 AM

Change NOW() for few dates and check it out...

If it works than great if not you need some serious sit down and think to get a PHP code to break down that date format.

jefferis · January 10th, 2006, 08:56 AM

I had a friend rebuild my table and queries on his server. He did. He replied:

Quote:

quote: um, you are not gonna like this:

SELECT * FROM utCalMenu WHERE DATE_FORMAT(utCalMenu.Calweek_date,'%v') = '02'
02 Week number

Array
(
[Calweek_date] => 2006-01-09
[PARKS] => 2
[CROSSING] => 2
[SOUTH] => 1
[I-HOUSE] => 1
[OTTAWA] => 3
)

2006-01-09= this week
Arrayrows
1 total rows
PARKS Value = 2

The ONLY thing I changed was the db connection. Try having a look at the connection

Why would or could a connection work partially?

<?php
# FileName="Connection_php_mysql.htm"
# Type="MYSQL"
# HTTP="true"
$hostname_utMenuCal = "db71b.pair.com";
$database_utMenuCal = "avifoods_avifoods";
$username_utMenuCal = "*****";
$password_utMenuCal = "****";
$utMenuCal = mysql_pconnect($hostname_utMenuCal, $username_utMenuCal, $password_utMenuCal) or trigger_error(mysql_error(),E_USER_ERROR);
?>

~~~~~~~~~~~~~
http://www.petersonsales.net

jefferis · January 10th, 2006, 09:18 AM

I tried the NOW() with different values, and it appears to work just fine!

~~~~~~~~~~~~~
http://www.petersonsales.net