Jump to content

Update multiple records with a where statement


bschultz

Recommended Posts

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.
Link to comment
Share on other sites

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?
Link to comment
Share on other sites

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.
Link to comment
Share on other sites

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]
Link to comment
Share on other sites

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
Link to comment
Share on other sites

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]
Link to comment
Share on other sites

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]
Link to comment
Share on other sites

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!!!
Link to comment
Share on other sites

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]
Link to comment
Share on other sites

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]
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • 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.