[Resolved] Updating multiple rows in db at once

webhamster

Here's the situation. After reading through just about every post on updating multiple rows in one submit I haven't found anything that offers a workaround to this problem.

A site has a 3 column layout. Previously in the script the user has decided which content modules he wants to be active. Now, he's being asked in which columns he would like the active content displayed. Content type 1 is "narrow" so it can only go in the side columns wheresas content type 2 is wide (center column).

So, in the submit I need to pass the row value as well as the user's column preference for each active content module and have an UPDATE statement that updates the column settings for all active content based on what the user selected.

Perhaps the code would make this clearer:

	print "<FORM NAME=\"column_org\" METHOD=\"POST\" ACTION=\"prefs.php\">\n";
	print "<INPUT TYPE=\"hidden\" NAME=\"sessid\" VALUE=\"$sessid\">\n";
	print "<INPUT TYPE=\"hidden\" NAME=\"userID\" VALUE=\"$userID\">\n";
	print "<INPUT TYPE=\"hidden\" NAME=\"x\" VALUE=\"update_column_org\">\n";

$query = "SELECT ucp.active,ucp.id,cl.id,cl.name,cl.type FROM content_list AS cl, user_content_prefs AS ucp WHERE ucp.active=cl.id AND ucp.user='$userID'";
$q = mysql_query("$query") or die (mysql_error());
	while(list ($active,$ucpid,$cid,$cname,$ctype) = mysql_fetch_array($q)) {
	print "$cname: <SELECT NAME=\"col[$ucpid]\">\n";
		if ($ctype == "1") {
			print "<OPTION VALUE=\"1\">Column 1</OPTION>\n";
			print "<OPTION VALUE=\"3\">Column 3</OPTION>\n";
			}
		elseif ($ctype == "2") {
			print "<OPTION VALUE=\"2\">Column 2</OPTION>\n";
			}
	print "</SELECT><br>\n";
	}
mysql_free_result($q);
print "<br><INPUT TYPE=\"submit\" VALUE=\"Next >>\">\n</FORM>";

The row value $ucpid gets overwritten by the OPTION value during the submit...that's my dilemma. I need both values. Does anyone have a solution I'm just not seeing?

Thanks in advance.

kevinsequeira

would love to help you out, but i dont understand the issue 😕 😕

reg
kevin

webhamster

Thanks for the offer, Kevin. Let me try to rephrase the problem.

I have a table called user_content_prefs which stores a number of different preferences for the user. Among these are active,col, and order. There are a number of "modules" (different pieces of content) the user can select (this occurs in another part of the script). When they do this the program creates a new row in user_content prefs that uses the id from the content_list table (master list of all content modules) and places it in the "active" column. Thus when the user visits the site it only displays those "content modules" they've selected to view.

The site has a multi-column layout so here I want to let the user select which column on the site each module will be displayed (there can be more than 1 "module" to a column.

In the example above I'm querying to display all of the active content modules for user X and then trying to create a form that will allow them to decide which column the content will show up in. They can set their preference for each module at once and submit it.

The problem happens when I submit the form. I need to pass the row number (row from user_content_prefs shows as $ucpid) and the value from the select list (1,2, or 3 in this case) and update accordingly for each affected row.

Basically, I need some way to say:

UPDATE user_content_prefs SET col='value_from_form' WHERE id='$ucpid'

and do that for each $ucpid value.

I hope that clarifies it a bit. If not, please tell me where I'm stumping you...I'm here to help you help me. 🙂

kevinsequeira

a bit clearer but not sure if this is what ure asking but it might help u out.
why dont u pass the more than one parameter in the drop down name like this

<SELECT NAME=ABC>
<OPTION VALUE=UCPID|1>Column 1</OPTION>

then when u process the form, for the select names value just extract the data seperated by the |, so u will get the UCPID and the dropdown value

looks like ure building some sort of CMS, happy coding
reg
kevin

leatherback

Hi,

IF I understand correctly, all you are stuck with at the moment is that you are not submitting the value of $ucpid.

So.,.. add a hiden variable to the form:
<input type=\"hidden\" name="ucpid" value="$ucpid">

Or am I completely amiss here?

webhamster

leatherback,

What I need to do is basically submit an array of $ucpid values with a matching array of values from the form and do that for each $ucpid that is called out of the database to build the form. Your method would only allow me to update one setting at a time and would present the user with 50 submit buttons because there would be no way to associate the hidden value with the value in the SELECT unless it was a standalone form.

Consider it expressed this way:

ucpid[24] => col[1]
ucpid[47] => col[1]
ucpid[367] => col[2]
ucpid[9652] => col[3]

etc.

I need to make sure that the associated $col[] value remains connected to it's $ucpid[] value so that I can submit the updates to the database in a foreach loop or something.

I think kevinsequeira may have found the solution I need, I'm going to work on that in a few minutes and will keep you updated. Thanks for all the help.

webhamster

Kevin, thank you! I see you're from Montreal...if you're ever in Ottawa let me know, I owe you a beer!

The final solution (for those following along) is this:

1) Generate the SELECT's from the database like kevin suggested:

	print "<FORM NAME=\"column_org\" METHOD=\"POST\" ACTION=\"prefs.php\">\n";
	print "<INPUT TYPE=\"hidden\" NAME=\"x\" VALUE=\"update_column_org\">\n";

$query = "SELECT ucp.active,ucp.id,cl.id,cl.name,cl.type FROM content_list AS cl, user_content_prefs AS ucp WHERE ucp.active=cl.id AND ucp.user='$userID'";
$q = mysql_query("$query") or die (mysql_error());
	while(list ($active,$ucpid,$cid,$cname,$ctype) = mysql_fetch_array($q)) {
	print "$cname: <SELECT NAME=\"abc[]\">\n";
		if ($ctype == "1") {
			print "<OPTION VALUE=\"$ucpid|1\">Column 1</OPTION>\n";
			print "<OPTION VALUE=\"$ucpid|3\">Column 3</OPTION>\n";
			}
		elseif ($ctype == "2") {
			print "<OPTION VALUE=\"$ucpid|2\">Column 2</OPTION>\n";
			}
	print "</SELECT><br>\n";
	}
mysql_free_result($q);
print "<br><INPUT TYPE=\"submit\" VALUE=\"Next >>\">\n</FORM>";

Then, split the data in a while loop once it's been submitted and send it to the db one row at a time:

	while (list($key, $value) = each ($abc)) {
	$data = split("[\\|]",$value);
	$query = "UPDATE user_content_prefs SET col='$data[1]' WHERE id='$data[0]'";
	$q = mysql_query("$query") or die (mysql_error());	}

This does exactly what I needed it to do! Again, thanks, kevin!

kevinsequeira

no problem, happy to help. could u mark this resolved, there is a link below

reg
kevin

tomandhannah

I know this is resolved, but I just wanted to post that Kevin's idea was so brilliant and I never would have thought it up myself! It helped me to do something very similar... Thanks guys.