Convert db fields to "pretty names"

[RopeADope]

Hi all.

 

I was wondering if there was a way (other than retrieving db fields with a query) to show something like "customer_id" as "Customer ID".  Is there a functionality of this nature in either MySQL or PHP?  I'm aware of preg_replace and such but that would still require two queries; one for the field names and one for the data (right?)

[Psycho]

Personally, I would recommend against using the db field names in this manner. You should have the "labels" in the application explicitly defined. Either hard code the values in your code, or even better, use a resource file. A resource file allows you to change the name of a label throughout your application by making one change and it allows you to re-purpose the application for different languages if you so choose.

 

By programatically using the DB names you risk displaying text that may be inappropriate/confusion if a DB field is changed at a later time and you have now boxed yourself into using names that have to be able to be converted to a "friendly" value that may not make sense for your DB structure.

 

But, to answer your question anyway, you can simply replace all underscores with a space and then use ucwords() to upper-case the first letter of every word. You don't need two queries. The field name is available in the query results as long as you use the right mysql_fetch statement.

 

Here is a rough example

<?php

$query = "SELECT * FROM table";
$result = mysql_query($query);

$show_header = true;
$HTMLoutput = '';
while($row = mysql_fetch_assoc($result))
{
    if($show_header)
    {
        //Display header row if first record
        $HTMLoutput .= "<tr>\n";
        foreach($row as $label => $value)
        {
            $label = ucwords(str_replace('_', '', $label));
            $HTMLoutput .= "<th>{$label}</th>\n";
        }
        $HTMLoutput .= "</tr>\n";
        $show_header = false;
    }

    //Display record row
    $HTMLoutput .= "<tr>\n";
    foreach($row as $value)
    {
        echo "<td>{$value}</td>\n";
    }
    $HTMLoutput .= "<tr>\n";
}

?>
<table>
<?php echo $HTMLoutput; ?>
</table>

[RopeADope]

Thanks for the prompt reply!  Can you direct me to any resources (or give advice) on the resource file you mentioned?  I think that's probably what I want to do because there are several database fields displayed in the application currently.  It'd be nice to change them globally with ease.

[Psycho]

You could try and google PHP and language file or localization. I typically just build my own solution. Here is a very rough example

 

en.php (english language file)

$__TEXT__ = array
(
    'name_label' => "Name",
    'customer_id_label' => "Customer ID",
    'address_label' => "Address"
}

 

If you want to support different languages, you would have a separate file for each and you would need to implement logic to determine the appropriate language for each user and choose the right one. Otherwise just hard code the language file you will use.

 

Then in your scripts use a reference to the variable to use.

include('language\en.php');

$query = "SELECT * FROM table";
$result = mysql_query($query);

$HTMLoutput = '';
while($row = mysql_fetch_assoc($result))
{
    $HTMLoutput .= "<tr>\n";
    $HTMLoutput .= "  <th>{$__TEXT__['name_label']}</th>\n";
    $HTMLoutput .= "  <td>{$row['name']}</td>\n";
    $HTMLoutput .= "<tr>\n";
    $HTMLoutput .= "<tr>\n";
    $HTMLoutput .= "  <th>{$__TEXT__['client_id_label']}</th>\n";
    $HTMLoutput .= "  <td>{$row['client_id']}</td>\n";
    $HTMLoutput .= "<tr>\n";
    $HTMLoutput .= "<tr>\n";
    $HTMLoutput .= "  <th>{$__TEXT__['address_label']}</th>\n";
    $HTMLoutput .= "  <td>{$row['address']}</td>\n";
    $HTMLoutput .= "<tr>\n";
    $HTMLoutput .= "<tr><td colspan=\"2\"> </td></tr>\n";
}

[The Little Guy]

You could do something like this:

 

select column_a as `Column A`, first_name as `First Name` from members where member_id =1234;

[RopeADope]

I have a function that I use to auto-build input forms.  How would I apply the $__TEXT__ array to this?

 

Instead of

ucwords(preg_replace("/_/"," ",$fields->name))

Could I do

$__TEXT__[$fields->name]

?

 

The function:

