Update multiple records with a where statement

[bschultz]

I'm having a hard time figuring out how to setup my WHERE statement to update multiple records. Here's what I have:

[code]
mysql_connect($DBhost,$DBuser,$DBpass) or die("Unable to connect to database");

@mysql_select_db("$DBName") or die("Unable to select database $DBName");


$usql = "UPDATE weather SET
event='$_POST[event]',
hrs='$_POST[hrs]',
closed='$_POST[closed]',
notes='$_POST[notes]' WHERE $_POST[row_number] = row_number"; // compiles query

$dosql = mysql_query($usql); // executes query


  if ($dosql){
    echo "<p>Thank You, your entry has been submitted!</p>  
    <meta http-equiv=Refresh content=1;url='http://www.mysite.com/'>";
  }
  else{
    echo mysql_errno().": ".mysql_error()."<BR>";
  }
  mysql_close ();
[/code]

The "row_number" field is set to primary key, auto increment. The problem with the code is that it's only updating the LAST record, not all the other records that need updating.

Can someone point me in the right direction on how to word the WHERE statement?

Thanks.

[jworisek]

are you trying to update many records in the table that all have the same primary key? You can't have duplicates of primary key values, so your update query will only ever update 1 row if you are using where [primary_key]='value'.

[wickning1]

How are you getting more than one row number from the form data? I think we need to see a little of your HTML before we can help you more.

[bschultz]

Here's the html of the "edit.php" page.

[code]

//do your normal mysql setup and connection calls  
$dbc = mysql_pconnect('xxx','xxx','xxx');  
mysql_select_db('weather',$dbc);  
//now get stuff from a table  
$sql = "SELECT row_number, event, hrs, closed, notes FROM weather WHERE today=CURDATE()";  


?>

  

<img src="/images/1360am.jpg" width="180" height="82"><strong><img src="/images/realcountry.jpg" width="180" height="82"><img src="/images/mix.JPG" width="180" height="82"><br>
<br>
RP Broadcasting Weather Related Announcements for
<?php
$day = date("F j, Y");
echo "$day";
?>
</strong><br>

      <?php  
      
$dbq = mysql_query($sql,$dbc);

//now spit out the table and rows for the table  
    
$rs = mysql_query($sql,$dbc);  
$matches = 0;  
?>
<table width='100%' border='1'>
<form method="POST" action="edit_db.php">
  <p style="margin-bottom: 0"><br>
    <font color="#000000">

    <br>
    <br>
    </font></p>
  
   <tr>
       <td><div align="center">Event or School</div></td>
      <td><div align="center">2 Hours Late?</div></td>
      <td><div align="center">Closed?</div></td>
      <td><div align="center">Notes - when rescheduled or when open</div></td>
    </tr>
    <tr>
    
<?php      
while ($row = mysql_fetch_assoc($rs)) {
$matches++;

?>


    
      
      <input name="row_number" type="hidden" id="row_number" value="<?php echo"$row[row_number]" ?>" />
      
         <td><div align="center"><font color="#000000">
           <input name="event" type="text" id="event" size="50" maxlength="200"  value="<?php echo"$row[event]" ?>" />
          </font></div></td>

      <td><div align="center">
           <input name="hrs" type="text" id="hrs" size="11" maxlength="15"  value="<?php echo"$row[hrs]" ?>" />
        </div></td>

     <td><div align="center">
           <input name="closed" type="text" id="closed" size="7" maxlength="7"  value="<?php echo"$row[closed]" ?>" />
      </div></td>

      <td><div align="center"><font color="#000000">
          <input name="notes" type="text" id="notes" size="50" maxlength="200"  value="<?php echo"$row[notes]" ?>" />
          </font></div></td>

</TR>

<?php
}  

if (! $matches) {
echo ("</table>There are no weather related announcements today");
}  
echo "</TABLE>";  
?>
    <input name="submit" type="submit" value="Submit" />
    <input type="reset" name="Reset" value="Reset" />
</form>
[/code]

