www.webdeveloper.com
Results 1 to 6 of 6

Thread: compare to date

  1. #1
    Join Date
    Jul 2010
    Posts
    106

    compare to date

    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 Code:
    <?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>

  2. #2
    Join Date
    May 2006
    Location
    the netherlands
    Posts
    454
    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?

  3. #3
    Join Date
    Jul 2010
    Posts
    106
    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 Code:
    <?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>

  4. #4
    Join Date
    May 2006
    Location
    the netherlands
    Posts
    454
    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

  5. #5
    Join Date
    Jul 2010
    Posts
    106
    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 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();
    ?>

  6. #6
    Join Date
    May 2006
    Location
    the netherlands
    Posts
    454
    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.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center



Recent Articles