Page 1 of 1

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

Posted: Thu Jul 17, 2003 5:39 am
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>");

	


?>

Posted: Thu Jul 17, 2003 5:50 am
by sjon
Easy... :)

Code: Select all

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


Sjon.

Posted: Thu Jul 17, 2003 5:56 am
by gasoga
thanx a million!!! that worked!! yay!! your a star!! :)