How to perform MySQL export data?

Asked By 30 points N/A Posted on -
qa-featured

Hi,

How to perform mysql export data? I am using mysql server database for my business company website with the application of PHP programming language. However there are some cases that I have to make a documentation and user manual for the website planning program. I have to export the data in mysql server and transfer it to Microsoft excel application, how can I possibly make it? I need some guidance.

Regards,

Bryan James.

SHARE
Answered By 0 points N/A #178651

How to perform MySQL export data?

qa-featured

Hello,

There are many ways to export data from a MySQL database. You can export the data into an XML file to be displayed on a webpage, export to a text file or to an .SQL file.

To export to a webpage, you can use the example code below:

<? PHP

$hostname = "localhost";
$dbuser = "root";
$dbpassword = "";
$dbname = "Amboseli";

     $db_link=mysql_connect($hostname, $dbuser, $dbpassword)
     Or die ("Unable to connect to the server!");

     mysql_select_db($dbname)
     Or die ("Unable to connect to the database");

        $fields_array=array();
        $num_fields=0;
        $num_row=0;
       

        $sql="select * from bookings order by id ";

// Find position of "FROM" in the query
        $fpos=strpos($sql, 'from');

        // Get string starting from the first word after "FROM"
        $strfrom=substr($sql, $fpos+5, 50);

        // Find the position of the first space after the first word in the string
        $Opos=strpos($strfrom,' ');

        //Get table name. If query pulls data from more than one table only first table name will be read.
        $table=substr($strfrom, 0,$Opos);

       // Get result from a query
        $result=mysql_query ($sql);
        $num_row=mysql_num_rows($result);

        Print ('<html>');
        Print ('<head><title>');
        Print ('View&nbsp'. $table.'</title>');
        Print ('<link rel="stylesheet" href="csstyle.css">');

        Print ("</head>");
        Print ('<body><br>');

        If ($num_row >0)
        {
                //Get number of fields in the query
                $num_fields=mysql_num_fields($result);

     

       # get column metadata
        $i = 0;

         //Set table width 15% for each column
        $width=10 * $num_fields;

        Print ('<br><table width="1000" align="center" border="1"><tr>');
        print('<tr><th colspan='.$num_fields.'>View&nbsp'.$table.'</th></tr>');

         While ($i < $num_fields)
         {

          //Get fields (columns) names
        $meta = mysql_fetch_field($result);

        $fields_array [] =$meta->name;

       //Display column headers in upper case
   Print ('<the><font face=Maiandra GD size=1/><b>'. strtoupper($fields_array[$i]).'</b></font></th>');

                $i=$i+1;
                }

        Print ('</tr>');
            

               //Get values for each row and column
            While ($row=mysql_fetch_row ($result))
            {
             Print ('<tr>');

                    For ($i=0; $i<$num_fields; $i++)
                    {
                    //Display values for each row and column
                    Print ('<TD align="center">'. $row [$i].'</td>');

                    }

            Print ('</tr>');
            }

}

?>

To export to an external file, use the following code:

SELECT … INTO OUTFILE "file_name". In the "file_name", put the name of the file you want the contents to be transferred to.

Thank you.

About Dexter Alberto
Questions
3
Answers
99
Best Answers
34

How to create css image reflection dreamweaver?

Previous

What is the best vbscript style in making enrollment system?

Next

Related Questions