[SOLVED] PHP MySQL update script not working

[bschultz]

I have a script that works on one server, but doesn't on another server.  The server that doesn't work does have a few more variables and columns in the DB, but the code is in essence, the same.

 

Here's the code to display the DB contents into an html table:

 

<?php  


//do your normal mysql setup and connection calls  
$dbc = mysql_connect(xxx,xxx,xxx);  
mysql_select_db('refs',$dbc);  
//now get stuff from a table  
$sql = "SELECT * FROM calendar1";  
      
$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"> 

    </font></p>
   
   <tr> 
      <td><div align="center">Delete</div></td>
      <td><div align="center">Row Number</div></td>
      <td><div align="center">Date</div></td>
      <td><div align="center">Gender</div></td>
      <td><div align="center">Visitor</div></td>
      <td><div align="center">Home</div></td>
      <td><div align="center">Time</div></td>
      <td><div align="center">Ref 1</div></td>
      <td><div align="center">Ref 2</div></td>
      <td><div align="center">Ref 3</div></td>
      <td><div align="center">Ref 4</div></td>
    </tr>
    <tr> 
     
<?php   
$i = 0;  
while ($row = mysql_fetch_assoc($rs)) { 
$matches++;

?>

       
         <td><div align="center"><font color="#000000">
           <input name="cmd[<?php echo $i; ?>]" type="checkbox" id="cmd" value="delete" />
          </font></div></td>
	  
 <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="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[gender]" ?>" />
        </div></td>

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

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

      <td><div align="center"><font color="#000000">
          <input name="ref1[<?php echo $i; ?>]" type="text" id="comments" size="30" maxlength="30"  value="<?php echo"$row[ref1]" ?>" />
          </font></div></td>
          
                <td><div align="center"><font color="#000000">
          <input name="ref2[<?php echo $i; ?>]" type="text" id="comments" size="30" maxlength="30"  value="<?php echo"$row[ref2]" ?>" />
          </font></div></td>
          
                <td><div align="center"><font color="#000000">
          <input name="ref3[<?php echo $i; ?>]" type="text" id="comments" size="30" maxlength="30"  value="<?php echo"$row[ref3]" ?>" />
          </font></div></td>
          
                <td><div align="center"><font color="#000000">
          <input name="ref4[<?php echo $i; ?>]" type="text" id="comments" size="30" maxlength="30"  value="<?php echo"$row[ref4]" ?>" />
          </font></div></td>
</tr> 

<?php 
$i++;
}  

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

 

 

And here's the edit_db.php code:

 

<?php
$DBName = "refs"; 
$connect = mysql_connect("$host", "$username", "$password");  


$conn1 = $connect; 

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


$date = $_POST[date];
$gender = $_POST[gender];
$visitor = $_POST[visitor];
$home = $_POST[home];
$time = $_POST[time];
$ref1 = $_POST[ref1];
$ref2 = $_POST[ref2];
$ref3 = $_POST[ref3];
$ref4 = $_POST[ref4];
$row_number = $_POST[row_number];
$cmd = $_POST[cmd];

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


if($cmd[$i] =="delete") 
{
    $sqldelete = "DELETE FROM calendar1 WHERE row_number='$row_number[$i]'";
    $resultdelete = mysql_query($sqldelete) or die ("Error in Delete Query" . mysql_error());

}


else 
$usql = "UPDATE calendar1 SET date='$date[$i]', gender='$gender[$i]', visitor='$visitor[$i]', home='$home[$i]', time='$time[$i]', ref1='$ref1[$i]', ref2='$ref2[$i]', ref3='$ref3[$i]', ref4='$ref4[$i]' WHERE row_number='$row_number[$i]'"; 
// above compiles query


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

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

 

 

The code above doesn't update anything, and in fact, deletes every "gender" and "visitor" in the DB (all 115 records!). 

 

Like I said, pretty much the same code works on one server (only has five variables and columns in the Db, as opposed to ten on the server that doesn't work), just doesn't work on the second server.  Any ideas what might be wrong?

 

Thanks.

 

Brian

[Maq]

For starters you can pass arrays through an input field.  So you can just have "row_number[]". 

 

Second thing I noticed was:

 

""

 

Look weird?  You use double quotes around the variables when it should be:

 

""

 

Have you tried debugging, echoing your variables out?

[RussellReal]

you could also do

<?=$row['row_number'];?>

[bschultz]

"<?php echo $row[row_number]; ?>"

 

still deletes everything in "gender" and "visitor" for every record

[bschultz]

Ok...moral of the story here is when copying and pasting, change ALL of the variable and form field names...not just some of them!

 

script worked fine as soon as i changed the form fields name!

 

Thanks for the help, though, guys!

 

 

[Maq]

Ok...moral of the story here is when copying and pasting, change ALL of the variable and form field names...not just some of them!

 

script worked fine as soon as i changed the form fields name!

 

Thanks for the help, though, guys!

 

 

 

Not sure what you mean but if you understand what you did wrong than I guess it doesn't matter

[RussellReal]

Ok...moral of the story here is when copying and pasting, change ALL of the variable and form field names...not just some of them!

 

script worked fine as soon as i changed the form fields name!

 

Thanks for the help, though, guys!

 

 

 

Not sure what you mean but if you understand what you did wrong than I guess it doesn't matter

free post for me too

 

Good job at fixing ur script

[Maq]

free post for me too

 

Free post?  Since when did posting cost you anything?

[gevans]

you could also do

<?=$row['row_number'];?>

 

I wouldn't recommend short tags. As far as compatibility goes not all servers support short tags

[RussellReal]

free post for me too

 

Free post?  Since when did posting cost you anything?

 

damn you're good

 

+1 for me too!

[Maq]

free post for me too

 

Free post?  Since when did posting cost you anything?

 

damn you're good

 

+1 for me too!

 

I'm lost...  ???  But thanks for the compliment!