Databases
StackOverflow2013
Postsdbo
POhow to print the value instead of id in xls file with php and mysql
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POhow to print the value instead of id in xls file with php and mysql
    primarykey
    data
    text
    <p>I m using PHP Mysql.i submit a form and fetch the data into a table format in my page.now i want that the user can download the xls file from that table but my problem is that in my database table few values are in id form how can i retrieve them in xls file coz in xls file its showing id's as there are id's in databse table.i dont want to submit the values as name coz name can be more than one.</p> <p>here is my download xls link code:</p> <pre><code>&lt;span style="float:right;"&gt;&lt;a href="customer_xls.php" type="button" class="btn"&gt;Download XLS&lt;/a&gt;&lt;/span&gt; </code></pre> <p>and here is the customer_xls.php file:</p> <pre><code>&lt;?php //EDIT YOUR MySQL Connection Info: $DB_Server = "localhost"; //your MySQL Server $DB_Username = "root"; //your MySQL User Name $DB_Password = ""; //your MySQL Password $DB_DBName = "xyz"; //your MySQL Database Name $DB_TBLName = "customers"; //your MySQL Table Name // $term = $_REQUEST['term']; //$DB_TBLName, $DB_DBName, may also be commented out &amp; passed to the browser //as parameters in a query string, so that this code may be easily reused for //any MySQL table or any MySQL database on your server //DEFINE SQL QUERY: //you can use just about ANY kind of select statement you want - //edit this to suit your needs! $sql = "Select * from $DB_TBLName"; //die(); //Optional: print out title to top of Excel or Word file with Timestamp //for when file was generated: //set $Use_Titel = 1 to generate title, 0 not to use title $Use_Title = 1; //define date for title: EDIT this to create the time-format you need $now_date = date('m-d-Y H:i'); //define title for .doc or .xls file: EDIT this if you want $title = "Dump For Table $DB_TBLName from Database $DB_DBName on $now_date"; /* Leave the connection info below as it is: just edit the above. (Editing of code past this point recommended only for advanced users.) */ //create MySQL connection $Connect = @mysql_connect($DB_Server, $DB_Username, $DB_Password) or die("Couldn't connect to MySQL:&lt;br&gt;" . mysql_error() . "&lt;br&gt;" . mysql_errno()); //select database $Db = @mysql_select_db($DB_DBName, $Connect) or die("Couldn't select database:&lt;br&gt;" . mysql_error(). "&lt;br&gt;" . mysql_errno()); //execute query $result = @mysql_query($sql,$Connect) or die("Couldn't execute query:&lt;br&gt;" . mysql_error(). "&lt;br&gt;" . mysql_errno()); //if this parameter is included ($w=1), file returned will be in word format ('.doc') //if parameter is not included, file returned will be in excel format ('.xls') if (isset($w) &amp;&amp; ($w==1)) { $file_type = "msword"; $file_ending = "doc"; }else { $file_type = "vnd.ms-excel"; $file_ending = "xls"; } //header info for browser: determines file type ('.doc' or '.xls') header("Content-Type: application/$file_type"); header("Content-Disposition: attachment; filename=$DB_TBLName.$file_ending"); header("Pragma: no-cache"); header("Expires: 0"); /* Start of Formatting for Word or Excel */ if (isset($w) &amp;&amp; ($w==1)) //check for $w again { /* FORMATTING FOR WORD DOCUMENTS ('.doc') */ //create title with timestamp: if ($Use_Title == 1) { echo("$titlenn"); } //define separator (defines columns in excel &amp; tabs in word) $sep = "\r"; //new line character while($row = mysql_fetch_row($result)) { //set_time_limit(60); // HaRa $schema_insert = ""; for($j=0; $j&lt;mysql_num_fields($result);$j++) { //define field names $field_name = mysql_field_name($result,$j); //will show name of fields $schema_insert .= "$field_name:t"; if(!isset($row[$j])) { $schema_insert .= "NULL".$sep; } elseif ($row[$j] != "") { $schema_insert .= "$row[$j]".$sep; } else { $schema_insert .= "".$sep; } } $schema_insert = str_replace($sep."$", "", $schema_insert); $schema_insert .= "\t\n"; print(trim($schema_insert)); //end of each mysql row //creates line to separate data from each MySQL table row print "n----------------------------------------------------n"; } }else{ /* FORMATTING FOR EXCEL DOCUMENTS ('.xls') */ //create title with timestamp: if ($Use_Title == 1) { echo("$titlen"); } //define separator (defines columns in excel &amp; tabs in word) $sep = "\t"; //tabbed character //start of printing column names as names of MySQL fields for ($i = 0; $i &lt; mysql_num_fields($result); $i++) { echo mysql_field_name($result,$i) . "\t"; } print("\n"); //end of printing column names //start while loop to get data while($row = mysql_fetch_row($result)) { //set_time_limit(60); // HaRa $schema_insert = ""; for($j=0; $j&lt;mysql_num_fields($result);$j++) { if(!isset($row[$j])) $schema_insert .= "NULL".$sep; elseif ($row[$j] != "") $schema_insert .= "$row[$j]".$sep; else $schema_insert .= "".$sep; } //$schema_insert = str_replace($sep."$", "", $schema_insert); //following fix suggested by Josue (thanks, Josue!) //this corrects output in excel when table fields contain n or r //these two characters are now replaced with a space //$schema_insert = preg_replace("/rn|nr|n|r/", " ", $schema_insert); $schema_insert .= "\n\t"; print(trim($schema_insert)); print "\n\r"; } } ?&gt; </code></pre>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. This table or related slice is empty.
    1. USMontiyago
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POhow to print the value instead of id in xls file with php and mysql
      1. This table or related slice is empty.
      1. VTUpMod
    1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload