Jump to content

multiple row update

rbarnett

By rbarnett
in PHP Coding Help

Recommended Posts

rbarnett Member

rbarnett

Posted
Posted

I hope this is a simple one that I'm just overlooking but I am unable to update multiple rows.

 

I have 2 rows in the table and when I echo out the update query it shows the first row that should have id 1 as having id 2 and the second row is blank when it should be id 2.  When I echo the row ids in the select statement the rows ids show correctly.

 

Below is my code.  Any help would be greatly appreciated.

 

$schNameInsert = $_POST['schoolName'];
$dirNameInsert = $_POST['dirName'];
$diskQuotaInsert = $_POST['diskQuota'];
$schNameEdit = $_POST['schoolNameEdit'];
$dirNameEdit = $_POST['dirNameEdit'];
$diskQuotaEdit = $_POST['diskQuotaEdit'];
$path = "/var/data/moodleclient/";
require_once('/var/data/moodle_schools_connect.php');
$query = "select * from disk_quotas";
$result = mysql_query($query);
$num_rows = mysql_num_rows($result);

if(isset($_POST['add']))
{
  $queryInsert="INSERT INTO disk_quotas (school_name, disk_quota, dir_name) VALUES ('$schNameInsert',$diskQuotaInsert,'$dirNameInsert')";
  mysql_query($queryInsert);
  
  header('Location:'.$_SERVER['PHP_SELF']);
}

echo '<div style="font-size: 14px">';

        if($num_rows == 1)
        {
          echo $num_rows.' Record';
        }elseif($num_rows == 0)
        {
          echo 'No Records';
        }else
        {
          echo $num_rows.' Records';
        }
      echo ' Found';

if(!isset($_POST['edit']))
{
  echo '<div style="position:absolute;left:500px;top:0px;">
          <form action="'.$_SERVER['PHP_SELF'].'" method="post">
                  <input type="submit" name="edit" value="Edit" />
         </form>
        </div>';
}
echo '<form action="'.$_SERVER['PHP_SELF'].'" method="post">';
echo '<table class="sortable">
          <thead>
            <tr> 
              <th>School Name</th>
              <th>Directory Name</th>
              <th>Disk Quota</th>
            </tr>
          </thead>
          <tbody>';
while ($row = mysql_fetch_array($result, MYSQL_ASSOC))
{ 
  $diskQuota = $row['disk_quota'];
  $dirName = $row['dir_name'];
  $schoolName = $row['school_name'];
  $id = $row['school_id'];
   if(!isset($_POST['edit']))
   {
      echo '<tr>
              <td>'.$schoolName.'</td>
              <td>'.$dirName.'</td>
              <td>'.$diskQuota.'</td>
           </tr>';
   }
   else
   {
      echo '<tr>
              <td><input type="text" name="schoolNameEdit[]" value="'.$schoolName.'" /></td>
              <td><input type="text" name="dirNameEdit[]" value="'.$dirName.'" /></td>
              <td><input type="text" name="diskQuotaEdit[]" value="'.$diskQuota.'" /></td>
           </tr>';
    
   }
}

if(!isset($_POST['edit']))
{
  echo '
        <form action="'.$_SERVER['PHP_SELF'].'" method="post">
            <tr>
                <td><input type="text" name="schoolName" /></td>
                <td><input type="text" name="dirName" /></td>
                <td><input type="text" name="diskQuota" /></td>
            </tr>
          </tbody>
        </table>
        </div>
        <input type="submit" name="add" value="Add" />
        </form>';
}

if(isset($_POST['edit']))
{
    echo '<input type="submit" name="changes" value="Submit Changes" />
          </form>';
}

if(isset($_POST['changes']))
{
  for($i=0;$i<$num_rows;$i++)
  {
    $queryUpdate="UPDATE disk_quotas SET school_name='$schNameEdit[$i]', disk_quota=$diskQuotaEdit[$i], dir_name='$dirNameEdit[$i]' WHERE id=$id[$i]";
    $resultUpdate=mysql_query($queryUpdate);

    echo $queryUpdate;
  }

}

