[RESOLVED] MySQL UPDATE TABLE problem.

Hi.
What im trying to do is.Update a table full of max size's.These size's determine the "MAX FILESIZE" that a person can upload to the server with.Depending on their access level.

The problem im having right now (which probably has a simple solution) is updating the max size.

I am really stumped.I've tried everything from two foreach's too a for loop.

This is what the table, that i am using, looks like.
http://plague.chaoticdesignz.net/downloadmaxsize1.JPG

My problem is of course the updating of the max filesize.Using this code.HTML included.

PHP Code:

---

//----------------------------------
if(isset($_POST['update'])){
            
    if(is_array($_POST['maxsize'])){
        foreach($_POST['maxsize'] as $size){
    //??????????????????????????????????????? This is where i have the problem.
        }
    }
}
//==================================
$SQL = "SELECT dms.*, acl.* FROM ".DOWNLOADSMAXSIZE_TABLE." dms JOIN ".ACCESS_LVL_TABLE." acl ON dms.accessID = acl.access_id";
$RESULT = mysql_query($SQL) or print(mysql_error());
?>

<form id="form1" name="form1" method="post" action="download_management.php">
  <table width="31%" border="0" align="center" cellpadding="0" cellspacing="1" bgcolor="#000000">
    <tr>
      <td colspan="2" bgcolor="#003366" class="content_header">Download - Max File Size </td>
    </tr>
    <tr>
      <td colspan="2" bgcolor="#003366" class="content_header2">*Note: -1 = Unlimated</td>
    </tr>
    <?
  while($row = mysql_fetch_array($RESULT)):
  ?>
    <tr>
      <td width="42%" bgcolor="#003366" class="content_row1"><div align="left">
          <?=$row['access_name']?>
      </div></td>
      <td width="58%" bgcolor="#003366" class="content_row2">
          <input type="hidden" name="sizeID[]" value="<?=$row['accessID']?>"/>
      <input type="text" name="maxsize[]" class="txtBox" value="<?=$row['size']?>" /></td>
    </tr>
    <?
  endwhile;
  ?>
    <tr>
      <td colspan="2" bgcolor="#003366" class="content_header"><div align="center">
          <input type="submit" name="update" class="txtbox" value="Update"/>
      </div></td>
    </tr>
  </table>
</form>

---

Im stumped on this.

Im not sure if MySQL supports mutliple updates in the same query, but can you not loop though and create an update query for each item that needs updating?

What's the UPDATE SQL script you have tried?

Quote:

---

Originally Posted by john tindell

Im not sure if MySQL supports mutliple updates in the same query, but can you not loop though and create an update query for each item that needs updating?

---

I've tried alot of ways to update them. A few worked...But only changed every "size" value to the same one.


I know this is probably very simple. But i just cant think of how to do it.

Like i said in earlier post.

I tried utilizing a "for" loop and a foreach.One or the other,Both Together.

Some worked.With ugly results.Others did nothing.


If i could successfully catch the accessID of the size.I'd possibly able to update the size correctly.

But i am stumped.

Edit::


ROFL... I had thought of doing this earlier...but.. i didnt

PHP Code:

---

    if(is_array($_POST['maxsize'])){
        foreach($_POST['maxsize'] as $Key_size => $Key_value){
        echo $Key_size . " => " . $Key_value."<br /> \n";
        mysql_query("UPDATE ".DOWNLOADSMAXSIZE_TABLE." SET size='".$Key_value."' WHERE accessID='".$Key_size."'");        
        }
    } 


---

If you want to update only a single field, use the primary key in the WHERE clause to limit the update to just one row. If you use any other field you cnanot garuntee that only one row will be updated.

For this.I needed ( and have done.) to update all rows.

Yes, you have updated all rows, but each update contains different data, hence, you have executed multiple queries.

Code:

---

UPDATE tablename SET size=213;

---

Will update every row in the table setting the size to 213.

Code:

---

UPDATE tablename SET size=213 WHERE accessid=2324;

---

Will update only a single row, assuming accessid is the primary key or has a unique constraint (there is nothing saying that it has to be a primary key however).

Well.I mean this was the crude way of doing it...

I mean it's hoping that the accessID is the same as teh array#