function build_form($table_name){
   //The MySQL statement
$sql="SELECT * FROM $table_name";
//Result variable
$result=mysql_query($sql);
//If the result is empty, return.
if($result=="") return;
$i=0;
//Echo some HTML for the form
echo "<form method=\"post\" action=\"/php/process_data.php\">";
echo "<input type=\"hidden\" name=\"selected_table\" value=\"" . $table_name . "\"/>";
echo "<table>";
//Echo the table name
echo "<tr><td colspan=\"2\" style=\"font:1em arial;font-weight:bold;text-align:center;\">Input Form: " . $table_name ."</td></tr>";
//While $i is less than the number of returned fields, complete this loop
while ($i < mysql_num_fields($result)){
	$fields=mysql_fetch_field($result,$i);
	if(preg_match("/ID/",$fields->name) or preg_match("/_id/",$fields->name)){
   		   echo "<tr><td>" . ucwords(preg_replace("/_/"," ",$fields->name)) . "</td><td><input type=\"text\" size=\"30\" disabled=\"disabled\" name=\"" . $fields->name . "\" /></td></tr>";
	   }elseif(preg_match("/comment/i",$fields->name)){
	      echo "<tr><td>" . ucwords(preg_replace("/_/"," ",$fields->name)) . "</td><td style=\"padding:3px;\"><textarea style=\"width:98%;height:50px;resize:none;\" name=\"" . $fields->name . "\" /></textarea></td></tr>";
	   }elseif(preg_match("/owner/i",$fields->name)){
	      echo "<tr><td>" . ucwords(preg_replace("/_/"," ",$fields->name)) . "</td><td style=\"padding:3px;\"><select style=\"width:100%;height:24px;\" name=\"" . $fields->name . "\" /><option value=\"\"> </option>";
               $owner_sql="SELECT employee_id,last_name,first_name FROM employees";
               	$owner_result=mysql_query($owner_sql);
               	$y=0;
               	while($y < mysql_num_rows($owner_result)){
               	   $id=mysql_result($owner_result,$y,"employee_id");
               	   $last=mysql_result($owner_result,$y,"last_name");
              	   $first=mysql_result($owner_result,$y,"first_name");
              	   echo "<option value=\"$id\">$first $last</option>";
              	   $y++;
              	   };
           echo "</select></td></tr>";
   }else{
      echo "<tr><td>" . ucwords(preg_replace("/_/"," ",$fields->name)) . "</td><td><input type=\"text\" size=\"30\" name=\"" . $fields->name . "\" /></td></tr>";
	   };
	$i++;
	};
echo "<tr><td colspan=\"2\" style=\"text-align:center;\"><input type=\"submit\" value=\"Submit Data\" style=\"width:75%\" /></td></tr>";
echo "</table>";
echo "</form>"; 
};

[wildteen88]

Yes that should work. Just make sure you have populated the $__TEXT__ array for all your field names.

[RopeADope]

Ok so here's what I tried...

 

The line

echo "<tr><td>" . $__TEXT__[$fields->name] /*ucwords(preg_replace("/_/"," ",$fields->name))*/ . "</td><td style=\"padding:3px;\"><textarea style=\"width:98%;height:50px;resize:none;\" name=\"" . $fields->name . "\" /></textarea></td></tr>";

 

en.php

$__TEXT__=array
(
   'strategy'=>"Strategy",
   'Comments'=>"Comments"
);

 

It doesn't print out anything though.  I also tried it with the table name in the function $__TEXT__[$table_name] but it didn't print anything out.

[RopeADope]

~Bump~

[Muddy_Funster]

try just

$__TEXT__[$fields]

[RopeADope]

Still prints out blanks.  I checked the source for the page and there are just a bunch of empty <td></td> where the labels used to reside.

[RopeADope]

Ugh, this is driving me nuts.  This should in theory be very easy...I'm just accessing an array to print a label...

[PFMaBiSmAd]

What's your current relevant code? Doing an array lookup when you output something is not that hard, provided the starting data exists and has a matching entry in the lookup array.

 

Are you doing this on a system with error_reporting set to E_ALL and display_errors set to ON so that php will help you by reporting and displaying all the errors it detects?

[RopeADope]

The current code is the same as previously posted.  Error reporting is working but its not catching any errors.  I tried to just print_r($__TEXT__) and all it prints is "1".

 

