1

I am using below code to export columns from two mysql table to an excel file using phpexcel.

I am achieving it by running the query twice. Can someone help me on how to get this done by running only one query and using LIKE and UNION maybe.

Thank You!

EDIT: As per requirement, the number of table will increase going forward but will have a same kind of name...So have to find a solution which will select all the tables on the database using LIKE first and then query the column data from all the tables. And then output those data to an excel sheet side-by-side. Like table 1 data on col A1-AJ, the tab;e 2 data from AL-AR and so on.

Here is my code:

<?php

// connection with the database 
$dbhost = "localhost"; 
$dbuser = "root"; 
$dbpass = "redhat"; 
$dbname = "was"; 

mysql_connect($dbhost,$dbuser,$dbpass); 
mysql_select_db($dbname); 

// require the PHPExcel file 
require 'phpexcel/Classes/PHPExcel.php'; 

$query = "SELECT servicedate,recdate,dostoreclag,casesrec,scandate,casesentered,casespending,casecountdiff,entrydate,rcvdtolag FROM hos1report";
$query1 = "SELECT servicedate,recdate,dostoreclag,casesrec,scandate,casesentered,casespending,casecountdiff,entrydate,rcvdtolag FROM hos2report"; 
$headings = array('Service Date','Rec. Date','DOS to Rec. Lag','Cases Rec.','scan Date','Cases Entered','Cases Pending','Cases Count Diff','Entry Date','Rec. to Entry Lag');

if ($result = mysql_query($query) or die(mysql_error())) { 
    // Create a new PHPExcel object 
    $objPHPExcel = new PHPExcel(); 
    $objPHPExcel->getActiveSheet()->setTitle('Report'); 
    $rowNumber = 8; 
    $col = 'A'; 

    foreach($headings as $heading) { 
       $objPHPExcel->getActiveSheet()->setCellValue($col.$rowNumber,$heading); 
       $col++; 
    } 

    // Loop through the result set 
    $rowNumber = 9; 

    while ($row = mysql_fetch_row($result)) { 
       $col = 'A'; 
       foreach($row as $cell) { 
          $objPHPExcel->getActiveSheet()->setCellValue($col.$rowNumber,$cell); 
          $col++; 
       } 
       $rowNumber++; 
    }

 } else {

 echo 'a problem has occurred... no data retrieved from the database';
 }  

 if ($result1 = mysql_query($query1) or die(mysql_error())) { 
    // Create a new PHPExcel object 
    $objPHPExcel = new PHPExcel(); 
    $objPHPExcel->getActiveSheet(); 
    $rowNumber = 8; 
    $col = 'L'; 

    foreach($headings as $heading) { 
       $objPHPExcel->getActiveSheet()->setCellValue($col.$rowNumber,$heading); 
       $col++; 
    }


    // Loop through the result set 
    $rowNumber = 9; 

    while ($row = mysql_fetch_row($result)) { 
       $col = 'L'; 
       foreach($row as $cell) { 
          $objPHPExcel->getActiveSheet()->setCellValue($col.$rowNumber,$cell); 
          $col++; 
       } 
       $rowNumber++; 
    }

 } else {

 echo 'a problem has occurred... no data retrieved from the database';
 }   

 // Freeze pane so that the heading line won't scroll 
    $objPHPExcel->getActiveSheet()->freezePane('A2'); 

    // Save as an Excel BIFF (xls) file 
    $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5'); 

   header('Content-Type: application/vnd.ms-excel'); 
   header('Content-Disposition: attachment;filename="report.xls"'); 
   header('Cache-Control: max-age=0'); 

   $objWriter->save('php://output'); 

?>

All the tables will have same columns but different data

enter image description here

