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..

Answer 1

@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.

Answer 2

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.