[SOLVED] help using SET and SELECT BETWEEN xdate AND ydate (event calender help

[BandonRandon]

Hello,

 

I have an event calender script that stores a start date and end date in two different columns as DATETIME and what i need to do is pull out the events from a select range and put them into an array so the dates with events will become a hyperlink.

 

here is my sql query:

 

$query ="SET @startdate = STR_TO_DATE(((PERIOD_ADD( EXTRACT(YEAR_MONTH
FROM ADDDATE(SYSDATE(),INTERVAL -1 MONTH) ) , 1 ) *100) +1), '%Y%m%d'
);

SET @enddate = STR_TO_DATE(((PERIOD_ADD( EXTRACT(YEAR_MONTH FROM
ADDDATE(SYSDATE(),INTERVAL +0 MONTH) ) , 1 ) *100) +1), '%Y%m%d' );

SELECT * FROM `events` WHERE `start_date` BETWEEN @startdate AND
@enddate ORDER BY `start_date` ASC;";

 

then i have the code that puts it into an array and loop here:

$record = mysql_query($query);
if(!$record){
$counter = mysql_num_rows($record);

for($i=0; $i<$counter; $i++)
{
$holder[$i]=mysql_fetch_assoc($record);
$tempDateTime=explode(' ',$holder[$i][start_date]);
$tempDate=explode('-',$tempDateTime[0]);
$days[$tempDate[2]]=array('events.php?d=','linked-day');

}
}

 

however i get this error:

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in C:\serverpath\events.php on line 184

 

line 184 is the line containing:$record = mysql_query($query);

 

Also i believe that i can change the date range dynamically to get the next month but not sure how.  I'm using the calender script found here http://keithdevens.com/software/php_calendar then to get the month I'm using

 echo generate_calendar ($year, $display_month, $days, 3, NULL, 0, $pn);

 

$display_month is either the current month or if the user selects a month the month from the URL IE "index.php?m=10" for October 2009 and m=0 for December 2008

 

I probably provided to much information but any help would be great.

 

Thanks,

Brandon

 

 

 

[BandonRandon]

Edit: tested used mysql 4.1.22 and 5.1.30 with the same result.

[fenway]

You can't run 3 queries at once... at least not with the mysql_* libraries.

[BandonRandon]

thanks for the reply fenway,

 

if i understand you right what you are saying is i'm trying to run 3 queries at once so i should break them into 3 separate ones i tried that by doing this:

$start_date_query ="SET @startdate = STR_TO_DATE(((PERIOD_ADD( EXTRACT(YEAR_MONTH
FROM ADDDATE(SYSDATE(),INTERVAL -1 MONTH) ) , 1 ) *100) +1), '%Y%m%d'
);";

$end_date_query = "SET @enddate = STR_TO_DATE(((PERIOD_ADD( EXTRACT(YEAR_MONTH FROM
ADDDATE(SYSDATE(),INTERVAL +0 MONTH) ) , 1 ) *100) +1), '%Y%m%d' );";

$query ="SELECT * FROM `events` WHERE `start_date` BETWEEN '$start_date_query' AND
'$end_date_query' ORDER BY `start_date` ASC;";

 

of course that is essentially the same code and i know it's wrong but I'm not sure who to get it to work.

 

thank again for the help!

[fenway]

Actually, I didn't realize that you can use subqueries as well... try this:

 

SELECT * 
FROM `events` 
WHERE `start_date` BETWEEN 
( 
STR_TO_DATE(((PERIOD_ADD( EXTRACT(YEAR_MONTH
FROM ADDDATE(SYSDATE(),INTERVAL -1 MONTH) ) , 1 ) *100) +1), '%Y%m%d' ) 
)
AND 
( 
STR_TO_DATE(((PERIOD_ADD( EXTRACT(YEAR_MONTH FROM
ADDDATE(SYSDATE(),INTERVAL +0 MONTH) ) , 1 ) *100) +1), '%Y%m%d' )
)
ORDER BY `start_date` ASC

[BandonRandon]

thanks, that seams to have gotten me a lot closer but now i have a 'Resource id #7' error when i print the $record.

$record = mysql_query($query);
if($record){
$counter = mysql_num_rows($record);

for($i=0; $i<$counter; $i++)
{
    $holder[$i]=mysql_fetch_assoc($record);
    $tempDateTime=explode(' ',$holder[$i][start_date]);
    $tempDate=explode('-',$tempDateTime[0]);
    $currentMonthDays[$tempDate[2]]=array('some random url from god
knows hwere','linked-day');
}
} 
print_r($record);

[fenway]

Check mysql_error() after running the query... what mysql version?

[BandonRandon]

this is now solved here is the end quary:

 

if(isset($_GET[m]))
{
    $startMonthOffset=-1+$_GET[m]-1;
    $endMonthOffset=0+$_GET[m]-1;
}
else 
{
    $startMonthOffset=-1;
    $endMonthOffset=0;
}


$query="SELECT *
FROM `events`
WHERE `event_type` = '0'
AND `start_date` BETWEEN
(STR_TO_DATE(((PERIOD_ADD( EXTRACT(YEAR_MONTH FROM ADDDATE(SYSDATE(),INTERVAL +$startMonthOffset MONTH) ) , 1 ) *100) +1), '%Y%m%d' ))
AND
(STR_TO_DATE(((PERIOD_ADD( EXTRACT(YEAR_MONTH FROM ADDDATE(SYSDATE(),INTERVAL +$endMonthOffset MONTH) ) , 1 ) *100) +1), '%Y%m%d' ))
ORDER BY `start_date` ASC";


$record = mysql_query($query);
if($record){
    $counter = mysql_num_rows($record);

[fenway]

Great, but what version was it? Was the subquery is the issue?

[BandonRandon]

When i was testing i used  mysql v4.1.22 and v5.1.30. The db now sits on a server running v5.0.67-community.

 

I honestly couldn't tell you exactly what was wrong. I had a friend of mine who is a great programmer fix the issue for me. I know you were right when you said we couldn't run 3 queries at once as soon as we switched to the subquery you suggested it was just a matter of getting the loop to work properly.

 

Thanks for all your help,

Brandon