Date range form based query

arvin1

G'day,

I'm kinda new to PHP ... but here goes....

I would like to create a php form where users can select a date range ("From" and "To") and employee name, click the submit button to run a query and display the results in webpage.

how would I do this ???.... I have been able to connect to the MySQL tables and run basic ELECT queries

Any assistance is appreciated ...

Thank you in advance

bradgrafelman

How much can you do? What does your table structure look like?

It's not a complicated process, but there can be a lot to explain depending upon how much you do or do not know. Show us what you have so far and we can try to help you fill in the pieces.

arvin1

My apologize for the delay ......

Here is my html

<body>
<form name="srreports" method="post" action="rptgen.php"> 
<p>Select Employee Name 
<select name=oper>
<option></option>
<option value=Admin>Admin</option>
<option value=Annette>Annette</option>
<option value=Barb>Barb</option>
<option value=Belinda>Belinda</option>
<option value=Bernadine>Bernadine</option>
<option value=CSGMGR>CSGMGR</option>
<option value=Carole>Carole</option>
<option value=Debbie>Debbie</option>
<option value=Dolly>Dolly</option>
<option value=Donna>Donna</option>
<option value=Etta>Etta</option>
<option value=Gjeanson>Gjeanson</option>
<option value=Heather>Heather</option>
<option value=HeatherA>HeatherA</option>
<option value=Hkaita>Hkaita</option>
<option value=Jackie>Jackie</option>
<option value=Jody>Jody</option>
<option value=Kelly>Kelly</option>
<option value=Lea>Lea</option>
<option value=Lola>Lola</option>
<option value=Lormiston>Lormiston</option>
<option value=Marilyn>Marilyn</option>
<option value=Sandi>Sandi</option>
<option value=Smanness>Smanness</option>
<option value=Teresa>Teresa</option>
<option value=Terry>Terry</option>
<option value=UAT>UAT</option>
<option value=Vickie>Vickie</option>
<option value=admin>admin</option>
<option value=csgtest>csgtest</option>
<option value=debbie1>debbie1</option>
<option value=debbie2>debbie2</option>
<option value="-">-- none --</option>
</select>

</p> 
<p> 
<select name=drsday>
<option></option>
<option>1</option>
<option>2</option>
<option>3</option>
<option>4</option>
<option>5</option>

<option>6</option>
<option>7</option>
<option>8</option>
<option>9</option>
<option>10</option>
<option>11</option>
<option>12</option>
<option>13</option>
<option>14</option>

<option>15</option>
<option>16</option>
<option>17</option>
<option>18</option>
<option>19</option>
<option>20</option>
<option>21</option>
<option>22</option>
<option>23</option>

<option>24</option>
<option>25</option>
<option>26</option>
<option>27</option>
<option>28</option>
<option>29</option>
<option>30</option>
<option>31</option>
</select>

<select name=drsmon>
<option></option>
<option value=0>Jan</option>
<option value=1>Feb</option>
<option value=2>Mar</option>
<option value=3>Apr</option>
<option value=4>May</option>
<option value=5>Jun</option>
<option value=6>Jul</option>
<option value=7>Aug</option>

<option value=8>Sep</option>
<option value=9>Oct</option>
<option value=10>Nov</option>
<option value=11>Dec</option>
</select>
<select name=drsyear>
<option></option>
<option value=104>2004</option>
<option value=105>2005</option>
<option value=106>2006</option>

<option value=107>2007</option>
<option value=108>2008</option>
<option value=109>2009</option>
</select>
&nbsp;-&nbsp;<select name=dreday>
<option></option>
<option>1</option>
<option>2</option>
<option>3</option>
<option>4</option>

<option>5</option>
<option>6</option>
<option>7</option>
<option>8</option>
<option>9</option>
<option>10</option>
<option>11</option>
<option>12</option>
<option>13</option>

<option>14</option>
<option>15</option>
<option>16</option>
<option>17</option>
<option>18</option>
<option>19</option>
<option>20</option>
<option>21</option>
<option>22</option>

<option>23</option>
<option>24</option>
<option>25</option>
<option>26</option>
<option>27</option>
<option>28</option>
<option>29</option>
<option>30</option>
<option>31</option>

</select>
<select name=dremon>
<option></option>
<option value=0>Jan</option>
<option value=1>Feb</option>
<option value=2>Mar</option>
<option value=3>Apr</option>
<option value=4>May</option>
<option value=5>Jun</option>
<option value=6>Jul</option>

<option value=7>Aug</option>
<option value=8>Sep</option>
<option value=9>Oct</option>
<option value=10>Nov</option>
<option value=11>Dec</option>
</select>
<select name=dreyear>
<option></option>
<option value=104>2004</option>
<option value=105>2005</option>

<option value=106>2006</option>
<option value=107>2007</option>
<option value=108>2008</option>
<option value=109>2009</option>
</select>

</p>
<p>
<input type="submit" name="Submit" value="Submit"> 
</p> 
</form> 


</body>
</html>

and here is the php script that I'm trying to create

<? 
$host = "dbhost"; 
$user = "dbuid"; 
$pass = "dbpass"; 
$dbname = "mydbname"; 