if(isset($_POST['add']))
printf ("Records Added: %d\n", mysql_affected_rows());

Link to comment
https://forums.phpfreaks.com/topic/152061-multiple-row-update/
Share on other sites
manny Newbie

manny

Posted
Posted

I hope this is a simple one that I'm just overlooking but I am unable to update multiple rows.

 

I have 2 rows in the table and when I echo out the update query it shows the first row that should have id 1 as having id 2 and the second row is blank when it should be id 2.  When I echo the row ids in the select statement the rows ids show correctly.

 

Below is my code.  Any help would be greatly appreciated.

 

$schNameInsert = $_POST['schoolName'];
$dirNameInsert = $_POST['dirName'];
$diskQuotaInsert = $_POST['diskQuota'];
$schNameEdit = $_POST['schoolNameEdit'];
$dirNameEdit = $_POST['dirNameEdit'];
$diskQuotaEdit = $_POST['diskQuotaEdit'];
$path = "/var/data/moodleclient/";
require_once('/var/data/moodle_schools_connect.php');
$query = "select * from disk_quotas";
$result = mysql_query($query);
$num_rows = mysql_num_rows($result);

if(isset($_POST['add']))
{
  $queryInsert="INSERT INTO disk_quotas (school_name, disk_quota, dir_name) VALUES ('$schNameInsert',$diskQuotaInsert,'$dirNameInsert')";
  mysql_query($queryInsert);
  
  header('Location:'.$_SERVER['PHP_SELF']);
}

echo '<div style="font-size: 14px">';

        if($num_rows == 1)
        {
          echo $num_rows.' Record';
        }elseif($num_rows == 0)
        {
          echo 'No Records';
        }else
        {
          echo $num_rows.' Records';
        }
      echo ' Found';

if(!isset($_POST['edit']))
{
  echo '<div style="position:absolute;left:500px;top:0px;">
          <form action="'.$_SERVER['PHP_SELF'].'" method="post">
                  <input type="submit" name="edit" value="Edit" />
         </form>
        </div>';
}
echo '<form action="'.$_SERVER['PHP_SELF'].'" method="post">';
echo '<table class="sortable">
          <thead>
            <tr> 
              <th>School Name</th>
              <th>Directory Name</th>
              <th>Disk Quota</th>
            </tr>
          </thead>
          <tbody>';
while ($row = mysql_fetch_array($result, MYSQL_ASSOC))
{ 
  $diskQuota = $row['disk_quota'];
  $dirName = $row['dir_name'];
  $schoolName = $row['school_name'];
  $id = $row['school_id'];
   if(!isset($_POST['edit']))
   {
      echo '<tr>
              <td>'.$schoolName.'</td>
              <td>'.$dirName.'</td>
              <td>'.$diskQuota.'</td>
           </tr>';
   }
   else
   {
      echo '<tr>
              <td><input type="text" name="schoolNameEdit[]" value="'.$schoolName.'" /></td>
              <td><input type="text" name="dirNameEdit[]" value="'.$dirName.'" /></td>
              <td><input type="text" name="diskQuotaEdit[]" value="'.$diskQuota.'" /></td>
           </tr>';
    
   }
}

if(!isset($_POST['edit']))
{
  echo '
        <form action="'.$_SERVER['PHP_SELF'].'" method="post">
            <tr>
                <td><input type="text" name="schoolName" /></td>
                <td><input type="text" name="dirName" /></td>
                <td><input type="text" name="diskQuota" /></td>
            </tr>
          </tbody>
        </table>
        </div>
        <input type="submit" name="add" value="Add" />
        </form>';
}

if(isset($_POST['edit']))
{
    echo '<input type="submit" name="changes" value="Submit Changes" />
          </form>';
}

