PHO MySQL Multiple Rows Update issues

PHP
1

Can anyone explain to me why the following script does not work? I don’t get any errors, I just run the scripts and it doesn’t update anything in my database. What am I doing wrong? If anyone has a better way of doing this please let me know.

Thanks for any help in advance.

<form name=“form1” method=“post” action=“” enctype=“multipart/form-data”>
<table width=“100%” border=“1” cellspacing=“0” cellpadding=“0”>
<tr>
<td class=“formheadlines”>
Site
</td>
<td class=“formheadlines”>Team</td>
<td class=“formheadlines”>Login Details</td>
</tr>

  <?php

$sql="SELECT * FROM Article_Sites";
$result=mysql_query($sql);

// Count table rows
$count=mysql_num_rows($result);
while($rows=mysql_fetch_array($result)){
?>

 <tr>
    <td>
      <? $Id[]=$rows['Id']; ?><input type='text' name='Site[]' id='Site' value='<? echo $rows['Site']; ?>'>
      </td>
    <td><input type='text' name='Team[]' id='Team' value='<? echo $rows['Team']; ?>'></td>
    <td><input type='text' name='Login[]' id='Login' value='<? echo $rows['Login']; ?>'></td>
  </tr>
  <?php
}
?>
    <tr>
    <td class="formheadlines"></td>
    <td class="formheadlines"></td>
    <td class="formheadlines"><input type="submit" name="Submit" value="Submit" style="width:100px; height:25px; cursor:pointer; font-size:12px;"></td>
  </tr>    
  </table>
</form>
<?php
// Check if button name "Submit" is active, do this
if($_POST['Submit']){
for($i=0;$i<$count;$i++){

$sql1 = "UPDATE Article_Sites SET Site='{$_POST['Site'][$i]}', Team='{$_POST['Team'][$i]}', Login='{$_POST['Login'][$i]}' WHERE Id='{$_POST['Id'][$i]}'"; 
$result1=mysql_query($sql1);
}
}
mysql_close();
?>
2 

    // Change to code starts here
    if (!$result=mysql_query($sql1)) {
      echo "<p><b>Query Failed:</b><i> <b>".mysql_errno()."</b> ".mysql_error()."</i></p><p><b>SQL:</b><i> ".$sql1."</i><p/><br/>";
    } else {                           
      return $result;
    }
    // and ends here

The ‘return’ doesn’t make much sense to me.
Try changing it like this (I added a line to see the update queries as well):


    // add this line just to debug - you can delete it when everything works ok
    echo "sql1: $sql1 <br />";
    // Change to code starts here
    if (!$result=mysql_query($sql1)) {
      echo "<p><b>Query Failed:</b><i> <b>".mysql_errno()."</b> ".mysql_error()."</i></p><p><b>SQL:</b><i> ".$sql1."</i><p/><br/>";
    } 
    // and ends here
3

Please post your script again, as it is now

4

Loop through the sites with foreach:


<?php
// Check if button name "Submit" is active, do this
if ($_POST['Submit']) {
  foreach ($_POST['Site'] as $key => $value) {
    $sql1 = "UPDATE Article_Sites SET Site='{$_POST['Site'][$key]}', Team='{$_POST['Team'][$key]}', Login='{$_POST['Login'][$key]}' WHERE Id='{$_POST['Id'][$key]}'";
 
    // Change to code starts here
    if (!$result=mysql_query($sql1)) {
      echo "<p><b>Query Failed:</b><i> <b>".mysql_errno()."</b> ".mysql_error()."</i></p><p><b>SQL:</b><i> ".$sql1."</i><p/><br/>";
    } else {                           
      return $result;
    }
    // and ends here
  }
}
?>

And just a question: the code that creates the connection to the database is executed before this part, right?

5

ok tried that and what its now doing is updating the database but shows a blank screen. I have to refresh the page manually to get the page to show again?

6

Firstly thanks for the help with this.

I now get the following string(6) “Submit”

What does that mean?

I have also added another column to my table incase that makes more sense to anyone

7

This is what my script now looks like:

<?php

$sql="SELECT * FROM Article_Sites";
$result=mysql_query($sql);

