[SOLVED] Take info from one coloum and move to another

[herghost]

Hi all,

 

I have this:

$array = "SELECT stock_id FROM stocks WHERE stock_id BETWEEN '1' and '5' ";
$res = mysql_query($array);
$count = 0;
while ($row = mysql_fetch_assoc($res)){
   if (is_int($count / 10) || $count == 0){
     $rand_low = rand(1,5);
      $rand_high = rand(1,5);
   }
   $num = rand($rand_low,$rand_high);
   $update = "UPDATE stocks SET t_val=$num WHERE stock_id='" . $row['stock_id'] ."'";
   if (mysql_query($update)){
      echo "Updated record(s)<br />\n";
   }
}

 

Which simply updates a coloum called t_val with a random value between 1 and 5. I have another coloum in the table called y_val. What I would like to do is take the value of t_val before it is updated with the new rand and save it to the y_val coloum. How would I do this?

 

Many thanks

[severndigital]

UPDATE stocks SET y_val = t_val, t_val=$num WHERE stock_id='" . $row['stock_id'] ."'";

 

should do it

 

 

should really be in the MySQL forum though, but it's cool

[herghost]

Hi.

 

Thanks for your reply, however this just updates y_val wiyj yj enew t_val, not the current one before the rand is created

[lemmin]

You might have to do it in two queries:

"UPDATE stocks SET y_val = t_val WHERE stock_id='".$_row['stock_id']."'";
"UPDATE stocks SET t_val = $num WHERE stock_id='".$_row['stock_id']."'";

[herghost]

Thanks Lemmin, My entire code consists of 8 blocks identical to the 1st block apart from the where clauses (selects different rows in database) How would I implement this code (where would I put it!)

 

My code:

<?php
include('../common/dbconnect.php');

$array = "SELECT stock_id FROM stocks WHERE stock_id BETWEEN '1' and '5' ";
$res = mysql_query($array);
$count = 0;
while ($row = mysql_fetch_assoc($res)){
   if (is_int($count / 10) || $count == 0){
     $rand_low = rand(1,5);
      $rand_high = rand(1,5);
   }
   $num = rand($rand_low,$rand_high);
   $update = "UPDATE stocks SET t_val=$num WHERE stock_id='" . $row['stock_id'] ."'";
  if (mysql_query($update)){
      echo "Updated record(s)<br />\n";
   }
}

$array = "SELECT stock_id FROM stocks WHERE stock_id BETWEEN '6' and '10' ";
$res = mysql_query($array);
$count = 0;
while ($row = mysql_fetch_assoc($res)){
   if (is_int($count / 10) || $count == 0){
     $rand_low = rand(5,15);
      $rand_high = rand(5,15);
   }
   $num = rand($rand_low,$rand_high);
   $update = "UPDATE stocks SET t_val=$num WHERE stock_id='" . $row['stock_id'] ."'";
   if (mysql_query($update)){
      echo "Updated record(s)<br />\n";
   }
}

$array = "SELECT stock_id FROM stocks WHERE stock_id BETWEEN '11' and '20' ";
$res = mysql_query($array);
$count = 0;
while ($row = mysql_fetch_assoc($res)){
   if (is_int($count / 10) || $count == 0){
     $rand_low = rand(5,50);
      $rand_high = rand(5,50);
   }
   $num = rand($rand_low,$rand_high);
   $update = "UPDATE stocks SET t_val=$num WHERE stock_id='" . $row['stock_id'] ."'";
   if (mysql_query($update)){
      echo "Updated record(s)<br />\n";
   }
}

$array = "SELECT stock_id FROM stocks WHERE stock_id BETWEEN '21' and '40' ";
$res = mysql_query($array);
$count = 0;
while ($row = mysql_fetch_assoc($res)){
   if (is_int($count / 10) || $count == 0){
     $rand_low = rand(25,150);
      $rand_high = rand(25,150);
   }
   $num = rand($rand_low,$rand_high);
   $update = "UPDATE stocks SET t_val=$num WHERE stock_id='" . $row['stock_id'] ."'";
   if (mysql_query($update)){
      echo "Updated record(s)<br />\n";
   }
}

$array = "SELECT stock_id FROM stocks WHERE stock_id BETWEEN '41' and '60' ";
$res = mysql_query($array);
$count = 0;
while ($row = mysql_fetch_assoc($res)){
   if (is_int($count / 10) || $count == 0){
     $rand_low = rand(50,500);
      $rand_high = rand(50,500);
   }
   $num = rand($rand_low,$rand_high);
   $update = "UPDATE stocks SET t_val=$num WHERE stock_id='" . $row['stock_id'] ."'";
   if (mysql_query($update)){
      echo "Updated record(s)<br />\n";
   }
}

