0

OK, this should do it now:

gcrFunction.php

<?
include('./connect.php');

error_reporting(E_ALL ^ E_NOTICE);

function validateData()
{
	global $conn;
	
	$sDate=$_POST['sDate'];
	$eDate=$_POST['eDate'];
	//echo $sDate;
	//echo $eDate;

	$btn=$_POST['btnSubmit'];
		
	if(isset($btn))
	{				
		if(!$sDate)
		{
			echo '<center><font face=Verdana color=Red size="2"><b>Start Date field is empty!</b></font></center></td>
				  </tr><tr bgcolor="#ffffff"><td>
				  <table width="760" border="0" align="center" cellpadding="0" cellspacing="0" bordercolor="#FFFFFf">
				  <tr><td width="760" bgcolor="#336666" scope="col">&nbsp;</td></tr></table></td></tr><tr bgcolor="#ffffFF">
				  <td><p align="center" class="style30 style4" >&copy;ITT Department, Petrotrin. </p></td></tr></table>';
			die("Problem 1");
		}
		else if(!$eDate)
		{
			echo '<center><font face=Verdana color=Red size="2"><b>End Date field is empty!</b></font></center></td>
				  </tr><tr bgcolor="#ffffff"><td>
				  <table width="760" border="0" align="center" cellpadding="0" cellspacing="0" bordercolor="#FFFFFf">
				  <tr><td width="760" bgcolor="#336666" scope="col">&nbsp;</td></tr></table></td></tr><tr bgcolor="#ffffFF">
				  <td><p align="center" class="style30 style4" >&copy;ITT Department, Petrotrin. </p></td></tr></table>';
			die("Problem 2");
		}
		else
		{
			
			function parseCSVComments($comments) 
			{ 
				// First off escape all " and make them "" 
				$comments = str_replace('"', '""', $comments); 
				if(eregi(",", $comments) or eregi("\n", $comments)) 
				{ // Check if I have any commas or new lines 
				return '"'.$comments.'"'; // If I have new lines or commas escape them 
				} 
				else 
				{ 
					return $comments; // If no new lines or commas just return the value 
				} 
			} 
			
			// Start our query of the database 
			$query = "SELECT * FROM tsttbills where DATE_FORMAT(date, '%d-%m-%Y') >= '$sDate' and DATE_FORMAT(date, '%d-%m-%Y') <= '$eDate'";
			$sql = mysql_query($query, $conn);
			 
			//echo $query;
			$numberFields = mysql_num_fields($sql); // Find out how many fields we are fetching 
			
			if($numberFields) 
			{ // Check if we need to output anything 
				for($i=0; $i<$numberFields; $i++) 
				{ 
					// Create the headers for each column, this is the field name in the database 
					$head[] = mysql_field_name($sql, $i); 
				} 
				$headers = join(',', $head)."\n"; // Make our first row in the CSV 
			
				while($info = mysql_fetch_object($sql)) 
				{ 
					foreach($head as $fieldName) 
					{ // Loop through the array of headers as we fetch the data 
						$row[] = parseCSVComments($info->$fieldName); 
					} // End loop 
					$data .= join(',', $row)."\n"; // Create a new row of data and append it to the last row 
					$row = ''; // Clear the contents of the $row variable to start a new row 
				} 
				// Start our output of the CSV 
				$filename = $file."_".date("Y-m-d_H-i",time());
				header("Content-type: application/x-msdownload"); 
				header("Content-Disposition: attachment; filename=".$filename.".csv");
				header("Pragma: no-cache"); 
				header("Expires: 0");
				echo $headers.$data; 
			} 
			else 
			{ 
				// Nothing needed to be output. Put an error message here or something. 
				echo ("There is no data to create a CSV file."); 
			} 	
		}	//close the else
	}//close the if(isset($btn)) statement
}



?>
0

Ok, So I did a little changing in the $sql syntax from

$sql = mysql_query("SELECT * FROM tsttbills where DATE_FORMAT(date, '%d-%m-%Y') >= '$sDate' and DATE_FORMAT(date, '%d-%m-%Y') <= '$eDate'", $conn);

to

$sql = mysql_query("SELECT * FROM tsttbills where DATE_FORMAT(billDate, '%d-%m-%Y') >= '$sDate' and DATE_FORMAT(billDate, '%d-%m-%Y') <= '$eDate'", $conn);

the only problem now is that the data in the csv is not within what i selected....

I have data from only 1 month in the table, and i selected the start date to be the 1st, and the end date to be the 9th, and when i click the button 'Generate' i am getting a csv filled with data from the 10th onwards....

0

