Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2009-07-13
4
Medium Priority
?
182 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 1000 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 200 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 400 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 111

Assisted Solution

by:Ray Paseur
Ray Paseur earned 400 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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
Originally, this post was published on Monitis Blog, you can check it here . In business circles, we sometimes hear that today is the “age of the customer.” And so it is. Thanks to the enormous advances over the past few years in consumer techno…
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
Suggested Courses

782 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