compare to date

[ckdoublenecks]

I'm working on a program to print out records from a database with a 'datecompleted' that matches the current (computer) month. What I have now (not working) is comparing to an input box input, see the code below:

<?php
mysql_connect(localhost,root,"");
mysql_select_db(maintdb) or die( "Unable to select database");
if(!empty($_POST["submit"]))
{
$datecomplete = $_POST['datecomplete'];
$query="SELECT * FROM maintdata 
ORDER BY apt ASC, datereceived DESC  WHERE datecomplete='$datecomplete'";
$result=mysql_query($query);
$num=mysql_numrows($result);
 {
  echo "<form action='#' method='post'><b>Poject: Hammock Maintenance Log</b><br /><br />
         <table border='1'>   
      <tr>
      <TH>Apt</TH>
    <TH>Name</TH>
    <TH>Date Received</TH>
    <TH>Time Received</TH>
    <TH>Symptom</TH>
    <TH>Action</TH>
    <TH>Date Completed</TH>     
    </tr>";
while($row = mysql_fetch_array($result))
{
echo "<tr>";
echo "<td>" . $row['apt'] . "</td>";
echo "<td>" . $row['name'] . "</td>";
echo "<td>" . $row['datereceived'] . "</td>";
echo "<td>" . $row['time'] . "</td>";
echo "<td>" . $row['symptom'] . "</td>";
echo "<td>" . $row['action'] . "</td>";
echo "<td>" . $row['datecomplete'] . "</td>";
echo "</tr>";
   }
  echo "</table>";
  echo "</form>";
  }
 mysql_close();
 ?> 
<form method="post" action="#">
<br />
 <input type="text" name="datecomplete"/>
<p>
<input type="submit" name="select" value="Select mm"/>
</form>

[themarty]

When queries don't work, the first think you do is create the query in mysql (using the prompt or a program like heidi/phpmyadmin) and try to make the query work without php.
When you have succeeded doing that, then is when you try to create the same query using php. If that doesn't work: echo the query to your screen to see what the difference is and try to eliminate that difference.

Which of these steps is failing in your case?

[ckdoublenecks]

Thanks for the suggestions. It's obvious I'm just learning PHP. Actually I was trying to see why it wasn't working in general. I have it working now - kinda. The database containes records with a 'datecomplete' (mm/dd/yyyy) and the below program selects those fine but I really want to select from the database 'datecomplete' based on the 'mm'. ??

<?php
mysql_connect(localhost,root,"");
mysql_select_db(maintdb) or die( "Unable to select database");
if(!empty($_POST["submit"]))
{
 $datecomplete = $_POST['datecomplete'];
 $query="SELECT * FROM maintdata Where datecomplete='$datecomplete'";
$result=mysql_query($query);
if(mysql_num_rows($result))
{
  echo "<form action='#' method='post'><b>Poject: Hammock Maintenance Log</b><br><br>
      <table border='1'>
<tr>
       <TH>Apt</TH>
    <TH>Name</TH>
    <TH>Date Received</TH>
    <TH>Time Received</TH>
    <TH>Symptom</TH>
    <TH>Action</TH>
    <TH>Date Completed</TH>     
    </tr>";
while($row = mysql_fetch_array($result))
{
echo "<tr>";
echo "<td>" . $row['apt'] . "</td>";
echo "<td>" . $row['name'] . "</td>";
echo "<td>" . $row['datereceived'] . "</td>";
echo "<td>" . $row['time'] . "</td>";
echo "<td>" . $row['symptom'] . "</td>";
echo "<td>" . $row['action'] . "</td>";
echo "<td>" . $row['datecomplete'] . "</td>";
echo "</tr>";
}
echo "</table>";
echo "</form>";
 }
 else{echo "No listing for date $datecomplete.<br />Please select another.<br />";}
 }
?>
<form method="post" action="#">
 <br />
 <input type="text" name="datecomplete"/> <p>
<input type="submit" name="submit" value="datecomplete"/>
</form>

[themarty]

have a look at the DATE_FORMAT() function:

http://dev.mysql.com/doc/refman/5.1/...on_date-format

It allows you to format any datestring in your database.

So, with that you could do :

SELECT .... WHERE DATE_FORMAT(datecol, '%m')=[month]

or this:

SELECT .... WHERE DATE_FORMAT(datecol, '%Y%m')=[yearmonth]

which makes more sense in most cases

[ckdoublenecks]

I got this message:

Warning: mysql_numrows() expects parameter 1 to be resource, boolean given in C:\xampp\htdocs\hofiles\currentmaintlog.php on line 8
Poject: Hammock Maintenance Log

Apt Name Date Received Time Received Symptom Action Date Completed

Warning: mysql_fetch_array() expects parameter 1 to be resource, boolean given in C:\xampp\htdocs\hofiles\currentmaintlog.php on line 20

when I executed this code:

<?php
mysql_connect(localhost,root,"");
mysql_select_db(maintdb) or die( "Unable to select database");
$query="SELECT * FROM maintdata 
ORDER BY apt ASC, datereceived DESC 
WHERE DATE_FORMAT(datecomplete, '%m')=[month]";
$result=mysql_query($query);
$num=mysql_numrows($result);
  echo "<b>Poject: Hammock Maintenance Log</b><br><br>";
echo "<table border='1'>
<tr>
        <TH>Apt</TH>
    <TH>Name</TH>
    <TH>Date Received</TH>
    <TH>Time Received</TH>
    <TH>Symptom</TH>
    <TH>Action</TH>
    <TH>Date Completed</TH>     
    </tr>";
while($row = mysql_fetch_array($result))
{
echo "<tr>";
echo "<td>" . $row['apt'] . "</td>";
echo "<td>" . $row['name'] . "</td>";
echo "<td>" . $row['datereceived'] . "</td>";
echo "<td>" . $row['time'] . "</td>";
echo "<td>" . $row['symptom'] . "</td>";
echo "<td>" . $row['action'] . "</td>";
echo "<td>" . $row['datecomplete'] . "</td>";
echo "</tr>";
}
echo "</table>";
mysql_close();
?>

[themarty]

That's because your query resulted in an error and the reason for that is that you took my example too literally.

but instead of me taking you by the hand every step of the way, you would benefit a lot from it, trying to figure out what the problem is yourself.

try to rewrite your code in such a way that you first check if the query was successful and if it's not that you output the error to the screen (or write it to a log file ... but simply outputting is easier for now) so you can see what goes wrong (in this case: what error mysql returned)

see:
- php: mysql_result()
- php: mysql_error()
- mysql: date_format()

each of these topics contain examples that should get you on your way.