Jump to content

display selected fields from multiple mysql table


g.sobhani

Recommended Posts

hi, i'm new to php. i've got a problem.

I've 3 tables and table has following content

 

table 1

id  name  email  address  phone  execution_date  executor_name  web_address

 

 

table 2

id  name  email  address  phone  execution_date  executor_name  project_title

 

 

table 3

id  name  email  address  phone  execution_date  executor_name  reviewer

 

I want to export these table to and spreadsheet (.xls) with some criteria

 

a form will assign which data will published at the spreadsheet with these criteria

#all data can be exported from three tables

#some column can be selected from three tables

#some or all data can be selected from individual table

 

i've implement a script for this but it didn't meet my requirements. Can anyone help?

 

here is my script

 

<?php

$DB_Server = "localhost";		//your MySQL Server 
$DB_Username = "root";				 //your MySQL User Name 
$DB_Password = "pass";				//your MySQL Password 
$DB_DBName = "mydb";				//your MySQL Database Name 
$search_from_date = $_POST['start_date'];
$search_to_date = $_POST['end_date'];
$Connect = @mysql_connect($DB_Server, $DB_Username, $DB_Password)
or die("Couldn't connect to MySQL:<br>" . mysql_error() . "<br>" . mysql_errno());
$Db = @mysql_select_db($DB_DBName, $Connect)
or die("Couldn't select database:<br>" . mysql_error(). "<br>" . mysql_errno());
$now_date = date('m-d-Y H:i');

if(($_POST['typer_of_report'] == 'rrc_report')  || ($_POST['typer_of_report'] ==  'all_report'))
{
$DB_TBLName = 'rrc_record';
}

if(($_POST['typer_of_report'] == 'erc_report')  || ($_POST['typer_of_report'] ==  'all_report'))
{
$DB_TBLName2 = 'erc_record';
}

if(($_POST['typer_of_report'] == 'aeec_report')  || ($_POST['typer_of_report'] ==  'all_report'))
{
$DB_TBLName3 = 'aeec_record';
}



$file_type = "vnd.ms-excel";
$file_ending = "xls";
//}
header("Content-Type: application/$file_type");
header("Content-Disposition: attachment; filename=protocol_report.$file_ending");
header("Pragma: no-cache");
header("Expires: 0");

if($DB_TBLName)
{
$sql = "Select * from ".$DB_TBLName." where execution_date >= '".$search_from_date."' and 
			execution_date <= '".$search_to_date."' order by execution_date desc";
$Use_Title = 1;


$title = "Report for $DB_TBLName on $now_date";
$result = @mysql_query($sql,$Connect)
or die("Couldn't execute query:<br>" . mysql_error(). "<br>" . mysql_errno());

if ($Use_Title == 1)
{
	echo("$title\n");
}
$sep = "\t"; //tabbed character
for ($i = 0; $i < mysql_num_fields($result); $i++)
{
	echo mysql_field_name($result,$i) . "\t";
}
print("\n");
while($row = mysql_fetch_row($result))
{
	$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);
	$schema_insert = preg_replace("/\r\n|\n\r|\n|\r/", " ", $schema_insert);
	$schema_insert .= "\t";
	print(trim($schema_insert));
	print "\n";
}
//}

echo "\n...\n";
}

if($DB_TBLName2)
{
$sql = "Select * from ".$DB_TBLName2." where execution_date >= '".$search_from_date."' and 
			execution_date <= '".$search_to_date."' order by execution_date desc";
$result = @mysql_query($sql,$Connect)
or die("Couldn't execute query:<br>" . mysql_error(). "<br>" . mysql_errno());
$Use_Title = 1;
$title = "Report for $DB_TBLName2 on $now_date";
if ($Use_Title == 1)
{
	echo("$title\n");
}
$sep = "\t"; //tabbed character
for ($i = 0; $i < mysql_num_fields($result); $i++)
{
	echo mysql_field_name($result,$i) . "\t";
}
print("\n");
while($row = mysql_fetch_row($result))
{
	$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);
	$schema_insert = preg_replace("/\r\n|\n\r|\n|\r/", " ", $schema_insert);
	$schema_insert .= "\t";
	print(trim($schema_insert));
	print "\n";
}


echo "\n...\n";
}

if($DB_TBLName3)
{
$sql = "Select * from ".$DB_TBLName3." where execution_date >= '".$search_from_date."' and 
			execution_date <= '".$search_to_date."' order by execution_date desc";
$result = @mysql_query($sql,$Connect)
or die("Couldn't execute query:<br>" . mysql_error(). "<br>" . mysql_errno());
$Use_Title = 1;
$title = "Report for $DB_TBLName3 on $now_date";
if ($Use_Title == 1)
{
	echo("$title\n");
}
$sep = "\t"; //tabbed character
for ($i = 0; $i < mysql_num_fields($result); $i++)
{
	echo mysql_field_name($result,$i) . "\t";
}
print("\n");
while($row = mysql_fetch_row($result))
{
	$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);
	$schema_insert = preg_replace("/\r\n|\n\r|\n|\r/", " ", $schema_insert);
	$schema_insert .= "\t";
	print(trim($schema_insert));
	print "\n";
}
}

?>

 

have anyone any idea?

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.