PDA

View Full Version : Updating multiple rows in one go. Is it possible?


smitho
03-03-2008, 03:18 AM
I have a table that holds 30 number. I've created a form that allows a user to enter a list of 30 new numbers. Is it possible to update the 30 numbers with the 30 new ones?

For example if I have these text fields....
$number1 = $_POST['number1'];
$number1 = $_POST['number1'];And would like to update table in one go.
$query = sprintf("UPDATE numbers SET num='$number1', date='$expire' WHERE id=1"
$query = sprintf("UPDATE numbers SET num='$number2', date='$expire' WHERE id=2"Is this possible?

Cheers.

smitho
03-03-2008, 03:32 AM
Is this ok?



if($submit){
$query = sprintf("UPDATE numbers SET num='$number1', date='$expire' WHERE id=1";
$result = @mysql_query($query);
$query = sprintf("UPDATE numbers SET num='$number2', date='$expire' WHERE id=2";
$result = @mysql_query($query);
}
Forgot the "$result = @mysql_query($query);"

davidj
03-03-2008, 06:11 AM
you could do it that way if it meets your requirements but its not going to look very nice (or be elegant)

it would be better to utilise the power of a loop

when naming your fields in the HTML you can use the following convention


<input name="numbers['field1']" id="numbers['field1']" type="text" class="form-field-5" />
<input name="numbers['field2']" id="numbers['field2']" type="text" class="form-field-5" />


what this does is create an array called numbers with keys field1, field2 etc...

to catch the form contents you just need 'one' $_POST

$numbers = $_POST[numbers];

OK so now you have an array with all the form values in it

to find out whats in it use the following debugging method

after the $_POST do the following


echo "<pre>";
print_r($numbers);
echo "</pre>";


this will print the contents of the array to the page in a nice formated layout

(use this from now on to debug arrays)

OK so now you have an array

now we can use a foreach loop

a foreach loop uses the array and loops through the number of set keys in the array



/// where $numbers is the array and $values are the individual values from the array

$count = 0; ///<<< initialize count variable. (going to be used in your WHERE clause)

foreach($numbers as $values){

$count++ /// <<< increment count while in the loop (to be used in the WHERE clause)

$query = sprintf("UPDATE numbers SET num='$values', date='$expire' WHERE id='$count'";
$result = @mysql_query($query);

}


you could use the keys in your WHERE clause if they need to be specific to a relevant record when updating

smitho
03-03-2008, 09:50 AM
That works really well davidj thank you.

I was trying to work out how to use a loop. Would it also be possible to use a loop to query the database and echo the result in the text fields?

This would mean you would not need to redo all the numbers. Currenlty if you type only 20 out of the 30 you end up with 10 blank entries in the database.

<input name="numbers['field1']" id="numbers['field1']" type="text" class="form-field-5" value="$currentnumber" />

davidj
03-03-2008, 09:55 AM
you do this already when you loop through your recordset

by echoing... $row['db_column']; in the field value will return the value if its in a do while loop

smitho
03-03-2008, 10:44 AM
I've tried


<?php do{?>
<tr>
<td><input name="numbers['field1']" id="numbers['field1']" type="text" class="form-field-5" value="<?php echo $rownumber['num'];?>" /></td>
</tr>
<?php } while ($rownumber = mysql_fetch_array($result));?>
but when I try and enter a new number it doesn't update the database.

davidj
03-03-2008, 11:03 AM
if your looping the form with values from the db your going to have to think of a dynamic method of defining the array keys.

if the loop runs 3 times your going to end up with...

<input name="numbers['field1']" ....
<input name="numbers['field1']" ....
<input name="numbers['field1']" ....

Which is the same name[key].

you could omit the associative array by leaving the key empty. This will then be forced to use numeric keys but each will be differently named. e.g ...

<input name="numbers[]" .... ///<< would be accessed like numbers[0]
<input name="numbers[]" .... ///<< would be accessed like numbers[1]
<input name="numbers[]" .... ///<< would be accessed like numbers[2]

if your form is a static one but each row of fields go into a separate row in the db you can query the db as normal which uses the mysql_query function

$result = mysql_query($sql);

you can use the following...


/////////////
*** TABLE ***

ID Value
1 test1
2 test2
3 test3
4 test4

/////////////////



mysql_field_seek

this moves the pointer down to the next row. Remember that the pointer starts with 0 as the first record so the example below would = test3


echo $row['value']; /// <<< = 'test1'
mysql_field_seek($result,2);
echo $row['value']; /// <<< = 'test3'



you could use the following above to populate a static (none looping) form which is fed from a multi row table

smitho
03-03-2008, 11:08 PM
I've created a table in the dwc database called numbers with the following data:

mysql> select * from numbers;
+----+---------+---------------------+
| id | num | date |
+----+---------+---------------------+
| 1 | 1023333 | 2008-03-04 10:54:08 |
| 2 | 1023343 | 2008-03-04 10:54:24 |
| 3 | 1027453 | 2008-03-04 10:54:40 |
| 4 | 1026795 | 2008-03-04 10:55:03 |
+----+---------+---------------------+

Using the example from the PHP manual:


mysql_connect("localhost", "USER", "PASSWORD") or die(mysql_error());
mysql_select_db("dwc") or die(mysql_error());
$sql="SELECT * from numbers";
$result=mysql_query($sql);
$row = mysql_fetch_array($result);
echo $row['id'] . ' ' . $row['num']; // Output is (1 Hassan)
mysql_field_seek($result,2);
echo $row['id'] . ' ' . $row['num']; // Output is (3 Rose)
I get the following following result:

1 10233331 1023333

If I change ($result,2) to ($result,3) I get:

1 1023333
Warning: mysql_field_seek() [function.mysql-field-seek (http://localhost/%7Ekordasj/NewsSite/function.mysql-field-seek)]: Field 3 is invalid for MySQL result index 3 in /../../Test.php on line 8
1 1023333

Am I missing something?

davidj
03-04-2008, 06:07 AM
try this

mysql_data_seek

instead of

mysql_field_seek

davidj
03-04-2008, 06:52 AM
this i just made up that function

mysql_field_seek

smitho
03-04-2008, 08:37 AM
Sorry davidj,

a bit confused.

Did you try this:

mysql_connect("localhost", "USER", "PASSWORD") or die(mysql_error());
mysql_select_db("dwc") or die(mysql_error());
$sql="SELECT * from numbers";
$result=mysql_query($sql);
$row = mysql_fetch_array($result);
echo $row['id'] . ' ' . $row['num']; // Output is (1 Hassan)
mysql_field_seek($result,2);
echo $row['id'] . ' ' . $row['num']; // Output is (3 Rose)

Just tried it and mysql_data_seek but still getting the same result which is 1 1023333 1 1023333.

Did mysql_field_seek($result,2); work for you?

davidj
03-04-2008, 10:07 AM
i didnt test it

i just wrote it

so this doesnt work


echo $row['id'] . ' ' . $row['num']; // Output is (1 Hassan)
mysql_data_seek($result,2);
echo $row['id'] . ' ' . $row['num']; // Output is (3 Rose)

smitho
03-04-2008, 10:31 AM
Could it be somthing to do with my php setup that's not making this work?

Another approch I'm trying is to explode the array to string using:


mysql_connect("localhost", "USER", "PASSWORD") or die(mysql_error());
mysql_select_db("dwc") or die(mysql_error());
$sql="SELECT num from numbers";
$result=mysql_query($sql);

$nums = array();

while($row = mysql_fetch_assoc($result))
{
$nums[] = $row;
echo $row['num'];

}
echo "<pre>"; print_r($nums); echo "</pre>";
this gives me


1023333 1027453 1023343 1026795 Array
(
[0] => Array
(
[num] => 1023333
)

[1] => Array
(
[num] => 1027453
)

[2] => Array
(
[num] => 1023343
)

[3] => Array
(
[num] => 1026795
)

)

but when I try


$pieces = explode(" ", $nums);
echo $pieces[0];
It's not working.

davidj
03-04-2008, 10:35 AM
this wont work


$pieces = explode(" ", $nums);
echo $pieces[0];


because your exploding an array onto an array

$nums is an array already and your trying to make a new one

davidj
03-04-2008, 10:38 AM
you using a date time stamp so your array contains ...

1023333 1027453 1023343 1026795

This is how the db stores your field type

also you have just created a multi dimensional array


Array
(
[0] => Array
(
[num] => 1023333
)

[1] => Array
(
[num] => 1027453
)

[2] => Array
(
[num] => 1023343
)

[3] => Array
(
[num] => 1026795
)

)


to target this array you need to do ...


$num[0]['num'];


you have created the multi dimensional array accidentally therefore you need to take this back to the drawing board

smitho
03-04-2008, 10:47 AM
OK this is driving me mad I don't see why it will not work and you have confirmed that the code is correct.


mysql_select_db("dwc") or die(mysql_error());
$sql= sprintf("SELECT * from numbers");
$result=mysql_query($sql);
$row = mysql_fetch_array($result);
echo $row['id'] . ' ' . $row['num']; // Output should be 1 1023333
mysql_data_seek($result,1);
echo $row['id'] . ' ' . $row['num']; // Output should be 2 1027453
mysql_data_seek($result,2);
echo $row['id'] . ' ' . $row['num']; // Output should be 3 1023343
I get => 1 1023333 1 1023333 1 1023333

You're getting 1 1023333 2 10274533 1023343

Correct?

I jsut tried:

echo $nums[0]['num'];
echo $nums[1]['num'];
echo $nums[2]['num'];
echo $nums[3]['num'];

and it worked.

Not the way I want to get the numbers but as the mysql_data_seek approach is not working I don't think I've got a choice.

davidj
03-04-2008, 10:52 AM
i need to set this up myself to truely test it

i cant do this right now so i sugest you research this best you can

im snowed under at the moment

smitho
03-04-2008, 10:57 AM
Thanks davidj,

I'll use the multi array for the moment untill I nut out the mysql_data_seek problem. If you need me to upload the table let me know thanks for you help.

Cheers.