• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 318
  • Last Modified:

Exporting results to Excel

PART 1

I need to display a list of dealers on screen that are grouped by state and alphabetized. All states need to be shown.
In addition, each dealer needs to have a checkbox next to dealer name.

For Example, I need to show the following on screen:

Column1--> state1
Result 1-->(checkbox) Dealer1 in state1
                (checkbox) Dealer2 in state1
                 (checkbox)DealerX in state1

Column2--> state2
Result 2-->  (checkbox)Dealer1 in state2
                  (checkbox)Dealer2 in state2
                  (checkbox)DealerX in state2

Column2--> stateX
Result 2-->  (checkbox)Dealer1 in stateX
                  (checkbox)Dealer2 in stateX
                  (checkbox)DealerX in stateX
                   
this is my sql query to retrieve the data ==> "select dealer_id, name FROM `dealer` order by state,name"

QUESTIONS:

1. I would like to loop through the result set to see whether or not the "state" field changes. When it does, I want to start a new column. Then after x columns have been created on the page, i want to move 2 rows down to begin with the next column. Does anyone have a code example for this or anything similar?


PART 2

Once dealers are shown by state with a checkbox next to the dealer name, the user should be able to select X number of dealers and then click submit.
This would execute the following query ==> "select name, state,zipcode from dealers where dealer_id(which will be passed in the checkbox) in (1,2,.,n)"
The results need to open within microsoft excel with the following format:

state1 (bold)
name1        zip2 (located in column B in Excel)

state2 (bold)
name2        zip2 (located in column B in Excel)

stateX (bold)
nameX        zipX (located in column B in Excel)

QUESTION

1. How can i have microsoft excel automatically open with the result set within the worksheet?
2. How can I format the sheet to display my desired format above?

Codes sample would be highly appreciated.

thanks
0
danielm28
Asked:
danielm28
  • 20
  • 11
  • 3
  • +1
1 Solution
 
stefanaichholzerCommented:
danielm28,

I would say, export your data to a comma delimited file, .csv and you can always open that in excel. Make a simple file in excel to have it how you want it to look and then save it as a .csv file, then look at it with your text editor and see how the columns are placed, that way you can have PHP do the same for you.

You could call Excel after that using PHP's fork command: exec(); for more info on that take a look at:
 
 http://www.php.net/manual/en/ref.exec.php
 http://www.php.net/manual/en/function.exec.php

 That should give you a basic hot-to-start you project.

 Let me know how it goes...

 ;)
0
 
danielm28Author Commented:
thanks. anyone else have any input?
0
 
stefanaichholzerCommented:
danielm28,

Here's an example script that exports to .csv, so you can have a clearer vision of things.

 http://evoluted.net/archives/000012.php

And here is an exaple on you to save to excel directly:

 http://www.apg88.com/apgForm/

Let me know how it goes...

;)
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
ldbkuttyCommented:
0
 
danielm28Author Commented:
Ok I will try. Does anyone have any thoughts on PART 1?
0
 
JKlatteCommented:
PART 1:
For the 'columns', I'd use tables within tables in HTML, so that the number of rows per column don't have to match

$tmp_State = ""; //temporary holder for the state name
$first=1; //checks whether it's the first column

echo "<TABLE><TR><TD><TABLE>";
while ($row = mysql_fetch_array($result_set))
{
  if ($row['State']!=$tmp_State)
  { // Start new column, but not for the first
    if ($first==1) { $first = 0; }
    else { echo "</TABLE></TD><TD><TABLE>"; }
  }
  $tmp_State = $row['State']; //Set current state
  echo "<TR><TD><INPUT type='checkbox' name='dealer_id' value='".$row['Dealer_id']."'> ".$row['name']." in ".$row['State']."</TD></TR>";
}
echo "</TABLE></TD></TABLE>";
0
 
danielm28Author Commented:
jklatte, that worked great.

Now, on to Part II.

ldbkutty, I'm getting the following message when attempting to export my results into excel

Sql error : Access denied for user: 'dealerusername@ipaddress' (Using password: YES)

what do you think I need to do?

0
 
danielm28Author Commented:
the error "Access denied for user: 'dealerusername@ipaddress' (Using password: YES)"

is being generated by this stmt. "$sql = "SELECT * INTO OUTFILE 'c:/temp/mydata.csv' FROM dealer limit 2";"

how can i get around this permission problem?

thx
0
 
