[ahlai]

Hi,
I've been trying to figure out how to update multi datas into the database.

Here's is my sample code:

PHP Code:

$sql = "SELECT * FROM tbl_name";

$result = mysql_query($sql);



while($row = mysql_fetch_array($result))

{



  $data_name = $row['data_name'];

  $config_value = $row['data_value'];        

  $default[$data_name] = $data_value;                

  $new[$data_name] = $default[$data_name];

        

  if(isset($_POST['submit']))

 {

   $sql = "UPDATE tbl_name

             SET data_value = $new[$data_name] 

             WHERE data_name = '$data_name'";            

    $result = mysql_query($sql) or die('Could not UPDATE' . mysql_error());

 }

} 

I can display all the datas. But I can only update one.
Can someone help me out please?
I'm not that good yet.

thanks!

[duesi]

Hi ahlai,

can you be more specific?
If you use this statement:

Code:

             UPDATE tbl_name
             SET data_value = $new[$data_name] 
             WHERE data_name = '$data_name'

This means this in plain english:
* I would like to change all rows, whose column called data_name have a value of '$data_name' (of course expanded to the current value of this variable), such as the column data_value gets the new value $new[$data_name].

It does not matter how many rows match your WHERE condition - all get updated.
Also consider what happens if you have no WHERE - Statement: All rows of your table get changed.

As you see, an Update is nearly always updating more than one row.
You can also change mutliple columns:

Code:

             UPDATE tbl_name
             SET data_value = $new[$data_name], other_column = '$other_value' 
             WHERE data_name = '$data_name'

Does this help?

Happy Querying!

[ahlai]

Here's my DB:

PHP Code:

INSERT INTO tbl_name(data_name, data_value) VALUES('datOne','This is data one.');

INSERT INTO tbl_name(data_name, data_value) VALUES('datTwo','This is data two.');

INSERT INTO tbl_name(data_name, data_value) VALUES('datThree','This is data three.'); 

This is my form:

PHP Code:

<form method="post" action="" name="table">

        <input name="datOne" type="text" value="{DAT_ONE}" size="35" maxlength="32" />

         <input name="datTwo" type="text" value="{DAT_TWO}" size="35" maxlength="32" />

           <input name="datThree" type="text" value="{DAT_THREE}" size="35" maxlength="32" />

         

              <input name="submit" class="button" type="submit" value="Submit" />



    </form> 

Once I type in all my datas and submit to update, only the first row of data get updated.

Then it gives this error:
mysql_fetch_array(): supplied argument is not a valid MySQL result resource

It point to this location:
while($row = mysql_fetch_array($result)

By the way, I am using a template.

I hope it is clear to understand.
thanks

[UnrealEd]

the problem is this:
you use the variables $sql ("SELECT FROM...") and $result ("Resource ID #x") to get the data out of mysql in first place. during the while loop you change the values of those variables to a query with another value,being "UPDATE table..." for $sql and "Resource ID #y" for $result. here's an example of what you do:

PHP Code:

$numeric = 0;

while(is_numeric($numeric)){

  $numeric = "hello";

} 

you just have to change the name of the variables in the while-loop and it will work.

Greetz,
UnrealEd

[ahlai]

THANKS!!!

I got a little confuse a bit with your example. But I got it to work.

All I had to do is change in the UPDATE variable from $sql, $result ---> $sql1, $result1

Great Help. Thanks.
ahlai