Export SQL tables to CSV using PHP

Hi,

I’m trying to pull data from two tables within one database and display them in a CSV file with headers ( See Attachment).

I can’t figure out how to get the firstname data which is being pulled from the second table to display in the 2nd column on my spreadsheet. It displays below the first column data and if i place null before firstname it moves it into the third column!?

Anyone know what this problem is?

<?php include('dbase.php'); //header to give the order to the browser header('Content-Type: text/csv'); header('Content-Disposition: attachment;filename=exported-data.xls'); //query get data $sql = mysql_query("SELECT date_add, shipping_number, reference, delivery_date FROM ps_orders UNION ALL SELECT firstname, null, null, null, FROM ps_customer;"); $no = 1; while($data = mysql_fetch_assoc($sql)){ echo ' '; $no++; } ?>
DATE OF SALE STOCKIST FACTORY CODE REFERENCE ID DEADLINE DATE
'.$data['date_add'].' '.$data['firstname'].' '.$data['shipping_number'].' '.$data['reference'].' '.$data['delivery_date'].'

php.PNG

That’s because you’re using a union query, you need to change it to a JOIN.

But you need a column to do the join on, so how is the table ps_orders linked to ps_customers

Old:

[php]$sql = mysql_query(“SELECT date_add, shipping_number, reference, delivery_date FROM ps_orders UNION ALL SELECT firstname, null, null, null, FROM ps_customer;”);
[/php]

New:

[php]$sql = mysql_query(“SELECT date_add, firstname, shipping_number, reference, delivery_date FROM ps_orders, ps_customer where ps_orders.LINKING_COLUMN = ps_customers.LINKING_COLUMN;”);[/php]

You just need to complete the where clause on the columns that links the 2 tables together.

Hi,

thanks for your reply and help.

I’ve added a new column (via phpmyadmin) into ps_orders called ‘Stockist’ which is where I’d like to pull the firstname data from ps_customer. Using your suggestion would the code be:

[php]$sql = mysql_query(“SELECT date_add, firstname, shipping_number, reference, delivery_date FROM ps_orders, ps_customer where ps_orders.stockist = ps_customer.firstname;”);[/php]

Or have I got that completely wrong? At the moment using that code it’s not pulling any data into the CSV table.

Thanks

Let me explain -

You have two tables…

ps_orders and ps_customer

ps_orders
date_add
shipping_number
reference
delivery_date
customer_id

ps_customer
customer_id
firstname
lastname
gender

The linking column in the two tables in the instance above will be “customer_id” - You would enforce a Foreign Key Constraint on ps_orders to not allow any customer_id’s that don’t exist in the ps_customers table.

This links a customer to every order.

The query would look like in this example…

[php]$sql = mysql_query(“SELECT date_add, firstname, shipping_number, reference, delivery_date FROM ps_orders, ps_customer where ps_orders.customer_id= ps_customer.customer_id;”);[/php]

Sponsor our Newsletter | Privacy Policy | Terms of Service