Help with Updating multiple rows in Table

[eluser]BoyBlue[/eluser]
I'm not quite sure how to make this code work so that it will Calculate & Update the Table on every row that the same $Movie_ID is found...

I'm thinking I may need to use arrays or Batch...but I'm not sure...

Here is my current code:

Code:

function updateQuicktake_info($movie_id)
  {

   $sql = "SELECT * FROM movie_quicktakes
   WHERE movie_id = $movie_id
   ORDER BY helpful_percent DESC";
  
   $quicktake_helpful_math = $this->db->query($sql, array($movie_id));
   $row = $quicktake_helpful_math->row();
  
    $helpful_votes = $row->helpful_votes;
    $total_votes = $row->total_votes;
    $quicktake_id = $row->movie_quicktake_id;
    $helpful_percent = $row->helpful_percent;

    if ($helpful_votes / $total_votes != $helpful_percent)
    {
    $quicktake_helpful_decimal = $helpful_votes / $total_votes;
    $quicktake_helpful_percent =
    round((float)$quicktake_helpful_decimal * 100 ) . '%';
    }  

   //FOR TESTING PURPOSES
   //echo $quicktake_helpful_percent; //outputting correct calculation..
   //echo '<br />'.$quicktake_id; //still have correct ID...

   $new_data = array(
               'helpful_percent' => $quicktake_helpful_percent
            );
  
   $this->db->where('movie_quicktake_id', $quicktake_id);
   $this->db->update('movie_quicktakes', $new_data);  

  }

[

Thanks for the help in advance!
BoyBlue

[eluser]bgreene[/eluser]
I'd do it with a stored procedure because it lets the db do all the work. But you might not be familiar with stored procs. It would be something like
<code>
DECLARE crnt CURSOR FOR
SELECT movie_id,...// fields you need // FROM movie_quicktakes WHERE movie_id = $movie_id ORDER BY helpful_percent DESC";
OPEN crnt;
SET nomore = 0;
WHILE nomore = 0 DO
FETCH crnt INTO idx,...// set of predefined variables corresponding to fields in the select// ;
// calculate your fiddle factor for the crnt row
UPDATE movie_quicktakes SET helpful_percent = fiddlefactor WHERE id = idx
END WHILE;
</code>

[eluser]BoyBlue[/eluser]
wow--thanks BGreene!

Ok, I had no idea about most of that code! Is that Ci or is that C++ or Python. I'm thinking maybe I might want to try to stay with fairly regular PHP syntax.

[eluser]bgreene[/eluser]
its just an sql stored procedure which means you simply issue one query eg updateratings(movie_id) and all the work is done by the database engine. but since youre not familiar with it then dont bother, stick to what you know