Everybody! This is important. In a few days, these forums will be moving over to using the totally sweet Discourse platform. To ensure this migration happens smoothly with no loss of content, these forums are currently in a read-only mode. I do apologize for the inconvenience.

There is never a good time to turn the forums off for an extended period of time, but I promise the new forums will be a billion times better. I'm pretty sure of it.

See you all on the other side in a few days, and if you have any (non-technical) questions, please e-mail me at kirupa@kirupa.com. For technical questions, try to find a tutorial that corresponds to what you are looking for and post in the comments section of that page.

Cheers,
Kirupa

Results 1 to 4 of 4

Thread: Need help integrating PHP, MySQL, EXCEL

  1. #1

    Need help integrating PHP, MySQL, EXCEL

    Hello everyone,

    Been a long time since i came to this forum.
    I need a help.
    I have a database(MySQL). User can search through a form and get the related data. Now the resulting data needs to be saved in excel format for which i have the design. Please help. It is urgent

  2. #2
    if you use phpmyadmin export the database to csv format. Excel can then read that and you can save it as an excel worksheet

  3. #3

    I wanted php script

    Quote Originally Posted by LooInSpain View Post
    if you use phpmyadmin export the database to csv format. Excel can then read that and you can save it as an excel worksheet
    Well this is for my client and he wont be using Phpmyadmin.

    Though no problem. I have found the answer for my problem. I forgot the URL from where i got it but I am pasting the code for others who might need it.

    PHP Code:
    <?php 
    include("admin/config.php");


    $SQL "select id,title,detail,status from article";
    //echo $SQL;

    $result mysql_query($SQL)or die(mysql_error());
    $count mysql_num_fields($result);


    for (
    $i 0$i $count$i++){
    $header .= mysql_field_name($result$i)."\t";

    }


    while(
    $row mysql_fetch_row($result)){
    $line '';
    foreach(
    $row as $value){
    if(!isset(
    $value) || $value == ""){
    $value "\t";
    }else{
    # important to escape any quotes to preserve them in the data.
    $value str_replace('"''""'$value);
    # needed to encapsulate data in quotes because some data might be multi line.
    # the good news is that numbers remain numbers in Excel even though quoted.
    $value '"' $value '"' "\t";
    }
    $line .= $value;
    }
    $data .= trim($line)."\n";
    }
    # this line is needed because returns embedded in the data have "\r"
    # and this looks like a "box character" in Excel
    $data str_replace("\r"""$data);


    # Nice to let someone know that the search came up empty.
    # Otherwise only the column name headers will be output to Excel.
    if ($data == "") {
    $data "\nno matching records found\n";
    }

    $header=$header."\n";

    $dir "";
    $fname='Excel.xls';
    $fp fopen($fname,'w');
    fwrite ($fp,$header);
    fwrite ($fp,$data);





    /*
        $select =    "SELECT 'id' as 'ID#', 'title' AS 'Title','detail' as 'DETAIL','category' as 'Category' FROM article
    WHERE status = 1";
     
            
        // run query
        $export = mysql_query($select) or die(mysql_error());
        $fields = mysql_num_fields($export);
        
        // get field info for headers
        $header = '';
        for ( $i = 0; $i < $fields; $i++ )
        {
            $header .= mysql_field_name($export, $i) . "\t";
        }
        
        // extract data and convert into excel readable format
        $data = '';
        while ( $row = mysql_fetch_row($export))
        {
            $line = '';
            foreach ( $row as $value )
            {
                if ( ( !isset($value) ) OR ( $value == "" ) )
                {
                    $value = "\t";
                } 
                else 
                {                
                    $value = str_replace('"', '""', $value);
                    $value = '"' . $value . '"' . "\t";
                }
                $line .= $value;
            }
            $data .= trim($line)."\n";
        }
        $data = str_replace("\r", "", $data);
        
        // return message if query returns no data
        if ( $data == "" )
        {
            $data = "\n(0) Records Found!\n";
        }
        
        // setup headers with no caching
        header("Content-type: application/octet-stream");
        header("Content-Disposition: attachment; filename=data.xls");
        header("Pragma: no-cache");
        header("Expires: 0");
        print "$header\n$data";
    */
    ?>
    this will create a excel file from the mysql. Hope this is of help to others. Thanks

  4. #4
    But one more problem exists.
    Now i need to convert my excel file(.xls) to mysql.
    Any help is appreciated. And yes a php code related article or hep do not suggest softwares

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

Home About kirupa.com Meet the Moderators Advertise

 Link to Us

 Credits

Copyright 1999 - 2012