put this right under your mysql_query()

echo "SELECT * FROM tsttbills where DATE_FORMAT(billDate, '%d-%m-%Y') >= '$sDate' and DATE_FORMAT(billDate, '%d-%m-%Y') <= '$eDate'";

so we can get a look at your query including the variables.

0

the dates i entered for the search were: 1-11-2007 and 9-11-2007

these days don't have leading zeros, so your query will actually look like this

"SELECT * FROM tsttbills where DATE_FORMAT(billDate, '%e-%m-%Y') >= '$sDate' and DATE_FORMAT(billDate, '%e-%m-%Y') <= '$eDate'"

instead of this:

"SELECT * FROM tsttbills where DATE_FORMAT(billDate, '%d-%m-%Y') >= '$sDate' and DATE_FORMAT(billDate, '%d-%m-%Y') <= '$eDate'"

0

Would that require me changing the date format in the javascript or something? because there is the little calendar icon next to the input box that I would click and select the date, the date is then displayed in the input box in that format....

0

No you don't have to change anything but your query. Here is the function documentation with all of the date format symbols. The calendar function just needs to be consistent.
http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html#function_date-format

Thanks for that link....I understand now what you were trying to say before...
:):):)

Question

Could I change "SELECT * FROM tsttbills where DATE_FORMAT(billDate, '%e-%m-%Y') >= '$sDate' and DATE_FORMAT(billDate, '%e-%m-%Y') <= '$eDate'" to "SELECT * FROM tsttbills where DATE_FORMAT(billDate, '%Y-%m-%e') >= '$sDate' and DATE_FORMAT(billDate, '%Y-%m-%e') <= '$eDate'" ???

0

I am still getting the entire table of data, and the data within the dates selected...

0

I am still getting the entire table of data, and the data within the dates selected...

The only way we can know for sure is if you echo your query statement at runtime. So rather than running the query with mysql_query(), just echo it. Then post that.

0

When I echoed the query statement, I am getting a runtime error on the browser, and all the browser windows closed after...here is the code:


gcrFunction.php

<?
include('./connect.php');

error_reporting(E_ALL ^ E_NOTICE);

function validateData()
{
	global $conn;
	
	$sDate=$_POST['sDate'];
	$eDate=$_POST['eDate'];
	//echo $sDate;
	//echo $eDate;

	$btn=$_POST['btnSubmit'];
		
	if(isset($btn))
	{				
		if(!$sDate)
		{
			echo '<center><font face=Verdana color=Red size="2"><b>Start Date field is empty!</b></font></center></td>
				  </tr><tr bgcolor="#ffffff"><td>
				  <table width="760" border="0" align="center" cellpadding="0" cellspacing="0" bordercolor="#FFFFFf">
				  <tr><td width="760" bgcolor="#336666" scope="col">&nbsp;</td></tr></table></td></tr><tr bgcolor="#ffffFF">
				  <td><p align="center" class="style30 style4" >&copy;ITT Department. </p></td></tr></table>';
			die("Problem 1");
		}
		else if(!$eDate)
		{
			echo '<center><font face=Verdana color=Red size="2"><b>End Date field is empty!</b></font></center></td>
				  </tr><tr bgcolor="#ffffff"><td>
				  <table width="760" border="0" align="center" cellpadding="0" cellspacing="0" bordercolor="#FFFFFf">
				  <tr><td width="760" bgcolor="#336666" scope="col">&nbsp;</td></tr></table></td></tr><tr bgcolor="#ffffFF">
				  <td><p align="center" class="style30 style4" >&copy;ITT Department. </p></td></tr></table>';
			die("Problem 2");
		}
		else
		{
			
			function parseCSVComments($comments) 
			{ 
				// First off escape all " and make them "" 
				$comments = str_replace('"', '""', $comments); 
				if(eregi(",", $comments) or eregi("\n", $comments)) 
				{ // Check if I have any commas or new lines 
				return '"'.$comments.'"'; // If I have new lines or commas escape them 
				} 
				else 
				{ 
					return $comments; // If no new lines or commas just return the value 
				} 
			} 
			
			// Start our query of the database 
			$query = "SELECT * FROM tsttbills where DATE_FORMAT(billDate, '%e-%m-%Y') >= '$sDate' and DATE_FORMAT(billDate, '%e-%m-%Y') <= '$eDate'";
			
			echo $query;			
			
			$sql = mysql_query($query, $conn);			
			 
			//echo $sql;
			
			$numberFields = mysql_num_fields($sql); // Find out how many fields we are fetching 
			
			if($numberFields) 
			{ // Check if we need to output anything 
				for($i=0; $i<$numberFields; $i++) 
				{ 
					// Create the headers for each column, this is the field name in the database 
					$head[] = mysql_field_name($sql, $i); 
				} 
				$headers = join(',', $head)."\n"; // Make our first row in the CSV 
			
				//while($info = mysql_fetch_object($sql)) 
				//{ 	
					//print_r($info);	
					//echo "<br />";
				//}
				
				while($info = mysql_fetch_object($sql)) 
				{ 
					foreach($head as $fieldName) 
					{ // Loop through the array of headers as we fetch the data 
						$row[] = parseCSVComments($info->$fieldName); 
					} // End loop 
					$data .= join(',', $row)."\n"; // Create a new row of data and append it to the last row 
					$row = ''; // Clear the contents of the $row variable to start a new row 
				} 
				
				// Start our output of the CSV 
				/*$filename = $file."_".date("Y-m-d_H-i",time());
				header("Content-type: application/x-msdownload"); 
				header("Content-Disposition: attachment; filename=".$filename.".csv");
				header("Pragma: no-cache"); 
				header("Expires: 0");*/
				echo $headers.$data; 
			} 
			else 
			{ 
				// Nothing needed to be output. Put an error message here or something. 
				echo ("There is no data to create a CSV file."); 
			} 	
		}	//close the else
	}//close the if(isset($btn)) statement
}



