Display number of contacts per company

In this page you will use MX Query Builder to modify the main index.php page so that it will display the number of contacts per company. To get a count of a company's contacts, you will have to modify the master1company_com recordset, which was generated by the Nested Repeat Region server behavior.

The steps to take in order to accomplish this objective are as follows:

1. Begin by opening the main index.php page. In the Application panel, under the Bindings tab, you'll see the two queries which were generated by the Nested Repeat Region earlier in the tutorial.
   
   
    

2. Double click on the first recordset, master1company_com, to open it.

3. When the recordset window opens, remove the ORDER By name_com line from the SQL query. This step makes the query easier to view when you open MX Query Builder.
   
   
    

4.  Once the query code looks like the one below, click the QuB3 button on the right:
   
   
    

5. In the QuB Visual Recordset window that appears, click the New Query button:
   
   
    

6. Type a temporary name for the query like "temp", then click the OK button:
   
   
    

7. After clicking OK, the QuB interface will open in a separate window. On the left side there's a Tables panel containing a list of the tables in the current database. Select both tables as shown below:
   
   
    

8. Now both tables will be in the Database Diagram. Create a table relation by dragging the idcom_com foreign key and dropping it on the id_com field:
   
   
    

9. A blue line will now appear between the two tables. You don't have to do anything, just look at it.
   
   
    

10. For this query we will use the id_com, name_com and idcom_con fields. Use the checkboxes to select them from the Database Diagram:
    
    
     

11. After checking the boxes, the fields will now appear below in the Query Management panel.
    
    
     

12. With the necessary fields now listed, the various options can be used to further organize the data. On the contact_con.idcom_con field, we'll use the count option in the Grouping drop-down menu.
    
    
     

13. This last step will give you a count of all the contacts associated with a company. You can verify the result in the SQL/Results Preview Panel:
    
    
     

14. If the results match what you're looking for, then save the Query using the Query menu:
    
    
     

15. Now that the query is saved, you can close the MX Query Builder interface and go back into Dreamweaver. The QuB Visual Recordset window should still be open.

16. In the Query name dropdown, select the query you just created:
    
    
     

17. After selecting the query name, the SQL text area will contain the code built from the Query Builder interface.
    
    
     

18. Click OK. The new recordset will now appear alongside the others in the Application panel.

19. Now in the Application panel > Bindings tab, you can open the recordset and find the count_idcom_con1 field listed:
    
    
     

20.  Select the text labeled "Actions" in the Nested Repeat Region table:
    
    
     

21. Take the count_idcom_con_1 field (shown in step 19) and drag it over to the Nested Repeat Region table. Drop it in the place of the "Actions" text. When you're done, the table should look like the one below:
    
    
     

22. Just type the word "contacts" in the same cell and you're done:
    
    
     

Now you can view the finished page, with the number of contacts in the browser:
 

This concludes the Contact Management Application tutorial. In the next tutorial, Contact Form, you will build a form for sending e-mail messages to one or several contacts from the database.