Change database field name to a different word during query?

[hominid4]

On my website I have a HTML table that has 5 columns, 5 rows; within this table I have the below code. Going down the left column are the field names and their values going out to the right of each field name taking up those column cells; pulled from the MySQL database table:

$fieldArr = array('nav','pop','navchange','asofdate'); 

$fieldArr2 = array(); 
foreach($fieldArr as $fields) $fieldArr2[$fields] = array(); 

$sql = mysql_query("SELECT ".implode(' ,',$fieldArr)." FROM dailypricing") or die(mysql_error()); 
while($row = mysql_fetch_array($sql)) 

{ 
    foreach($fieldArr as $f) 
    { 
        if(!empty($row[$f])) 
        { 
            $fieldArr2[$f][] = $row[$f]; 
        }else{ 
            $fieldArr2[$f][] = ' '; 
        }   
    } 
} 
     
            echo '<tr>'; 
            foreach($fieldArr as $f) 
{ 
    echo '<td>'.$f.'</td>';    
    foreach($fieldArr2[$f] as $d) 
    { 
        echo '<td>'.$d.'</td>'; 
    } 
   echo '</tr>'; 
}

All works fine except I'm needing the field names that are pulled from the database that go down the left column to be different; such as I need 'navchange' to be "NAV Change", and 'asofdate' to be "As Of Date".

 

Could I please get some help on how I can change the field names during query? I've attached a sample of what my HTML table will look like.  The field names 'nav','pop','navchange','asofdate' changes daily, that's what part of my table is fixed and the database field names are dynamic.

 

Thank you!

 

 

 

[attachment deleted by admin]

[DeanWhitehouse]

ucfirst(); to make things uppercase, and your other idea won't work it is hard to make it create spaces without knowing where they are, try renaming your db tables to use a seperator for words, like

 

wordone

 

could be

 

word_one

[hominid4]

Thanks Blade.  If I used the underscore would there be a way for me to remove it when showing the results?  Maybe something like:

$fieldArr = str_replace('nav_change','NAV Change',$fieldArr); 
$fieldArr = str_replace('as_of_date','As Of Date',$fieldArr);

or

$fieldArr = str_replace('_',' ',$fieldArr);

 

Thanks again.

[DeanWhitehouse]

Yep, the last str_replace should do it

$fieldArr = str_replace('_',' ',$fieldArr);

[hominid4]

Good deal.  When I insert the $fieldArr = str_replace('_',' ',$fieldArr); it removes the _ correctly but I lose the values for those field names; the nav_change and as_of_date values are blank in my HTML table.  One place where I inserted it is after:

$sql = mysql_query("SELECT ".implode(' ,',$fieldArr)." FROM dailypricing") or die(mysql_error());
$fieldArr = str_replace('_',' ',$fieldArr);

 

Below is what I'm using for the HTML table that shows in my above attachment; I've removed the formatting to simplify it.  Would you have a suggestion where I need to insert the str_replace?

 

		<table>
		<tr>
			<td>Fund Name</td>
			<td>Japan Fund</td>
			<td>Australia / New Zealand Fund</td>
			<td>Global Fund</td>
			<td>Real Estate Securities Fund</td>
		</tr>
		<tr>
			<td>Symbol</td>
			<td>CNJFX</td>
			<td>CNZLX</td>
			<td>CNGLX</td>
			<td>CNREX</td>
		</tr>
		<?php
		echo '<tr>';
		foreach($fieldArr as $f)
{
    echo '<td>'.$f.'</td>';     
    foreach($fieldArr2[$f] as $d) 
    {
        echo '<td>'.$d.'</td>';
    }
   echo '</tr>';
}
?>
	</table>

[DeanWhitehouse]

Show the insertion code and where you are using the str_replace

[hominid4]

I've removed all the extra HTML coding and am back to my basic insertion code.

 

If I insert the str_replace in the top portion it changes the field name but the values come out blank.

$fieldArr = array('nav','pop','nav_change','as_of_date'); 

$fieldArr2 = array(); 
foreach($fieldArr as $fields) $fieldArr2[$fields] = array(); 

$sql = mysql_query("SELECT ".implode(' ,',$fieldArr)." FROM dailypricing") or die(mysql_error());

$fieldArr = str_replace('_',' ',$fieldArr);

while($row = mysql_fetch_array($sql))

{ 
    foreach($fieldArr as $f) 
    { 
        if(!empty($row[$f])) 
        { 
            $fieldArr2[$f][] = $row[$f]; 
        }else{ 
            $fieldArr2[$f][] = ' '; 
        }   
    } 
}
echo '<table border="1">';
foreach($fieldArr as $f)   
{ 
    echo '<tr><td>'.$f.'</td>';   
    foreach($fieldArr2[$f] as $d)   
    {   
        echo '<td>'.$d.'</td>';   
    }   
    echo '</tr>';   
}   
echo '</table>';

 

If I insert within the bottom portion I receive the error:

Invalid argument supplied for foreach() on line -> foreach($fieldArr2[$f] as $d)

 

Such as:

$fieldArr = array('nav','pop','nav_change','as_of_date'); 