$array = "SELECT stock_id FROM stocks WHERE stock_id BETWEEN '61' and '80' ";
$res = mysql_query($array);
$count = 0;
while ($row = mysql_fetch_assoc($res)){
   if (is_int($count / 10) || $count == 0){
     $rand_low = rand(100,750);
      $rand_high = rand(100,750);
   }
   $num = rand($rand_low,$rand_high);
   $update = "UPDATE stocks SET t_val=$num WHERE stock_id='" . $row['stock_id'] ."'";
   if (mysql_query($update)){
      echo "Updated record(s)<br />\n";
   }
}

$array = "SELECT stock_id FROM stocks WHERE stock_id BETWEEN '81' and '94' ";
$res = mysql_query($array);
$count = 0;
while ($row = mysql_fetch_assoc($res)){
   if (is_int($count / 10) || $count == 0){
     $rand_low = rand(250,1000);
      $rand_high = rand(250,1000);
   }
   $num = rand($rand_low,$rand_high);
   $update = "UPDATE stocks SET t_val=$num WHERE stock_id='" . $row['stock_id'] ."'";
   if (mysql_query($update)){
      echo "Updated record(s)<br />\n";
   }
}

$array = "SELECT stock_id FROM stocks WHERE stock_id BETWEEN '95' and '100' ";
$res = mysql_query($array);
$count = 0;
while ($row = mysql_fetch_assoc($res)){
   if (is_int($count / 10) || $count == 0){
     $rand_low = rand(2500,10000);
      $rand_high = rand(2500,10000);
   }
   $num = rand($rand_low,$rand_high);
   $update = "UPDATE stocks SET t_val=$num WHERE stock_id='" . $row['stock_id'] ."'";
   if (mysql_query($update)){
      echo "Updated record(s)<br />\n";
   }
}



   
?>

[severndigital]

ok so .. i'll show you where to add it and then i'll comment.

you will have to do this in each one.

 

$array = "SELECT stock_id FROM stocks WHERE stock_id BETWEEN '1' and '5' ";
$res = mysql_query($array);
$count = 0;
while ($row = mysql_fetch_assoc($res)){
   if (is_int($count / 10) || $count == 0){
     $rand_low = rand(1,5);
      $rand_high = rand(1,5);
   }
   $num = rand($rand_low,$rand_high);
   //add here
   $update1 = "UPDATE stocks SET y_val=t_val WHERE stock_id='" . $row['stock_id'] ."'";
   $update = "UPDATE stocks SET t_val=$num WHERE stock_id='" . $row['stock_id'] ."'";
  if (mysql_query($update)){
      echo "Updated record(s)<br />\n";
   }
}

 

 

ok .. now i'll comment.

 

if there is one thing i have learned over the years.... if you are writing the same code 8 times, there is a better way.

 

this code could be stripped down to at the most 1 of those 8 calls, just by using arrays and a for loop.

 

anyways, it's cool if the code works, just go with it... but if you need to start modifying all these query's over and over again, it would really help you to learn about arrays and for loops at the least.

 

I'm not hating, just trying to help out.

 

C

 

 

 

 

[herghost]

Thanks severndigital,

 

Thanks for the advice, I shall definitly look in to it

 

However this still just pastes the new value into the y_val, and not the previous one. Any ideas? This is what I have:

 

$array = "SELECT stock_id FROM stocks WHERE stock_id BETWEEN '1' and '5' ";
$res = mysql_query($array);
$count = 0;
while ($row = mysql_fetch_assoc($res)){
   if (is_int($count / 10) || $count == 0){
     $rand_low = rand(1,5);
      $rand_high = rand(1,5);
   }
   $num = rand($rand_low,$rand_high);
  $update1 = "UPDATE stocks SET y_val=t_val WHERE stock_id='" . $row['stock_id'] ."'";
   $update = "UPDATE stocks SET t_val=$num WHERE stock_id='" . $row['stock_id'] ."'";
  if (mysql_query($update)){
      echo "Updated record(s)<br />\n";
  }
  if (mysql_query($update1)){
      echo "Update y_val<br />\n";
  
   }
   else
   {
   echo "error";
   }

}

[herghost]

ahh got it!

 

include('../common/dbconnect.php');

$array = "SELECT stock_id FROM stocks WHERE stock_id BETWEEN '1' and '5' ";
$res = mysql_query($array);
$count = 0;
while ($row = mysql_fetch_assoc($res)){
   if (is_int($count / 10) || $count == 0){
     $rand_low = rand(1,5);
      $rand_high = rand(1,5);
   }
   $num = rand($rand_low,$rand_high);
  $update1 = "UPDATE stocks SET y_val=t_val WHERE stock_id='" . $row['stock_id'] ."'";
  if (mysql_query($update1)){
      echo "Update y_val<br />\n";
  
   }
   else
   {
   echo "error";
   }
   $update = "UPDATE stocks SET t_val=$num WHERE stock_id='" . $row['stock_id'] ."'";
  if (mysql_query($update)){
      echo "Updated record(s)<br />\n";
  }
  

}

 

many thanks to you all for the help