Improve this question
9
  • looks like data that would all be in one table considering the structure Commented Apr 12, 2015 at 20:41
  • Please read : How to create a Minimal, Complete, and Verifiable example. To the look of your question, and your code, I can see 99% of your code snippet not being related or even relevant to your question. Commented Apr 12, 2015 at 20:41
  • Actually No. There are different data having same column name. @Dagon Commented Apr 12, 2015 at 20:52
  • Just like my clients, get the job done, then they add more on top of it. At least post a sample schema if you expect us to do all your work for you dude. Commented Apr 12, 2015 at 20:52
  • possible duplicate of MYSQL if a select query returns 0 rows then another select query? Commented Apr 12, 2015 at 20:53

3 Answers 3

Reset to default
3

Try the following using a union (read https://dev.mysql.com/doc/refman/5.0/en/union.html)

$query = "
SELECT servicedate,recdate,dostoreclag,casesrec,scandate,casesentered,casespending,casecountdiff,entrydate,rcvdtolag FROM hos1report
union
SELECT servicedate,recdate,dostoreclag,casesrec,scandate,casesentered,casespending,casecountdiff,entrydate,rcvdtolag FROM hos2report
";

EDIT: Given your update which states " output those data to an excel sheet side-by-side. Like table 1 data on col A1-AJ, the tab;e 2 data from AL-AR and so on" just do a "SELECT DISTINCT ... LEFT JOIN" instead of a union... you can change the names to reflect the table, but that isn't a requirement unless you are addding conditions in the WHERE clause...

    $query = "
    SELECT DISTINCT servicedate,recdate,dostoreclag,casesrec,scandate,casesentered,casespending,casecountdiff,entrydate,rcvdtolag FROM hos1report
left join hos2report";

Performance will be horrible, if you could display all the data in col A1 through AJ it would be much simpler.... you could even add a column specifying which table was the source like so

$query = "
SELECT 'hos1report' as tablename, servicedate,recdate,dostoreclag,casesrec,scandate,casesentered,casespending,casecountdiff,entrydate,rcvdtolag FROM hos1report
union
SELECT  'hos2report' as tablename, servicedate,recdate,dostoreclag,casesrec,scandate,casesentered,casespending,casecountdiff,entrydate,rcvdtolag FROM hos2report
";
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

1 
SELECT t1.servicedate,t1.recdate,t2.servicedate ,t2.recdate  ... FROM hos1report t1 INNER JOIN hos2report t2 ON(t1.YOUR_PRIMARY_KEY = t2.YOUR_PRIMARY_KEY)
Improve this answer

3 Comments

@PeeJay to get all tables in db, you could use information_schema ->TABLES
Yes I have tried that but somehow the output is null... neither the excel is generating nor any error. So I thought that I could get some help here
@PeeJay try to use query SHOW TABLES;
1

UPDATED

You really should provide a data schema (even if its just a sample) so that people can put it into SQLFIDDLE and work with it. Also, it's just helpful to have a copy/paste version of the data we can use.

I created a sample schema and fiddle for you. Go to SQLFIDDLE SAMPLE OF YOUR DATA and you can see how to build a schema there and on the right, you can query the schema data and see how the site works.

I will now attempt to solve your NEW issue, even though I already solved your original issue without so much as even an upvote.

NOTE: You should really combine the two tables and just add an extra column to determine if the data is from table1 or table2. like ...

ALTER TABLE `hos1report` ADD `flag` tinyint(2) NOT NULL DEFAULT 0;

then just set flag to 1 for any data that comes from table2, 2 for any data f from table3, 3 for any data from table4. and table1, the original data in the table now, will have the flag column set to the default of 0.

Anyway ...

This will only pull from 2nd table if result from first table is 0 rows.

  SELECT SQL_CALC_FOUND_ROWS 
      `servicedate`,`recdate`,`dostoreclag`,`casesrec`,`scandate`,
      `casesentered`,`casespending`,`casecountdiff`,`entrydate`,`rcvdtolag`
  FROM 
      `hos1report`

  UNION ALL 

  SELECT 
      `servicedate`,`recdate`,`dostoreclag`,`casesrec`,`scandate`,
      `casesentered`,`casespending`,`casecountdiff`,`entrydate`,`rcvdtolag`
  FROM 
      `hos2report`
  WHERE 
      FOUND_ROWS() = 0
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.