Exporting Multiple MySQL queries to a Single CSV

General PHP Help
#1

Hello,
I have the below code that will export a MySQL query to a CSV (I grabbed it off a tutorial and modified a bit to my use). I am trying to modify it further so I can perform multiple select statements and output all of them to the same CSV. I am struggling a bit as I am new to PHP (I got the basic syntax, if, else, loops, etc. but that’s about it). Can anyone help?
[php]

<?php //include database configuration file include 'dbConfig.php'; //get records from database $query = $db->query("SELECT FirstName, LastName, CellPhone, Email FROM `Person` WHERE JobTitle=1 AND PrimaryLocation=2 OR JobTitle=2 AND PrimaryLocation=2"); if($query->num_rows > 0){ $delimiter = ","; $filename = "Store2" . date('Y-m-d') . ".csv"; //create a file pointer $f = fopen('php://memory', 'w'); //set column headers $fields = array('FirstName', 'LastName', 'CellPhone', 'Email'); fputcsv($f, $fields, $delimiter); //output each row of the data, format line as csv and write to file pointer while($row = $query->fetch_assoc()){ $lineData = array($row['FirstName'], $row['LastName'], $row['CellPhone'], $row['Email']); fputcsv($f, $lineData, $delimiter); } //move back to beginning of file fseek($f, 0); //set headers to download file rather than displayed header('Content-Type: text/csv'); header('Content-Disposition: attachment; filename="' . $filename . '";'); //output all remaining data on a file pointer fpassthru($f); } exit; ?>

[/php]

#2

Unless the data type is the same, you don’t want it in the same file, if I understand what you are asking about.

What will the csv be used for?

#3

I have a MySQL database with each of our store’s contact information. I have an internal website that users can go to that uses PHP and HTML to pull the info from the database and display it in an easy to read format for users. There is a section for managers (cell phones), general administration contacts (HR, employee service, etc.), and a section for each store’s service providers. I want to add an export to CSV function to the website. Hence I need to perform multiple select queries that export to the same CSV. So it would look something like below.

Managers
Manager contact info
Assistant Manager contact info
Assistant Manager contact info

GA contacts
HR contact info
Payroll contact info
Employee Service contact info
etc.

Service Providers
Service provider contact info
Service provider contact info
Service provider contact info
…

#4

So, the data would be the same for the columns?

#5

No, pulling data from different tables with different column names. But the data type is all VARCHAR.

#6

I get that it is from different tables. If the types are the same, you can do a single query for the file builder.

By type i mean,

Name ! Phone ! Phone ! Phone

#7

Hello, still not seeing a way to pull everything I need in one query, below are some of the queries I am running on the store pages.
[php]
SELECT LocationName, DescriptiveName, StoreStreet1, StoreCity, StoreState, StoreZip, MainLine, Backline, FaxLine, OperatingHours FROM Location WHERE LocationID=2
SELECT FirstName, LastName, CellPhone, Email FROM Person WHERE JobTitle=1 AND PrimaryLocation=2
SELECT FirstName, LastName, CellPhone FROM Person WHERE JobTitle=2 AND PrimaryLocation=2
Select ServiceTypeDesc from ServiceType where ServTypeID=1
Select ProviderName, ProviderPhone, ContactInfo from ServiceProvider where ServiceType=1 and Location=2
[/php]
The section for administrative contacts has multiple different queries, but I would be happy just be able to export the above queries to one CSV. The select for ServiceType and ServiceProvider repeats about 30 times incrementing on ServTypeID and ServiceType as I couldn’t figure out a join that would work properly for how I am looking to display the information.

#8

You can do unions, but you need to account for the column count deficiencies.

With the data being different, I don’t think a CSV is what you want to output it into. xls, is a different file type, but you can at least make it formatted to account for the different types/

Sponsor our Newsletter | Privacy Policy | Terms of Service