danielm28Author Commented:
i was able to resolve the error by removing the "INTO OUTFILE 'c:/temp/mydata.csv' " from the sql.

when a checkbox is selected, I need to be able to show the following columns in the xls files: name, city, state, total.

total needs to be derived from querying another table. here's the query to get the total.

SELECT COUNT(*) FROM dealer as d,transaction as t where search_date>= '$begin_date' and search_date<= '$end_date' and t.dealer_id = d.dealer_id and t.clicks= 1";

here's the query to retrieve name, city, state

SELECT name,city,state FROM dealer as d,transaction as t where search_date>= '$begin_date' and search_date<= '$end_date' and t.dealer_id = d.dealer_id order by state,name,city

how do i combine these queries so all four columns can be in the same result set?
0
 
ldbkuttyCommented:
Try this:

<?php

// DB Connection here

$select = "SELECT name,city,state FROM dealer as d,transaction as t where search_date>= '$begin_date' and search_date<= '$end_date' and t.dealer_id = d.dealer_id order by state,name,city";

$export = mysql_query ( $select ) or die ( "Sql error : " . mysql_error( ) );

$fields = mysql_num_fields ( $export );

for ( $i = 0; $i < $fields; $i++ )
{
    $header .= mysql_field_name( $export , $i ) . "\t";
}

$header .= "total\t";

while( $row = mysql_fetch_row( $export ) )
{
    $line = '';
    foreach( $row as $value )
    {                                            
        if ( ( !isset( $value ) ) || ( $value == "" ) )
        {
            $value = "\t";
        }
        else
        {
            $value = str_replace( '"' , '""' , $value );
            $value = '"' . $value . '"' . "\t";
        }
        $line .= $value;
    }
    $data .= trim( $line ) . "\n";
}

$query2 = "SELECT COUNT(*) FROM dealer as d,transaction as t where search_date>= '$begin_date' and search_date<= '$end_date' and t.dealer_id = d.dealer_id and t.clicks= 1";

$result2 = mysql_query( $query2 ) or die( "Sql2 error: " . mysql_error( ) );

$total = mysql_fetch_row( $result2 );

$data .= $total[0] . "\n";

$data = str_replace( "\r" , "" , $data );

if ( $data == "" )
{
    $data = "\n(0) Records Found!\n";                        
}

header("Content-type: application/octet-stream");
header("Content-Disposition: attachment; filename=your_desired_name.xls");
header("Pragma: no-cache");
header("Expires: 0");
print "$header\n$data";

?>
0
 
stefanaichholzerCommented:
;)
0
 
danielm28Author Commented:
ldbkutty, that code has allowed me to get the total # of products that the selected dealers have in stock. thanks.

I now need totals for each individual dealer selected.
I would like to add a column called 'total' to the spreadsheet.
for each dealer returned, i would like the total # of products that they have of ours that they sell.
for ex.

name      city     state     total
dealer1   L.A.     C.A.     15
dealer2   S.F.     C.A.     1
dealer3   N.Y.     N.Y.     10

how do i accomplish this? the original query is below. this would need to be modified somehow to retrieve passed values from the $dealer_id[] array. But how?

"SELECT COUNT(*) FROM dealer as d,transaction as t where search_date>= '$begin_date' and search_date<= '$end_date' and t.dealer_id = d.dealer_id and t.clicks= 1";
------------------

Also, the results that are returned via this query

"SELECT name,city,state FROM dealer as d,transaction as t where search_date>= '$begin_date' and search_date<= '$end_date' and t.dealer_id = d.dealer_id order by state,name,city"

returns duplicate records because there can be many dealer_id for a given dealer in 'transaction', but there is only one dealer_id   in 'dealer'
how can i return only 1 record from 'dealer' for each dealer?
0
 
danielm28Author Commented:
i was able to answer the second question by matching the dealerIDs passed from the form with the dealerids in the db.
still can't figure out the first question........
0
 
ldbkuttyCommented:
SELECT name, city, state FROM dealer AS d LEFT JOIN transaction AS t ON t.dealer_id = d.dealer_id WHERE search_date >= '$begin_date' AND search_date <= '$end_date' ORDER BY state, name, city

// If this is not what you want, can you show the code you have till now ?
0
 
danielm28Author Commented:
ldkbutty, I actually need the answer to the question below.
--------------------------------------------------------------------
I now need totals for each individual dealer selected.
I would like to add a column called 'total' to the spreadsheet.
for each dealer returned, i would like the total # of products that they have of ours that they sell.
for ex.

