[RESOLVED] Working with dates php/mysql

juniper747

I am trying to count the number of dates that occur for a specific mem_id, however, my count output is always "0"... heres my code:

$datechecker = $postdate;

//$postdate is a date variable that is posted via a form into mysql, stored as a DATE

$sql = mysql_query("SELECT * FROM members WHERE mem_id='$id' AND postdate='$datechecker'");
$Counter = mysql_num_rows($sql);
if($Counter >= 0) {
echo "$datechecker $Counter";
exit();
}

This is the output I get: 2011-01-01 0

Even though I have about 10 occurrences of 2011-01-01, so why does my count say "0"? Can anyone help me solve this, or provide an alternate?

bradgrafelman

What does the SQL query string look like if you echo it out? Do $id and $datechecker appear to have the correct values?

juniper747

If I remove $datechecker from the SQL string, $id returns the correct value count. So I believe the problem has to do with SQL not recognizing the date variable. Maybe something to do with how SQL stores dates as strings??? For some reason, it's just not equating the values in the postdate column of sql with the '$datechecker' date value.

bradgrafelman

juniper747 wrote:
the '$datechecker' date value.

... which is what?

Again, echo out the SQL query and paste it here for us to see.

Derokorian

If date checker is in a different format IE MM-DD-YYYY instead of YYYY-MM-DD it will not find a match. This is why you should echo the query whenever you have query problems, so that you can see if it is comparing the right values.

juniper747

Here is my code:

$datechecker = $postdate;
//$postdate is a date variable that is posted via a form into mysql, stored as a DATE

$sql = mysql_query("SELECT * FROM members WHERE mem_id='$id' AND postdate='$datechecker'");
$Counter = mysql_num_rows($sql);
if($Counter >= 0) {
echo "$datechecker </br> $Counter" </br> "$postdate" </br> "$id" </br>;
exit();
}

and here is the echo output I get:

2011-01-01

2011-01-01
99

in my sql database, I have about 10 different rows with id 99, and date 2011-01-01. The mysql data "type" for the postdate field is DATE.

juniper747

by the way, I'm not sure if it matters but I am using

$postdate = "$Year-$Month-$Day";

in order to set $postdate (since on my form, each of the year, month, day are drop-down menu style)

juniper747

When I change the sql query to:

$sql = mysql_query("SELECT * FROM members WHERE mem_id='$id' AND postdate='2011-01-01'");

It works, and I get:

2011-01-01
10
2011-01-01
99

So, I'm not sure what is going on....

dagon

$postdate = "$Year-$Month-$Day";

should be

$postdate = "$_POST['Year']-$_POST['Month']-$_POST['Day']";

juniper747

dagon;10978879 wrote:

$postdate = "$Year-$Month-$Day";

should be

$postdate = "$_POST['Year']-$_POST['Month']-$_POST['Day']";

Sorry, but that did not work.... any other ideas?

dagon

echo the actual query , and show us the form

$q="SELECT * FROM members WHERE mem_id='$id' AND postdate='$datechecker'";
echo $q;
$sql = mysql_query($q);

juniper747

Solved it...... thanks for all your help-- I had misspelled $datechecker as $datchecker.... thanks again. Maybe I should use a different text editor other than Text Wrangler.... Since I am a beginner it would be nice if such errors would automatically be highlighted- any recommendations on good editor for beginners?