?>
0

I commented off most of the code for it to echo the sql...here is the edited code:

.
.
.
			function parseCSVComments($comments) 
			{ 
				// First off escape all " and make them "" 
				$comments = str_replace('"', '""', $comments); 
				if(eregi(",", $comments) or eregi("\n", $comments)) 
				{ // Check if I have any commas or new lines 
				return '"'.$comments.'"'; // If I have new lines or commas escape them 
				} 
				else 
				{ 
					return $comments; // If no new lines or commas just return the value 
				} 
			} 
			
			// Start our query of the database 
			$query = "SELECT * FROM tsttbills where DATE_FORMAT(billDate, '%e-%m-%Y') >= '$sDate' and DATE_FORMAT(billDate, '%e-%m-%Y') <= '$eDate'";
			
			$sql = mysql_query($query, $conn);			
			 
			echo $sql;
			/*
			$numberFields = mysql_num_fields($sql); // Find out how many fields we are fetching 
			
			if($numberFields) 
			{ // Check if we need to output anything 
				for($i=0; $i<$numberFields; $i++) 
				{ 
					// Create the headers for each column, this is the field name in the database 
					$head[] = mysql_field_name($sql, $i); 
				} 
				$headers = join(',', $head)."\n"; // Make our first row in the CSV 
			
				//while($info = mysql_fetch_object($sql)) 
				//{ 	
					//print_r($info);	
					//echo "<br />";
				//}
				
				while($info = mysql_fetch_object($sql)) 
				{ 
					foreach($head as $fieldName) 
					{ // Loop through the array of headers as we fetch the data 
						$row[] = parseCSVComments($info->$fieldName); 
					} // End loop 
					$data .= join(',', $row)."\n"; // Create a new row of data and append it to the last row 
					$row = ''; // Clear the contents of the $row variable to start a new row 
				} 
				
				// Start our output of the CSV 
				//$filename = $file."_".date("Y-m-d_H-i",time());
				//header("Content-type: application/x-msdownload"); 
				//header("Content-Disposition: attachment; filename=".$filename.".csv");
				//header("Pragma: no-cache"); 
				//header("Expires: 0");
				//echo $headers.$data; 
			} 
			else 
			{ 
				// Nothing needed to be output. Put an error message here or something. 
				echo ("There is no data to create a CSV file."); 
			}*/
.
.
.

and here is the result:

Resource id #6

I should be seeing exactly 10072 records for the dates selected...
Dates selected:
Start Date = 1-11-2007
End Date = 1-11-2007

0

replace echo $sql;
with echo $query;

Here is the output:

SELECT * FROM tsttbills where DATE_FORMAT(billDate, '%e-%m-%Y') >= '1-11-2007' and DATE_FORMAT(billDate, '%e-%m-%Y') <= '1-11-2007'

0

test data

-- 
-- Table structure for table 'tsttbills'
-- 