name      city     state     total
dealer1   L.A.     C.A.     15
dealer2   S.F.     C.A.     1
dealer3   N.Y.     N.Y.     10

how do i accomplish this? the original query is below. this would need to be modified somehow to retrieve passed values from the $dealer_id[] array. But how?

"SELECT COUNT(*) FROM dealer as d,transaction as t where search_date>= '$begin_date' and search_date<= '$end_date' and t.dealer_id = d.dealer_id and t.clicks= 1";
0
 
danielm28Author Commented:
HERE'S THE CODE THAT I CURRENTLY HAVE
=============

<?php      
      //handles dealer id array to be passed to sql string
      $dealerConcat = implode(",", $dealer_id);
      
      $sql = "SELECT distinct t.dealer_id,name,city,state FROM dealer as d,transaction as t where search_date>= '$begin_date' and search_date<= '$end_date' and t.dealer_id = d.dealer_id and d.dealer_id in ($dealerConcat) order by state,name,city";

      $export = mysql_query ( $sql ) or die ( "Sql error : " . mysql_error( ) );

      $fields = mysql_num_fields ( $export );
      
      for ( $i = 0; $i < $fields; $i++ )
{
    $header .= mysql_field_name( $export , $i ) . "\t";
}

while( $row = mysql_fetch_row( $export ) )
{
    $line = '';
    foreach( $row as $value )
    {                                            
        if ( ( !isset( $value ) ) || ( $value == "" ) )
        {
            $value = "\t";
        }
        else
        {
            $value = str_replace( '"' , '""' , $value );
            $value = '"' . $value . '"' . "\t";
        }
        $line .= $value;
    }
    $data .= trim( $line ) . "\n";
}

//gives total count.
$query2 = "SELECT COUNT(*) FROM dealer as d,transaction as t where search_date>= '$begin_date' and search_date<= '$end_date' and t.dealer_id = d.dealer_id and t.dealer_id in ($dealerConcat) and t.clicks= 1";

$result2 = mysql_query( $query2 ) or die( "Sql2 error: " . mysql_error( ) );

$total = mysql_fetch_row( $result2 );

$data .= $total[0] . " total clicks\n";

$data = str_replace( "\r" , "" , $data );

if ( $data == "" )
{
    $data = "\n(0) Records Found!\n";                        
}

header("Content-type: application/octet-stream");
header("Content-Disposition: attachment; filename=advancedtrackingresults.xls");
header("Pragma: no-cache");
header("Expires: 0");
print "$header\n$data";

?>
0
 
ldbkuttyCommented:
count(*) will just return the number of records, NOT the sum.

If there is a DB column for "total", you just need to get the column in the first query and there is no need for second query.
0
 
danielm28Author Commented:
The count(*) query that appears above actually tells you the total # of times a dealer link has been clicked. Every time someone clicks on a dealer link to view contact information, that's recorded in the transaction table. I was mistaken in my last post when i said I need the # of products each dealer sells.I don't need the sum. I actually need to find out how many times a given dealer link has been clicked on.
there is no db column for total. Need to create it on the fly.
what do you think I need to do to get the individual counts and place them in a Total column?
0
 
ldbkuttyCommented:
Hi daniel,

I am too tired now, early morning here and did not have sleep at all !

I'll have a look in the morning. Thanks.
0
 
danielm28Author Commented:
on prob.thx
0
 
danielm28Author Commented:
the question above is the last one that I have.
Please let me know thoughts on it.

Thanks!!
0
 
ldbkuttyCommented:
Sorry I forgot to look at this question, I'll see right now.
0
 
danielm28Author Commented:
thanks kind sir.
0
 
ldbkuttyCommented:
How is the result you are getting now !? I guess you should have duplicates as you didn't used a JOIN !
0
 
ldbkuttyCommented:
could you post the structure + some datas of "dealer" and "transaction" tables, so I can test here. :=)
0
 
danielm28Author Commented:
no duplicates. I used distinct which took care of that problem.
the results that open into excel look like this:

dealer_id      name      city      state
216      The Good Guys      Northridge      CA
228      The Good Guys      Orange      CA
222      The Good Guys      Pasadena      CA
112      Audio Perfection *      Minneapolis      MN
126      Absolute Audio, Inc *      Cerrillos      NM
128      ABCO Appliance Sales      Carson City      NV
278      Homeworks Audio & Video      Las Vegas      NV
127      Image Sound & Control *      Las Vegas      NV
289      Sound by Singer *      New York      NY
133      Sound City *      New York      NY
132      Stereo Exchange *      New York      NY
1401 total clicks                  

