Simple add column[solved]

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
pyoungson
Forum Newbie
Posts: 18
Joined: Thu Aug 09, 2007 5:35 am

Simple add column[solved]

Post 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?
Last edited by pyoungson on Mon Aug 20, 2007 8:39 am, edited 1 time in total.
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Post 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?
There are 10 types of people in this world, those who understand binary and those who don't
pyoungson
Forum Newbie
Posts: 18
Joined: Thu Aug 09, 2007 5:35 am

Post 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.
User avatar
CoderGoblin
DevNet Resident
Posts: 1425
Joined: Tue Mar 16, 2004 10:03 am
Location: Aachen, Germany

Post 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).
miro_igov
Forum Contributor
Posts: 485
Joined: Fri Mar 31, 2006 5:06 am
Location: Bulgaria

Post by miro_igov »

I think UPDATE should have WHERE condition, otherwise is meaningless to have same value for this column in every record.
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Post 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.
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Post 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.
There are 10 types of people in this world, those who understand binary and those who don't
pyoungson
Forum Newbie
Posts: 18
Joined: Thu Aug 09, 2007 5:35 am

Post 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.
Post Reply