The row_number is a hidden field here that again is primary key, auto increment in the database. So, the first record in the database is "1" and the second is "2" and so on. If I echo that back in this html, it does display the correct information. Am I way off base in my logic, or am I on the right track?

[jworisek]

you can only have one value per variable name... each time that cycles through, it overwrites the previous loops data. So in the end only the last row is there.

On your page that submits the query to update the database, comment out the update query and echo your post variables and you will see this.

[bschultz]

so how should I design this so that it works the way that I want?

[jworisek]

change your inputs to: (adding [] after each input name)

[code]
<input name="event[]" type="text" id="event" size="50" maxlength="200"  value="<?php echo"$row[event]" ?>" />
[/code]

then when you are doing the update, you can just do

[code]
for ($i=0;$i<count($_POST[event]);$i++){

  $sql=update weather set event='$_POST[event][$i]',....etc where WHERE row_number='$_POST[row_number] [$i]'

}
[/code]

[bschultz]

Thanks, jworisek, for the help, but it isn't updating anything now...not even that last record.

edit.php
[code]
//do your normal mysql setup and connection calls  
$dbc = mysql_pconnect('xxx','xxx','xxx');  
mysql_select_db('weather',$dbc);  
//now get stuff from a table  
$sql = "SELECT row_number, event, hrs, closed, notes FROM weather WHERE today=CURDATE()";  


?>

  

<img src="/images/1360am.jpg" width="180" height="82"><strong><img src="/images/realcountry.jpg" width="180" height="82"><img src="/images/mix.JPG" width="180" height="82"><br>
<br>
RP Broadcasting Weather Related Announcements for
<?php
$day = date("F j, Y");
echo "$day";
?>
</strong><br>

      <?php  
      
$dbq = mysql_query($sql,$dbc);

//now spit out the table and rows for the table  
    
$rs = mysql_query($sql,$dbc);  
$matches = 0;  
?>
<table width='100%' border='1'>
<form method="POST" action="edit_db.php">
  <p style="margin-bottom: 0"><br>
    <font color="#000000">

    <br>
    <br>
    </font></p>
  
   <tr>
       <td><div align="center">Event or School</div></td>
      <td><div align="center">2 Hours Late?</div></td>
      <td><div align="center">Closed?</div></td>
      <td><div align="center">Notes - when rescheduled or when open</div></td>
    </tr>
    <tr>
    
<?php      
while ($row = mysql_fetch_assoc($rs)) {
$matches++;

?>


    
      
      <input name="row_number[]" type="hidden" id="row_number" value="<?php echo"$row[row_number]" ?>" />
      
         <td><div align="center"><font color="#000000">
           <input name="event[]" type="text" id="event" size="50" maxlength="200"  value="<?php echo"$row[event]" ?>" />
          </font></div></td>

      <td><div align="center">
           <input name="hrs[]" type="text" id="hrs" size="11" maxlength="15"  value="<?php echo"$row[hrs]" ?>" />
        </div></td>

     <td><div align="center">
           <input name="closed[]" type="text" id="closed" size="7" maxlength="7"  value="<?php echo"$row[closed]" ?>" />
      </div></td>

      <td><div align="center"><font color="#000000">
          <input name="notes[]" type="text" id="notes" size="50" maxlength="200"  value="<?php echo"$row[notes]" ?>" />
          </font></div></td>

</TR>

<?php
}  

if (! $matches) {
echo ("</table>There are no weather related announcements today");
}  
echo "</TABLE>";  
?>
    <input name="submit" type="submit" value="Submit" />
    <input type="reset" name="Reset" value="Reset" />
</form>
[/code]

and edit_db.php
[code]
mysql_connect($DBhost,$DBuser,$DBpass) or die("Unable to connect to database");

@mysql_select_db("$DBName") or die("Unable to select database $DBName");

for ($i=0;$i<count($_POST[event]);$i++){
$usql = "UPDATE weather SET
event='$_POST[event][$i]',
hrs='$_POST[hrs]',
closed='$_POST[closed]',
notes='$_POST[notes]' WHERE row_number='$_POST[row_number][$i]'"; // compiles query
}
$dosql = mysql_query($usql); // executes query


  if ($dosql){
    echo "<p>Thank You, your entry has been submitted!</p>
    <meta http-equiv=Refresh content=1;url='http://www.mysite.com'>";
  }
  else{
    echo mysql_errno().": ".mysql_error()."<BR>";
  }
  mysql_close ();