CREATE TABLE tsttbills (
  tsttbillspk int(11) NOT NULL auto_increment,
  billname varchar(60) default NULL,
  billphone varchar(12) default NULL,
  billDate date default NULL,
  PRIMARY KEY  (tsttbillspk)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8;

-- 
-- Dumping data for table 'tsttbills'
-- 

INSERT INTO tsttbills VALUES (1, 'billy', '9895475487', '2007-12-06');
INSERT INTO tsttbills VALUES (2, 'steven', '9895475487', '2007-01-11');
INSERT INTO tsttbills VALUES (3, 'robert', '9895475487', '2007-01-11');
INSERT INTO tsttbills VALUES (4, 'nickie', '9895475487', '2007-01-11');
INSERT INTO tsttbills VALUES (5, 'robbie', '9895475487', '2007-01-11');
INSERT INTO tsttbills VALUES (6, 'malahni', '9895475487', '2007-12-06');
INSERT INTO tsttbills VALUES (7, 'bo', '9895475487', '2007-12-06');
INSERT INTO tsttbills VALUES (8, 'bill', '9895475487', '2007-12-06');
INSERT INTO tsttbills VALUES (9, 'steve', '9895475487', '2007-12-06');
INSERT INTO tsttbills VALUES (10, 'I', '9895475487', '2007-01-11');
INSERT INTO tsttbills VALUES (11, 'they', '9895475487', '2007-12-06');
INSERT INTO tsttbills VALUES (12, 'them', '9895475487', '2007-01-11');
INSERT INTO tsttbills VALUES (13, 'you', '9895475487', '2007-12-06');
INSERT INTO tsttbills VALUES (14, 'me', '9895475487', '2007-01-11');
INSERT INTO tsttbills VALUES (15, 'ed', '9895475487', '2007-12-06');
INSERT INTO tsttbills VALUES (16, 'joe', '9895475487', '2007-01-11');
INSERT INTO tsttbills VALUES (17, 'bill', '9895475487', '2007-12-06');
INSERT INTO tsttbills VALUES (18, 'bob', '9895475487', '2007-01-11');
SELECT *
FROM tsttbills
WHERE DATE_FORMAT( billDate, '%c-%d-%Y' ) >= '1-11-2007' AND DATE_FORMAT( billDate, '%c-%d-%Y' ) <= '1-11-2007';

'%c-%d-%Y' works for 'm-dd-yyyy'
brought back 9 rows

SELECT * FROM tsttbills where DATE_FORMAT(billDate, '%e-%m-%Y') >= '1-11-2007' and DATE_FORMAT(billDate, '%e-%m-%Y') <= '1-11-2007'

'%e-%m-%Y' works for 'dd-mm-yyyy' brought back 0 rows because this is the wrong format

you need to study your date picker and match that to the data formats with in this table
http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html#function_date-format

0

So this is my changed chunk of code:

.
.
.
include('./connect.php');
			global $conn;
			
			// Start our query of the database 
			$query = "SELECT * FROM tsttbills where DATE_FORMAT(billDate, ''%m-%d-%Y') >= '$sDate' and DATE_FORMAT(billDate, ''%m-%d-%Y') <= '$eDate'";
			
			$sql = mysql_query($query, $conn);			
			 
			//echo $query;
			echo "Result:<br />";
			echo $sql;
.
.
.

and nothing shows for the result...

0

did you update your html form inputs and js calendars to be like this?

<input id="demo1" type="text" size="25"><a href="javascript:NewCal('demo1','mmddyyyy')"><img src="images/cal.gif" width="16" height="16" border="0" alt="Pick a date"></a>
0

yes i did, this is what I see after clicking the generate button...

Result:

0

According to the documentation of that date picker, that should work. Are you sure that nothing is modifying $sDate and $eDate prior to your query. What you may want to do is this. Change the action on your form to post to another file and in that file just put this.

<?
echo "SELECT * FROM tsttbills where DATE_FORMAT(billDate, '%e-%m-%Y') >= '" . $_POST['sDate'] . "' and DATE_FORMAT(billDate, '%e-%m-%Y') <= '" . $_POST['eDate'] . "'";
?>

This way we can eliminate the date picker as the issue.

0

I have done what you said to, and here is the result:

SELECT * FROM tsttbills where DATE_FORMAT(billDate, '%e-%m-%Y') >= '11-1-2007' and DATE_FORMAT(billDate, '%e-%m-%Y') <= '11-1-2007'

that is what was echoed...

0

can't tell if 11-1-2007 is mm-d-yyyy or if it is dd-m-yyyy, let's use a date that will show us exactly what we are dealing with here. Do the same thing, but use the date February 1st, 2007 and post those results. This should pretty much tell us what we need to know like if months has leading 0s and if days has leading 0s etc...

0

Er....well, I changed this:

my input box on the form

<a href="javascript:NewCal('sDate','[U]mmmddyyyy[/U]')"><img src="images/cal.gif" width="16" height="16" border="0" alt="Pick a Start Date"></a>

and echo statement:

echo "SELECT * FROM tsttbills where DATE_FORMAT(billDate, [U]'%b-%e-%Y'[/U]) >= '" . $_POST['sDate'] . "' and DATE_FORMAT(billDate, [U]'%b-%e-%Y'[/U]) <= '" . $_POST['eDate'] . "'";

result:

SELECT * FROM tsttbills where DATE_FORMAT(billDate, '%b-%e-%Y') >= 'Nov-1-2007' and DATE_FORMAT(billDate, '%b-%e-%Y') <= 'Nov-1-2007'
0

Ok, that's fine, leave it like that and adjust your query to that in your script:

$query = "SELECT * FROM tsttbills where DATE_FORMAT(billDate, '%b-%e-%Y') >= '$sDate' and DATE_FORMAT(billDate, '%b-%e-%Y') <= '$eDate'";
0

does this

SELECT * FROM tsttbills where DATE_FORMAT(billDate, '%b-%e-%Y') >= 'Nov-1-2007' and DATE_FORMAT(billDate, '%b-%e-%Y') <= 'Nov-1-2007'

get any results when you stick it in your database manager?

0

In my DB Manager.......you mean MySQL Query Browser?

if that's what you mean.....then here is the error message:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT * FROM tsttbills where DATE_FORMAT(billDate, '%b-%e-%Y') >= 'Nov-1-2007' ' at line 1

Error No. 1064

0

here is the error message:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT * FROM tsttbills where DATE_FORMAT(billDate, '%b-%e-%Y') >= 'Nov-1-2007' ' at line 1

Error No. 1064

0

Worked perfectly fine for me in phpmyadmin(db manager program shouldn't matter), here is my test database and data and the query that I ran.
table:

CREATE TABLE `tsttbills` (
  `tsttbillspk` int(11) NOT NULL auto_increment,
  `billname` varchar(60) default NULL,
  `billphone` varchar(12) default NULL,
  `billDate` date default NULL,
  PRIMARY KEY  (`tsttbillspk`)
)

data:

INSERT INTO `tsttbills` VALUES (1, 'billy', '9895475487', '2007-12-06');
INSERT INTO `tsttbills` VALUES (2, 'steven', '9895475487', '2007-11-01');
INSERT INTO `tsttbills` VALUES (3, 'robert', '9895475487', '2007-11-01');
INSERT INTO `tsttbills` VALUES (4, 'nickie', '9895475487', '2007-11-01');
INSERT INTO `tsttbills` VALUES (5, 'robbie', '9895475487', '2007-11-01');
INSERT INTO `tsttbills` VALUES (6, 'malahni', '9895475487', '2007-12-06');
INSERT INTO `tsttbills` VALUES (7, 'bo', '9895475487', '2007-12-06');
INSERT INTO `tsttbills` VALUES (8, 'bill', '9895475487', '2007-12-06');
INSERT INTO `tsttbills` VALUES (9, 'steve', '9895475487', '2007-12-06');
INSERT INTO `tsttbills` VALUES (10, 'I', '9895475487', '2007-11-01');
INSERT INTO `tsttbills` VALUES (11, 'they', '9895475487', '2007-12-06');
INSERT INTO `tsttbills` VALUES (12, 'them', '9895475487', '2007-11-01');
INSERT INTO `tsttbills` VALUES (13, 'you', '9895475487', '2007-12-06');
INSERT INTO `tsttbills` VALUES (14, 'me', '9895475487', '2007-11-01');
INSERT INTO `tsttbills` VALUES (15, 'ed', '9895475487', '2007-12-06');
INSERT INTO `tsttbills` VALUES (16, 'joe', '9895475487', '2007-11-01');
INSERT INTO `tsttbills` VALUES (17, 'bill', '9895475487', '2007-12-06');
INSERT INTO `tsttbills` VALUES (18, 'bob', '9895475487', '2007-11-01');

query:

SELECT * FROM tsttbills where DATE_FORMAT(billDate, '%b-%e-%Y') >= 'Nov-1-2007' and DATE_FORMAT(billDate, '%b-%e-%Y') <= 'Nov-1-2007'

results:
2 steven 9895475487 2007-11-01
3 robert 9895475487 2007-11-01
4 nickie 9895475487 2007-11-01
5 robbie 9895475487 2007-11-01
10 I 9895475487 2007-11-01
12 them 9895475487 2007-11-01
14 me 9895475487 2007-11-01
16 joe 9895475487 2007-11-01
18 bob 9895475487 2007-11-01

This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.