Just for clarification, here's the contents of the en.php file again:

<?php

$__TEXT__=array
(
   'strategy'=>"Strategy",
   'comments'=>"Comments",
   'date'=>"Date",
   'bus_strategy'=>"Business Strategy",
   'vcto_tasks'=>"vCTO Tasks"
);

?>

 

And a line with an attempt to use the contents of en.php

echo "<tr><td>" . $__TEXT__[$fields->name] . "</td><td><input type=\"text\" size=\"30\" name=\"" . $fields->name . "\" /></td></tr>";

 

I've also tried it as $__TEXT__[$fields] and I've tried it with a simple string variable that matches a label in the en.php file.  Always prints out a blank.

 

EDIT/UPDATE:  I tried the following and got an invalid argument warning

   foreach($__TEXT__ as $key=>$value){
      echo $key,$value;
      }

[PFMaBiSmAd]

Error reporting is working

 

It might be doing something, but it is apparently not set as I suggested. You would be getting errors when you reference $__TEXT__[...] as an array or those errors are hidden in the 'view source' of your page because they are being output inside of a html tag. Have you checked the 'view source' of the page in your browser?

 

The only way anyone here can help you with what your code is doing is if you post your code that produces/reproduces the problem. Changing ONE line in your code can prevent it from working. It's new code at that point and if you want help from someone who is not standing right next to you, you need to post it.

[RopeADope]

It might be doing something, but it is apparently not set as I suggested. You would be getting errors when you reference $__TEXT__[...] as an array or those errors are hidden in the 'view source' of your page because they are being output inside of a html tag. Have you checked the 'view source' of the page in your browser?

 

The source only shows the errors that are output to the web page.  Namely the one about an invalid argument in the foreach loop.

 

This is a sample of the source.  The first set of <td></td> is where the labels should be shown.

<form method="post" action="/php/process_data.php"><input type="hidden" name="selected_table" value="strategy"/><table><tr><td colspan="2" style="font:1em arial;font-weight:bold;text-align:center;">Input Form: Strategy</td></tr>

<tr><td><br /> 
<b>Warning</b>:  Invalid argument supplied for foreach() in <b>C:\xampp\htdocs\php\functions.php</b> on line <b>45</b><br /> 
</td></tr><tr><td>Strategy Id</td><td><input type="text" size="30" disabled="disabled" name="strategy_id" /></td></tr>

<tr><td></td><td><input type="text" size="30" name="date" /></td></tr>

<tr><td></td><td><input type="text" size="30" name="bus_strategy" /></td></tr>

<tr><td></td><td><input type="text" size="30" name="tech_strategy" /></td></tr>

<tr><td></td><td><input type="text" size="30" name="co_values" /></td></tr>

 

This is the function that builds the forms:

