mysql to csv(or similar)

[truck7758]

Hi,

 

would it be possible to add a export function in php so it exports all my sql results to a csv or something similar (just to be able to be viewed in excel).

 

Thanks,

Mike

[truck7758]

any ideas, tips, prod in the right direction. I seem to be able to find instructions on how to do it using mysql but for some reason i only seem to be able to use mysqli (excuse my noobiness is this doesn't make sense)

 

Thanks

[Alexhoward]

Hi,

 

I take it you're not using phpMyAdmin then...?

[truck7758]

nope 

[Alexhoward]

I'm a noob too

 

so where's you're sql saved?

 

how do you access it directly?

[truck7758]

its saved locally. to connect i use

 

$mysqli = new mysqli('localhost','username','password');

[Alexhoward]

OK....

 

so it's got no built in front end...?

[truck7758]

i have created a php site where you can add/view the database but now i want to add a link where you can download the contents of a particular table into an csv file

 

Thanks 

[MatthewJ]

<?php
mysql_select_db($database_CDBG, $CDBG);
$query_rsReport = "Your query";
$rsReport = mysql_query($query_rsReport, $CDBG) or die(mysql_error());
$totalRows_rsReport = mysql_num_rows($rsReport);
$fields = mysql_num_fields($rsReport);
for ($i = 0; $i < $fields; $i++)
{
$header .= mysql_field_name($rsReport, $i) . ",";
}
while ($row = mysql_fetch_row($rsReport))
{
$line = '';
foreach ($row as $value)
{
	$value = '"' . $value . '"' . ",";
	$line .= $value;
}
$data .= trim($line) . "\n";
}
$title = "Will appear in the top left cell";
$data = str_replace("\r", " ", $data);
header("Content-type: application/x-msdownload");
header("Content-Disposition: attachment; filename=cdbg_r1.csv");
header("Pragma: no-cache");
header("Expires: 0");
print "$title\n\n$header\n$data";
mysql_free_result($rsReport);
?>

 

This exports to csv...

 

I left most of my specific information in there to help make it a bit clearer what it is doing, hope that helps.

[truck7758]

Hi,

 

Whats $CDBG? also i dont think this will work as only mysqli queries seem to work on mine (have no idea why)

 

cheers 

[Alexhoward]

If it pulls your data into an HTML table on the page,

 

there is a way to export this to Excel,

 

Rather than pull it directly from your database

[MatthewJ]

change the mysql_ items to mysqli items... should not be any difference once you do that if I remember (have only used mysqli once)

 

And CDBG is community development block grant... it is a specific to the script I wrote. You said you were a noob, I just did not want to cut out everything that made it sensible to look at

[truck7758]

yeh i re-read your post after i replied and realised that 

 

how do you go about changing the mysql to mysqli as i may not know much but i know its not just a case of adding the 'i' onto the end of all the mysql's 

 

thanks 

[MatthewJ]

You can probably look through here and get the items that need to be changed

 

http://www.php.net/mysqli

 

 

[truck7758]

ok, heres what iv managed to get

 

<?php
$mysqli = new mysqli('localhost','username','password');
	$mysqli->select_db('orders');

$query_rsReport = "select * from orders";
$rsReport = mysqli->query($query_rsReport, $mysqli);
$totalRows_rsReport = mysqli->num_rows($rsReport);
$fields = mysqli->field_count($rsReport);
for ($i = 0; $i < $fields; $i++)
{
$header .= mysqli->field_name($rsReport, $i) . ",";
}
while ($row = mysqli->fetch_row($rsReport))
{
$line = '';
foreach ($row as $value)
{
	$value = '"' . $value . '"' . ",";
	$line .= $value;
}
$data .= trim($line) . "\n";
}
$title = "Will appear in the top left cell";
$data = str_replace("\r", " ", $data);
header("Content-type: application/x-msdownload");
header("Content-Disposition: attachment; filename=cdbg_r1.csv");
header("Pragma: no-cache");
header("Expires: 0");
print "$title\n\n$header\n$data";
$rsReport->close;
?>

 

this gives the error message:

 

Parse error: syntax error, unexpected T_OBJECT_OPERATOR in c:\webs\test\export.php on line 6

 

line 6 = $rsReport = mysqli->query($query_rsReport, $mysqli);

 

any ideas?

Thanks 

[truck7758]

iv now sorted that by making it: $rsReport = $mysqli->query($query_rsReport, $mysqli);

 

now im getting: Fatal error: Call to undefined method mysqli::num_rows() in c:\webs\test\export.php on line 7

 

line 7 = $totalRows_rsReport = $mysqli->num_rows($rsReport);

 

any ideas,

cheers 

[truck7758]

iv now managed to get to this

 

<?php
$mysqli = new mysqli('localhost','root','newr00t');
	$mysqli->select_db('orders');

$query_rsReport = "select * from orders";
$rsReport = $mysqli->query("SELECT * FROM orders");
$totalRows_rsReport = mysqli_num_rows($rsReport);
if (mysqli_field_count($rsReport)) {
    /* this was a select/show or describe query */
    $fields = mysqli_store_result($rsReport);

//$fields = mysqli_field_count($rsReport);
for ($i = 0; $i < $fields; $i++)
{
$header .= $mysqli->field_name($rsReport, $i) . ",";
}
while ($row = $mysqli->fetch_row($rsReport))
{
$line = '';
foreach ($row as $value)
{
	$value = '"' . $value . '"' . ",";
	$line .= $value;
}
$data .= trim($line) . "\n";
}
$title = "Will appear in the top left cell";
$data = str_replace("\r", " ", $data);
header("Content-type: application/x-msdownload");
header("Content-Disposition: attachment; filename=cdbg_r1.csv");
header("Pragma: no-cache");
header("Expires: 0");
print "$title\n\n$header\n$data";
mysqli_free_result($fields);
$rsReport->close;
?>

 

and now im getting the error: Parse error: syntax error, unexpected $end in c:\webs\test\export.php on line 36 which is my last line.

 

Any ideas,

Mike

[truck7758]

sorted now. dont know how lol heres the code i used for anyone else who may find it useful

 

<?php
$host = 'localhost';
$user = 'username';
$pass = 'password';
$db = 'orders';
$table = 'orders';
$file = 'order';

$link = new mysqli(localhost, username, password) or die("Can not connect1." . mysql_error());
mysqli_select_db($link, "orders") or die("Can not connect2.");

$result = mysqli_query($link, "SHOW COLUMNS FROM ".$table."", MYSQLI_STORE_RESULT);
$i = 0;
if (mysqli_num_rows($result) > 0) {
while ($row = mysqli_fetch_assoc($result)) {
$csv_output .= $row['Field'].", ";
$i++;
}
}
$csv_output .= "\n";

$values = mysqli_query($link, "SELECT * FROM ".$table."");
while ($rowr = mysqli_fetch_row($values)) {
for ($j=0;$j<$i;$j++) {
$csv_output .= $rowr[$j].", ";
}
$csv_output .= "\n";
}

$filename = $file."_".date("Y-m-d_H-i",time());
header("Content-type: application/vnd.ms-excel");
header("Content-disposition: csv" . date("Y-m-d") . ".csv");
header( "Content-disposition: filename=".$filename.".csv");
print $csv_output;
exit;
?>

 

Thanks All 

[MatthewJ]

Awesome, glad I could help!