Based on an old post (Export MYSQL Date to CSV) i try to create an sql query with .csv download option. The date range picker is appear, but doesn't give any records, and the downloaded file is empty too...:( I'm a newbie in sql, and i think the problem with is the sql query. Here is my code:
<script src="http://code.jquery.com/jquery-1.10.2.js"></script>
<script src="http://code.jquery.com/ui/1.11.0/jquery-ui.js">
</script>
<link href="https://code.jquery.com/ui/1.10.4/themes/ui-lightness/jquery-ui.css"
rel="stylesheet">
<script type="text/javascript" language="javascript">
jQuery(function() {
jQuery( "#from" ).datepicker({
defaultDate: "+1w",
changeMonth: true,
changeYear: true,
numberOfMonths: 1,
dateFormat: "yy-mm-dd",
onClose: function( selectedDate ) {
$( "#to" ).datepicker( "option", "minDate", selectedDate );
}
});
jQuery( "#to" ).datepicker({
defaultDate: "+1w",
changeMonth: true,
changeYear: true,
numberOfMonths: 1,
dateFormat: "yy-mm-dd",
onClose: function( selectedDate ) {
jQuery( "#from" ).datepicker( "option", "maxDate", selectedDate );
}
});
});
</script>
<h1> Reporting Info</h1>
<form method="post">
<p>Select a date range: </p><label style="color:#FFF;" for="from">From</label>
<input type="text" id="from" name="from" />
<label style="color:#FFF;" for="to" >to</label>
<input type="text" id="to" name="to" />
<input name="export" type="submit" value="Sort" />
</form>
<?php
$from = $_POST['from'];
$to = $_POST['to'];
echo "from: " . $from;
echo " to: " . $to;
$hostname = "localhost";
$dbusername = "username";
$dbname = "dbname";
$dbpassword = "password";
$link = mysqli_connect($hostname, $dbusername, $dbpassword, $dbname);
if (!$link)
{
die('Connect Error (' . mysqli_connect_errno() . ') ' . mysqli_connect_error());
}
$sql = "SELECT order_number, order_total,m1uzl_virtuemart_orders.created_on,
order_item_name, first_name, email, order_status_code
FROM m1uzl_virtuemart_orders, m1uzl_virtuemart_order_items,
m1uzl_virtuemart_order_userinfos, m1uzl_virtuemart_orderstates
WHERE m1uzl_virtuemart_orders.created_on =
m1uzl_virtuemart_order_items.created_on AND
m1uzl_virtuemart_order_items.created_on =
m1uzl_virtuemart_order_userinfos.created_on AND
m1uzl_virtuemart_orders.created_on >= STR_TO_DATE
('" . $from . "', '%Y-%m-%d')AND
m1uzl_virtuemart_orders.created_on <= STR_TO_DATE
('" . $to . "', '%Y-%m-%d'))AND order_status_code='C'";
if ($result = mysqli_query($link, $sql)) {
echo "
<table width=100% border='1px'>
<tr align='center' bgcolor='#999999'>
<td>Név</td><td>Tagkód</td><td>Termék</td>
<td>Összeg</td><td>Dátum</td><td>Befizető azonosító</td>
</tr>
";
while($row = $result->fetch_assoc()){
$nev = $row['first_name'];
$tagkod = $row['email'];
$termek = $row['order_item_name'];
$osszeg = $row['order_total'];
$datum = $row['m1uzl_virtuemart_orders.created_on'];
$befizaz = $row['order_number'];
echo " <tr align='center'><td> " . $nev;
echo "</td><td> " . $tagkod;
echo " </td><td> " . $termek;
echo " </td><td> " . $osszeg;
echo "</td><td> " . $datum;
echo "</td><td> " . $befizaz;
echo " </td></tr> ";
}
}
echo "</table>";
echo "<form action='export.php'> <input type='submit'
name='export' value= 'Export CSV'></form>";
mysqli_close($link);
?>
And the export.php file:
<?php
header('Content-Disposition: attachment; filename="befizetesek.csv"');
$hostname = "localhost";
$dbusername = "username";
$dbname = "dbname";
$dbpassword = "password";
$dbhandle = mysql_connect($hostname, $dbusername, $dbpassword)
or die("Nem sikerült kapcsolódni az adatbázishoz");
$selected = mysql_select_db($dbname,$dbhandle)
or die("Nincs kiválasztva adatbázis tábla");
$query ="SELECT order_number, order_total,
m1uzl_virtuemart_orders.created_on, order_item_name, first_name, email,
order_status_code
FROM m1uzl_virtuemart_orders, m1uzl_virtuemart_order_items,
m1uzl_virtuemart_order_userinfos, m1uzl_virtuemart_orderstates
WHERE m1uzl_virtuemart_orders.created_on =
m1uzl_virtuemart_order_items.created_on
AND m1uzl_virtuemart_order_items.created_on = m1uzl_virtuemart_order_userinfos.created_on
AND ((m1uzl_virtuemart_orders.created_on >= '$from') AND
(m1uzl_virtuemart_orders.created_on <= '$to'))
AND order_status_code='C'";
$export = mysql_query ($query ) 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";
}
print "$header\n$data";
exit();
?>
You can see the page is http://teszt.tvep.hu/tranzakciok-letoltese.html
Any help greatly appreciate!
