Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > PHP/MySQL > PHP Databases
Password Reminder
Register
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
PHP Databases Using PHP in conjunction with databases. PHP questions not specific to databases should be directed to one of the other PHP forums.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the PHP Databases section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #11 (permalink)  
Old November 30th, 2007, 05:36 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: Oklahoma City, Oklahoma, USA.
Posts: 249
Thanks: 0
Thanked 0 Times in 0 Posts
Default

if you are wanting previous months you could change DATE_ADD to DATE_SUB in my code.
Reply With Quote
  #12 (permalink)  
Old November 30th, 2007, 07:57 PM
Authorized User
 
Join Date: Aug 2003
Location: Schuylerville, NY, USA.
Posts: 29
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Sorry, Mike. I desperately needed some food, so I went out to dinner. I'm back in the office and will be here for another 2 hours or so.

I tried substituting DATA_SUB and it still doesn't work. I had:
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");
which gave me actual output and substituted your query:
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 entry_date BETWEEN DATE_SUB(CURDATE(), INTERVAL 1 MONTH) AND DATE_SUB(CURDATE(), INTERVAL 3 MONTH) ORDER BY entry_date, last_name");
so that the interval-thing would work, but it's still not cooperating. I checked my MySQL database, and it's reading version 4.0.something. The only thing I'm questioning at this point is if DATA_SUB will work in that version.

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

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
Reply With Quote
  #13 (permalink)  
Old November 30th, 2007, 11:13 PM
Authorized User
 
Join Date: Aug 2003
Location: Schuylerville, NY, USA.
Posts: 29
Thanks: 0
Thanked 0 Times in 0 Posts
Default

OK, so I lied about when I was leaving my desk, and my hubby's not to happy about it either. I'm going to bed now, but I'll leave y'all with this tidbit.
http://www.hudsonmohawkastd.org/index-test.php
I've switched the months to be "month(curdate()) -2" in the 1st column, "month(curdate()) -1" in the second column, and "month(curdate())" in the 3rd column.FYI - In the current database, there are no entries for 2 months before (September), nor are there any for the current month (November).

1) What SHOULD happen is this: the name of the requested month will appear (in a <p></p> with a particular style sheet), then if there are no entries for that month, it will echo "No new members", otherwise, it echoes "<p><strong>first_name last_name</strong><br />company</p>". I've got the latter list to work really well.

ONE THING HERE: I want the requested month to appear just once at the top of the list.

2) Right now, my coding hinges on the YEAR(entry_date)=YEAR(curdate()). I need to change the coding so that each column reflects the actual "month(curdate()) -2", "month(curdate()) -1", or "month(curdate())". i.e., If the current date is actually February 2008, I want to have December 2007 in the 1st column, January 2008 in the 2nd column, and February 2008 in the 3rd column.

NOTE: $results are numbered "2, 3, and 4" because there is a $results call earlier in the page. Here's what I've got:
Code:
<table width="516" border="0" cellpadding="0" cellspacing="0">
<tr>
<td width="163" valign="top">
<?php
$result2 = mysql_query("SELECT id, first_name, last_name, company, monthname( entry_date ) AS monname FROM membership WHERE YEAR( entry_date ) = YEAR( curdate( ) ) AND MONTH( entry_date ) = ( MONTH( CURDATE( ) ) - 2 ) ORDER BY last_name");

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

for ($ib = 0; $data = mysql_fetch_array($result2); $ib++) {
    echo "<p class=\"months\">".$data['monname']."</p>";
    if(!$data['id']) {
        echo "<p class='newbies'>No new members</p>";
    } else {
        echo "<p class='newbies'><strong>".$data['first_name']." ".$data['last_name']."</strong><br />".$data['company']."</p>";
    }
}
?>
</td>
<td width="163" valign="top">
<?php
$result3 = mysql_query("SELECT id, first_name, last_name, company, monthname( entry_date ) AS monname FROM membership WHERE YEAR( entry_date ) = YEAR( curdate( ) ) AND MONTH( entry_date ) = ( MONTH( CURDATE( ) ) - 1 ) ORDER BY last_name");

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

for ($ic = 0; $data = mysql_fetch_array($result3); $ic++) {
    echo "<p class=\"months\">".$data['monname']."</p>";
    if(!$data['id']) {
        echo "<p class='newbies'>No new members</p>";
    } else {
        echo "<p class='newbies'><strong>".$data['first_name']." ".$data['last_name']."</strong><br />".$data['company']."</p>";
    }
}
?>
</td>
<td width="162" valign="top">
<?php
$result4 = mysql_query("SELECT id, first_name, last_name, company, monthname( entry_date ) AS monname FROM membership WHERE YEAR( entry_date ) = YEAR( curdate( ) ) AND MONTH( entry_date ) = MONTH( CURDATE( ) ) ORDER BY last_name");

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

for ($id = 0; $data = mysql_fetch_array($result4); $id++) {
    echo "<p class=\"months\">".$data['monname']."</p>";
    if(!$data['id']) {
        echo "<p class='newbies'>No new members</p>";
    } else {
        echo "<p class='newbies'><strong>".$data['first_name']." ".$data['last_name']."</strong><br />".$data['company']."</p>";
    }
}
?>
</td>
<td width="19" valign="top"><img src="./images/spacer.gif" alt="" width="19"></td>
</tr>
</table>
Hope someone can help! Thanks in advance...
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

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
Reply With Quote
  #14 (permalink)  
Old December 1st, 2007, 12:43 PM
Authorized User
 
Join Date: Aug 2003
Location: Schuylerville, NY, USA.
Posts: 29
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Mike,

Thanks for all of your help! I am going to go to the client and ask if I can alter the layout of the home page slightly to accommodate the new coding, which, by the way, now works!

Here is the final result:
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_SUB( CURDATE( ) ,INTERVAL 2 MONTH ) AND CURDATE( ) ORDER BY entry_date, last_name";

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

//echo $sql;
//echo "<BR>count=".mysql_num_rows($result2);

$blnWriteMonth = true;
while ($data = mysql_fetch_array($result2)) {
    if ($blnWriteMonth) {
        echo "<span class='months'>".$data['formatted_month']."</span>";
        $blnWriteMonth = false;
    }
    echo "<p class='newbies'><strong>".$data['first_name']." ".$data['last_name']."</strong><br />".$data['company']."<br />".$data['formatted_date']."</p>";
}
Hope anyone who needs this type of coding in future finds it useful!

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

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
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Date Comparison jroxit Classic ASP Databases 5 October 5th, 2007 05:39 PM
month comparison,update status to 'Due Soon' miracles Excel VBA 1 May 14th, 2007 11:33 PM
Date comparison lily611 General .NET 5 January 4th, 2005 07:08 AM
Date Comparison hoffmann Classic ASP Databases 7 October 21st, 2004 09:00 AM
Date Comparison cmiller PHP How-To 3 June 19th, 2003 02:58 PM



All times are GMT -4. The time now is 03:40 PM.


Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.