[SOLVED] Getting the wrong row_number...

[bschultz]

I'm trying to write a script to edit and delete numerous records in a mysql table.  The mysql is fine, but I'm getting the wrong row number.  Here's the code:

 

<?php 

require "config.php"; 

$conn1 = $connect; 

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

$date = $_POST[date];
$type = $_POST[type];
$event = $_POST[event];
$action = $_POST[action];
$comments = $_POST[comments];
$row_number = $_POST[row_number];
$cmd = $_POST[cmd];


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


if($cmd[$i] =="delete") 
{
    $sqldelete = "DELETE FROM cancellations WHERE row_number='$_POST[row_number]'";
    $resultdelete = mysql_query($sqldelete) or die ("Error in Delete Query" . mysql_error());
    echo "<meta http-equiv=Refresh content=1;url='/weather/edit.php'>" ;

}


else 
$usql = "UPDATE cancellations SET 
date='$date[$i]', 
type='$type[$i]', 
event='$event[$i]', 
action='$action[$i]', 
comments='$comments[$i]' 

WHERE row_number='$row_number[$i]'"; 
// above 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='/weather/edit.php'>";
} 
else{ 
echo mysql_errno().": ".mysql_error()."<BR>"; 
} 
mysql_close (); 
?>  

 

I've also tried this:

 

WHERE row_number='".$_POST[row_number]."'";

 

But that doesn't work either.  With the second piece of code, I can edit, but can't delete.  Any ideas where I've gone wrong?

 

Thanks.

[poleposters]

Your SQL looks ok. If you're deleting the wrong row number then you must have the wrong value being posted. Show us the form script that posts this info.

[sasa]

try

WHERE row_number='".$_POST['row_number'][$i]."'";

[bschultz]

sasa, I tried that code, and it still didn't work.  Here's the code for the form:

 

<?php  
require "config.php";

//do your normal mysql setup and connection calls  
$dbc = mysql_connect($host,$username,$password);  
mysql_select_db('testing',$dbc);  
//now get stuff from a table  
$sql = "SELECT row_number, date, type, event, action, comments FROM cancellations ORDER BY event";  


?> 

other non-php page layout stuff 


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

//now spit out the table and rows for the table  
    
$rs = mysql_query($sql,$dbc);  
$matches = 0;  
?>


  more non-php layout stuff


<form method="POST" action="edit_db.php">
  

table layout


     
<?php      
while ($row = mysql_fetch_assoc($rs)) { 
$matches++;

?>
 <td> <input name="row_number[]" type="text" id="row_number" value="<?php echo"$row[row_number]" ?>" /> </td>
       
         <td><div align="center"><font color="#000000">
           <input name="cmd[]" type="checkbox" id="cmd" value="delete" />
          </font></div></td>
	  
	  
	  <td><div align="center"><font color="#000000">
           <input name="date[]" type="text" id="date" size="10" maxlength="10"  value="<?php echo"$row[date]" ?>" />
          </font></div></td>

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

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

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

      <td><div align="center"><font color="#000000">
          <input name="comments[]" type="text" id="comments" size="50" maxlength="300"  value="<?php echo"$row[comments]" ?>" />
          </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>

 

If I try to delete row 3, row 1 is deleted...if I try to delete row 5, row 3 is deleted.  So, I'm off by 2.  The row numbers echoed in the form are correct, by the way, so the problem appears to be in the original script getting the wrong row number.

[sasa]

change form to

<?php   
$i = 0;  
while ($row = mysql_fetch_assoc($rs)) { 
$matches++;

?>
 <td> <input name="row_number[<?php echo $i; ?>]" type="text" id="row_number" value="<?php echo"$row[row_number]" ?>" /> </td>
       
         <td><div align="center"><font color="#000000">
           <input name="cmd[<?php echo $i; ?>]" type="checkbox" id="cmd" value="delete" />
          </font></div></td>
	  
	  
	  <td><div align="center"><font color="#000000">
           <input name="date[<?php echo $i; ?>]" type="text" id="date" size="10" maxlength="10"  value="<?php echo"$row[date]" ?>" />
          </font></div></td>

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

     <td><div align="center"> 
           <input name="event[<?php echo $i; ?>]" type="text" id="event" size="50" maxlength="250"  value="<?php echo"$row[event]" ?>" />
      </div></td>

      <td><div align="center"><font color="#000000">
          <input name="action[<?php echo $i; ?>]" type="text" id="action" size="20" maxlength="25"  value="<?php echo"$row[action]" ?>" />
          </font></div></td>

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

<?php 
$i++;
}  

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>

[bschultz]

That did it.  Thanks so much!  I'm not sure how it did it, but it did it.  So is this

 

<?php echo $i; ?>

 

just getting the correct value to the array?

 

Thanks again.