update an enum type field

[Dedix]

I have this code:

$query = ("UPDATE $dbtbl_card SET mem_only='1' WHERE card_id IN $qry_in");

It sets mem_only field (enum type, values: 0,1) to 1, but I want this to change the fields value, not set.
If mem_only is 0 then set it to 1 and if mem_only is 1 set it to 0.

Any idea how should I modify the code?

[Sxooter]

I tried to make it work in 3.x but couldn't, but XOR might work here:

update table set field = field XOR 1 where id=123;

this would appear to be a 4.0 and above thing. I wish MySQL would leave the old versions of their docs up, like Postgresql does, so you can lookup syntax for the older versions easily.

[Sxooter]

aha. In 3.x you can do this:

update table set field = (field AND (not 1)) OR ((not field) and 1);

[Sxooter]

Well, I tested it on enum fields and it doesn't seem to work quite right there. Change it to an int with a check constraint (haha, little MySQL humor, since MySQL still doesn't have check constraints...) of in(1,0) and it will work.

by the way, the postgresql way is:

update test set field = field # 1 where id =123

Note that there is no SQL spec for XOR, I guess you're expected to use the longer AND / NOT / OR construct...

[Dedix]

Thanks for your replies, I have tried what you suggested to me, but it wouldn't work.
Actually I forgot to mention that I also want to modify more than one fileds at the same time, not always one. :mad:

I use MySQL v4.0.13-nt (on Win XP) with PHP v4.3.2

Actually I'm building an e-card system where users can register so they can send more card than regular users that are not registered. I have a table that stores the card data with a field called "mem_only". If it's 1 the card could be sent only by registered members. At the admin area I have a form that shows the pics of the gallery I have choosen, and all pics has a checkbox. What I want is If I check the box the card's mem_only fiels would be set to 1 and vica versa and I like if that also would be possible to change multiple cards at the same time.

Here is some more code:

Form gallerybrowser.php (shows the form with checkboxes)

<form action="gallery.php" method="POST"><input type=hidden name=mode value="edit_memonly">
<table width="80%" border="0" cellspacing="2" cellpadding="8" align="center">
<TR>
<?php
$icounter ="0";
while ($row = $DB_site->fetch_array($result))
{
	$checked = $row[mem_only]?"checked":'';
	echo "<TD align=\"center\" valign=\"middle\"><A HREF=\"gallery.php?mode=post_edit&card_id=$row[card_id]\"><IMG SRC=\"$card_imageURL/$row[card_thm]\" BORDER=\"1\"><br>$MsgAdminEdit</A>&nbsp;-&nbsp;Mem only?&nbsp;<input type=\"checkbox\" name=\"selected_box[]\" value=\"$row[card_id]\" $checked class=\"form\" style=\"font-size: 9px\"><input type=\"hidden\" name=\"mem_only\" value=\"1\"></TR>\n";
	$icounter++;
	if($icounter == $Admin_gallery_cols)
	{
		echo "</TR><TR>\n"; $icounter="0";
	}
}
	echo "<TR><input type=\"submit\" value=\"ok\" class=\"form\"></TR>";
ENDIF;
?>

And from gallery.php (runs the query)

if ($mode == "edit_memonly")
{
		if (!empty($selected_box)) {
		$selected_cnt = count($selected_box);
		// builds the query
		$qry_in = '(';
		for ($i = 0; $i < $selected_cnt; $i++) {
			$qry_in .= ($i == 0) ? '' : ',';
			$qry_in .= $selected_box[$i];
		}
		$qry_in .= ')';

$query = ("UPDATE $dbtbl_card SET mem_only='1' WHERE card_id IN $qry_in");
$result = $DB_site->query($query);
echo "$TagStartText";
	}
if (empty($selected_box) || $result)
{
	echo "$MsgPostcards $MsgAdminModified<br>";
}else{
	echo ("$MsgPostcards $MsgAdminNoModified".
	"<P>Error: " . mysql_error());
}
echo "$TagEndText";
echo TagBackBar(1,$MsgBacktoSection);

exit;
}

I know its not correct because it always sets mem_only to 1, even if I clear the checkbox at some pics. But I have no idea how should I modify my code to do its job well.

I hope I have been exact at this time, sorry for my stupidity.

[Sxooter]

Well, the XOR thing should work then, I would think. As long as it's not on an enum field. hehe. Enums are pretty much a varchar with a check constraint built in.

Maybe cast (enumfield as bool) would work:

update table set enumfield = cast(enumfield as bool) XOR 1 where rowid=123;

[Dedix]

Thanks, now it's working fine!
I had to modify the checkbox to "change card state" and use XOR thing and now it's cool. 🙂

Thanks for your help again man 😉

[Sxooter]

Glad to have helped. Now I'm gonna have to upgrade my linux distro so I can get a newer flavor of MySQL installed and start playing with it. :-)