How to perform MySQL export data?
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 '. $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 '.$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.
