Databases
StackOverflow2013
Postsdbo
POmysql export into single excell cell different records
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POmysql export into single excell cell different records
    primarykey
    data
    text
    <p>Hello I am exporting data from mysql table to excel, I am using a popular code circulating over the internet for the excel export. The problem is that inside my SQL query I am going over a few selections from table which results should be put inside the same excel cell. </p> <p>Basicaly in one excel cell for column name "titulo" and "status" there are variouse results from the table, right now it lists only one result and I would like to put all the results in one cell of excel devided with comma for example.</p> <p>Here is the code with the SQL dump: </p> <pre><code>$sql = "SELECT users.*, cursos.titulo, subscriptions.status FROM users LEFT OUTER JOIN subscriptions ON users.userID = subscriptions.user_id LEFT OUTER JOIN cursos ON cursos.id = subscriptions.curso_id"; //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 = 0; //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=MDT_DB_$now_date.$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("$title\n\n"); } //define separator (defines columns in excel &amp; tabs in word) $sep = "\n"; //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"; 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("$title\n"); } //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("/\r\n|\n\r|\n|\r/", " ", $schema_insert); $schema_insert .= "\t"; PRINT(TRIM($schema_insert)); PRINT "\n"; } } ?&gt; </code></pre> <p>So right now in php what this is doing is exporting all the data from table users depending how many times user_id is inside the subscription table. I would like the information from the subscription table to be put inside a single excel cell for every user</p> <p><img src="https://i.stack.imgur.com/grUts.jpg" alt="cursos database"></p> <p><img src="https://i.stack.imgur.com/sgWjM.jpg" alt="enter image description here"></p> <p>What I think right now is that maybe the SQL Query is not the correct one because right now it creates new arrays for every match from the subscription table and it list all the infromation from users just with diferent fields titulo and status</p>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USJohn Siniger
    1. USJohn Siniger
    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. This table or related slice is empty.
    1. COYou probably want [group_concat()](http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html)
      singulars
      1. POmysql export into single excell cell different records
      1. USMarc B
    2. COOut of interest, why do you put all the PHP code in capitals? It's rather odd to see... PL/SQL background? ;-)
      singulars
      1. POmysql export into single excell cell different records
      1. USFlukey
 

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