Wrox Programmer Forums
Go Back   Wrox Programmer Forums > PHP/MySQL > PHP Databases
| 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 software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
  #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.
  #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
  #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
  #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




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





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