Board index   FAQ   Search  
Register  Login
Board index PHP PHP General

Export my sql Data into Excel format

General discussions related to php

Moderators: macek, egami, gesf

Export my sql Data into Excel format

Postby Gamers » Fri Oct 19, 2012 4:09 am

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.
Gamers
New php-forum User
New php-forum User
 
Posts: 48
Joined: Mon Jun 11, 2012 9:13 am

Re: Export my sql Data into Excel format

Postby seandisanti » Fri Oct 19, 2012 8:27 am

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:
Code: Select all
$sql = "SELECT fields FROM table WHERE datefield BETWEEN ".$_POST['start_date']." AND ".$_POST['end_date']

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.
seandisanti
php-forum Fan User
php-forum Fan User
 
Posts: 783
Joined: Mon Oct 01, 2012 12:32 pm

Re: Export my sql Data into Excel format

Postby Gamers » Fri Oct 19, 2012 8:34 am

Hopefully i will be easier for me, but i dont think so, ok let me try it , after i will update you :)

thanks again sir for the first post
Gamers
New php-forum User
New php-forum User
 
Posts: 48
Joined: Mon Jun 11, 2012 9:13 am

Re: Export my sql Data into Excel format

Postby seandisanti » Fri Oct 19, 2012 8:48 am

no problem. happy to help when i can.
seandisanti
php-forum Fan User
php-forum Fan User
 
Posts: 783
Joined: Mon Oct 01, 2012 12:32 pm

Re: Export my sql Data into Excel format

Postby Gamers » Mon Oct 22, 2012 12:26 am

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);
}
Gamers
New php-forum User
New php-forum User
 
Posts: 48
Joined: Mon Jun 11, 2012 9:13 am

Re: Export my sql Data into Excel format

Postby seandisanti » Mon Oct 22, 2012 9:15 am

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.
seandisanti
php-forum Fan User
php-forum Fan User
 
Posts: 783
Joined: Mon Oct 01, 2012 12:32 pm

Re: Export my sql Data into Excel format

Postby Gamers » Mon Oct 22, 2012 9:49 am

But could u tell me why this error comes, because my first foreach loop is working fine, but why second foreach statement is not working.
Gamers
New php-forum User
New php-forum User
 
Posts: 48
Joined: Mon Jun 11, 2012 9:13 am

Re: Export my sql Data into Excel format

Postby Gamers » Sun Oct 28, 2012 2:01 am

i solved this issues , the problem was i terminated while loop with semicolon(;)
Gamers
New php-forum User
New php-forum User
 
Posts: 48
Joined: Mon Jun 11, 2012 9:13 am


Return to PHP General

Who is online

Users browsing this forum: No registered users and 1 guest

Sponsored by Sitebuilder Web hosting and Traduzioni Italiano Rumeno and antispam for cPanel.