Please Help MYSQL to Excel Problem

[jay0316]

Hi, I have 3 tables.

"dealers" Structure

Dealer_ID, Name, Address , ect

 

"dealers_visit" Structure

ID, Date

 

"dealers_visit_lookup" Structure

ID, Dealer_ID, Date_ID

 

The tables allow us to record multiple visit dates for each store.  What we'd like to have is a list of all the stores with the visit dates on the same row as the stores and put them into Quarter1, Quarter2, Quarter3, Quarter4 columns. 

 

So, I'd like to get all of these into excel spreadsheet.  As of right now I get results with a company's info listed multiple times for each visit that was made.  Which becomes a problem as more and more visits are made. Is there a way to consolidate the company information and keep the visits?

 

I am not an advanced php user, but found the code below to print to an excel file. 

 

Here is my excel doc code.

<?php



//EDIT YOUR MySQL Connection Info:
$DB_Server = "";		//your MySQL Server 
$DB_Username = "";				 //your MySQL User Name 
$DB_Password = "";				//your MySQL Password 
$DB_DBName = "";				//your MySQL Database Name 
$DB_TBLName = "";				//your MySQL Table Name 
//$DB_TBLName,  $DB_DBName, may also be commented out & passed to the browser
//as parameters in a query string, so that this code may be easily reused for
//any MySQL table or any MySQL database on your server

//DEFINE SQL QUERY:
//you can use just about ANY kind of select statement you want - 
//edit this to suit your needs!
$sql = "SELECT *
FROM dealers , dealers_visit_lookup , dealers_visit 

WHERE 
(dealers.Dealer_ID = dealers_visit_lookup.Dealer_ID )AND (dealers_visit_lookup.Date_ID =dealers_visit.ID )  ORDER BY `dealers`.`Name`  DESC";

//Optional: print out title to top of Excel or Word file with Timestamp
//for when file was generated:
//set $Use_Titel = 1 to generate title, 0 not to use title
$Use_Title = 1;
//define date for title: EDIT this to create the time-format you need
$now_date = date('m-d-Y H:i');
//define title for .doc or .xls file: EDIT this if you want
$title = "Dump For Table $DB_TBLName from Database $DB_DBName on $now_date";
/*

Leave the connection info below as it is:
just edit the above.

(Editing of code past this point recommended only for advanced users.)
*/
//create MySQL connection
$Connect = @mysql_connect($DB_Server, $DB_Username, $DB_Password)
	or die("Couldn't connect to MySQL:<br>" . mysql_error() . "<br>" . mysql_errno());
//select database
$Db = @mysql_select_db($DB_DBName, $Connect)
	or die("Couldn't select database:<br>" . mysql_error(). "<br>" . mysql_errno());
//execute query
$result = @mysql_query($sql,$Connect)
	or die("Couldn't execute query:<br>" . mysql_error(). "<br>" . mysql_errno());

//if this parameter is included ($w=1), file returned will be in word format ('.doc')
//if parameter is not included, file returned will be in excel format ('.xls')
if (isset($w) && ($w==1))
{
	$file_type = "msword";
	$file_ending = "doc";
}else {
	$file_type = "vnd.ms-excel";
	$file_ending = "xls";
}
//header info for browser: determines file type ('.doc' or '.xls')
header("Content-Type: application/$file_type");
header("Content-Disposition: attachment; filename=animalbehavior.$file_ending");
header("Pragma: no-cache");
header("Expires: 0");

/*	Start of Formatting for Word or Excel	*/

if (isset($w) && ($w==1)) //check for $w again
{
	/*	FORMATTING FOR WORD DOCUMENTS ('.doc')   */
	//create title with timestamp:
	if ($Use_Title == 1)
	{
		echo("$title\n\n");
	}
	//define separator (defines columns in excel & tabs in word)
	$sep = "\n"; //new line character

	while($row = mysql_fetch_row($result))
	{
		//set_time_limit(60); // HaRa
		$schema_insert = "";
		for($j=0; $j<mysql_num_fields($result);$j++)
		{
		//define field names
		$field_name = mysql_field_name($result,$j);
		//will show name of fields
		$schema_insert .= "$field_name:\t";
			if(!isset($row[$j])) {
				$schema_insert .= "NULL".$sep;
				}
			elseif ($row[$j] != "") {
				$schema_insert .= "$row[$j]".$sep;
				}
			else {
				$schema_insert .= "".$sep;
				}
		}
		$schema_insert = str_replace($sep."$", "", $schema_insert);
		$schema_insert .= "\t";
		print(trim($schema_insert));
		//end of each mysql row
		//creates line to separate data from each MySQL table row
		print "\n----------------------------------------------------\n";
	}
}else{
	/*	FORMATTING FOR EXCEL DOCUMENTS ('.xls')   */
	//create title with timestamp:
	if ($Use_Title == 1)
	{
		echo("$title\n");
	}
	//define separator (defines columns in excel & tabs in word)
	$sep = "\t"; //tabbed character

	//start of printing column names as names of MySQL fields
	for ($i = 0; $i < mysql_num_fields($result); $i++)
	{
		echo mysql_field_name($result,$i) . "\t";
	}
	print("\n");
	//end of printing column names

	//start while loop to get data
	while($row = mysql_fetch_row($result))
	{
		//set_time_limit(60); // HaRa
		$schema_insert = "";
		for($j=0; $j<mysql_num_fields($result);$j++)
		{
			if(!isset($row[$j]))
				$schema_insert .= "NULL".$sep;
			elseif ($row[$j] != "")
				$schema_insert .= "$row[$j]".$sep;
			else
				$schema_insert .= "".$sep;
		}
		$schema_insert = str_replace($sep."$", "", $schema_insert);
		//following fix suggested by Josue (thanks, Josue!)
		//this corrects output in excel when table fields contain \n or \r
		//these two characters are now replaced with a space
		$schema_insert = preg_replace("/\r\n|\n\r|\n|\r/", " ", $schema_insert);
		$schema_insert .= "\t";
		print(trim($schema_insert));
		print "\n";
	}
}

?>

[Brian W]

http://www.tizag.com/mysqlTutorial/mysqlsum.php

or you can use code to group them and add up to find out how many visits.

[jay0316]

Hey Brian,

 

Thanks for the reply.

 

I'm aware of using Group By to group the results together.  However, the Sum will not work because I'm not doing a total number of visits.  It needs to show each date the store was visited.

 

So right now I'm getting a list like this:

Dealer 1  | 3-12-08

Dealer 2  | 2-20-09

Dealer 2  | 3-02-09

Dealer 2  | 3-05-09

Dealer 3  | 3-01-09

 

What I'd like to get is:

Dealer 1  | 3-12-08

Dealer 2  | 2-20-09 | 3-02-09 | 3-05-09

Dealer 3  | 3-01-09

[Brian W]

Okay, don't group or use sum, my mistake. Order them by dealer (or unique id).

In the loop, only go to the next row and make the first column the dealer if the dealer id or whatever you are using to identify them changed.

[jay0316]

I think I understand what your saying.  I just don't understand how to apply it to code that makes the excel document.

 

Are you saying that I would modify these lines:

 

//start while loop to get data
      while($row = mysql_fetch_row($result))
      {
         //set_time_limit(60); // HaRa
         $schema_insert = "";
         for($j=0; $j<mysql_num_fields($result);$j++)
         {
            if(!isset($row[$j]))
               $schema_insert .= "NULL".$sep;
            elseif ($row[$j] != "")
               $schema_insert .= "$row[$j]".$sep;
            else
               $schema_insert .= "".$sep;
         }

 

Thanks for the help.