help with a query to database.

lucilue2003

banners_history_id int(11) PRI NULL auto_increment
banners_id int(11) 0

banners_shown int(5) 0

banners_clicked int(5) 0

banners_history_date datetime 0000-00-00 00:00:00

banners_user_ip varchar(64)

banners_shown_on_page varchar(64)

I have a page that allows me to see stats for today, the month and the year. I am having trouble writing the query to find stats for this week. I want to show stats of this week starting from sunday till today Thursday. Likewise, for tomorrow it will be
from sunday till Friday. However, it has to be for this week alone.

Is this correct?

$query= "SELECT * FROM banners_history WHERE banners_clicked = '1' and date_format(banners_history_date, '%U')<= date_format(now(), '%U')";

2day:
$query= "SELECT * FROM banners, banners_history WHERE banners_clicked = '1' and date_format(banners_history_date, '%Y%m%d') = date_format(now(), '%Y%m%d') and banners.banners_id = banners_history.banners_id" ;

this month:
$query= "SELECT * FROM banners, banners_history WHERE banners_clicked = '1' and date_format(banners_history_date, '%Y%m') = date_format(now(), '%Y%m') and banners.banners_id = banners_history.banners_id" ;

This year:
$query= "SELECT * FROM banners, banners_history WHERE banners_clicked = '1' and date_format(banners_history_date, '%Y') = date_format(now(), '%Y') and banners.banners_id = banners_history.banners_id" ;

tHANKS.

dalecosp

Not too good with SQL, but seems like you could maybe get something to work with PHP's strtotime() and the UNIX_TIMESTAMP function in MySQL...if it worked, you'd save some lines, for sure, and quite a lot of time in a big loop on a Friday or Saturday, I'd think. Something along the lines of:

$Sunday=strtotime("Last Sunday");

$query= "SELECT * FROM banners_history WHERE banners_clicked = '1' and UNIX_TIMESTAMP(banners_history_date)>= $Sunday)";

Like I said, I'm not quite up on the SQL syntax, but I think the idea's a valid one ....

lmayer4

Try this:

SELECT * FROM banners_history 
WHERE banners_clicked = '1' 
and banners_history_date  >= GETDATE() - 8

lucilue2003

This eats of the computer resources. Causes almost an infinite loop.
$Sunday=strtotime("Last Sunday");
$query= "SELECT * FROM banners_history WHERE banners_clicked = '1' and UNIX_TIMESTAMP(banners_history_date)>= $Sunday)";

This does not work and causes an error:
SELECT * FROM banners_history

WHERE banners_clicked = '1'

and banners_history_date >= GETDATE() - 8

error:
1064 - You have an error in your SQL syntax near '-8' at line 1

-8

[TEP STOP]

PHPmill

GETDATE() makes an array:

Array
(
[seconds] => 40
[minutes] => 58
[hours] => 21
[mday] => 17
[wday] => 2
[mon] => 6
[year] => 2003
[yday] => 167
[weekday] => Tuesday
[month] => June
[0] => 1055901520
)

and our banners_history_date is in mysql datatime format: 0000-00-00 00:00:00

this is not a good way to do this. use timestamps instead, convert back and forth with strtotime and date functions

at any rate if you subtract 8 days from the current day, you will not start on a sunday unless it was 8 days ago.

here's an unrelated function that I use to determine a future date, maybe it will help in some way.

function futuredate($date, $days) { 	
/*a mysql type datetime is inputted (YYYY-MM-DD), a date is calculated that is 32 days in the future and converted back to mysql format and returned*/
	$date = strtotime(substr($date,0,10));
	$date = $date + (86400*$days); //seconds per day times number of days
	$date = date("Y-m-d", $date);
	return $date;
}

dalecosp

Originally posted by lucilue2003
This eats of the computer resources. Causes almost an infinite loop.

Well, if you remove the parse error, then what? Looks like I slipped a stray parenthesis in on you ... I'd say I did it on purpose, to annoy newbs, but it's not true; what's true is I'm pretty fat-fingered for all my blather, and I do a lot of forumming in the dark as well...

However... in spite of the dim light, I see no reason why, in a well-crafted script, a query such as this should cause such a problem. In order to create a loop, you have to have a conditional structure; if the query's creating a "loop", show us the actual PHP that might be responsible...

lucilue2003

Okay, based on the suggestions, I backed up my banners_history table, dropped it and re-created it with timestamp field.

Since the stat viewing it done by administrators, I'm temporarilly going with the suggested query :

$Sunday=strtotime("Last Sunday");
$query= "SELECT * FROM banners_history WHERE banners_clicked = '1' and UNIX_TIMESTAMP(banners_history_date)>= $Sunday)";

I will look into speeder functions later....

I do however have a question. Is there a way that i can keep my old data ? I would like to populate my new table with the old data (keeping in mind that the old talbe had the date field as datetime and the new table has it as a timestamp)

thanks.

dalecosp

😕

You mean "keep the old data" from the table that you "dropped" ?? It's quite gone, is it not? [edit]: DOH! "I backed up my banners_history table..."[/edit] :rolleyes:

As for the new table, why are you changing the field types? "banners_history_date" can still be a DATETIME type ... the UNIX_TIMESTAMP function converts it to a time stamp so that the SQL engine can make a comparison to $Sunday (which, incidentally, is the reason for any slowness, I suppose ... it's true that it might be faster to just compare timestamps).

As for converting the data, make a good backup and then try something like update table set banners_history_date = UNIX_TIMESTAMP(banners_history_date);

Note I said MAKE A BACKUP. I have not tested this suggestion and can't be responsible if this doesn't work. :eek:

[edit]If you try this, be very careful with field types. I'd recommend manipulating the field as type "text", because if you foofoo around with ALTER TABLE and change field types to something designated otherwise, MySQL may change it [the DATA, that is!!!] to fit its definition....[/edit]