Bulk editing mysql database with php

Adam

I want to allow a user to edit a bunch of details in one page. What I mean by that is I have a table of customers (~1000) and I want to create a script that will allow me to edit many at once. So I'll have the alphabet a to z as links and when you click 'a' you get all the customers beginning with 'a'. Then I will have say five input boxes across the screen, customer name, address, phone, fax, email or something, which are duplicated down the page for about fifty rows... following me?

So lets say the five boxes in row one need to be popullated with the details of the first customer beginning with 'a', the second row of five boxes popullated with the details of the second customer beginning with 'a'... etc. etc. down the page for about fifty rows...

Then I need to change all those values and click save once and have an UPDATE query that updates ALL the customers at once...

Its a toughie, any one got some help or suggestions, I'm fairly baffled by this one!

daymobrew

It sounds like something that phpMyAdmin might do. If it does then you can search its code to see how.

You could call UPDATE multiple times without requiring multiple clicks in the browser, once for each row.

is_that_so

Hmm. Why would you want to update multiple records with data input on the same page? What kind of application is it ? Potential of "gigo" and confusion there. One page per record is common sense imho. Maybe you can put in some code to show how waht you want to do might work. Updating such a large set of records I would look at mySQL 5 and use stored procedures in the DB. http://dev.mysql.com/doc/refman/5.0/en/stored-procedures.html

louie

I had to do similar thing when i changed the database type, but it was only a one off thing. I don't see the point of everyday use.

Nietzschean

how i'd do it is have a hidden item on the page giving the number of items being displayed.(i.e. customers)

each row's input boxes should be named like name="line_x_fieldname" , including one hidden input box with each set like name="line_x_id" value="$field_value"

then in your php update page just use a for loop
for(int $counter=0;$counter<$_POST["num_rows"];$counter++) {
$id = $_POST["line_".$counter."_id"];
$SQL = "Update cust_data set bla = ".$_POST["line_".$counter."_bla"].".... where ID = $id";
$res = mysql_query($SQL);

}


Something like that? or am i missing what you asked for?

Adam

Nietzschean wrote:

how i'd do it is have a hidden item on the page giving the number of items being displayed.(i.e. customers)

each row's input boxes should be named like name="line_x_fieldname" , including one hidden input box with each set like name="line_x_id" value="$field_value"

then in your php update page just use a for loop
for(int $counter=0;$counter<$_POST["num_rows"];$counter++) {
$id = $_POST["line_".$counter."_id"];
$SQL = "Update cust_data set bla = ".$_POST["line_".$counter."_bla"].".... where ID = $id";
$res = mysql_query($SQL);

}


Something like that? or am i missing what you asked for?

That sounds bang on, could you elaborate on the hidden fields section a bit please? I've only been at php/mysql for 3 months and its all hands on learning so there's alot of things I use but don't fully understand! I'm trying to catch up with theory as I work now!

Zaph0d

is_that_so wrote:

Hmm. Why would you want to update multiple records with data input on the same page? What kind of application is it ? Potential of "gigo" and confusion there. One page per record is common sense imho.

It's fairly common to require multiple updates in applications like data entry. Last client I had for this wanted to submit data only every 50 rows: otherwise they would have to click submit 500 times in a typical morning.

To avoid dynamic SQL and multiple round trips to the database, I would write this as a stored procedure with a large varchar parameter and encode the updates in the varchar string.

Nietzschean

fade_to_grey wrote:

That sounds bang on, could you elaborate on the hidden fields section a bit please? I've only been at php/mysql for 3 months and its all hands on learning so there's alot of things I use but don't fully understand! I'm trying to catch up with theory as I work now!

ok well an example might be...

[form action="myphp_page.php" method="post"]

[input type=hidden name="num_rows" value="<?=$num_rows?>"]

<?
for($counter=0;$counter<$num_rows;$counter++) {
/*grab the current mysql record into an array...*/
echo "<input type=hidden name=line_$counter_id value=$result_array[id]>\n";
echo "<input type=input name=line_$counter_customer value=$result_array[customer]>\n";
}
?>
</form>

thats the sort of for loops you could use to generate the page

is that what you were wondering about?