Extract MySQL data via date range selection from html form and export to excel

arvin · June 23, 2009

Hello,

I'm new to php ... but have a lil knowledge and seem to "wing it" now and then. However this current issue has me a lil confused .....

What I'm trying to do ......

Currently, I'm trying to access data stored in a MySQL db by selecting a date range from a html form and click "submit" and the php script runs ... but nothing is displayed .....

Here is my html form code

<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
  <title>Date Selector Sample</title>
  <script type="text/javascript" language="javascript">
<!--
    var date_selector =
    {
      frm:null,
      init:function(frm1)
      {
        this.frm=frm1;
      },
      getMonthTable:function(year, month, date)
      {
        var tableText = "";
        var months =
        [
          "January", "February", "March", "April", "May", "June", "July",
          "August", "September", "October", "November", "December"
        ];
        var days = ["Sun", "Mon", "Tue", "Wed", "Thu", "Fri", "Sat"];
        month = (month == null || isNaN(month) ? null : month);
        if(year == null || isNaN(year) || month == null || isNaN(month))
        {
          var dt = new Date();
          year = dt.getFullYear();
          month = dt.getMonth();
          date = dt.getDate();
        }
        date = (date == null || isNaN(date) ? null : date);
        var previousMonth = new Date(year, month-1, 1);
        var nextMonth = new Date(year, month+1, 1);
        tableText =
        (
          "<table align=\"center\" id=\"dateSelector_sample\" \
          style=\"border-collapse:collapse;\" \
          cellpadding=\"1\" cellspacing=\"0\">\
          <tr>\
          <th style=\"cursor:pointer;text-align:left;\" \
          onclick=\"date_selector.updateDateSelectorPane("
          +previousMonth.getFullYear()+", "+previousMonth.getMonth()
          +");\">&#8592;</th>\
          <th style=\"text-align:center;\" colspan=\"4\">"+months[month]
          +", "+year+"</th>\
          <th style=\"cursor:pointer;text-align:right;\" \
          onclick=\"date_selector.updateDateSelectorPane("
          +nextMonth.getFullYear()+", "+nextMonth.getMonth()+");\">&#8594;</th>\
          <th style=\"text-align:right;cursor:pointer;\" \
          onclick=\"date_selector.updateDateElement();\">X</th>\
          </tr>"
        );
        for(var i=0; i<days.length; i++)
        {
          tableText += "<th>"+days[i]+"</th>";
        }
        tableText += "</tr>";
        var tempDate;
        for(var i=1-(new Date(year, month, 1)).getDay();; i++)
        {
          tempDate = new Date(year, month, i);
          if(tempDate.getDay() == 0)
          {
            tableText += "<tr>";
          }
          tableText +=
          (
            "<td"+(tempDate.getMonth() == month ? " \
            onclick=\"date_selector.updateDateElement("
            +tempDate.getFullYear()+", "+
            tempDate.getMonth()+", "+tempDate.getDate()+");\""+
            (tempDate.getDate() == date
            ? " style=\"border:solid 2px black;font-weight:bold;\"" : "")
            : " \
            style=\"color:silver;cursor:default;\"")+">"+
            tempDate.getDate()+
            "</td>"
          );
          if(tempDate.getDay() == 6)
          {
            tableText += "</tr>";
          }
          tempDate = new Date(year, month, i+1);
          if(tempDate.getMonth() != month && tempDate.getDay() == 0)
          {
            break;
          }
        }
        tableText += "</table>";
        return tableText;
      },
      updateDateSelectorPane:function(year, month, day)
      {
        var d = document.getElementById("dateSelectorPane");
        d.innerHTML = this.getMonthTable(year, month, day);
        d.style.display = "block";
      },
      showDateSelector:function(elementName)
      {
        if(elementName == null)
        {
          this.updateDateSelectorPane();
        }
        else
        {
          this.dateElementToUpdate = elementName;
          var e = this.frm.elements[elementName];
          e = e.value.split("/");
          try
          {
            e[0] = parseInt(e[0], 10);
            e[1] = parseInt(e[1], 10);
            e[2] = parseInt(e[2], 10);
            this.updateDateSelectorPane(e[2], e[0]-1, e[1]);
          }
          catch(e)
          {
            this.updateDateSelectorPane();
          }
        }
      },
      updateDateElement:function(year, month, day)
      {
        document.getElementById("dateSelectorPane").style.display="none";
        if(year != null && month != null && day != null)
        {
          month = (month+1)+"";
          day += "";
          month = (month.length == 1 ? "0"+month : month);
          day = (day.length == 1 ? "0"+day : day);
          this.frm.elements[this.dateElementToUpdate].value
          = (month+"/"+day+"/"+year);
        }
        this.dateElementToUpdate = null;
      },
      dateElementToUpdate:null
    };
    function printDateRange()
    {
      var frm = date_selector.frm;
      window.alert
      (
        'This Popup summarizes the date range you entered.\n\n'
        +'Minimum Date:  '+frm.minimumDate.value
        +'\n\nMaximum Date:  '+frm.maximumDate.value
      );
    }