?>  
[/code]

If I echo $usql this is what I get:

[!--quoteo--][div class=\'quotetop\']QUOTE[/div][div class=\'quotemain\'][!--quotec--]
UPDATE weather SET event='Array[1]', hrs='Array', closed='Array', notes='Array' WHERE row_number='Array[1]'
[/quote]

Thanks again for the help.

Brian

[jworisek]

Well you only made changes for event and nothing else... I just used event as an example since Im too lazy to type the whole thing out ;)


try either of these to get it working...

sometimes it has issues when calling a multidimensional array.. so then just say

[code]
$event=$_POST[event];
$hrs=$_POST[hrs];
$closed=$_POST[closed];
$notes=$_POST[closed];
$row_number=$_POST[row_number];

for ($i=0;$i<count($_POST[event]);$i++){
$usql = "UPDATE weather SET
event='$event[$i]',
hrs='$hrs[$i]',
closed='$closed[$i]',
notes='$notes[$i]' WHERE row_number='$row_number[$i]'"; // compiles query
}
[/code]

or

[code]
for ($i=0;$i<count($_POST[event]);$i++){
$usql = "UPDATE weather SET
event='".$_POST[event][$i]."',
hrs='".$_POST[hrs][$i]."',
closed='".$_POST[closed][$i]."',
notes='".$_POST[notes][$i]."' WHERE row_number='".$_POST[row_number][$i]."'"; // compiles query
}
[/code]

[bschultz]

I did what you said, and it's only updating the last record again!

[jworisek]

Save yourself a huge headache and just echo the values instead of trying to do queries when you are testing code.

Post and echo of all 5 values from inside the loop to see if it is actually giving different values.


... and a quick tip, never use <font> tags... you can just stick that in your div tag like so:

[code]
<div align="center" style="color:#000000;">
          <input name="notes[]" type="text" id="notes" size="50" maxlength="200"  value="<?php echo"$row[notes]" ?>" />
</div></td>
[/code]

[bschultz]

Here's the code now in edit_db.php
[code]
mysql_connect($DBhost,$DBuser,$DBpass) or die("Unable to connect to database");

@mysql_select_db("$DBName") or die("Unable to select database $DBName");

$event=$_POST[event];
$hrs=$_POST[hrs];
$closed=$_POST[closed];
$notes=$_POST[notes];
$row_number=$_POST[row_number];

for ($i=0;$i<count($_POST[event]);$i++){
$usql = "UPDATE weather SET
event='$event[$i]',
hrs='$hrs[$i]',
closed='$closed[$i]',
notes='$notes[$i]'

WHERE row_number='$row_number[$i]'"; // compiles query
}
echo "$usql";
//$dosql = mysql_query($usql); // executes query


//  if ($dosql){
//    echo "<p>Thank You, your entry has been submitted!</p>
//    <meta http-equiv=Refresh content=1;url='http://www.kkbjam.com/weather/edit.php'>";
//  }
//  else{
//    echo mysql_errno().": ".mysql_error()."<BR>";
//  }
//  mysql_close ();
?>  
[/code]

Here's the result:

UPDATE weather SET event='school 2', hrs='2 Hours Late', closed='', notes='school 2 notes' WHERE row_number='2'

Still only the second record...thanks again for the help on this!!!

[jworisek]

like I said, place the echo [b]inside the loop[/b] so we can see whats going on instead of just showing me the last query that we know works.

the problem is that you are not submitting your query inside the loop... everytime the loop runs your data is overwritten so of course only the last row is run.

Try this:
[code]
for ($i=0;$i<count($_POST[event]);$i++){
  $usql = "UPDATE weather SET
  event='$event[$i]',
  hrs='$hrs[$i]',
  closed='$closed[$i]',
  notes='$notes[$i]'

  WHERE row_number='$row_number[$i]'"; // compiles query

  $dosql = mysql_query($usql); // executes query
}