// Count table rows
$count=mysql_num_rows($result);
while($rows=mysql_fetch_array($result)){
?>

 <tr>
    <td>
      <? $Id[]=$rows['Id']; ?><input type="text" name="Site[]" id="Site" value="<? echo $rows['Site']; ?>">
      </td>
    <td><input type="text" name="Team[]" id="Team" value="<? echo $rows['Team']; ?>"></td>
    <td><input type="text" name="Username[]" id="Username" value="<? echo $rows['Username']; ?>"></td>
    <td><input type="text" name="Password[]" id="Password" value="<? echo $rows['Password']; ?>"></td>
  </tr>
  <?php
}
?>
    <tr>
    <td class="formheadlines"></td>
    <td class="formheadlines"></td>
    <td class="formheadlines"></td>
    <td class="formheadlines"><input type="submit" name="Submit" value="Submit" style="width:100px; height:25px; cursor:pointer; font-size:12px;"></td>
  </tr>    
  </table>
</form>
<?php
// Check if button name "Submit" is active, do this
var_dump($_POST['Submit']); // <--- *** this line here ***
if($_POST['Submit']){
for($i=0;$i<$count;$i++){

$sql1 = "UPDATE Article_Sites SET Site='{$_POST['Site'][$i]}', Team='{$_POST['Team'][$i]}', Username='{$_POST['Username'][$i]}', Password='{$_POST['Password'][$i]}' WHERE Id='{$_POST['Id'][$i]}'"; 
$result1=mysql_query($sql1);
}
}
mysql_close();
?>
8

done that still the same?

I do really appreciate the help with this because I’m at a loss as to what to do.

9 
<form name="form1" method="post" action=""  enctype="multipart/form-data">
        <table width="100%" border="1" cellspacing="0"  cellpadding="0">
        <tr>
    <td class="formheadlines">
      Site
      </td>
    <td class="formheadlines">Team</td>
    <td class="formheadlines">Login Details</td>
  </tr>
<?php

$sql="SELECT * FROM Article_Sites";
$result=mysql_query($sql);

// Count table rows
$count=mysql_num_rows($result);
while($rows=mysql_fetch_array($result)){
?>

 <tr>
    <td>
      <? $Id[]=$rows['Id']; ?><input type='text' name='Site[]' id='Site' value='<? echo $rows['Site']; ?>'>
      </td>
    <td><input type='text' name='Team[]' id='Team' value='<? echo $rows['Team']; ?>'></td>
    <td><input type='text' name='Login[]' id='Login' value='<? echo $rows['Login']; ?>'></td>
  </tr>
  <?php
}
?>
    <tr>
    <td class="formheadlines"></td>
    <td class="formheadlines"></td>
    <td class="formheadlines"><input type="submit" name="Submit" value="Submit" style="width:100px; height:25px; cursor:pointer; font-size:12px;"></td>
  </tr>    
  </table>
</form>
<?php
// Check if button name "Submit" is active, do this
var_dump($_POST['Submit']); // <--- *** this line here ***
if($_POST['Submit']){
for($i=0;$i<$count;$i++){

$sql1 = "UPDATE Article_Sites SET Site='{$_POST['Site'][$i]}', Team='{$_POST['Team'][$i]}', Login='{$_POST['Login'][$i]}' WHERE Id='{$_POST['Id'][$i]}'"; 
$result1=mysql_query($sql1);
}
}
mysql_close();
?>

Add the line indicated, what does it show?

10

My current code. Which I think is now behaving strangely with my database. It seems to be updating the wrong fields now!

<?php
session_start();
if($_SESSION["username"]=="") {
	header("location: index.php");
}
?>
<?php require("includes/info.php"); ?>
<?php
// Check if button name "Submit" is active, do this
if ($_REQUEST['Submit']) {
  foreach ($_POST['Site'] as $key => $value) {
    $sql1 = "UPDATE Article_Sites SET Site='{$_POST['Site'][$key]}', Team='{$_POST['Team'][$key]}', Username='{$_POST['Username'][$key]}', Password='{$_POST['Password'][$key]}' WHERE Id='{$_POST['Id'][$key]}'";
 
    // Change to code starts here
    if (!$result=mysql_query($sql1)) {
      echo "<p><b>Query Failed:</b><i> <b>".mysql_errno()."</b> ".mysql_error()."</i></p><p><b>SQL:</b><i> ".$sql1."</i><p/><br/>";
    } else {                           
      return $result;
    }
    // and ends here
  }
}
?>
<div class="global">
<div class="page_header">
<div class="home-button"><a href="main.php">Home</a></div>
<div class="page_welcome"><?php
	echo "Welcome User : ".$_SESSION["username"]." || <a href='logout.php'>Log Out</a> ";