-->
</script>
    <style type="text/css">
      div#dateSelectorPane th,div#dateSelectorPane td
      {
        font-size:10pt;
        text-align:right;
      }
      div#dateSelectorPane td
      {
        cursor:pointer;
      }
      div#dateSelectorPane
      {
        display:none;
      }
      div#dateSelectorPane table
      {
        border:solid 2px black;
      }
    .style1 {font-family: Verdana, Arial, Helvetica, sans-serif}
    </style>
</head>
  <body onload="date_selector.init(window.document.forms['test1']);">
    <form style="display:inline;" method="post" name="test1" action="test.php">
      <table style="border-collapse:collapse;vertical-align:top;"
      cellpadding="0" cellspacing="0">
        <tr>
          <td width="280">
            <div align="center">
              <p class="style1">Select a <a href="javascript:;" onclick="printDateRange();">date range</a></p>
              <p class="style1"><br></p>
            </div>
            <div style="text-align:center;">
              <input type="text" name="minimumDate" style="width:75px;font-size:8pt;"
              value="YYYY/MM/DD">
              <input type="button" style="height:20px;width:20px;" value="..."
              onclick="date_selector.showDateSelector('minimumDate');">
              and
              <input type="text" name="maximumDate" style="width:75px;font-size:8pt;"
              value="YYYY/MM/DD">
              <input type="button" style="height:20px;width:20px;" value="..."
              onclick="date_selector.showDateSelector('maximumDate' );">
            </div>
            <div id="dateSelectorPane"></div>
          <p align="center"><input type="submit" name="Submit" value="Submit"> </p></td>
        </tr>
      </table>
    </form>
  </body>
</html>

And here is my php script

$connect = mysql_connect($server, $username, $password)
or die("Error connecting: " . mysql_error());
$select = mysql_select_db($database, $connect)
or die("Error selecting DB: " . mysql_error());

// Select a specific entry
if(strlen($myID) > 0) {
$query = "SELECT * FROM tickets WHERE id='$myID'"; }
// Select EVERY entry
else {
$query = $sql = 'SELECT * FROM `tickets` WHERE UPDATED BETWEEN ['minimumDate'] AND ['maximumDate'] ';  }
?>

<center>
<table border='1' cellspacing='0' cellpadding='2'>
<tr>
  <th>logo here </th>
  <th colspan="3"><span class="style4">Fixed Units Reports</span> </th>
  <th>Rpt date here </th>
    </tr>
<tr>
	<th><span class="style3">Employee Name</span></th>
	<th><span class="style3">Service Request#</span></th>
	<th><span class="style3">Date</span></th>
	<th><span class="style3">Department</span></th>
	<th><span class="style3">Sub Activity</span></th>
</tr>

<?php

$run = mysql_query($query)
or die("Error running query: " . mysql_error());
while($row = mysql_fetch_array($run)) {
print("\n<tr><td>{$row['OPER']}</td>");
print("\n<td>{$row['ID']}</td>");
print("\n<td>{$row['UPDATED']}</td>");
print("\n<td>{$row['GRP']}</td>");
print("\n<td>{$row['C29']}</td></tr>"); }

?>
</table>
</center>
</body>
</html>

**** Note*** I haen't even began to create the export to excel code yet as I'm still battling with the query itself .....

Any help is appreciated ..... Thank you in advance

Sign In

Extract MySQL data via date range selection from html form and export to excel

Recommended Posts

arvin

Link to comment

Share on other sites

Archived

Browse

Activity

Important Information