if(isset($_POST['changes']))
{
  for($i=0;$i<$num_rows;$i++)
  {
    $queryUpdate="UPDATE disk_quotas SET school_name='$schNameEdit[$i]', disk_quota=$diskQuotaEdit[$i], dir_name='$dirNameEdit[$i]' WHERE id=$id[$i]";
    $resultUpdate=mysql_query($queryUpdate);

    echo $queryUpdate;
  }

}

if(isset($_POST['add']))
printf ("Records Added: %d\n", mysql_affected_rows());

 

Ok

We'll change a few things

 

one

replace 

while ($row = mysql_fetch_array($result, MYSQL_ASSOC))
{ 
  $diskQuota = $row['disk_quota'];
  $dirName = $row['dir_name'];
  $schoolName = $row['school_name'];
  $id = $row['school_id'];
   if(!isset($_POST['edit']))
   {
      echo '<tr>
              <td>'.$schoolName.'</td>
              <td>'.$dirName.'</td>
              <td>'.$diskQuota.'</td>
           </tr>';
   }
   else
   {
      echo '<tr>
              <td><input type="text" name="schoolNameEdit[]" value="'.$schoolName.'" /></td>
              <td><input type="text" name="dirNameEdit[]" value="'.$dirName.'" /></td>
              <td><input type="text" name="diskQuotaEdit[]" value="'.$diskQuota.'" /></td>
           </tr>';
    
   }
}

 

with


$_x = 0 ;
while ($row = mysql_fetch_array($result, MYSQL_ASSOC))
{
  $diskQuota[$_x] = $row['disk_quota'];
  $dirName[$_x] = $row['dir_name'];
  $schoolName[$_x] = $row['school_name'];
  $id[$_x] = $row['school_id'];
   if(!isset($_POST['edit']))
   {
      echo '<tr>
              <td>'.$schoolName[$_x].'</td>
              <td>'.$dirName[$_x].'</td>
              <td>'.$diskQuota[$_x].'</td>
           </tr>';
   }
   else
   {
      echo '<tr>
              <td><input type="text" name="schoolNameEdit[]" value="'.$schoolName[$_x].'" /></td>
              <td><input type="text" name="dirNameEdit[]" value="'.$dirName[$_x].'" /></td>
              <td><input type="text" name="diskQuotaEdit[]" value="'.$diskQuota[$_x].'" /></td>
           </tr>';
   
   }
   $_x++;
}

 

2:

Replace 

if(isset($_POST['changes']))
{
  for($i=0;$i<$num_rows;$i++)
  {
    $queryUpdate="UPDATE disk_quotas SET school_name='$schNameEdit[$i]', disk_quota=$diskQuotaEdit[$i], dir_name='$dirNameEdit[$i]' WHERE id=$id[$i]";
    $resultUpdate=mysql_query($queryUpdate);

    echo $queryUpdate;
  }

}

 

 

with


if(isset($_POST['changes']))
{
$i=0;
  foreach($id as $row_id)
  {
    $queryUpdate="UPDATE disk_quotas SET school_name='$schNameEdit[$i]', disk_quota=$diskQuotaEdit[$i], dir_name='$dirNameEdit[$i]' WHERE id=$row_id";
    $resultUpdate=mysql_query($queryUpdate);

    echo $queryUpdate;
    $i++;
  }

}

 

I hope that dose it for you

please let us know

Link to comment
https://forums.phpfreaks.com/topic/152061-multiple-row-update/#findComment-798636
Share on other sites
manny Newbie

manny

Posted
Posted

manny,

 

That worked.  A million thanks!  I believe I understand what you did too so even better.

 

Beautiful..

well i can explain.

The loop runs and echos the Data but... every loop it goes through you keep changing the value.

so in the end you are left with one value in the array which is the last one to get set( in your case Row number 2 )

i am happy i could help

Link to comment
https://forums.phpfreaks.com/topic/152061-multiple-row-update/#findComment-798691
Share on other sites

Archived

This topic is now archived and is closed to further replies.

Go to topic listing
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.