select info from data base where the info is between 2 dates

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
gasoga
Forum Commoner
Posts: 36
Joined: Thu Apr 17, 2003 4:15 am
Location: Ireland

select info from data base where the info is between 2 dates

Post by gasoga »

Hey

I want to select information from my database on a certain company between certain dates , the dates on the form are in drop down menu form.
I have a function which creates this form and a function which when the dates are submitted concantanates the date together. It only seems to be taking the first dat though and not the second and therefore wont diplay the results , would much appreciate if someone could take a look at my code and see what im doin worng or has any suggestion!! Thanx!

Functions:

Code: Select all

<?php
function WriteDateSelect($BeginYear = 0, 
                         $EndYear = 0, 
                         $IsPosted = true,
                         $Prefix = '')
{
  if (! $BeginYear)
  {
    $BeginYear = date('Y');
  }
		
  if (! $EndYear)
  {
    $EndYear = $BeginYear;
  }
	
  $Year = $IsPosted 
          ? (int) $_POST[$Prefix . 'Year']
          : (int) $_GET[$Prefix . 'Year'];
  $Month = $IsPosted 
          ? (int) $_POST[$Prefix . 'Month']
          : (int) $_GET[$Prefix . 'Month'];
  $Day = $IsPosted 
          ? (int) $_POST[$Prefix . 'Day']
          : (int) $_GET[$Prefix . 'Day'];
	
  echo '<select name="', $Prefix, 'Year">
         ';
	
  for ($i = $BeginYear; $i <= $EndYear; $i++)
  {
    echo '<option ';
		
    if ($i == $Year)
      echo 'selected="yes"';
			
    echo '>', $i, '</option>
         ';
  }
	
  echo '</select>-
        <select name="', $Prefix, 'Month">
          ';	

  for ($i = 1; $i <= 12; $i++)
  {
    echo '<option ';
		
    if ($i == $Month)
      echo 'selected="yes"';
			
    echo '>', $i, '</option>
         ';
  }

  echo '</select>-
        <select name="', $Prefix, 'Day">
          ';	

  for ($i = 1; $i <= 31; $i++)
  {
    echo '<option ';
		
    if ($i == $Day)
      echo 'selected="yes"';
			
    echo '>', $i, '</option>
         ';
  }

  echo '</select>
       ';
  return;
}


function GetDateSelectString($IsPosted = true,
                             $Prefix = '')
{
  if ($IsPosted)
  {
    return (int) $_POST[$Prefix . 'Year']
           . '-' . (int) $_POST[$Prefix . 'Month']
           . '-' . (int) $_POST[$Prefix . 'Day'];
  }

  return (int) $_GET[$Prefix . 'Year']
         . '-' . (int) $_GET[$Prefix . 'Month']
         . '-' . (int) $_GET[$Prefix . 'Day'];
}




?>
code for the form :

Code: Select all

&lt;FORM ACTION='GenerateReports.php' METHOD ='POST'ENCTYPE="multipart/form-data"&gt;
	&lt;TABLE ALIGN='center' BORDER='0' CELLSPACING='0' CELLPADDING='6' CLASS='TextField2' WIDTH='300'&gt;
	&lt;br&gt;
   								
								&lt;TR&gt;
									&lt;TD ALIGN='LEFT' VALIGN=MIDDLE COLSPAN='3'&gt;
							   			&lt;b&gt;Generate the Report you want by filling in the necessary fields below&lt;/b&gt;
							   		&lt;/TD&gt;&lt;/tr&gt;
									&lt;TR&gt;
									&lt;TD ALIGN='LEFT' VALIGN=MIDDLE COLSPAN='3'&gt;
							   			&lt;b&gt;Please Fill in &lt;/b&gt;
							   		&lt;/TD&gt;&lt;/tr&gt;
   								
						
								&lt;tr&gt;&lt;td align='left' colspan='8'&gt;Company Name:&lt;/td&gt;&lt;/tr&gt;
								&lt;tr&gt;
								&lt;td align = left&gt;
								&lt;select name='company_name'&gt;
								&lt;option value="company"&gt;company
								&lt;option value="company"&gt;company
								
								&lt;/select&gt;&lt;/tr&gt;&lt;/td&gt;
						&lt;TR&gt;
									&lt;TD ALIGN='LEFT' VALIGN=MIDDLE colspan='8'&gt;
							   			Period of time for which you want the report:
							   		&lt;/TD&gt;
									&lt;/tr&gt;
									&lt;tr&gt;
									&lt;td CLASS='Title'&gt;From&lt;/td&gt;
									&lt;td nowrap valign=top&gt;

		&lt;?php WriteDateSelect(2000, 2004,true,'begin'); ?&gt; 
		&lt;/td&gt;&lt;/tr&gt;

			&lt;tr&gt;
			&lt;td CLASS='Title'&gt;To&lt;/td&gt;
									&lt;td nowrap valign=top&gt;
									&lt;?php WriteDateSelect(2000, 2004,true,'end'); ?&gt; 
		&lt;/td&gt;&lt;/tr&gt;

			&lt;TR&gt;
									&lt;TD ALIGN='CENTER' VALIGN=MIDDLE COLSPAN='2'&gt;
										&lt;INPUT CLASS='TextField' TYPE='SUBMIT' NAME='Generate Report' VALUE='Generate Report'&gt;
									&lt;/TD&gt;
								&lt;/TR&gt;
								
					
							&lt;?
							
							if ($ErrorCode != "")
								&#123;
   								echo "&lt;TR&gt;\n"
   									."	&lt;TD CLASS='ErrorCode' ALIGN='CENTER' VALIGN=MIDDLE COLSPAN='2'&gt;\n"
									."		$ErrorCode&lt;BR&gt;\n"
   									."	&lt;/TD&gt;\n"
   									."&lt;/TR&gt;\n";
								&#125;
							?&gt;
							&lt;/TABLE&gt;
						&lt;/FORM&gt;
						
						
						
					&lt;/TD&gt;
				&lt;/TR&gt;