[/code]

[bschultz]

That did it...thanks SO much for the help.

[wickning1]

edit_db.php:
[code]<?php
mysql_connect($DBhost,$DBuser,$DBpass) or die("Unable to connect to database");
mysql_select_db("$DBName") or die("Unable to select database $DBName");

$sql = "SELECT row_number FROM weather WHERE today=CURDATE()";

//now spit out the table and rows for the table
$rs = mysql_query($sql,$dbc);
$noerrors = TRUE;
while ($row = mysql_fetch_array($rs)) {
    $i = $row['row_number'];
    $usql = 'UPDATE weather ' .
            'SET event="' . $_POST['event'.$i] . '",' .
            'hrs="' . $_POST['hrs'.$i] . '",' .
            'closed="' . $_POST['closed'.$i] . '",' .
            'notes="' . $_POST['notes'.$i] . '" ' .
            'WHERE row_number="' . $i . '";
    if(!mysql_query($usql)) { $noerrors = FALSE; $msg = mysql_errno().": ".mysql_error()."<BR>"; }        
}

if ($noerrors) {
    echo "<p>Thank You, your entry has been submitted!</p>" .
         "<meta http-equiv=Refresh content=1;url='http://www.mysite.com'>";
} else {
    echo $msg;
}
?>[/code]
edit.php:
[code]<?php
//do your normal mysql setup and connection calls
$dbc = mysql_pconnect('xxx','xxx','xxx');
mysql_select_db('weather',$dbc);
?>
<img src="/images/1360am.jpg" width="180" height="82">
<strong>
<img src="/images/realcountry.jpg" width="180" height="82">
<img src="/images/mix.JPG" width="180" height="82"><br>
<br>
RP Broadcasting Weather Related Announcements for
<?php echo date("F j, Y"); ?>
</strong><br>
<?php
//now get stuff from a table
$sql = "SELECT row_number, event, hrs, closed, notes FROM weather WHERE today=CURDATE()";

//now spit out the table and rows for the table
$rs = mysql_query($sql,$dbc);
?>
<form method="POST" action="edit_db.php">
<table width='100%' border='1'>
    <tr>
        <td><div align="center">Event or School</div></td>
        <td><div align="center">2 Hours Late?</div></td>
        <td><div align="center">Closed?</div></td>
        <td><div align="center">Notes - when rescheduled or when open</div></td>
    </tr>
    <tr>
<?php
while ($row = mysql_fetch_assoc($rs)) {
    $event = '"event' . $row['row_number'] . '"';
    $hrs = '"hrs' . $row['row_number'] . '"';
    $closed = '"closed' . $row['row_number'] . '"';
    $notes = '"notes' . $row['row_number'] . '"';
?>
        <td><div align="center"><font color="#000000">
        <input name=<?php echo $event ?> type="text" id=<?php echo $event ?> size="50" maxlength="200"  value="<?php echo $row['event']; ?>" />
        </font></div></td>

        <td><div align="center">
        <input name=<?php echo $hrs ?> type="text" id=<?php echo $hrs ?> size="11" maxlength="15"  value="<?php echo $row['hrs']; ?>" />
        </div></td>
        
        <td><div align="center">
        <input name=<?php echo $closed ?> type="text" id=<?php echo $closed ?> size="7" maxlength="7"  value="<?php echo $row['closed']; ?>" />
        </div></td>
        
        <td><div align="center"><font color="#000000">
        <input name=<?php echo $notes ?> type="text" id=<?php echo $notes ?> size="50" maxlength="200"  value="<?php echo $row['notes']; ?>" />
        </font></div></td>
    </tr>
<?php
}
if (!mysql_num_rows($rs)) {
    echo "<tr><td colspan=4>There are no weather related announcements today.</td></tr>";
}
echo "</TABLE>";
?>
    <input name="submit" type="submit" value="Submit" />
    <input type="reset" name="Reset" value="Reset" />
</form>[/code]