Need date comparison and month from same db field

scottiegirl · November 30th, 2007, 01:25 PM

Greetings all, and happy holidays!

I need to find records of those who were entered into a db 3, 2, and 1 month(s) from the current month. I also need to echo the name of the month they were entered. The record(s) should appear as such (html coding included for line break purposes only):

%M //<<--3 month(s) from current month
first_name last_name company //<--each entry from that month (looped)
(repeat the above sequence for 2 and 1 month(s) from current month)

I can get the loop of records within each month just fine, but my retrieving and echoing the name of the required month isn't happening. This is what I have:

Code:

$result = mysql_query("SELECT id, first_name, last_name, company, date_format(entry_date, '%m-%d-%Y') as formatted_date, date_format(entry_date, '%M') as formatted_month FROM {$mbsp_tablename} WHERE (MONTH(entry_date)) = (MONTH(curdate())-3) ORDER BY last_name");
echo "<p>".$data['formatted_month']."</p>";
for ($i = 0; $data = mysql_fetch_array($result); $i++)
{
echo "<p><b>".$data['first_name']." ".$data['last_name']."</b><br />".$data['company']."</p>";
}

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Thanks a bunch in advance for your help!

HollyAnn
aka Scottiegirl

"I was put on Earth to accomplish a certain number of things. Right now I am so far behind, I will never die." - Calvin, Calvin and Hobbs

harpua · November 30th, 2007, 01:44 PM

You could do something like this

