Change database field name to a different word during query?

hominid4 · January 24, 2009

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 · January 24, 2009

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 · January 24, 2009

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 · January 24, 2009

Yep, the last str_replace should do it

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

hominid4 · January 24, 2009

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 · January 24, 2009

Show the insertion code and where you are using the str_replace

hominid4 · January 24, 2009

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 · January 24, 2009

Have you tried doing

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

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

hominid4 · January 24, 2009

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 · January 24, 2009

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 · January 25, 2009

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 · January 25, 2009

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

hominid4 · January 25, 2009

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 · January 25, 2009

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

hominid4 · January 25, 2009

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 · January 25, 2009

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 · January 25, 2009

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 · January 25, 2009

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

hominid4 · January 25, 2009

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 · January 25, 2009

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 · January 25, 2009

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 · January 25, 2009

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 · January 25, 2009

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

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

DeanWhitehouse · January 25, 2009

Ok then try doing

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

hominid4 · January 25, 2009

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

Sign In

Change database field name to a different word during query?

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information