function build_form($table_name){

   //The MySQL statement

$sql="SELECT * FROM $table_name";

//Result variable

$result=mysql_query($sql);

//If the result is empty, return.

if($result=="") return;

$i=0;



//Echo some HTML for the form

echo "<form method=\"post\" action=\"/php/process_data.php\">";

echo "<input type=\"hidden\" name=\"selected_table\" value=\"" . $table_name . "\"/>";

echo "<table>";



//Echo the table name

echo "<tr><td colspan=\"2\" style=\"font:1em arial;font-weight:bold;text-align:center;\">Input Form: " . ucwords(preg_replace("/_/"," ",$table_name)) ."</td></tr>" . PHP_EOL;
echo "<tr><td>";
   foreach($__TEXT__ as $key=>$value){
      echo $key,$value;
      }
echo "</td></tr>";

//While $i is less than the number of returned fields, complete this loop

while ($i < mysql_num_fields($result)){

	$fields=mysql_fetch_field($result,$i);
	if(preg_match("/ID/",$fields->name) or preg_match("/_id/",$fields->name)){

   		   echo "<tr><td>" . ucwords(preg_replace("/_/"," ",$fields->name)) . "</td><td><input type=\"text\" size=\"30\" disabled=\"disabled\" name=\"" . $fields->name . "\" /></td></tr>" . PHP_EOL;

	   }elseif(preg_match("/comment/i",$fields->name)){

	      echo "<tr><td>" . ucwords(preg_replace("/_/"," ",$fields->name)) . "</td><td style=\"padding:3px;\"><textarea style=\"width:98%;height:50px;resize:none;\" name=\"" . $fields->name . "\" /></textarea></td></tr>" . PHP_EOL;

	   }elseif(preg_match("/owner/i",$fields->name)){

	      echo "<tr><td>" . ucwords(preg_replace("/_/"," ",$fields->name)) . "</td><td style=\"padding:3px;\"><select style=\"width:100%;height:24px;\" name=\"" . $fields->name . "\" /><option value=\"\"> </option>";

               $owner_sql="SELECT employee_id,last_name,first_name FROM employees";

               	$owner_result=mysql_query($owner_sql);

               	$y=0;

               	while($y < mysql_num_rows($owner_result)){

               	   $id=mysql_result($owner_result,$y,"employee_id");

               	   $last=mysql_result($owner_result,$y,"last_name");

               	   $first=mysql_result($owner_result,$y,"first_name");

               	   echo "<option value=\"$id\">$first $last</option>";

               	   $y++;

               	   };

            echo "</select></td></tr>" . PHP_EOL;

	   }else{

	      echo "<tr><td>" . $__TEXT__[$fields->name] /*ucwords(preg_replace("/_/"," ",$fields->name))*/ . "</td><td><input type=\"text\" size=\"30\" name=\"" . $fields->name . "\" /></td></tr>" . PHP_EOL;

	   };

	$i++;

	};

echo "<tr><td colspan=\"2\" style=\"text-align:center;\"><input type=\"submit\" value=\"Submit Data\" style=\"width:75%\" /></td></tr>";

echo "</table>";

echo "</form>"; 

};

 

This is the actual warning I get from the foreach()

Warning: Invalid argument supplied for foreach() in C:\xampp\htdocs\php\functions.php on line 45

[PFMaBiSmAd]

$__TEXT__ is the argument that you are supplying to the foreach() loop that is invalid, because $__TEXT__ doesn't exist inside your function. If you are defining/including $__TEXT__ in your main program scope, it doesn't exist inside your function. You need to pass it into the function as a parameter when you call the function (like what you are doing with $table_name.)

[RopeADope]

What would be the easiest way to do that without rewriting all the build_form() calls in my pages?  Could I add include(en.php) inside the build_form() function definition?

[RopeADope]

Second question...is there a way I can make the $__TEXT__ array available to everything including functions (without passing it as an argument)?  I'd like to be able to use it for labels throughout the site.

[The Little Guy]

function name(){
    global $__TEXT__;
    // More code
}

[RopeADope]

So I would have to take my current $__TEXT__ array and put it into its own function to make it global?  Or am I supposed to add

global $__TEXT__;

to the existing build_form() function?  Or can I just add the word "global" before my $__TEXT__ array in the en.php file?

[The Little Guy]

You would do it something like this:

 

$__TEXT__ = array(1,2,3,3,4,5,6,6,7);
function name(){
    global $__TEXT__;
    foreach($__TEXT__ as $value){
        echo "$value<br />";
    }
}

[RopeADope]

Ah, okay.  So if I included the en.php file at the top of my function library, all I would have to do is add

global $__TEXT__;

to a function to use it?

[PFMaBiSmAd]

Ummm. The $__TEXT__ array contains information that is specific to one database table. By using the global keyword, you are creating a situation where the function will only operate on that specifically named array of values and you will need to use more code to shuffle around values and you will need to keep track of where you are calling the function from so that you can setup the array correctly before calling the function for different tables (functions are supposed to make coding easier, not more work.)

 

For the same reason that you are passing the $table_name into the function as a parameter (so that you can reuse the code in that function with any table without needing to modify and retest the code in the function each time you use it), you would also pass in the $__TEXT__ array as a parameter along with the $table_name (you could use a single parameter that is an array to hold both pieces of information) or you would write the code in the function so that it would get the correct $__TEXT__ array that matches the table name it was called with.

[RopeADope]

So really, the $__TEXT__ array should be split up into several table-specific label arrays?  I guess I'm confused as to why its not easier to have $__TEXT__ as a global "fits all" array that can be used anywhere.  That's where my thought process was...just have a huge list of labels that works regardless of context.  What sort of problems would that cause?