What is the best way to substitute a variable within a loop?

Alright, here's the deal.

I'm creating a simple CMS of sorts for managing video files.  Obviously, one of the fundamental things this script must do is create an HTML table to display the videos currently in the system.  To resolve this, I did some Googling for PHP code snippets that would accomplish what I'm needed.

What I found was a script (attached) that displays the database records in an HTML table, with one major flaw (in my case).  The top cell of each column (explaining what the data is) is the name derived from the MySQL database.  Makes perfect sense to me, but not what I want to be doing when other people begin to use it.

With that said, I was going to hardcode the values in, but I know there's a better way to do this.

The first idea that popped into my head was to create an IF statement on line 23 that would do the following:

if($field_name = "desc") {
   $field_name ="Description" }

Of course this could be a function too, but I think that I'm moving the wrong way on this one.  Any PHP / MySQL gurus that can lend a hand would be greatly appreciated.
function display_db_query($query_string, $connection,
	$header_bool, $table_params)
	{
		// perform the database query
		$result_id = mysql_query($query_string, $connection)
		or die("display_db_query:" . mysql_error());
		
		// find out the number of columns in result
		$column_count = mysql_num_fields($result_id)
		or die("display_db_query:" . mysql_error());
		
		// Here the table attributes from the $table_params variable are added
		print("<TABLE $table_params >\n");
		
		// optionally print a bold header at top of table
		if ($header_bool)
		{
			print("<TR>");
			for ($column_num = 0;
			$column_num < $column_count;
			$column_num++)
			{
				$field_name =
				mysql_field_name($result_id, $column_num);
				print("<TH>$field_name</TH>");
			}
			print("</TR>\n");
		}
		
		// print the body of the table
		while ($row = mysql_fetch_row($result_id))
		{
			print("<TR ALIGN=LEFT VALIGN=TOP>");
			for ($column_num = 0;
			$column_num < $column_count;
			$column_num++)
			{
				print("<TD>$row[$column_num]</TD>\n");
			}
				print("</TR>\n");
		}
		print("</TABLE>\n"); 
	}
 
	function display_db_table($tablename, $connection,
	$header_bool, $table_params)
	{
		$query_string = "SELECT * FROM $tablename";
		display_db_query($query_string, $connection,
		$header_bool, $table_params);
	}

Open in new window

LVL 1
WavyGravyAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

brodaseatingCommented:
Hi,

You have right idea with hard-coding it. Often your DB table includes meta-data that you dont want to display - modified on, last updated by, column size etc. A common practice is to choose you display columns ahead of time: Filename, File Size, Format, Submitted By, Duration etc, then loop through your record set populating the appropriate column.

Broda.

<table>
	<th>Filename</th>
	<th>File Size</th>
	<th>Duration</th>
	<?		
		while ($row = mysql_fetch_assoc($result))
		{
			echo "<td>$row['filename']</td>";
			echo "<td>$row['size']</td>";
			echo "<td>$row['duration']</td>";
		}
	?>
</table>

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
twocandlesCommented:
Column headers are usually hardcoded with your custom values. If you don't want to hardcode them into the scrip I would use this approach: create an array mapping db column names to your header names, for example:

$my_headers = [ "desc" => "Description", ... ]

with you headers. You just have to index this array with the value yielded by mysql_field_name
0
Graceful_PenguinCommented:
Try this code, I just added a nother param to the function witch takes an array of strings with the table headers.
function display_db_query($query_string, $connection,
	$header_bool, $table_params, $tableheaders)
	{
		// perform the database query
		$result_id = mysql_query($query_string, $connection)
		or die("display_db_query:" . mysql_error());
		
		// find out the number of columns in result
		$column_count = mysql_num_fields($result_id)
		or die("display_db_query:" . mysql_error());
		
		// Here the table attributes from the $table_params variable are added
		print("<TABLE $table_params >\n");
		
		// optionally print a bold header at top of table
		if ($header_bool)
		{
			print("<TR>");
			for ($column_num = 0;
			$column_num < $column_count;
			$column_num++)
			{
				print('<TH>'.$tableheaders[$column_num].'</TH>');
			}
			print("</TR>\n");
		}
		
		// print the body of the table
		while ($row = mysql_fetch_row($result_id))
		{
			print("<TR ALIGN=LEFT VALIGN=TOP>");
			for ($column_num = 0;
			$column_num < $column_count;
			$column_num++)
			{
				if (count($column_num) < $column_count)
				{
					$field_name = mysql_field_name($result_id, $column_num);
					print("<TH>$field_name</TH>");
				}
				else
					print("<TD>$row[$column_num]</TD>\n");
			}
				print("</TR>\n");
		}
		print("</TABLE>\n"); 
	}
 
	function display_db_table($tablename, $connection,
	$header_bool, $table_params)
	{
		$query_string = "SELECT * FROM $tablename";
		display_db_query($query_string, $connection,
		$header_bool, $table_params);
	}

Open in new window

0
Ray PaseurCommented:
You might use something like this function to translate the field names into the text you want to display.  Probably you want to package this function (along with others) into a "config" type file that gets loaded once into each script.

Add a line like this after line 24 of your code snippet above:

$field_name = translate_field_name($field_name);

Best regards, ~Ray
function translate_field_name($field_name)
{
// MAN PAGE HERE: http://us.php.net/manual/en/control-structures.switch.php
// NOTE THIS: The switch statement is similar to a series of IF statements ON THE SAME EXPRESSION.
 
    switch(strtolower($field_name))
    {
        case "desc"  : $fn = 'Description';  break;
        case "vid"   : $fn = 'Video Name';   break;
        case "vidn"  : $fn = 'Video Number'; break;
        case "price" : $fn = 'Price (each)'; break;
        case "desc"  : $fn = 'Description';  break;
 
        default      : $fn = '???';
    }
    return $fn;
}

Open in new window

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.