$fieldArr2 = array(); 
foreach($fieldArr as $fields) $fieldArr2[$fields] = array(); 

$sql = mysql_query("SELECT ".implode(' ,',$fieldArr)." FROM dailypricing") or die(mysql_error()); 
while($row = mysql_fetch_array($sql)) 

{ 
    foreach($fieldArr as $f) 
    { 
        if(!empty($row[$f])) 
        { 
            $fieldArr2[$f][] = $row[$f]; 
        }else{ 
            $fieldArr2[$f][] = ' '; 
        }   
    } 
}

$fieldArr = str_replace('_',' ',$fieldArr);

echo '<table border="1">';
foreach($fieldArr as $f)   
{ 
    echo '<tr><td>'.$f.'</td>';   
    foreach($fieldArr2[$f] as $d)   
    {   
        echo '<td>'.$d.'</td>';   
    }   
    echo '</tr>';   
}   
echo '</table>';

or

$fieldArr = array('nav','pop','nav_change','as_of_date'); 

$fieldArr2 = array(); 
foreach($fieldArr as $fields) $fieldArr2[$fields] = array(); 

$sql = mysql_query("SELECT ".implode(' ,',$fieldArr)." FROM dailypricing") or die(mysql_error()); 
while($row = mysql_fetch_array($sql)) 

{ 
    foreach($fieldArr as $f) 
    { 
        if(!empty($row[$f])) 
        { 
            $fieldArr2[$f][] = $row[$f]; 
        }else{ 
            $fieldArr2[$f][] = ' '; 
        }   
    } 
}

echo '<table border="1">';
foreach($fieldArr as $f)   
{
   $f = str_replace('_',' ',$f);
    echo '<tr><td>'.$f.'</td>';   
    foreach($fieldArr2[$f] as $d)   
    {   
        echo '<td>'.$d.'</td>';   
    }   
    echo '</tr>';   
}   
echo '</table>';

[DeanWhitehouse]

Have you tried doing

print_r(str_replace('_',' ',$fieldArr));

 

Check if everything is as it should be, try applying it before the loop

[hominid4]

I've inserted the print_r before the loop, and other places, but it just prints:

 

Array ( [0] => nav [1] => pop [2] => nav change [3] => as of date )

[DeanWhitehouse]

And is that what the row names should be after the str_replace?

 

if so it works, and you are doing something wrong somewhere else.

[hominid4]

Good to hear I'm getting closer, yes that what the field names are supposed to be. I'm afraid I don't know what I'm doing wrong. Would you have a suggestion what part of the code I should focus on?  I inserted the print_r(str_replace()) on every line but with no luck, would that mean I should modify the other code?

 

Thanks for your help.

[DeanWhitehouse]

Where are you printing it out, or where are you getting a problem

[hominid4]

I'm printing out the table on my web page.  My web page includes my database connection code and the insertion code.

 

Below is my full html on my page that I'm testing this out on, and where I currently have the print_r(str_replace()) line. I've also attached what the table prints out as.

 

<?php 
$link = mysql_connect('localhost', 'MYDATABASE-USERNAME', 'MYDATABASE-PASSWORD')
mysql_select_db('MYDATABASE-NAME') 
?>
<html>

<head>
	<title>Daily Pricing</title>
</head>

<body>
	<?php $fieldArr = array('nav','pop','nav_change','as_of_date');
$fieldArr2 = array(); 
foreach($fieldArr as $fields) $fieldArr2[$fields] = array(); 

$sql = mysql_query("SELECT ".implode(' ,',$fieldArr)." FROM dailypricing") or die(mysql_error());

print_r(str_replace('_',' ',$fieldArr));

while($row = mysql_fetch_array($sql))

{
    foreach($fieldArr as $f) 
   
    { 
        if(!empty($row[$f])) 
        { 
            $fieldArr2[$f][] = $row[$f]; 
        }else{ 
            $fieldArr2[$f][] = ' '; 
        }   
    } 
}

echo '<table border="1">';

foreach($fieldArr as $f)

{ 
    echo '<tr><td>'.$f.'</td>'; 
     
    foreach($fieldArr2[$f] as $d)   
    {   
        echo '<td>'.$d.'</td>';   
    }   
    echo '</tr>';   
}   
echo '</table>'; 
?>
</body>

</html>

 

[attachment deleted by admin]

[DeanWhitehouse]

Ok, in the for loop shouldn't you be echoing $fieldArr2

[hominid4]

I'm sorry, I'm really new at this.  Are you speaking of putting print_r(str_replace('_',' ',$fieldArr2)); inside the for loop, and if so which one?  I did try it in different places and I get this printed out above the table:

Array ( [nav] => Array ( ) [pop] => Array ( ) [nav_change] => Array ( ) [as_of_date] => Array ( ) )

 

Also, isn't $fieldArr2 for the values of the field names?  And shouldn't I be working with $fieldArr or $f so that it's only for the field names themselves?

 

Thank you!

[DeanWhitehouse]

No

 

try

<?php 
$link = mysql_connect('localhost', 'MYDATABASE-USERNAME', 'MYDATABASE-PASSWORD')
mysql_select_db('MYDATABASE-NAME') 
?>
<html>

   <head>
      <title>Daily Pricing</title>
   </head>

   <body>
      <?php 
