Solved

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

Posted on 2009-07-13
4
163 Views
Last Modified: 2012-05-07
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

0
Comment
Question by:WavyGravy
4 Comments
 
LVL 1

Accepted Solution

by:
brodaseating earned 250 total points
ID: 24839339
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
 
LVL 6

Assisted Solution

by:twocandles
twocandles earned 50 total points
ID: 24839378
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
 
LVL 9

Assisted Solution

by:Graceful_Penguin
Graceful_Penguin earned 100 total points
ID: 24839582
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
 
LVL 109

Assisted Solution

by:Ray Paseur
Ray Paseur earned 100 total points
ID: 24841442
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

Featured Post

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article discusses four methods for overlaying images in a container on a web page
This article discusses how to create an extensible mechanism for linked drop downs.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

831 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question