Hi ,
I am working on the project, which is based on php, and using backend mysql database, Here is the summary of the project.
I actually take a information from the users on the daily basic about their task. i have store this information into a my sql database all the information is store in the single file.
My senior want reporting facilities they want to take information in Excel or PDF format.
so for this i had create a webpage and text box ,dropdown box & and button. in the text box i want user to enter date(date they want to fetch the data) and from dropdown box user can select the file format of data(PDF OR XLS) and one button which is export the file in the selected format.
but dont know how to do this, if anyone have ever done this before then please help me in this. i will be very thankfull.
Hope you understand what is want .
Please reply me ASAP.
Export my sql Data into Excel format
Moderators: egami, macek, gesf
-
- php-forum Fan User
- Posts: 973
- Joined: Mon Oct 01, 2012 12:32 pm
Don't worry, it's easier than you think. when you get date/time from user, make sure to convert the format on it to be the same as your database, and then your query would just be:
depending on your date format in the database you may need to add single quotes around the date values in the where field.
once you've gotten your result set, to send it to excel, just output your headers and then field values to a file separated by commas. save with extension of .xls and any version of excel should have no problem opening it.
Code: Select all
$sql = "SELECT fields FROM table WHERE datefield BETWEEN ".$_POST['start_date']." AND ".$_POST['end_date']
once you've gotten your result set, to send it to excel, just output your headers and then field values to a file separated by commas. save with extension of .xls and any version of excel should have no problem opening it.
-
- php-forum Fan User
- Posts: 973
- Joined: Mon Oct 01, 2012 12:32 pm
no problem. happy to help when i can.
I tried And i think i almost got succeed but facing one error.
Warning: Invalid argument supplied for foreach() in /var/www/html/checklist/Project/reporting.php on line 88 I marked with bold below where i have a error in foreach statement
I am using text box and one button in my page, and exporting the data using date which is entered by user.
[script]
if(isset($_POST['submit']))
{
$filename = 'uploads/'.strtotime("now").'.csv';
//echo $filename;
$fp=fopen($filename,"w");
$selectcondition = "SELECT * FROM sample where DATE='".$_POST['search']."'";
$mydata=mysql_query($selectcondition,$con);
$row=mysql_fetch_assoc($mydata);
$seperator="";
$comma="";
foreach($row as $name => $value)
{
$seperator .= $comma . '' .str_replace('','""',$name);
$comma = ",";
}
$seperator .= "\n";
echo $seperator;
fputs($fp,$seperator);
mysql_data_seek($mydata, 0);
while ($row=mysql_fetch_assoc($mydata));
{
$seperator="";
$comma="";
foreach($row as $name => $value) //error line
{
$seperator .= $comma . '' .str_replace('','""',$value);
$comma = ",";
}
$seperator .= "\n";
fputs($fp,$seperator);
}
fclose($fp);
}
Warning: Invalid argument supplied for foreach() in /var/www/html/checklist/Project/reporting.php on line 88 I marked with bold below where i have a error in foreach statement
I am using text box and one button in my page, and exporting the data using date which is entered by user.
[script]
if(isset($_POST['submit']))
{
$filename = 'uploads/'.strtotime("now").'.csv';
//echo $filename;
$fp=fopen($filename,"w");
$selectcondition = "SELECT * FROM sample where DATE='".$_POST['search']."'";
$mydata=mysql_query($selectcondition,$con);
$row=mysql_fetch_assoc($mydata);
$seperator="";
$comma="";
foreach($row as $name => $value)
{
$seperator .= $comma . '' .str_replace('','""',$name);
$comma = ",";
}
$seperator .= "\n";
echo $seperator;
fputs($fp,$seperator);
mysql_data_seek($mydata, 0);
while ($row=mysql_fetch_assoc($mydata));
{
$seperator="";
$comma="";
foreach($row as $name => $value) //error line
{
$seperator .= $comma . '' .str_replace('','""',$value);
$comma = ",";
}
$seperator .= "\n";
fputs($fp,$seperator);
}
fclose($fp);
}
-
- php-forum Fan User
- Posts: 973
- Joined: Mon Oct 01, 2012 12:32 pm
your foreach isn't necessary. your while loop is already separating your result set into rows, and you're working with one row at a time.