sql code:

Code: Select all

<?php

db_connect();

	
	
	
	$PostedDate = GetDateSelectString();
	$PostedDate2 = GetDateSelectString();

	$Q12 = "select * from calls_logged where calls_logged.company_name = '$company_name' and calls_logged.Entered >='$PostedDate' and calls_logged.Entered <= '$PostedDate2' order by PK_Call_ID "; 
	$R12 = mysql_query($Q12)or die(mysql_error());
	$result = mysql_fetch_array($R12);
		
	echo("<b>Previous Calls you have logged:</b>");
	echo "<table border='1' align = 'left' CELLSPACING='6' CELLPADDING='6' CLASS='TextField2' WIDTH='300'> \n ";
	echo "<br>";
	echo(" $date2");

	if($result != 0)
	{
	$Call_ID=$result[PK_Call_ID];
	print("<tr>");
	echo "<tr align ='left'><th>Call ID</th>";
	print("<td align ='right'>$Call_ID</td></tr>");
	$emp_username=$result[emp_username];
	echo "<tr align ='left'><th>Employee User Name</th>";
	print("<td align ='right'>$emp_username</td></tr>");
	$cust_username=$result[cust_username];
	echo "<tr align ='left'><th>Customer User Name</th>";
	print("<td align ='right'>$cust_username</td></tr>");
	$company_name=$result[company_name];
	echo "<tr align ='left'><th>Company Name</th>";
	print("<td align ='right'>$company_name</td></tr>");
	$date=$result[date];
	echo "<tr align ='left'><th>Date</th>";
	print("<td align ='right'>$date</td></tr>");
	$time=$result[time];
	echo "<tr align ='left'><th>Time</th>";
	print("<td align ='right'>$time</td></tr>");
	$call_type=$result[call_type];
	echo "<tr align ='left'><th>Call Type</th>";
	print("<td align ='right'>$call_type</td></tr>");
	$description=$result[description];
	echo "<tr align ='left'><th>Description</th>";
	print("<td align ='right'>$description</td></tr>");
	$priority=$result[priority];
	echo "<tr align ='left'><th>Priority</th>";
	print("<td align ='right'>$priority</td></tr>");
	$res_call_imm=$result[PK_Solution_ID];
	echo "<tr align ='left'><th>Solution ID</th>";
	print("<td align ='right'>$res_call_imm</td></tr>");
	$assigned_to=$result[FK_Call_ID];
	echo "<tr align ='left'><th>Call ID</th>";
	print("<td align ='right'>$assigned_to</td></tr>");
	$resolved=$result[Assigned_to];
	echo "<tr align ='left'><th>Assigned To</th>";
	print("<td align ='right'>$resolved</td></tr>");
	$solution2=$result[Description_solution];
	echo "<tr align ='left'><th>Solution</th>";
	print("<td align ='right'>$solution2</td></tr>");
	$call_closed_date=$result[Call_closed];
	echo "<tr align ='left'><th>Close Call :: Date(YYYY-MM-DD)</th>";
	print("<td align ='right'>$call_closed_date</td></tr>");
	print("</tr>");
	
	}
	echo "</table>\n";
	echo("<br>");

	


?>
sjon
Forum Newbie
Posts: 6
Joined: Thu Jun 26, 2003 5:11 pm
Location: EU.NL

Post by sjon »

Easy... :)

Code: Select all

<?php
   $isPosted = (strtolower($_SERVER['REQUEST_METHOD']) == 'post');
   $PostedDate = GetDateSelectString($isPosted, 'begin');
   $PostedDate2 = GetDateSelectString($isPosted, 'end');
?>


Sjon.
gasoga
Forum Commoner
Posts: 36
Joined: Thu Apr 17, 2003 4:15 am
Location: Ireland

Post by gasoga »

thanx a million!!! that worked!! yay!! your a star!! :)
Post Reply