Jump to content

Nested while statements


pizote

Recommended Posts

I am trying to dynamically create a form where I pass a table name and it presents me with a table of all the columns (varying number and names of columns) into an HTML table.  Both of my queries are working, but for some reason, my nested while statements are not working.

Any ideas?

function getLuTableBody($lu_tableName) {
    $tableBodyHtml ="";

    $tableColumns=getTableColumns($lu_tableName);
    $tableData=getTableData($lu_tableName);

    if ($tableData->num_rows > 0) {
        while($row = $tableData->fetch_assoc()) {
            if ($tableColumns->num_rows > 0) {
                $tableBodyHtml .= "<tr>";
                while($column = $tableColumns->fetch_assoc()) {
                    $columnName=$column['COLUMN_NAME'];
                    $tableBodyHtml .= "<td>$row[$columnName]<td>";
                }
                $tableBodyHtml .= "</tr>";
            } else {
                echo "problem with column data";
                }
            }
    } else {
        echo "problem with record data";
        }
    return $tableBodyHtml;
}

 

Here's the contents of the db

id	bundleRole	recordStatus
1	Database	A
2	Web			A
3	Base		A
4	Application	A
5	Print		A


Here's the output

 

<table id="advanced_table" class="table dataTable">
	<thead>
		<tr>
        	<th>Record ID</th>
          	<th>Bundle Role</th>
          	<th>Record Status</th></tr>
  		</thead>
    	<tbody>
			<tr>
            	<td>1</td>
              	<td></td>
              	<td>Database</td>
              	<td></td>
              	<td>A</td>
              	<td></td>
        	</tr>
  		</tbody>
</table>

 

Edited by pizote
added detail
Link to comment
Share on other sites

Post getTableColumns() and getTableData().

Looking at everything, it kinda looks like you're overthinking things - you want the column names as table headers and the data as the table data, right? That's not how your output is set up right now. It's going to loop through all the records in tableData and output the column names for every line of data. But you're not printing the data and it seems like there's no actual header row output. I recommend drawing out what you want to see on paper - map the source for every table cell, then write the code to make that output happen.

Link to comment
Share on other sites

I’ll load the code in a bit. both functions work and return their proper data. The getColumnTables() returns the column table names. The getTableData() returns all data for the table. 
 

the nested while statements are what’s picking at me. The current code returns proper headers. And proper data - for one row. 
 

if I reverse the while statements, I was getting still only one row, but with one record id for each column. 

Link to comment
Share on other sites

Here's the code and results:
 

function getTableColumns($lu_tableName) {
    $columns = "";

    $sqlSelect = "SELECT COLUMN_NAME, COLUMN_COMMENT";
    $sqlFrom = "FROM information_schema.columns";
    $sqlWhere = "WHERE table_name = '$lu_tableName'";
    $sqlOrder = "";
    $columns = sqlCall($sqlSelect,$sqlFrom,$sqlWhere,$sqlOrder);

    return $columns;
}
function getLuTableData($lu_tableName) {
    $data="";
    $sqlSelect = "SELECT *";
    $sqlFrom = "FROM $lu_tableName";
    $sqlWhere = "";
    $sqlOrder = "";
    $data = sqlCall($sqlSelect,$sqlFrom,$sqlWhere,$sqlOrder);

    return $data;
}
function getLuTableBody($lu_tableName) {
    $tableBodyHtml ="";

    $tableColumns=getTableColumns($lu_tableName);
    $tableData=getTableData($lu_tableName);


    if ($tableData->num_rows > 0) {
        while($row = $tableData->fetch_assoc()) {
            if ($tableColumns->num_rows > 0) {
                while($column = $tableColumns->fetch_assoc()) {
                    $columnName=$column['COLUMN_NAME'];
                    $tableBodyHtml .= "<td>$row[$columnName]</td>";
                }
            } else {
                echo "problem with column data";
                }
            }
    } else {
        echo "problem with record data";
        }

    return $tableBodyHtml;
}

 

Screen Shot 2020-06-14 at 08.08.03.png

Screen Shot 2020-06-14 at 08.10.43.png

Screen Shot 2020-06-14 at 08.11.45.png

Edited by pizote
updated code with proper function
Link to comment
Share on other sites

Quote

while($column = $tableColumns->fetch_assoc()) {

You can't loop over the column result-set multiple times.  That code will work for the first data row, but then return false for every other data row because you've already reached the end of the column result set.  

You should fetch that column data into an array before hand then loop over that array.

function getLuTableBody($lu_tableName) {
    $tableColumns=getTableColumns($lu_tableName);
    $columns = [];
    while ($columnData = $tableColumns->fetch_assoc()){
        $columns[] = $columnData['COLUMN_NAME'];
    }

    //Might as well check for 0 columns and bail early.
    if (!$columns){ 
        echo "problem with column data";
        return; 
    }

    $tableBodyHtml ="";
    $tableData=getTableData($lu_tableName);
    if ($tableData->num_rows > 0) {
        while($row = $tableData->fetch_assoc()) {
            foreach ($columns as $columnName){
                $tableBodyHtml .= "<td>$row[$columnName]</td>";
            }
        }
    } else {
        echo "problem with record data";
    }

    return $tableBodyHtml;
}

 

You're also missing your <tr> </tr> tags for each row, and you shouldn't be echo'ing stuff if you find a problem. Throw an exception or return false or something.

  • Great Answer 1
Link to comment
Share on other sites

6 minutes ago, kicken said:

You can't loop over the column result-set multiple times.  That code will work for the first data row, but then return false for every other data row because you've already reached the end of the column result set.  

You should fetch that column data into an array before hand then loop over that array.

 

You're also missing your <tr> </tr> tags for each row, and you shouldn't be echo'ing stuff if you find a problem. Throw an exception or return false or something.

Thank you! 

Creating the column[] and then populating it seemed to do the trick.

The <tr> tags was discovered and corrected previously.

FYI - this allows me to have one edit/add page for a multitude of lookup tables.  I have a menu that is auto-generated so any time I add a lookup table, it populates the menu.  The link sends a POST to the table listing, which generates the table header and table body based on the $_GET.  Each row is then linked to a single edit/add page.

Three images:  

Dynamic Menu - generated from the TABLE_COMMENT for table names that match a pattern

Dynamic table list #1 

Dynamic table list #2

Now to create the add/edit pages

Screen Shot 2020-06-14 at 08.43.40.png

Screen Shot 2020-06-14 at 08.43.58.png

Screen Shot 2020-06-14 at 08.44.22.png

Link to comment
Share on other sites

You don't need a separate query to get the column names. If your fetch() returns an associative array, the keys are the column names.

$res = $db->query("SELECT * FROM lu_phase");

$row = $db->fetch(PDO::FETCH_ASSOC);
echo "<table>";
echo "<tr><th>" . join('</th><th>', array_keys($row)) . "</th></tr>";
do {
    echo "<tr><td>" . join('</td><td>', $row) . '</td></th>';
} while ($row = $db->fetch(PDO::FETCH_ASSOC));
echo "</table>";                    

 

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.