Nested while statements

[pizote]

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 June 13, 2020 by pizote
added detail

[maxxd]

45 minutes ago, pizote said:

$tableBodyHtml .= "<td>$row[$columnName]<td>";

Look closely at the tag pair in this line.

[pizote]

Thank you Maxxd - that solves the duplicate <td>, but it still doesn't resolve that only one record of $tableData is being returned.  Still stumped on that one.

[maxxd]

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.

[pizote]

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. 

[pizote]

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;
}

 

Edited June 14, 2020 by pizote
updated code with proper function

[kicken]

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.

[pizote]

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

[Barand]

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>";