# Updating multiple rows in mysql



## James7 (Aug 18, 2002)

Hi all, I am trying to pull data from a database but it in a form so it can be edited and upon clicking submit the data is fed back to the database,

My Code is;


```
<?php include'./includes/connect.php'

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

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

[TABLE]

[TR]
[TD]
[TABLE]

[TR]
[TD][B]Id[/B][/TD]
[TD][B]Picture[/B][/TD]
[TD][B]Thumbnail[/B][/TD]
[TD][B]Caption[/B][/TD]
[/TR]
<?php
while($rows=mysql_fetch_array($result)){
?>
[TR]
[TD]<? $id[]=$rows['id']; ?><? echo $rows['id']; ?>[/TD]
[TD]">[/TD]
[TD]">[/TD]
[TD]">[/TD]
[/TR]
<?php
}
?>

[TR]
[TD][/TD]
[/TR]
[/TABLE]
[/TD]
[/TR]

[/TABLE]

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

if($Submit){
for($i=0;$i<$count;$i++){
$sql1="UPDATE pictures SET picture='$picture[$i]', thumbnail='$thumbnail[$i]', caption='$caption[$i] WHERE id='$id[$i]'";

$result1=mysql_query($sql1);

}
}

if($result1){
echo "Member pictures have been updated [URL=picturesview.php]click here to view changes[/URL]";
}
mysql_close();
?>
```
This seems to take the data and display it in the form, however the submit button does not seem to activate the SQL command, any ideas?

Thanks in advance
James


----------



## 00trav (Sep 6, 2007)

you are missing the action in the form tag. you need to call the page back.


----------



## James7 (Aug 18, 2002)

I assume you mean

```
<form name="form1" method="post" action="picturesedit.php">
```
(where picturesedit.php is the current page.

I have tried this and no change, I don't think this would be an issue as it is the php what should pick up the form values after clicking submit (i.e. I'm not posting the data anywhere)

edit: I am using the example at
http://www.phpeasystep.com/mysqlview.php?id=10
as a guide, so I assume that it should work


----------



## 00trav (Sep 6, 2007)

try adding "or die(mysql_error())"


```
$result1=mysql_query($sql1) or die(mysql_error());
```
Also, I know it sounds lame, but make sure your mysql user has the right to update.


----------



## creativemedia (Aug 5, 2008)

Hi,


I am having the same problem. No mysql error coming up, but it will still not change the table data. Thought it might be the column settings but I have tried the table creation in the example and still no joy.

Any advice would be great, thanks.


----------



## 00trav (Sep 6, 2007)

Well there is also a single quote after $caption in the sql statement, should have seen that before


```
if($Submit){
for($i=0;$i<$count;$i++){
$sql1="UPDATE pictures SET picture='$picture[$i]', thumbnail='$thumbnail[$i]', caption='$caption[$i] WHERE id='$id[$i]'";

$result1=mysql_query($sql1);

}
} 
// should be

if($Submit){
for($i=0;$i<$count;$i++){
$sql1="UPDATE pictures SET picture='$picture[$i]', thumbnail='$thumbnail[$i]', caption='$caption[$i]' WHERE id='$id[$i]'";

$result1=mysql_query($sql1);

}
}
```
let me know if that works now


----------



## awatson (Jan 29, 2008)

Have it echo out the query right before you run it, then copy/paste the query into the mysql client or phpmyadmin and see if the query runs, and what errors it gives you if not.


----------



## 00trav (Sep 6, 2007)

or you can put this code on your page where ther $result1 is:

```
$result1=mysql_query($sql1) or die("sql statement: ".$sql1." --error: ".mysql_error());
```


----------