$result = mysql_query("SELECT id, first_name, last_name, company,
            date_format(entry_date, '%m-%d-%Y') as formatted_date, date_format(entry_date, '%M') as formatted_month
            FROM {$mbsp_tablename}
            WHERE entry_date
            BETWEEN SUBADD(CURDATE(), INTERVAL 1 MONTH)
            AND SUBADD(CURDATE(), INTERVAL 3 MONTH)
            ORDER BY entry_date, last_name");

mike

scottiegirl · November 30th, 2007, 02:00 PM

Mike, Wow! That was fast!

I put it in and I'm getting an error:

Quote:

quote:"Error: 1064: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near '(CURDATE(), INTERVAL 1 MONTH) AND SUBADD(CURDATE(), INTERVAL 3"

Thoughts?

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

HollyAnn
aka Scottiegirl

"I was put on Earth to accomplish a certain number of things. Right now I am so far behind, I will never die." - Calvin, Calvin and Hobbs

harpua · November 30th, 2007, 02:54 PM

Sorry, change SUBADD to DATE_ADD, my brain was in a couple different places.

$result = mysql_query("SELECT id, first_name, last_name, company, date_format(entry_date, '%m-%d-%Y') as formatted_date, date_format(entry_date, '%M') as formatted_month
FROM {$mbsp_tablename}
WHERE entry_date BETWEEN DATE_ADD(CURDATE(), INTERVAL 1 MONTH) AND DATE_ADD(CURDATE(), INTERVAL 3 MONTH) ORDER BY entry_date, last_name");

scottiegirl · November 30th, 2007, 03:17 PM

No problem.

I've substituted your new code and don't get any visible results. The page in question is http://www.hudsonmohawkastd.org/index-test2.php. The information should show up under the graphic (August and its members) under the "New Members" section at the bottom of the page.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

HollyAnn
aka Scottiegirl

"I was put on Earth to accomplish a certain number of things. Right now I am so far behind, I will never die." - Calvin, Calvin and Hobbs

harpua · November 30th, 2007, 04:12 PM

Dumb question time, for my benefit : ). Are you sure you are connecting to the DB? It is good practice to add the "or die" statement to the end of the mysql_query. Also you should write everything out like below. In your statement you are trying to write the formatted month before assigning it to the $data variable. Also are you sure the dates in the database are 1 month out? I have tested the statement and it worked correctly.

$sql = "SELECT id, first_name, last_name, company, date_format(entry_date, '%m-%d-%Y') as formatted_date, date_format(entry_date, '%M') as formatted_month FROM company_tbl_remove WHERE entry_date BETWEEN DATE_ADD(CURDATE(), INTERVAL 1 MONTH) AND DATE_ADD(CURDATE(), INTERVAL 3 MONTH) ORDER BY entry_date, last_name";

$result = mysql_query($sql)
 or die(mysql_error()."could not connect");

echo $sql;
echo mysql_num_rows($result)."--count ";

$blnWriteMonth = true;
while ($data = mysql_fetch_array($result)) {
 if ($blnWriteMonth) {
 echo "".$data['formatted_month']."";
 $blnWriteMonth = false;
 }
 echo "".$data['first_name']." ".$data['last_name']." ".$data['company']."";
}

scottiegirl · November 30th, 2007, 04:45 PM

Definitely not a dumb question. I know I'm connecting to the db properly because the query for the "Upcoming Programs & Events" section above works perfectly (see below).

Code:

$result = mysql_query("SELECT UPEid, potm, title, date_format(date, '%m-%d-%Y') as formatted_date, time, speaker, location FROM upe WHERE date >= curdate() ORDER BY date");
if(!$result) error_message(sql_error());

for ($i = 0; $data = mysql_fetch_array($result); $i++)
{
    echo "<div class=\"date\">Save the Date</div>";
    if($data['potm'] == 'Yes') echo "<p class=\"subheadb\" style=\"padding: 0px;\">Program of the Month</p>";
    echo "<p><strong>Title:</strong> ".$data['title']."<br /><strong>Date:</strong>  ".$data['formatted_date']."<br /><strong>Time:</strong>  ".$data['time']."<br /><strong>";
    if($data['potm'] == 'No') echo "Keynote ";
    echo "Speaker:</strong> ".$data['speaker']."<br /><strong>Location:</strong>  ".$data['location']."</p>";
}

I took the liberty of changing all of the items labeled "results" in your coding to "results2" just to be cautious (see below). It still doesn't work for me.

Code:

$sql = "SELECT id, first_name, last_name, company, date_format(entry_date, '%m-%d-%Y') as formatted_date, date_format(entry_date, '%M') as formatted_month FROM membership WHERE entry_date BETWEEN DATE_ADD(CURDATE(), INTERVAL 1 MONTH) AND DATE_ADD(CURDATE(), INTERVAL 3 MONTH) ORDER BY entry_date, last_name";

$result2 = mysql_query($sql) or die(mysql_error()."could not connect");

echo $sql;

echo mysql_num_rows($result2)."--count<BR>";

$blnWriteMonth = true;
while ($data = mysql_fetch_array($result2)) {
    if ($blnWriteMonth) {
        echo "<p>".$data['formatted_month']."</p>";
        $blnWriteMonth = false;
    }

    echo "<p><b>".$data['first_name']." ".$data['last_name']."</b><br />".$data['company']."</p>";

}

I'm feeling very mentally small right now and close to tears.
~~~~~~~~~~~~~~~~~~~~~~~~~~

HollyAnn
aka Scottiegirl

"I was put on Earth to accomplish a certain number of things. Right now I am so far behind, I will never die." - Calvin, Calvin and Hobbs

scottiegirl · November 30th, 2007, 04:46 PM

SORRY! I forgot to tell you to use the newer URL: http://www.hudsonmohawkastd.org/index-test3.php

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

HollyAnn
aka Scottiegirl

"I was put on Earth to accomplish a certain number of things. Right now I am so far behind, I will never die." - Calvin, Calvin and Hobbs

scottiegirl · November 30th, 2007, 05:05 PM

OK. New plan everybody! Here's the scoop:

Check this out:

Code:

$result2 = mysql_query("SELECT id, first_name, last_name, company, date_format( entry_date, '%Y' ) AS formatted_year, date_format( entry_date, '%m' ) AS formatted_month
FROM membership 
WHERE YEAR( entry_date ) = YEAR( curdate( ) )
ORDER BY MONTH( entry_date ) , last_name");

if(!$result2) error_message(sql_error());

for ($i = 0; $data = mysql_fetch_array($result2); $i++)
{
    echo "<p class='newbies'><strong>".$data['first_name']." ".$data['last_name']."</strong><br />".$data['company']."</p>";
}

People show up! http://www.hudsonmohawkastd.org/index-test.php The new exercise is to
1) trim their numbers down to last month, the month before, and the month before that and
2) put them in columns by month: 3rd month previous in column 1, 2nd month previous in column 2, and last month in column 3.

Got any ideas?
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

HollyAnn
aka Scottiegirl

"I was put on Earth to accomplish a certain number of things. Right now I am so far behind, I will never die." - Calvin, Calvin and Hobbs

harpua · November 30th, 2007, 05:07 PM

We are close. It does look like it's working, so that is good. It is telling me that 0-- count was returned so that means that we should look at the data in the DB.

Can you verify the field is a Date field and that you do have days that are at least one month out. Can you update a date to something like 2008-01-16, I know this date will work.