Display comma-separated values

In this topic you will learn how to display the records just inserted. For each student the name of the foreign language courses will be retrieved from the related table and displayed. The logic to achieve this is:

1. First create a recordset that retrieves all students.

2. Display the student name, and apply a repeated region to show all values.

3. Manually add a new query that, for each student retrieves the associated languages and displays them.

Retrieve and display student details

In order to show all student names from the database you must create a recordset:

1. Open the index page from the site root.

2. Go to the Bindings tab of the Application panel > Plus (+) > Recordset (Query). Configure the rsStudents recordset dialog box as follows:
   
   
    

After retrieving student information - the ID, name and list of foreign key to the languages table you have to display them on the page. The first step is to display the student names:

1. Open the index page from the site root if needed.

2. From the Bindings tab expand the rsStudent recordset (click the Plus (+) icon next to its name) and drag and drop the name_std field onto the page.

3. Place the cursor after the dynamic value added to the page and press SHIFT+ENTER to create a line break.

4. Press CTRL+A to select all page content and go to the Application tab of the Insert bar. Click the Repeated Region icon.

5. Configure the Repeat region server behavior as follows:

• In the Recordset drop-down menu select rsStudents.

• In the Show radio group click on the All records option.
  
  
   

• Click OK to apply the repeat region.

6. A grey line is displayed around the region that will be repeated.

To test the page you've created so far press F12 to load it in a browser. The existing student names will be displayed:


 

Next you have to display the language courses that each student is taking.

Retrieve and display the course list

This section of the tutorial will guide you on building a recordset nested within the repeat region. For each student, it retrieves and displays the list of languages. Code is provided for all supported server models:

1. Open the index page in Dreamweaver, if needed.

2. From the View menu select Code. You need to add code that retrieves the list of languages and displays it right after the student name.

3. Locate the area that loops through the recordset and displays the student name. It should look like the following:

• For PHP
  
  <?php echo $row_rsStudents['name_std']; ?>
  <br />
  <?php } while ($row_rsStudents = mysql_fetch_assoc($rsStudents)); ?>
   

• For ASP_VBScript
  
  <%
  While ((Repeat1__numRows <> 0) AND (NOT rsStudents.EOF))
  %>
  <%=(rsStudents.Fields.Item("name_std").Value)%>  <br />
  <%
  <%
  Repeat1__index=Repeat1__index+1
  Repeat1__numRows=Repeat1__numRows-1
  rsStudents.MoveNext()
  Wend
  %>
   

• For ColdFusion
  
  <cfoutput query="rsStudents" > #rsStudents.name_std# <br />
  </cfoutput>
   

4. Place the mouse cursor before the <br /> tag and add code that creates a new recordset, filtered by the value of the languages_std field - the one storing references to the language_lan table. All languages that have their ID in the list will be retrieved by the query. The code to add is:

• For PHP:
  
  <?php
  $query_rsLanguages = "SELECT * FROM language_lan WHERE id_lan IN (".$row_rsStudents['languages_std'].")";
  $rsLanguages = mysql_query($query_rsLanguages, $connStudents) or die(mysql_error());
  $row_rsLanguages = mysql_fetch_assoc($rsLanguages);  
  ?>
   

• For ASP_VBScript:
  
  <%
  Dim rsLanguages
  Dim rsLanguages_numRows
  
  Set rsLanguages = Server.CreateObject("ADODB.Recordset")
  rsLanguages.ActiveConnection = MM_connStudents_STRING
  rsLanguages.Source = "SELECT * FROM language_lan WHERE id_lan IN (" & rsStudents.Fields.Item("languages_std").Value & ")"
  rsLanguages.CursorType = 0
  rsLanguages.CursorLocation = 2
  rsLanguages.LockType = 1
  rsLanguages.Open()
  
  rsLanguages_numRows = 0  
  %>
   

• For ColdFusion:
  
  <cfquery name="rsLanguages" datasource="connection_name">
  SELECT *
  FROM path to mdb file\database.language_lan WHERE id_lan IN (#rsStudents.languages_std#)
  </cfquery>
   

5. The code above works as follows:

• First the query to use is defined - it is a SELECT SQL statement which retrieves the records in the language_lan table where the id_lan column has a value within the current list of languages for the student. Note that the entire code is placed within the repeat region, and it gets executed for each student. Therefore, for each iteration through the loop - or in other words, for each student - the set of languages changes.

• Next the query is executed, and the list of language rows built.

6. Now to display the actual values you have to create a loop, in code view this time. The code to add before the <br /> tag  in the section in step 3 is:

• For PHP:
  
  do {
  echo $row_rsLanguages['name_lan']." ";
  } while ($row_rsLanguages = mysql_fetch_assoc($rsLanguages));
   

• For ASP_VBScript:
  
  <%
  Dim Repeat2__numRows
  Dim Repeat2__index
  Repeat2__numRows = -1
  Repeat2__index = 0
  rsLanguages_numRows = rsLanguages_numRows + Repeat2__numRows
  While ((Repeat2__numRows <> 0) AND (NOT rsLanguages.EOF))
  Response.Write((rsLanguages.Fields.Item("name_lan").Value) & " ")
  Repeat2__index=Repeat2__index+1
  Repeat2__numRows=Repeat2__numRows-1
  rsLanguages.MoveNext()
  Wend
  %>
   

• For ColdFusion:
  
  <cfloop query="rsLanguages">#rsLanguages.name_lan# </cfloop>
   

7. Also add a colon sign after the line that displays the student name, to better separate the names from the list. The line to look for is:

• For PHP:
  
  <?php echo $row_rsStudents['name_std']; ?>
   

• For ASP_VBScript:
  
  <%=(rsStudents.Fields.Item("name_std").Value)%>
   

• For ColdFusion:
  
  <cfoutput query="rsStudents" > #rsStudents.name_std#
   

8. Now you can save the page and press F12 to preview it in a browser. The list of languages is displayed next to each name:
   
   

The last thing you should do is add a link to the page that allows adding a student, to the main index:

1. Open the index page in Dreamweaver if needed.

2. Place the cursor after the last line of content and press Enter / Return to create a new paragraph.

3. Type: Add a student.

4. Select the entered text and click the Folder icon next to the Link text box in the Property Inspector. From the dialog box that opens select the insert page in the site root. Click OK to create the link.

Now students can be viewed and added easily into the database.

In the next topic, an alternative approach to a slightly different problem is presented: how to display comma separated values in another format when using a single table.