MySQL Forums
Forum List  »  MySQL for Excel

How do I export an SQL Query to Excel? [Help please!]
Posted by: Alex Bake
Date: August 11, 2011 04:16AM

For starters, here is my code:

<?php

require 'inc/conn.php';

if (isset($_GET['SC01001'])&&isset($_GET['SC01002'])&&isset($_GET['SC01005'])&&isset($_GET['SC01052'])&&isset($_GET['SC01058'])&&isset($_GET['SC01060'])) {
$SC01001 = trim($_GET['SC01001']);
$SC01002 = trim($_GET['SC01002']);
$SC01005 = trim($_GET['SC01005']);
$SC01052 = trim($_GET['SC01052']);
$SC01058 = trim($_GET['SC01058']);
$SC01060 = trim($_GET['SC01060']);

$query = "SELECT * FROM SC010100
WHERE SC01001 LIKE '%".$SC01001."%'
AND SC01002 + SC01003 LIKE '%".$SC01002."%'
AND SC01005 LIKE '%".$SC01005."%'
AND SC01052 LIKE '%".$SC01052."%'
AND SC01058 LIKE '%".$SC01058."%'
AND SC01060 LIKE '%".$SC01060."%'
ORDER BY SC01001 ASC";

$qr = odbc_exec($conn, $query);

if (odbc_num_rows($qr)>=1) {

echo '<div id="wrapip"><h2>Results Found: <a href="search.php">New Search?</a> <a href="#">Export to Excel</a></h2>';
echo '<table id="inplace">
<tr>
<td class="r1">StockCode</th>
<td class="r2">Description</th>
<td class="r3">NewPriceLCU</th>
<td class="r4">AverCostPri1</th>
<td class="r5">SupplierCode</th>
<td class="r6">SupplStockCo</th>
</tr></table></div><br /><table id="results_table">';

while ($query_row = odbc_fetch_array($qr)) {
echo '<tr>
<td class="r1">'.$query_row['SC01001'].'&nbsp;</td>
<td class="r2">'.$query_row['SC01002'].$query_row['SC01003'].'&nbsp;</td>
<td class="r3">&euro; '.$query_row['SC01005'].'&nbsp;</td>
<td class="r4">&euro; '.$query_row['SC01052'].'&nbsp;</td>
<td class="r5">'.$query_row['SC01058'].'&nbsp;</td>
<td class="r6">'.$query_row['SC01060'].'&nbsp;</td>
</tr>';
}

echo '</table><br /><br />';
} else {
echo 'No results found.';
}
}
include 'inc/searchform.php';
?>

I know its a bit confusing, but I don't know what to leave in and what to cut out.
So, this page includes a form, and when the form is submitted with search criteria, it displays a table of the results.
What I would like to do, but cannot seem to figure out, is export the results into an excel spreadsheet with a click of a button. How can I do this?
I tried making a separate odbc_exec that included the sql command INSERT INTO OPENROWSET, but that didn't seem to work.
I've found a few tutorials online that export SQL data into Excel using PHP, but it's been very difficult to implement into my current code.

If anyone can help, or link me to something that can help it would be very much appreciated. Thank you.

Options: ReplyQuote


Subject
Views
Written By
Posted
How do I export an SQL Query to Excel? [Help please!]
3569
August 11, 2011 04:16AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.