I need to add another column 'total' which gives individual click rates...........................

therefore, the following stmt. needs to be modified

 "SELECT COUNT(*) FROM dealer as d,transaction as t where search_date>= '$begin_date' and search_date<= '$end_date' and t.dealer_id = d.dealer_id and t.dealer_id in ($dealerConcat) and t.clicks= 1"
0
 
ldbkuttyCommented:
"distinct" won't work in your case, as you have different columns in select list (try removing the distinct, you'll get the same results!).

We should make it work with one query as the "count" value should be appended to "each" row in the CSV !
0
 
danielm28Author Commented:
#
# Table structure for table `dealer`
#

CREATE TABLE dealer (
  dealer_id int(11) NOT NULL auto_increment,
  name varchar(255) NOT NULL default '',
  contact_first varchar(30) default NULL,
  contact_last varchar(30) default NULL,
  address1 varchar(128) default NULL,
  address2 varchar(128) default NULL,
  city varchar(48) default NULL,
  state varchar(8) default NULL,
  zip int(5) default '0',
  phone varchar(20) default NULL,
  fax varchar(20) default NULL,
  phone_area char(3) default NULL,
  email varchar(128) default NULL,
  url varchar(128) default NULL,
  country varchar(40) default NULL,
  UNIQUE KEY dealer_id (dealer_id)
) TYPE=ISAM PACK_KEYS=1;

#
# Dumping data for table `dealer`
#