?></div></div>
<div class="sheet-list-title">
  <div class="sheet-title">Spreadsheet: Articles Sites</div></div>
<div class="formholder">
<form name="form1" method="post" action=""  enctype="multipart/form-data">
        <table width="100%" border="1" cellspacing="0"  cellpadding="0">
        <tr>
    <td class="formheadlines">
      Site
      </td>
    <td class="formheadlines">Team</td>
    <td class="formheadlines">Username</td>
    <td class="formheadlines">Password</td>
  </tr>
<?php

$sql="SELECT * FROM Article_Sites";
$result=mysql_query($sql);

// Count table rows
$count=mysql_num_rows($result);
while($rows=mysql_fetch_array($result)){
?>

 <tr>
    <td>
      	<input type='text' name='Site[]' id='Site' value='<? echo $rows['Site']; ?>'><input name="Id" type="hidden" id="Id" value="<? echo $rows['Id']; ?>">
      </td>
    <td><input type='text' name='Team[]' id='Team' value='<? echo $rows['Team']; ?>' style="width:100px;"></td>
    <td><input type='text' name='Username[]' id='Username' value='<? echo $rows['Username']; ?>' style="width:200px;"></td>
    <td><input type='text' name='Password[]' id='Password' value='<? echo $rows['Password']; ?>' style="width:100px;"></td>
  </tr>
  <?php
}
?>
    <tr>
    <td class="formheadlines"></td>
    <td class="formheadlines"></td>
    <td class="formheadlines"></td>
    <td class="formheadlines"><input type="submit" name="Submit" value="Submit" style="width:100px; height:25px; cursor:pointer; font-size:12px;"></td>
  </tr>    
  </table>
</form>
<?php
mysql_close();
?>
</div>
</div>
11

You are amazing!!! Thankyou so much for all your help and patience. I’ve only tested it quickly but it now seems to be doing everything that I needed it to. Sitepoint forum members are the best on the web!

12

First error (I think): you didn’t give the hidden field an ‘array’ name. Change it like this:


<input name="Id[]" type="hidden" id="Id" value="<? echo $rows['Id']; ?>">

Secondly, the script didn’t go in a loop.
There are 40 lines in your form, 35 are empty. So I guess you also have 35 empty lines in your database table?

13

yeah all test data :slight_smile:

14

Ok I worked out what I’d done wrong, mysql_close was stopping the other queries. Problem is the script is not updating the databse anymore! Any other suggestions?

15

Btw, I hope those usernames and passwords are just test data?

16

As instructed. Now getting following:

