[SOLVED] Update MySql db from 2 Arrays

[sej]

Good day all,

 

I'm building a CMS and I'm having a problem updating a MySql db using 2 arrays that are being sent to php from flash. What I'm trying to accomplish is a section reordering method where when the user drags the section to a different position number in the section list, flash calculates the new section numbers associated with the section id's in the db and passes the changes to php in 2 arrays (sectIDArray & renumArray). The only way I can seem to get this to work is:

 

if ($_POST['action'] == 'sectionReorder') {

  // reorder section nums

  $sectIDArray = split ( ',', $_POST['sectIDArray']);

  $renumArray = split ( ',', $_POST['renumArray']);

  for ( $i=0;$i<count($sectIDArray);$i++){

    $var = $db .$i;

  ${$var} = new Database('server','username','password','dbname');

  $sql = 'UPDATE sections SET section_num = "'.$renumArray[$i].'" WHERE section_id = '.$sectIDArray[$i];

  ${$var}->query($sql);

  ${$var}->close();

  }

  $reorder = 'Sections have been reordered.';

  $output = 'duplicate=n&message='.urlencode($reorder);

  // display revised list

  $revisedList = 'SELECT * FROM sections'.$sectionOrder;

  echo $output .= '&'.getSectionList($revisedList);

}

 

But it would obviously be better and more efficent to only open 1 db connection so I've been trying to get this to work:

 

if ($_POST['action'] == 'sectionReorder') {

  // reorder section nums

  $sectIDArray = split ( ',', $_POST['sectIDArray']);

  $renumArray = split ( ',', $_POST['renumArray']);

  $db = new Database('server','username','password','dbname');

  for ( $i=0;$i<count($sectIDArray);$i++){

    $sql = 'UPDATE sections SET section_num = "'.$renumArray[$i].'" WHERE section_id = '.$sectIDArray[$i];

  }

  $db->query($sql);

  $db->close();

  $reorder = 'Sections have been reordered.';

  $output = 'duplicate=n&message='.urlencode($reorder);

  // display revised list

  $revisedList = 'SELECT * FROM sections'.$sectionOrder;

  echo $output .= '&'.getSectionList($revisedList);

}

 

But all this does is modify the last array element in the db and doesn't alter the other changed values. What am I doing wrong? Any help would be greatly appreciated.

 

Take care,

shayne

[btherl]

Put $db->query($sql) inside the loop

 

Sometimes it's the most obvious things that are the hardest to spot

[sej]

Ya I figured it would be something painfully obvious. Works great now. Thanks btherl!