Solved

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

Posted on 2009-07-13
4
171 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 110

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

Upcoming Webinar: Securing your MySQL/MariaDB data

Join Percona’s Chief Evangelist, Colin Charles as he presents Securing your MySQL®/MariaDB® data on Tuesday, July 11, 2017 at 7:00 am PDT / 10:00 am EDT (UTC-7).

Question has a verified solution.

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

Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
The viewer will learn how to dynamically set the form action using jQuery.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

717 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