View Full Version : How to empty a query field?
03-04-2009, 07:41 PM
Hi, I want to make a field in the database empty before I submit the new query. So it doesn't stack information.
This is an example query.
$update = mysql_query("UPDATE users SET permissions='' WHERE id='$id'");
But it completely ignors that query. I need it so it empties permissions, and then a few lines down another query runs to put information back in. Or unless both can be done on the same query.
03-04-2009, 07:44 PM
SQL will never stack.
UPDATE table SET x = 'new value' WHERE y = 'value'
will always completely overwrite the value originally held by x. This is different than attempting to append values with a SET x = x + 'value', or SET x = x & 17.
If the value is incorrect, than there is something wrong with the final query being run, somewhere in there it is appending values that sound like they don't belong.
03-04-2009, 09:14 PM
Well it keeps adding onto with my query.
$userpermit = explode(",", $row["fortleadper"]);
$new_userpermit = implode(",", $userpermit);
$update = mysql_query("UPDATE users SET fortleadper='$new_userpermit' WHERE id='" . $row["id"] . "');
When I execute that it will be like
But if I run it again it looks like this.
It doesn't keep only 7 characters long.
03-04-2009, 09:35 PM
Well, from the looks of the code, you are first reading in the value in $row['fortleaderper'] (presumably from your db), then appending the value in $use1 and re-writing the combined data back into fortleadper in your db. So, it's not MySQL that's doing it, it's your logic.
I might add that although I don't know what fortleadper is, I suspect that you should not be storing the data as csv. Instead, it should be a separate table linked to users by a foreign key. Then the fortleadper table can contain one value per row. This would be a more 'normalized' form for your data structure.
03-04-2009, 09:42 PM
It stands for Fort Leader Permissions. So there is 7 permissions, if you have a 1, you can access blah, if you have a 2 you can access blah. Or else you can't access it.
So how do I get it to clear or overwrite the last query?
03-04-2009, 10:59 PM
Aside from the normalized route, you should instead consider using a bitwise value for these.
This works like so, each bitwise signifies a high order bit. So, these values are 1, 2, 4, 8, 16, 32, 64.... 2147483648. You're limited to 32 possible bitwise values in PHP, since integers are stored in a signed format.
Then you associate each number to a single permission value. For example, can read would be 1, can write would be 2, can edit would be 4. Then, you assign the value of the permissions using an OR command. So, someone who has read, write and edit permissions would have a value of 7 (1 | 2 | 4), someone who can read and edit but not write would have a value of 5 (1 | 5). As you've probably noticed, the maximum value you can actually assign is equal to the current highest value << 2. So, the next permission in my example would be valued at 8 (since the previous was 4), while all the permissions before it are equal to 7 (1 + 2 + 4).
Then you check with a bitwise & operation.
$aPermissions = array(
1 => 'Can Read',
2 => 'Can Write',
4 => 'Can Edit'
// Maybe we'll promote this to a defined value.
while (list($key, $value) = each($aPermissions))
define(strtoupper(str_replace(' ', '_', $value)), $key);
// Remember, boolean in PHP is defined as FALSE and !FALSE
// $userpermissionvalue is a single integer value, like 7
if ($somethingtoindicateread && ($userpermissionvalue & CAN_READ)) // Should be 0 or the CAN_READ value
// Do the read stuff, since they have privilege
Does that make sense?
Oh yeah completely missed this lol.
Looks like to do what you're asking is to simply use the $use1 variable instead of the $userpermit. This will get interesting when you're altering it though, since you will need to explode it, check to see if it already exists, add if it doesn't remove where you don't need, then implode it again. This is why the normalized route is the best one, and then the bitwise for hardcoded permissions.
03-05-2009, 12:59 AM
EEK! I don't know half of those code tags. I know like 5% of the total PHP tags available. What's the easiest and most beginner way of doing it? The functions work perfectly when it checks if you have access to it or not.
03-05-2009, 10:54 PM
03-05-2009, 11:40 PM
For the bitwise handling, the easiest way to handle it is like I posted - hard coded permissions.
// I'd actually use an object instead, to be more flexible in the future, but thats ok.
$aPermissions = array(
1 => 'Can Read',
2 => 'Can Write',
4 => 'Can Edit',
8 => 'Can Delete',
// And so on, *2 each value on
while (list($key, $val) = each($aPermissions))
define(strtoupper(str_replace(' ', '_', $val)), $key);
// This has just set up our definitions, letting us use CAN_READ, CAN_WRITE, and so forth.
// Do what you gotta do to get the user permissions
$_GET['do'] = 'read';
$func = null;
$perm = 0;
$func = 'doRead';
$perm = CAN_READ;
$func = 'doWrite';
$perm = CAN_WRITE;
$func = 'doEdit';
$perm = CAN_EDIT;
$func = 'doDelete';
$perm = CAN_DELETE;
if ($userpermission & $perm) // Eg: userpermission 5 & 1 = 1
Something like this. That help?
The best part is how easy it is to generate the permissions involved. A simple form with a set of checkboxes, and an array sum. Super sweet:
<form method="post" action="assignPermissions.php">
foreach ($aPermissions AS $permKey => $permName)
printf("<label for=\"chk_%d\">%s</label>\n", $permKey, $permName);
printf("<input type=\"checkbox\" name=\"perm\" id=\"chk_%d\" value=\"%d\" />\n", $permKey, $permKey);
<input type="submit" name="submit" value="Change Permissions" />
$_POST['perm'] can be array_sum'd.
03-05-2009, 11:42 PM
That isn't capable with my website because it uses page arrays instead of echo and print. :(