Get date in db and change to today

[BIOSTALL]

Hey, i have a database with two fields: 'TableDate' and 'Count' What i want is if the date stored in 'TableDate' is less then today (ie. Yesterday) then the TableDate will be replace with todays date and the count reset to zero. So far, i have the code below but it keeps returning 0 where as it shud b adding one every time.

$TableDate = $row['TableDate'];
		$Today = date("yyyy-mm-dd");
		$Count = $row['Count'];

	if ($TableDate != $Today) {
		$row['TableDate'] = $Today;
		$row['Count'] = 0;
		echo $row['Count'];
		echo " Visitors Today";
	} else {
		$Count = $Count + 1;
		$row ['Count'] = $Count;
		echo $Count;
		echo " Visitors Today";
	}

Any help would be great, thanks, BIOSTALL

[bradgrafelman]

Want to solve all your problems?

Read up on how to use [man]date[/man] correctly.

$Today = date("yyyy-mm-dd"); // returns 05050505-0404-2222
$Today = date("Y-m-d"); // returns 2005-04-22

🙂

EDIT: Also, you can't just change $row's value and expect MySQL to update. You have to run an UPDATE query on the MySQL table...

[kellog]

I don't see anything wrong with your code. The only thing I can think of that would cause it to always return 0 is that $TableDate is never = $Today. Try to echo out both variables and make sure they're in the same format.

[bradgrafelman]

kellog; you must have overlooked the date() and MySQL update errors stated in my above post!

[BIOSTALL]

Its still not working, my datais being written to the database as just zeros: '0000-00-00'. My code now is:

$TableDate = $row['TableDate'];
		$Today = date("Y-mm-dd");
		$Count = $row['Count'];

	if ($TableDate != $Today) {
		$Count = 0;
		$sql = "INSERT INTO DayCount
		(TableDate, Count)
		VALUES
		(\"$today\", \"$Count\")";
		$result = mysql_query($sql, $connection) or die("Error4");	
		echo $Count;
		echo " Visitors Today";
	} else {
		$Count = $Count + 1;
		$sql = "INSERT INTO Daycount
		(Count)
		VALUES
		(\"$Count\")";
		$result = mysql_query($sql, $connection) or die("Error5");
		echo $Count;
		echo " Visitors Today";
	}

And also, how do i overwrite whats already in the database instead of adding a new row??

Thanks, BIOSTALL

[Skull]

            $sql = "INSERT INTO DayCount
            (TableDate, Count)
            VALUES
            (\"$today\", \"$Count\")"; 

your trying to use $today, but it has not been set. You set the variable $Today but that is different.

            $sql = "INSERT INTO DayCount
            (TableDate, Count)
            VALUES
            (\"$Today\", \"$Count\")"; 

UPDATE QUERY:

            $sql = "UPDATE `DayCount` SET `TableDate` = '".$Today."',Count='".$Count."' WHERE ID=??"; 

dont expect to be able to copy and paste the above code...

[Weedpacket]

Originally posted by BIOSTALL
My code now is:

And your use of [man]date[/man] is still wrong. Go back and read bradgelfman's post again. Go and read the [man]date[/man] manual page again.

[BIOSTALL]

Ok, i've got it to update the date to todays date. But the count remains zero 🙁 It should go up by one everytime the page is refreshed. Heres my code again:

$sql = "SELECT * FROM DayCount";
		$result = @mysql_query($sql, $connection) or die("Couldnt execute query");

	$TableDate = $row['TableDate'];
	$Today = date("Y-m-d");
	$Count = $row['Count'];

	if ($TableDate !== $Today) {
		$Count = 0;
		$sql = "UPDATE DayCount SET TableDate = '".$Today."', Count = '".$Count."'";
		$result = mysql_query($sql, $connection) or die("Error4");	
		echo $Count;
		echo " Visitors Today";
	} else {
		$Count = $Count +1;
		$sql = "UPDATE DayCount SET Count = '".$Count."'";
		$result = mysql_query($sql, $connection) or die("Error5");
		echo $Count;
		echo " Visitors Today";
	}

btw, Cheers Skull for the help

[Drakla]

You're not fetching a row after running the query - after it you need to user [man]mysql_fetch_assoc[/man] to pull the row up.

[nemonoman]

Could I suggest please that you use MySQL's built in date finctions and group statements to make your life a LOT easier:

Rather than trying to build some date in PHP and hand it to MySQL:

UPDATE DayCount SET tableDate=CURRENT_DATE()

Or
INSERT INTO DayCount SET tableDate=CURRENT_DATE()

Although this separate DayCount table is really unneeded, and should in fact be avoided.

I assume you have some date capture on another table, like Visitor?

SELECT count(*) FROM Visitor WHERE
visitDate=CURRENT_DATE()

Will returnthe number of today's visitors.

For yesterday's visitors

SELECT count(*) FROM Visitor WHERE
visitDate=CURRENT_DATE() - INTERVAL 1 DAY

To see all visitors counts by all days:

SELECT visitDate, count(*) FROM Visitor GROUP BY visitDate
ORDER BY visitDate DESC

[bradgrafelman]

Originally posted by Weedpacket
And your use of [man]date[/man] is still wrong. Go back and read bradgelfman's post again. Go and read the [man]date[/man] manual page again.

bradgelfman's?

Also, excellent post by nemonoman. Take his advice 😉

[Weedpacket]

Originally posted by bradgrafelman
bradgelfman's?

Sorry :o. Too much Red Dwarf, methinks.

[bradgrafelman]

So, you should correct your date usage as Weedsmoker - sorry, Weedpacket - suggested, and try nemonoman's suggested modifications.

:p

[BIOSTALL]

Originally posted by nemonoman
Could I suggest please that you use MySQL's built in date finctions and group statements to make your life a LOT easier:

Rather than trying to build some date in PHP and hand it to MySQL:

UPDATE DayCount SET tableDate=CURRENT_DATE()

Or
INSERT INTO DayCount SET tableDate=CURRENT_DATE()

Although this separate DayCount table is really unneeded, and should in fact be avoided.

So in my database, would i set the datatype to text or date??

I assume you have some date capture on another table, like Visitor?

SELECT count(*) FROM Visitor WHERE
visitDate=CURRENT_DATE()

Will returnthe number of today's visitors.

For yesterday's visitors

SELECT count(*) FROM Visitor WHERE
visitDate=CURRENT_DATE() - INTERVAL 1 DAY

To see all visitors counts by all days:

SELECT visitDate, count(*) FROM Visitor GROUP BY visitDate
ORDER BY visitDate DESC

Do i really need this? What i want is as soon as the first visitor of today visits the site, the counter will reset. Then for every other person it will increase the count by 1. btw... I only need to display the amount of visitors today, not yesterday or anything.

Thanks for your help all, i really do appreciate it.

BIOSTALL

[nemonoman]

What i want is as soon as the first visitor of today visits the site, the counter will reset.

Actually what you want is a count of the day's visitors. This code returns that number:

SELECT count(*) FROM Visitor WHERE
visitDate=CURRENT_DATE()

So in my database, would i set the datatype to text or date??

Although both would work, setting the type as date would be much preferable.