Page 1 of 1

Simple add column[solved]

Posted: Mon Aug 20, 2007 8:09 am
by pyoungson
I need to add a column to a mysql database. This should be fairly simple but I have never seen it done before where the values in the new column are based on the values in the other columns and the variables that have previously been defined in the php.

Code: Select all

$averagepolar = ($maxpolar + $minimumpolar) / 2;

mysql_query("alter table solvents add column Red decimal (4,3)");

$result = mysql_query("select * from solvents");

while($row = mysql_fetch_array($result))
{
mysql_query("update solvents set Red = $row['Polar'] * $averagepolar");
}


$result2 = mysql_query("SELECT * FROM solvents ORDER BY Red");

while($row2 = mysql_fetch_array($result2))
  {
  echo $row2['Name'];
  echo " " . $row2['Red'];
  echo "<br />";
  }
I don't understand why this does not work. The steps seem to be logical. Where should I go from here?

Posted: Mon Aug 20, 2007 8:13 am
by VladSun
I don't understand other thing - why do you create field which value can be calculated from exesting field in the same table?

Posted: Mon Aug 20, 2007 8:20 am
by pyoungson
Unfortunately, the variable averagepolar will be different each time this script is carried out. Therefore the new column will have different values everytime the script is run.

Maybe I am using a elephant gun to kill a mouse but I need to return the values of the new column ordered in ascending order. If I just simply multiply them individually (row by row) then I can't sort them, so I came up with the idea of creating a new column and sorting that.

I'm happy for someone to correct me if this is the wrong thing to do. I also tried using usort() but could not get this to work.

Posted: Mon Aug 20, 2007 8:21 am
by CoderGoblin
You don't actually mention what is failing. Is it the table alteration itself. If so check the permissions on the web user.

Also

Code: Select all

mysql_query("update solvents set Red = $row['Polar'] * $averagepolar");
Should be

Code: Select all

mysql_query("update solvents set Red = {$row['Polar']} * $averagepolar");
or

Code: Select all

mysql_query("update solvents set Red = ".$row['Polar']." * $averagepolar");
depending on preference. You cannot output arrays in an output string like you can normal variables.

I would have to also question the basic database design if you are having to add columns using php. Normally you would have another table storing Red,Yellow, whatever with possibly a joining table sovent2colour. (Without seeing exactly what you are trying to do this is difficult to judge).

Posted: Mon Aug 20, 2007 8:22 am
by miro_igov
I think UPDATE should have WHERE condition, otherwise is meaningless to have same value for this column in every record.

Posted: Mon Aug 20, 2007 8:26 am
by VladSun
And I think that using proper SQL would be better ;)

Code: Select all

$averagepolar = ($maxpolar + $minimumpolar) / 2;

mysql_query("alter table solvents add column Red decimal (4,3)");
mysql_query("update solvents set Red = Polar * $averagepolar");

$result2 = mysql_query("SELECT * FROM solvents ORDER BY Red");
while($row2 = mysql_fetch_array($result2))
  {
  echo $row2['Name'];
  echo " " . $row2['Red'];
  echo "<br />";
  }
The "update" should be done in DB layer, not in code layer.

Posted: Mon Aug 20, 2007 8:29 am
by VladSun
pyoungson wrote:Unfortunately, the variable averagepolar will be different each time this script is carried out. Therefore the new column will have different values everytime the script is run.

Maybe I am using a elephant gun to kill a mouse but I need to return the values of the new column ordered in ascending order. If I just simply multiply them individually (row by row) then I can't sort them, so I came up with the idea of creating a new column and sorting that.

I'm happy for someone to correct me if this is the wrong thing to do. I also tried using usort() but could not get this to work.
OK ...
1. averagepolar is a constant - so you may order by Polar field.
2. You can use math operations directly into SQL query - think about it.

Posted: Mon Aug 20, 2007 8:37 am
by pyoungson
Thank you vladsun and codergoblin that worked perfectly. I will have a look into whether there are better solutions but for now this is great.