$connection = mysql_connect($host,$user,$pass) or die (mysql_errno().": ".mysql_error()."<BR>"); 
mysql_select_db($dbname); 

$sql = "select * from tickets where OPER=" . $_POST['OPER'] . " and oper='" . $_POST['OPER'] . "'"; 
echo $sql; 

$query = mysql_query($sql); 


while ($row = mysql_fetch_array($query)) {  echo "<p>",$row['OPER'],": ",$row['OPER']; }
?>

arvin1

Okay ...so I have been playing around in PHP ... and have come a long way in one week🙂

I have created my php script that connects to the db and runs a query .... the I'm have is ... the query returns only one complete row of values .... and the other rows only display the 1st 3 values .....

also .... how can i get the query output to display in a table with titles and such ....

here is my php script

$connection = mysql_connect($host,$user,$pass) or die (mysql_errno().": ".mysql_error()."<BR>"); 
mysql_select_db($dbname); 
echo "You are connected to the Db"."<BR>"."<BR>";

echo "This is the query that ran"."<BR>"."<BR>";
$sql = 'SELECT `tickets` . `UPDATED` , `tickets` . `ID` , `tickets` . `OPER` , `tickets` . `C31` , `tickets` . `C32` , `tickets` . `C34` , `tickets` . `C28` FROM tickets ORDER BY `tickets` . `UPDATED` DESC LIMIT 0, 30 '; 
echo $sql."<BR>"."<BR>"; 

$query = mysql_query($sql); 

echo "Here is the query output"."<BR>"."<BR>";
while ($row = mysql_fetch_array($query)) {  echo "<p>",$row['UPDATED']," ",$row['ID']," ",$row['OPER']," ",$row['C31']," ",$row['C32']," ",$row['C32']," ",$row['C34']," ",$row['C28']; }
?>

Any help is appreciate ...thank you in advance

Arvin

arvin1

Hello,

Just wondering if anyone can please assist me with this still ...please and thank you ?

jwagner

arvin1;10919128 wrote:
Okay ...so I have been playing around in PHP ... and have come a long way in one week🙂

I have created my php script that connects to the db and runs a query .... the I'm have is ... the query returns only one complete row of values .... and the other rows only display the 1st 3 values .....

also .... how can i get the query output to display in a table with titles and such ....

here is my php script
$connection = mysql_connect($host,$user,$pass) or die (mysql_errno().": ".mysql_error()." "); 
mysql_select_db($dbname); 
echo "You are connected to the Db"." "." ";

echo "This is the query that ran"." "." ";
$sql = 'SELECT `tickets` . `UPDATED` , `tickets` . `ID` , `tickets` . `OPER` , `tickets` . `C31` , `tickets` . `C32` , `tickets` . `C34` , `tickets` . `C28` FROM tickets ORDER BY `tickets` . `UPDATED` DESC LIMIT 0, 30 '; 
echo $sql." "." "; 

$query = mysql_query($sql); 

echo "Here is the query output"." "." ";
while ($row = mysql_fetch_array($query)) { echo "",$row['UPDATED']," ",$row['ID']," ",$row['OPER']," ",$row['C31']," ",$row['C32']," ",$row['C32']," ",$row['C34']," ",$row['C28']; }
?> 
Any help is appreciate ...thank you in advance

Arvin

Seems like you are using commas in your echo statement instead of periods.
Seems like you are not running any SQL commands to filter out the results by date
You need to convert from the Date format recieved from the form to a DATETIME format used by MySQL

arvin1

Okay ..... I have revised the code for both ...the html form and php script ....

CURRENT ISSUE

If I select my from and to date on the html form and click submit ... the php script goes wacko .... (Internal 500 error)

if I hard code the from and to dates in to the php code ...the script runs fine ...... confused .... a lil .... I guess what would like to know is, how to refer to the html form input feilds in the sql query in php?

Here is the new html form

  <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>

Here if the PHP code

<?php

$myID = $_GET['id'];

$server   = 'sql5c10a.megasqlservers.com:3306';
$username = 'devcsgmtsn705589';
$password = 'r3c3v6';
$database = 'arvin_dev_csg_mts_net';

$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>"); }

?>

Please help ... and thank you in advance

arvin1

Okay ..... I have revised the code for both ...the html form and php script ....

CURRENT ISSUE

If I select my from and to date on the html form and click submit ... the php script goes wacko .... (Internal 500 error)

Here is the new html form

  <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>

Here if the PHP code

<?php

$myID = $_GET['id'];

$server   = 'host';
$username = 'user';
$password = 'pass';
$database = 'db';

$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>"); }

?>

Please help ... and thank you in advance

arvin1

LOL ......

Thank you bradgrafelman for removing the db info ... my bad ...I apologize

bradgrafelman

No need to apologize... I just figured there was no need to share it with the world. 🙂

I haven't forgotten about this thread - I promise; I've just been busy lately between life and juggling multiple jobs at once. If you're still having issues late tonight, I'll try to pop on after work and read through the thread and (hopefully) offer some insight.