modifying multiple rows

chrisjlee

How do i go about editing multiple rows of mysql entries?

say i have a form that displays a top ten list of favorite links.

using php's foreach() it displays and creates 10 different textfields with different names.

names like "link1", "link2", "link3", etc.

Then i use a submit form to update each one.

The mySQL table is structured like this

autoID (int) , link (varchar(30)) , title (varchar (30))

help please?

bretticus

I usually, loop through the $_POST array such as:

foreach ($_POST as $key => $value) {
	if (substr($key,4) == "link") {
		//extract the number from the $key var then you can write SQL statements using the extracted number as the database key, for example. 
               //$value is of course, is the value of the POST element to be used for insert/update.
	}
}

This depends on the number in "link1" being the unique id from the database to work properly. Meaning, your list is created via looping through a recordset etc.

Roger_Ramjet

Bretticus, I hope you don't mean 'loop through and run 10 queries' :eek:

The easy way around this is to use the "INSERT .... VALUES (.....) ON DUPLICATE KEY UPDATE" syntax (requires version 4.1.1 or higher)

You would loop through the post data to build the query, then run 1 query to update all 10 rows.


// assuming you have used the autoID value as the name for the link textbox

$values = array();
foreach ($_POST as $key=>$val) {
    $values[] = "(" . $key . ",'" . $val . "')";
}

$sql = "INSERT INTO link_table(autoID, link) VALUES " . implode(',' , $values) . " ON DUPLICATE KEY UPDATE";

PS, remember to clean the input with a mysql_real_escape_string function to protect yourself from sql injection attacks. I've not included it here so you can see what is going on more easily.

chrisjlee

ahh that seems more practical.
true programmers are lazy

thanks that helps a lot more

bretticus

Bretticus, I hope you don't mean 'loop through and run 10 queries

I certainly do. Try running your one QUERY on another database 😃

I usually have a checkbox associated with the textaarea to check for or I'll compare hidden fields (kind of cludgy by comparison.) However, yes, your way is much more efficient when you get to use MySQL (which most PHP coders typically use.) Thanks for the enlightenment (I wrote too much ASP code in the past!)

chrisjlee, if you insist on naming textareas things like "link1" where "1" is the database ID...OR...if you have other input objects in your form that you need to handle, the ...

if (substr($key,4) == "link") {
}

substring function is useful to isolate a "link" and extract the database ID. From that point Roger Ramjets suggestion is stellar!

Roger_Ramjet

In another db engine without such usefull features, I'd insert all into a work table then update all through a join: 3 queries max (one to clear the work table). But then I typically work with 100s of updates on table containing 100Ks of records.