INSERT INTO dealer VALUES (276, 'Custom Entertainment Options, Inc      ', 'fname', 'lname', 'By Appointment Only', NULL, 'Midland', 'MI', 48640, '989-111-1111', '(989) 837-1873', '989', email@email.net', NULL, 'USA');
INSERT INTO dealer VALUES (2, 'Fidler HiFi Inc.', NULL, NULL, '3506 Montlimar Plaza Park', NULL, 'Mobile', 'AL', 36609, '251-111-1111', '251-111-1121', '251', NULL, NULL, NULL);


#
# Table structure for table `transaction`
#

CREATE TABLE transaction (
  transaction_id int(11) NOT NULL auto_increment,
  dealer_id int(11) NOT NULL default '0',
  search_date date NOT NULL default '0000-00-00',
  term varchar(255) NOT NULL default '',
  clicks int(1) NOT NULL default '0',
  PRIMARY KEY  (transaction_id)
) TYPE=MyISAM;

#
# Dumping data for table `transaction`
#

INSERT INTO transaction VALUES (1, 140, '2005-02-03', 'OK', 1);
INSERT INTO transaction VALUES (2, 102, '2005-02-03', '20901', 0);
INSERT INTO transaction VALUES (3, 102, '2005-02-03', 'MD', 1);
INSERT INTO transaction VALUES (4, 20, '2005-02-03', 'San Francisco', 0);
INSERT INTO transaction VALUES (5, 30, '2005-02-03', 'San Francisco', 0);
INSERT INTO transaction VALUES (6, 33, '2005-02-03', 'San Francisco', 0);
INSERT INTO transaction VALUES (7, 168, '2005-02-03', 'San Francisco', 1);
INSERT INTO transaction VALUES (8, 170, '2005-02-03', 'San Francisco', 1);
INSERT INTO transaction VALUES (9, 172, '2005-02-03', 'San Francisco', 1);
INSERT INTO transaction VALUES (10, 244, '2005-02-03', 'VA', 0);
INSERT INTO transaction VALUES (11, 102, '2005-02-03', 'MD', 0);
INSERT INTO transaction VALUES (12, 234, '2005-02-03', '91006', 0);
INSERT INTO transaction VALUES (13, 229, '2005-02-03', '91006', 0);
INSERT INTO transaction VALUES (14, 222, '2005-02-03', '91006', 1);
INSERT INTO transaction VALUES (15, 217, '2005-02-03', '91006', 0);
INSERT INTO transaction VALUES (16, 12, '2005-02-03', '91006', 0);
INSERT INTO transaction VALUES (17, 221, '2005-02-03', '91006', 0);
INSERT INTO transaction VALUES (18, 216, '2005-02-03', '91006', 0);
INSERT INTO transaction VALUES (19, 18, '2005-02-03', '91006', 0);
INSERT INTO transaction VALUES (20, 26, '2005-02-03', '91006', 0);
INSERT INTO transaction VALUES (21, 218, '2005-02-03', '91006', 0);


0
 
danielm28Author Commented:
hi ldbkutty,

I removed the distinct by changing the query to
"SELECT t.dealer_id,name,city,state FROM dealer as d,transaction as t where search_date>= '$begin_date' and search_date<= '$end_date' and t.dealer_id = d.dealer_id and d.dealer_id in ($dealerConcat) order by state,name,city"

and got numerous duplicates.......................

When the distinct is in the query i get no duplicates.
0
 
ldbkuttyCommented:
Try this:

<?php    
//handles dealer id array to be passed to sql string
$dealerConcat = implode(",", $dealer_id);

$sql = "SELECT t.dealer_id, name, city, state, count(t.dealer_id) AS total FROM dealer AS d,transaction AS t WHERE search_date >= '$begin_date' AND search_date <= '$end_date' AND t.dealer_id = d.dealer_id AND d.dealer_id IN ($dealerConcat) GROUP BY t.dealer_id ORDER BY state, name, city";

$export = mysql_query ( $sql ) or die ( "Sql error : " . mysql_error( ) );

$fields = mysql_num_fields ( $export );

for ( $i = 0; $i < $fields; $i++ )
{
    $header .= mysql_field_name( $export , $i ) . "\t";
}

while( $row = mysql_fetch_row( $export ) )
{
    $line = '';
    foreach( $row as $value )
    {                                            
        if ( ( !isset( $value ) ) || ( $value == "" ) )
        {
            $value = "\t";
        }
        else
        {
            $value = str_replace( '"' , '""' , $value );
            $value = '"' . $value . '"' . "\t";
        }
        $line .= $value;
    }
    $data .= trim( $line ) . "\n";
}

$data = str_replace( "\r" , "" , $data );

if ( $data == "" )
{
    $data = "\n(0) Records Found!\n";                        
}

header("Content-type: application/octet-stream");
header("Content-Disposition: attachment; filename=advancedtrackingresults.xls");
header("Pragma: no-cache");
header("Expires: 0");
print "$header\n$data";

?>

// Ok you have distinct from the "transaction" table, not in the "dealers" table.

Note: I didn't used "distinct" but used GROUP BY clause.
0
 
danielm28Author Commented:
that's working well.
What do I need to do to add the total of all the individual dealer clicks (like we had before) to the last row of the report?
0
 
danielm28Author Commented:
never mind. I think I've got it.
just need to test a little bit more..........
0
 
ldbkuttyCommented:
I dont know how you have done, but if you have used second query for the grand total, it is not efficient.

Try this:

<?php    
//handles dealer id array to be passed to sql string
$dealerConcat = implode(",", $dealer_id);

$sql = "SELECT t.dealer_id, name, city, state, count(t.dealer_id) AS total FROM dealer AS d,transaction AS t WHERE search_date >= '$begin_date' AND search_date <= '$end_date' AND t.dealer_id = d.dealer_id AND d.dealer_id IN ($dealerConcat) GROUP BY t.dealer_id ORDER BY state, name, city";

$export = mysql_query ( $sql ) or die ( "Sql error : " . mysql_error( ) );

$fields = mysql_num_fields ( $export );

for ( $i = 0; $i < $fields; $i++ )
{
    $header .= mysql_field_name( $export , $i ) . "\t";
}

$grandTotal = 0;

while( $row = mysql_fetch_row( $export ) )
{
    $line = '';
    foreach( $row as $value )
    {                                            
        if ( ( !isset( $value ) ) || ( $value == "" ) )
        {
            $value = "\t";
        }
        else
        {
            $value = str_replace( '"' , '""' , $value );
            $value = '"' . $value . '"' . "\t";
        }
        $line .= $value;
    }
    $data .= trim( $line ) . "\n";
   
    $grandTotal = $grandTotal + intval($row[4]);
}

$data .= "Total clicks: $grandTotal \n";

$data = str_replace( "\r" , "" , $data );

if ( $data == "" )
{
    $data = "\n(0) Records Found!\n";                        
}

header("Content-type: application/octet-stream");
header("Content-Disposition: attachment; filename=advancedtrackingresults.xls");
header("Pragma: no-cache");
header("Expires: 0");
print "$header\n$data";

?>
0
 
danielm28Author Commented:
i will try this in a couple of hours and will get back to u.
0
 
danielm28Author Commented:
thanks. you're a lifesaver!!
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 20
  • 11
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now