$fieldArr = array('nav','pop','nav_change','as_of_date');
$fieldArr2 = array(); 
foreach($fieldArr as $fields) $fieldArr2[$fields] = array(); 

$sql = mysql_query("SELECT ".implode(' ,',$fieldArr)." FROM dailypricing") or die(mysql_error());

echo str_replace('_',' ',$fields);

while($row = mysql_fetch_array($sql))

{
    foreach($fieldArr as $f) 
   
    { 
        if(!empty($row[$f])) 
        { 
            $fieldArr2[$f][] = $row[$f]; 
        }else{ 
            $fieldArr2[$f][] = ' '; 
        }   
    } 
}

echo '<table border="1">';

foreach($fieldArr as $f)

{ 
    echo '<tr><td>'.$f.'</td>'; 
     
    foreach($fieldArr2[$f] as $d)   
    {   
        echo '<td>'.$d.'</td>';   
    }   
    echo '</tr>';   
}   
echo '</table>'; 
?>
   </body>

</html>

[hominid4]

When I inserted the echo str_replace('_',' ',$fields); it just wrote "as of date" at the top of the table.  Early I did play around with $fields = str_replace('_',' ',$fields); but didn't have any luck with that either.

[DeanWhitehouse]

Ok try putting it in the second foreach loop, although your code and structure don't make much sense

[hominid4]

When I do that it repeats "as of date" four times.  The reason I'm using the arrays as I am is to turn the database table on it's side so that the field names are in the left column; instead of the standard way where the field names are at the top of the columns.

 

Shouldn't though I be able to take the field names and change them no matter how my insertion code is; since my insertion code is after the $sql query?

[redarrow]

try this

<?php 
$link = mysql_connect('localhost', 'MYDATABASE-USERNAME', 'MYDATABASE-PASSWORD')
mysql_select_db('MYDATABASE-NAME') 
?>
<html>

   <head>
      <title>Daily Pricing</title>
   </head>

   <body>
      <?php $fieldArr = array('nav','pop','nav_change','as_of_date');
$fieldArr2 = array(); 
foreach($fieldArr as $fields) $fieldArr2[$fields] = array(); 

$sql = mysql_query("SELECT ".implode(' ,',str_replace('_',' ',($fieldArr)))." FROM dailypricing") or die(mysql_error());

while($row = mysql_fetch_array($sql))

{
    foreach($fieldArr as $f) 
   
    { 
        if(!empty($row[$f])) 
        { 
            $fieldArr2[$f][] = $row[$f]; 
        }else{ 
            $fieldArr2[$f][] = ' '; 
        }   
    } 
}

echo '<table border="1">';

foreach($fieldArr as $f)

{ 
    echo '<tr><td>'.$f.'</td>'; 
     
    foreach($fieldArr2[$f] as $d)   
    {   
        echo '<td>'.$d.'</td>';   
    }   
    echo '</tr>';   
}   
echo '</table>'; 
?>
   </body>

</html>

[hominid4]

Thanks redarrow, that seems to be on the right track but I'm receiving the error:

 

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'change ,as of date FROM dailypricing' at line 1

 

try this

<?php 
$link = mysql_connect('localhost', 'MYDATABASE-USERNAME', 'MYDATABASE-PASSWORD')
mysql_select_db('MYDATABASE-NAME') 
?>
<html>

   <head>
      <title>Daily Pricing</title>
   </head>

   <body>
      <?php $fieldArr = array('nav','pop','nav_change','as_of_date');
$fieldArr2 = array(); 
foreach($fieldArr as $fields) $fieldArr2[$fields] = array(); 

$sql = mysql_query("SELECT ".implode(' ,',str_replace('_',' ',($fieldArr)))." FROM dailypricing") or die(mysql_error());

while($row = mysql_fetch_array($sql))

{
    foreach($fieldArr as $f) 
   
    { 
        if(!empty($row[$f])) 
        { 
            $fieldArr2[$f][] = $row[$f]; 
        }else{ 
            $fieldArr2[$f][] = ' '; 
        }   
    } 
}

echo '<table border="1">';

foreach($fieldArr as $f)

{ 
    echo '<tr><td>'.$f.'</td>'; 
     
    foreach($fieldArr2[$f] as $d)   
    {   
        echo '<td>'.$d.'</td>';   
    }   
    echo '</tr>';   
}   
echo '</table>'; 
?>
   </body>

</html>

[DeanWhitehouse]

I thought the names in the db were stored as

 

name_one etc..

 

So doing implode(' ,',str_replace('_',' ',($fieldArr))) will look for the word as name one not name_one

[hominid4]

Yes, you're correct, my database field names are:

'nav','pop','nav_change','as_of_date'

 

[DeanWhitehouse]

Ok then try doing

$sql = mysql_query("SELECT ".implode(' ,','_',' ',($fieldArr))." FROM dailypricing") or die(mysql_error());

[hominid4]

Thanks Blade, I've been playing around with your query but keep getting the error:

Wrong parameter count for implode().
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM dailypricing' at line 1