Jump to content


Photo

Update multiple records with a where statement


  • Please log in to reply
14 replies to this topic

#1 bschultz

bschultz
  • Members
  • PipPipPip
  • Advanced Member
  • 434 posts
  • LocationMinnesota, USA

Posted 01 March 2006 - 03:22 AM

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

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 (); 

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.

#2 jworisek

jworisek
  • Members
  • PipPipPip
  • Advanced Member
  • 112 posts

Posted 01 March 2006 - 04:15 AM

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'.



#3 wickning1

wickning1
  • Members
  • PipPipPip
  • Advanced Member
  • 405 posts

Posted 01 March 2006 - 05:21 AM

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.

#4 bschultz

bschultz
  • Members
  • PipPipPip
  • Advanced Member
  • 434 posts
  • LocationMinnesota, USA

Posted 01 March 2006 - 01:42 PM

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

 
//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>

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?

#5 jworisek

jworisek
  • Members
  • PipPipPip
  • Advanced Member
  • 112 posts

Posted 01 March 2006 - 04:21 PM

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.

#6 bschultz

bschultz
  • Members
  • PipPipPip
  • Advanced Member
  • 434 posts
  • LocationMinnesota, USA

Posted 01 March 2006 - 04:26 PM

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

#7 jworisek

jworisek
  • Members
  • PipPipPip
  • Advanced Member
  • 112 posts

Posted 01 March 2006 - 04:26 PM

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

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

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

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]'

}


#8 bschultz

bschultz
  • Members
  • PipPipPip
  • Advanced Member
  • 434 posts
  • LocationMinnesota, USA

Posted 01 March 2006 - 05:00 PM

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

edit.php
//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>

and edit_db.php
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 (); 
?>  

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

#9 jworisek

jworisek
  • Members
  • PipPipPip
  • Advanced Member
  • 112 posts

Posted 01 March 2006 - 06:12 PM

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

$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
}

or

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
}


#10 bschultz

bschultz
  • Members
  • PipPipPip
  • Advanced Member
  • 434 posts
  • LocationMinnesota, USA

Posted 01 March 2006 - 06:19 PM

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

#11 jworisek

jworisek
  • Members
  • PipPipPip
  • Advanced Member
  • 112 posts

Posted 01 March 2006 - 06:27 PM

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:

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


#12 bschultz

bschultz
  • Members
  • PipPipPip
  • Advanced Member
  • 434 posts
  • LocationMinnesota, USA

Posted 01 March 2006 - 06:31 PM

Here's the code now in edit_db.php
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 (); 
?>  

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!!!

#13 jworisek

jworisek
  • Members
  • PipPipPip
  • Advanced Member
  • 112 posts

Posted 01 March 2006 - 06:45 PM

like I said, place the echo inside the loop 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:
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
}



#14 bschultz

bschultz
  • Members
  • PipPipPip
  • Advanced Member
  • 434 posts
  • LocationMinnesota, USA

Posted 01 March 2006 - 06:50 PM

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

#15 wickning1

wickning1
  • Members
  • PipPipPip
  • Advanced Member
  • 405 posts

Posted 01 March 2006 - 07:12 PM

edit_db.php:
<?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;
}
?>
edit.php:
<?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>





0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users