|
Subject:
|
Help getting This Week from query.
|
|
Posted By:
|
jefferis
|
Post Date:
|
1/9/2006 11:14:34 AM
|
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:
<?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
|
|
Reply By:
|
Mantis
|
Reply Date:
|
1/9/2006 9:48:04 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.
|
|
Reply By:
|
jefferis
|
Reply Date:
|
1/9/2006 11:32:54 PM
|
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
|
|
Reply By:
|
jefferis
|
Reply Date:
|
1/9/2006 11:39:06 PM
|
On a different note, would this be a safer query to use? I am getting the proper row returned for this week with it:
SELECT * FROM utCalMenu WHERE NOW() >= (utCalMenu.Calweek_date) and NOW() <= (ADDDATE(utCalMenu.Calweek_date, 6) )
~~~~~~~~~~~~~
http://www.petersonsales.net
|
|
Reply By:
|
Mantis
|
Reply Date:
|
1/10/2006 5:45:50 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.
|
|
Reply By:
|
jefferis
|
Reply Date:
|
1/10/2006 7:56:26 AM
|
I had a friend rebuild my table and queries on his server. He did. He replied:
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
|
|
Reply By:
|
jefferis
|
Reply Date:
|
1/10/2006 8:18:51 AM
|
I tried the NOW() with different values, and it appears to work just fine!
~~~~~~~~~~~~~
http://www.petersonsales.net
|
