[SOLVED] Not updating multiple rows

[happypete]

Apache version 1.3.41 (Unix)

PHP version 5.2.6

MySQL version 5.0.81-community

Architecture x86_64

Operating system Linux

 

Been searching for ages for something that updates multiple rows on submit, and found this script, but it doesn't work, when you hit submit it doesn't update and refreshes the content that was originally called from the database. Tried it on a different server and not working there either.

 

Please help me find what is wrong with it.... ?

 

 

CREATE TABLE `test_mysql` (
`id` int(4) NOT NULL auto_increment,
`name` varchar(65) NOT NULL default '',
`lastname` varchar(65) NOT NULL default '',
`email` varchar(65) NOT NULL default '',
PRIMARY KEY (`id`)
) TYPE=MyISAM AUTO_INCREMENT=7 ;

-- 
-- Dumping data for table `test_mysql`
-- 

INSERT INTO `test_mysql` VALUES (1, 'Billly', 'Blueton', '[email protected]');
INSERT INTO `test_mysql` VALUES (2, 'Jame', 'Campbell', '[email protected]');
INSERT INTO `test_mysql` VALUES (3, 'Mark', 'Jackson', '[email protected]');
INSERT INTO `test_mysql` VALUES (4, 'Linda', 'Travor', '[email protected]');
INSERT INTO `test_mysql` VALUES (5, 'Joey', 'Ford', '[email protected]');
INSERT INTO `test_mysql` VALUES (6, 'Sidney', 'Gibson', '[email protected]');

 

<strong>Update multiple rows in mysql</strong><br>

<?php
$host="localhost"; // Host name
$username=""; // Mysql username
$password=""; // Mysql password
$db_name="test"; // Database name
$tbl_name="test_mysql"; // Table name

// Connect to server and select databse.
mysql_connect("$host", "$username", "$password")or die("cannot connect");
mysql_select_db("$db_name")or die("cannot select DB");

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

// Count table rows
$count=mysql_num_rows($result);
?>
<table width="500" border="0" cellspacing="1" cellpadding="0">
<form name="form1" method="post" action="">
<tr>
<td>
<table width="500" border="0" cellspacing="1" cellpadding="0">


<tr>
<td align="center"><strong>Id</strong></td>
<td align="center"><strong>Name</strong></td>
<td align="center"><strong>Lastname</strong></td>
<td align="center"><strong>Email</strong></td>
</tr>
<?php
while($rows=mysql_fetch_array($result)){
?>
<tr>
<td align="center"><? $id[]=$rows['id']; ?><? echo $rows['id']; ?></td>
<td align="center"><input name="name[]" type="text" id="name" value="<? echo $rows['name']; ?>"></td>
<td align="center"><input name="lastname[]" type="text" id="lastname" value="<? echo $rows['lastname']; ?>"></td>
<td align="center"><input name="email[]" type="text" id="email" value="<? echo $rows['email']; ?>"></td>
</tr>
<?php
}
?>
<tr>
<td colspan="4" align="center"><input type="submit" name="Submit" value="Submit"></td>
</tr>
</table>
</td>
</tr>
</form>
</table>
<?php
// Check if button name "Submit" is active, do this
if($Submit){
for($i=0;$i<$count;$i++){
$sql1="UPDATE $tbl_name SET name='$name[$i]', lastname='$lastname[$i]', email='$email[$i]' WHERE id='$id[$i]'";
$result1=mysql_query($sql1);
}
}

if($result1){
header("location:update_multiple.php");
}
mysql_close();
?>

 

 

[cags]

At what point in your code are you setting the variables $submit, $name, $lastname, $email, $id?

[happypete]

At what point in your code are you setting the variables $submit, $name, $lastname, $email, $id?

 

OK, that's probably why it is not working... could you help me out and show me how to set the variables...K

[cags]

Well assumably your wanting to use the values you submitted from the form. So...

 

<?php
$submit = $_POST['submit'];
$name = $_POST['name'];
$last name = $_POST['lastname'];
...
// etc
?>

 

I'd also personallly recommend moving the whole block contained in the following to the top...

 

if($submit) {
}

[happypete]

Well assumability your wanting to use the values you submitted from the form. So...

 

<?php
$submit = $_POST['submit'];
$name = $_POST['name'];
$last name = $_POST['lastname'];
...
// etc
?>

 

I'd also personallly recommend moving the whole block contained in the following to the top...

 

if($submit) {
}

 

thanks but I still don't think I've quite got it:

 

added:

 

$submit = $_POST['submit'];
$name = $_POST['name'];
$lastname = $_POST['lastname'];
$email = $_POST['email'];
$id = $_POST['id'];

and put the other bit at the top, tried both wasn't sure which bit exactly

 

if($Submit){
for($i=0;$i<$count;$i++){
$sql1="UPDATE $tbl_name SET name='$name[$i]', lastname='$lastname[$i]', email='$email[$i]' WHERE id='$id[$i]'";
$result1=mysql_query($sql1);
}
}

 

&

 

<?php

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

if($Submit){

for($i=0;$i<$count;$i++){

$sql1="UPDATE $tbl_name SET name='$name[$i]', lastname='$lastname[$i]', email='$email[$i]' WHERE id='$id[$i]'";

$result1=mysql_query($sql1);

}

}

 

if($result1){

header("location:update_multiple.php");

}

mysql_close();

?>

 

 

but it still didn't update the database...

 

[cags]

PHP variable names are case sensitive. You are setting $submit, but checking against the value of $Submit.

[happypete]

awesome thanks 

 

OK, it uploads the changed text to the database, but does not refresh the page, so it does not get displayed in the boxes until you refresh the page.

 

So if you change the text in the box again before hitting refresh it will overwrite the original changed text that is stored in the database but not shown on screen.

 

is there a way to get it to refresh when it has been submitted..?

[cags]

Yes, by moving the if($submit) {} block to the top of the page. Look at the page as PHP parses it in the order you had it originally. Load values from database, fill in form, update database.

 

Now imagine it with the submit block at the top.... Update database, load values from database, fill in form.

[happypete]

ok thanks, it was at the top of the page, but doesnt load it...thanks anyway at least the form is functioning, thanks