How to put results of multiple querys properly in one table

Question

<center><h2>Kaarten overzicht</h2></center>

<table border="1" id="table">
    <tr>
        <th>Patiente Naam</th>
        <th>Arts</th>
        <th>Huisarts</th>
        <th>Diagnose</th>
    </tr>

    <!--- Alle informatie van patiente in een table zetten. --->
    <cfloop query="VARIABLES.overzicht">
        <cfoutput>
            <tr>
                <td>#Voornaam# #Achternaam#</td>
        </cfoutput>
    </cfloop>
    <cfloop query="VARIABLES.overzichtArtsen">
        <cfoutput>
                <td>#Voornaam# #Achternaam#</td>
        </cfoutput>
    </cfloop>
    <cfloop query="VARIABLES.overzichtHuisartsen">
        <cfoutput>
                <td>#Voornaam# #Achternaam#</td>
        </cfoutput>
    </cfloop>
    <cfloop query="VARIABLES.overzichtDiagnose">
        <cfoutput>
                <td>#Type#</td>
            </tr>   
        </cfoutput>
    </cfloop>
</table>

It doesn't come out the way I wanted the results are on wrong places.. I use ColdFusion with the framework Fusebox. And the queries are SELECT * FROM [table_name];.

Please help..

You have 4 different queries for all this? You should only need 1 query, joining all your tables together — duncan, May 02 '14 at 09:12
cfoutput has a query attribute so you could get rid of the cfloops and put if you want to cut the code down. — Will, May 02 '14 at 09:22
Have you tried dumping out the 4 queries to make sure they are all different? — Will, May 02 '14 at 09:23

Charlie · Accepted Answer · 2014-05-02T16:37:48.083

@Duncan is correct about joining tables being the likely best solution, but here's an answer to your question on how to reference multiple queries in one loop.

This assumes that your queries all returned the same number of records.

<center><h2>Kaarten overzicht</h2></center>

<table border="1" id="table">
    <tr>
        <th>Patiente Naam</th>
        <th>Arts</th>
        <th>Huisarts</th>
        <th>Diagnose</th>
    </tr>

    <!--- Alle informatie van patiente in een table zetten. --->
    <cfoutput>
    <cfloop query="VARIABLES.overzicht">
    <tr>
        <td>#Voornaam# #Achternaam#</td>
        <td>
          #VARIABLES.overzichtArtsen.Voornaam[CurrentRow]#
          #VARIABLES.overzichtArtsen.Achternaam[CurrentRow]#
        </td>
        <td>
          #VARIABLES.overzichtHuisartsen.Voornaam[CurrentRow]#
          #VARIABLES.overzichtHuisartsen.Achternaam[CurrentRow]#
        </td>
        <td>
          #VARIABLES.overzichtDiagnose.Type[CurrentRow]#
        </td>
    </tr>
    </cfloop>
    </cfoutput>
</table>

Queries are accessible as a struct with keys for each column, and each column is an array of values. CurrentRow is the index of the row you are currently looping over in the cfloop.

score 0 · Answer 2 · answered May 02 '14 at 12:04

Duncan's comment about joining tables is valid, but even if you followed it, you might still have a problem because you have opening and closing tags inside different loops.

Your code start by creating a single table row with 4 cells. Then you have an opening tag inside a loop, but no closing tag. You now have malformed html which is why your display is not what you had hoped for.

It's hard to suggest alternative code because it it not entirely clear what your final output is supposed to look like.

How to put results of multiple querys properly in one table

2 Answers2