post array: array(6) { [“Site”]=> array(40) { [0]=> string(17) “www.1000goals.com” [1]=> string(17) “www.trulyreds.com” [2]=> string(32) “www.manuniteddevils.blogspot.com” [3]=> string(38) “www.manchesterunitedsblog.blogspot.com” [4]=> string(19) “www.kbsoccernet.com” [5]=> string(0) “” [6]=> string(0) “” [7]=> string(0) “” [8]=> string(0) “” [9]=> string(0) “” [10]=> string(0) “” [11]=> string(0) “” [12]=> string(0) “” [13]=> string(0) “” [14]=> string(0) “” [15]=> string(0) “” [16]=> string(0) “” [17]=> string(0) “” [18]=> string(0) “” [19]=> string(0) “” [20]=> string(0) “” [21]=> string(0) “” [22]=> string(0) “” [23]=> string(0) “” [24]=> string(0) “” [25]=> string(0) “” [26]=> string(0) “” [27]=> string(0) “” [28]=> string(0) “” [29]=> string(0) “” [30]=> string(0) “” [31]=> string(0) “” [32]=> string(0) “” [33]=> string(0) “” [34]=> string(0) “” [35]=> string(0) “” [36]=> string(0) “” [37]=> string(0) “” [38]=> string(0) “” [39]=> string(0) “” } [“Id”]=> string(2) “40” [“Team”]=> array(40) { [0]=> string(11) “Generaltest” [1]=> string(7) “Man Utd” [2]=> string(7) “Man Utd” [3]=> string(7) “Man UTD” [4]=> string(7) “General” [5]=> string(0) “” [6]=> string(0) “” [7]=> string(0) “” [8]=> string(0) “” [9]=> string(0) “” [10]=> string(0) “” [11]=> string(0) “” [12]=> string(0) “” [13]=> string(0) “” [14]=> string(0) “” [15]=> string(0) “” [16]=> string(0) “” [17]=> string(0) “” [18]=> string(0) “” [19]=> string(0) “” [20]=> string(0) “” [21]=> string(0) “” [22]=> string(0) “” [23]=> string(0) “” [24]=> string(0) “” [25]=> string(0) “” [26]=> string(0) “” [27]=> string(0) “” [28]=> string(0) “” [29]=> string(0) “” [30]=> string(0) “” [31]=> string(0) “” [32]=> string(0) “” [33]=> string(0) “” [34]=> string(0) “” [35]=> string(0) “” [36]=> string(0) “” [37]=> string(0) “” [38]=> string(0) “” [39]=> string(0) “” } [“Username”]=> array(40) { [0]=> string(3) “FNB” [1]=> string(5) “simon” [2]=> string(26) “footballnewsblog@aol.co.uk” [3]=> string(26) “footballnewsblog@aol.co.uk” [4]=> string(23) “http://kbsoccernet.com/” [5]=> string(0) “” [6]=> string(0) “” [7]=> string(0) “” [8]=> string(0) “” [9]=> string(0) “” [10]=> string(0) “” [11]=> string(0) “” [12]=> string(0) “” [13]=> string(0) “” [14]=> string(0) “” [15]=> string(0) “” [16]=> string(0) “” [17]=> string(0) “” [18]=> string(0) “” [19]=> string(0) “” [20]=> string(0) “” [21]=> string(0) “” [22]=> string(0) “” [23]=> string(0) “” [24]=> string(0) “” [25]=> string(0) “” [26]=> string(0) “” [27]=> string(0) “” [28]=> string(0) “” [29]=> string(0) “” [30]=> string(0) “” [31]=> string(0) “” [32]=> string(0) “” [33]=> string(0) “” [34]=> string(0) “” [35]=> string(0) “” [36]=> string(0) “” [37]=> string(0) “” [38]=> string(0) “” [39]=> string(0) “” } [“Password”]=> array(40) { [0]=> string(10) “fnbwritter” [1]=> string(6) “sim123” [2]=> string(10) “rangersno1” [3]=> string(10) “rangersno1” [4]=> string(0) “” [5]=> string(0) “” [6]=> string(0) “” [7]=> string(0) “” [8]=> string(0) “” [9]=> string(0) “” [10]=> string(0) “” [11]=> string(0) “” [12]=> string(0) “” [13]=> string(0) “” [14]=> string(0) “” [15]=> string(0) “” [16]=> string(0) “” [17]=> string(0) “” [18]=> string(0) “” [19]=> string(0) “” [20]=> string(0) “” [21]=> string(0) “” [22]=> string(0) “” [23]=> string(0) “” [24]=> string(0) “” [25]=> string(0) “” [26]=> string(0) “” [27]=> string(0) “” [28]=> string(0) “” [29]=> string(0) “” [30]=> string(0) “” [31]=> string(0) “” [32]=> string(0) “” [33]=> string(0) “” [34]=> string(0) “” [35]=> string(0) “” [36]=> string(0) “” [37]=> string(0) “” [38]=> string(0) “” [39]=> string(0) “” } [“Submit”]=> string(6) “Submit” }
key: 0
sql1: UPDATE Article_Sites SET Site=‘www.1000goals.com’, Team=‘Generaltest’, Username=‘admin’, Password=‘fnbwritter’ WHERE Id=‘4’
key: 1
sql1: UPDATE Article_Sites SET Site=‘www.trulyreds.com’, Team=‘Man Utd’, Username=‘admin’, Password=‘sim123’ WHERE Id=‘0’
key: 2
sql1: UPDATE Article_Sites SET Site=‘www.manuniteddevils.blogspot.com’, Team=‘Man Utd’, Username=‘admin’, Password=‘rangersno1’ WHERE Id=‘’
key: 3
sql1: UPDATE Article_Sites SET Site=‘www.manchesterunitedsblog.blogspot.com’, Team=‘Man UTD’, Username=‘admin’, Password=‘rangersno1’ WHERE Id=‘’
key: 4
sql1: UPDATE Article_Sites SET Site=‘www.kbsoccernet.com’, Team=‘General’, Username=‘http://kbsoccernet.com/’, Password=‘’ WHERE Id=‘’
key: 5
sql1: UPDATE Article_Sites SET Site=‘’, Team=‘’, Username=‘’, Password=‘’ WHERE Id=‘’
key: 6
sql1: UPDATE Article_Sites SET Site=‘’, Team=‘’, Username=‘’, Password=‘’ WHERE Id=‘’
key: 7
sql1: UPDATE Article_Sites SET Site=‘’, Team=‘’, Username=‘’, Password=‘’ WHERE Id=‘’
key: 8
sql1: UPDATE Article_Sites SET Site=‘’, Team=‘’, Username=‘’, Password=‘’ WHERE Id=‘’
key: 9
sql1: UPDATE Article_Sites SET Site=‘’, Team=‘’, Username=‘’, Password=‘’ WHERE Id=‘’
key: 10
sql1: UPDATE Article_Sites SET Site=‘’, Team=‘’, Username=‘’, Password=‘’ WHERE Id=‘’
key: 11
sql1: UPDATE Article_Sites SET Site=‘’, Team=‘’, Username=‘’, Password=‘’ WHERE Id=‘’
key: 12
sql1: UPDATE Article_Sites SET Site=‘’, Team=‘’, Username=‘’, Password=‘’ WHERE Id=‘’
key: 13
sql1: UPDATE Article_Sites SET Site=‘’, Team=‘’, Username=‘’, Password=‘’ WHERE Id=‘’
key: 14
sql1: UPDATE Article_Sites SET Site=‘’, Team=‘’, Username=‘’, Password=‘’ WHERE Id=‘’
key: 15
sql1: UPDATE Article_Sites SET Site=‘’, Team=‘’, Username=‘’, Password=‘’ WHERE Id=‘’
key: 16
sql1: UPDATE Article_Sites SET Site=‘’, Team=‘’, Username=‘’, Password=‘’ WHERE Id=‘’
key: 17
sql1: UPDATE Article_Sites SET Site=‘’, Team=‘’, Username=‘’, Password=‘’ WHERE Id=‘’
key: 18
sql1: UPDATE Article_Sites SET Site=‘’, Team=‘’, Username=‘’, Password=‘’ WHERE Id=‘’
key: 19
sql1: UPDATE Article_Sites SET Site=‘’, Team=‘’, Username=‘’, Password=‘’ WHERE Id=‘’
key: 20
sql1: UPDATE Article_Sites SET Site=‘’, Team=‘’, Username=‘’, Password=‘’ WHERE Id=‘’
key: 21
sql1: UPDATE Article_Sites SET Site=‘’, Team=‘’, Username=‘’, Password=‘’ WHERE Id=‘’
key: 22
sql1: UPDATE Article_Sites SET Site=‘’, Team=‘’, Username=‘’, Password=‘’ WHERE Id=‘’
key: 23
sql1: UPDATE Article_Sites SET Site=‘’, Team=‘’, Username=‘’, Password=‘’ WHERE Id=‘’
key: 24
sql1: UPDATE Article_Sites SET Site=‘’, Team=‘’, Username=‘’, Password=‘’ WHERE Id=‘’
key: 25
sql1: UPDATE Article_Sites SET Site=‘’, Team=‘’, Username=‘’, Password=‘’ WHERE Id=‘’
key: 26
sql1: UPDATE Article_Sites SET Site=‘’, Team=‘’, Username=‘’, Password=‘’ WHERE Id=‘’
key: 27
sql1: UPDATE Article_Sites SET Site=‘’, Team=‘’, Username=‘’, Password=‘’ WHERE Id=‘’
key: 28
sql1: UPDATE Article_Sites SET Site=‘’, Team=‘’, Username=‘’, Password=‘’ WHERE Id=‘’
key: 29
sql1: UPDATE Article_Sites SET Site=‘’, Team=‘’, Username=‘’, Password=‘’ WHERE Id=‘’
key: 30
sql1: UPDATE Article_Sites SET Site=‘’, Team=‘’, Username=‘’, Password=‘’ WHERE Id=‘’
key: 31
sql1: UPDATE Article_Sites SET Site=‘’, Team=‘’, Username=‘’, Password=‘’ WHERE Id=‘’
key: 32
sql1: UPDATE Article_Sites SET Site=‘’, Team=‘’, Username=‘’, Password=‘’ WHERE Id=‘’
key: 33
sql1: UPDATE Article_Sites SET Site=‘’, Team=‘’, Username=‘’, Password=‘’ WHERE Id=‘’
key: 34
sql1: UPDATE Article_Sites SET Site=‘’, Team=‘’, Username=‘’, Password=‘’ WHERE Id=‘’
key: 35
sql1: UPDATE Article_Sites SET Site=‘’, Team=‘’, Username=‘’, Password=‘’ WHERE Id=‘’
key: 36
sql1: UPDATE Article_Sites SET Site=‘’, Team=‘’, Username=‘’, Password=‘’ WHERE Id=‘’
key: 37
sql1: UPDATE Article_Sites SET Site=‘’, Team=‘’, Username=‘’, Password=‘’ WHERE Id=‘’
key: 38
sql1: UPDATE Article_Sites SET Site=‘’, Team=‘’, Username=‘’, Password=‘’ WHERE Id=‘’
key: 39
sql1: UPDATE Article_Sites SET Site=‘’, Team=‘’, Username=‘’, Password=‘’ WHERE Id=‘’

17

Yes, it goes in a loop.
Let’s debug some more?


// Check if button name "Submit" is active, do this
if ($_REQUEST['Submit']) {
  // another debug line
  echo "post array: "; var_dump($_POST); echo "<br />";

  foreach ($_POST['Site'] as $key => $value) {
    $sql1 = "UPDATE Article_Sites SET Site='{$_POST['Site'][$key]}', Team='{$_POST['Team'][$key]}', Username='{$_POST['Username'][$key]}', Password='{$_POST['Password'][$key]}' WHERE Id='{$_POST['Id'][$key]}'";
 
    // add this line just to debug - you can delete it when everything works ok
    echo "key: $key <br />";
    echo "sql1: $sql1 <br />";
    // Change to code starts here
    if (!$result=mysql_query($sql1)) {
      echo "<p><b>Query Failed:</b><i> <b>".mysql_errno()."</b> ".mysql_error()."</i></p><p><b>SQL:</b><i> ".$sql1."</i><p/><br/>";
    }
    // and ends here  }
}
18

Sorry I might be being really thick here but I’m now getting loads of erros like the following:

Warning: mysql_query() [function.mysql-query]: Access denied for user ‘nobody’@‘localhost’ (using password: NO) in

what have I done wrong?

19

Ahh, sorry, put the last line (with mysql_close) back at the end of the script :smiley:

20

Put this part at the top of the script:


<?php
// Check if button name "Submit" is active, do this

if($_POST['Submit']){
for($i=0;$i<$count;$i++){

$sql1 = "UPDATE Article_Sites SET Site='{$_POST['Site'][$i]}', Team='{$_POST['Team'][$i]}', Login='{$_POST['Login'][$i]}' WHERE Id='{$_POST['Id'][$i]}'"; 


// Change to code starts here

          if (!$result=mysql_query($sql1)) {
              echo("<p><b>Query Failed:</b><i> <b>".mysql_errno()."</b> ".mysql_error()."</i></p><p><b>SQL:</b><i> ".$sql1."</i><p/><br/>");
          } else {                           
              return $result;
          }

// and ends here
}
}
mysql_close();
?>

The script doesn’t continue after the submit button when the user submits the form. It gets executed entirely from the beginning. If you put the form validation and the update of the database after getting the data from the database and displaying the form, you won’t see the latest modifications.