PHP remove specific numbers from MySQL field

Question

My db is structured like:

id  |  posts |  groups
----+--------+-----------
1   |   10   |  2
2   |   30   |  2
3   |   20   |  2
4   |   50   |  2,8
5   |   54   |  2,8

When a user gets to 50 or more posts I want the script to remove the group '2'. There is already a prior script that adds the '8'.

I have this:

$cusstring = mysql_query("SELECT `groups` FROM `users` WHERE `postnum` >= 50 ");

$row = mysql_fetch_array($cusstring);

$array = explode(',', $row[groups]);
$pos = array_search('2', $array);
unset($array[$pos]);
$row[groups] = implode(',', $array);

mysql_query("UPDATE `users` SET `groups` = $row[groups] WHERE `postnum` >= 50 ");

It just doesn't seem to update though. I don't know if this is because it picks up multiple fields in the array or if I'm doing something wrong with the greater than or equal to symbol.

Can anyone offer a solution?

Thanks.

EDIT: I've worked out that if I change the symbol to equal to the query works on the first row it comes across with a post count of 50 but it leaves the rest. It would appear it's only able to process one row.

I will suggest trigger in your mysql, I can make one for you it will runs directly in your mysql database. let me know if you want that — Maytham Fahmi
– Maytham Fahmi, Commented Jul 26, 2015 at 19:27
When the user gets to 50 the value should only be 8? UPDATE `users` SET `groups` = 8 WHERE `postnum` >= 50 — chris85
– chris85, Commented Jul 26, 2015 at 19:28
@chris85 not necessarily. Since it may have other non-related groups as-well. The 8 gets added when the user reaches 50 posts and the 2 group should get removed. Which is why I can't just re-write over the field. — Ben
– Ben, Commented Jul 26, 2015 at 19:31
You should fix your data structure to have a proper UserGroups junction table and not to store numbers in a coma-delimited string. — Gordon Linoff
– Gordon Linoff, Commented Jul 26, 2015 at 19:43

chris85 · Accepted Answer · 2015-07-26 19:50:12Z

3

I think this would work for you. I think a SQL approach would be more efficient but you've said you want to keep it in PHP.

$cusstring = mysql_query("SELECT `groups`, `id ` FROM `users` WHERE `postnum` >= 50 ");
while($row = mysql_fetch_array($cusstring)) {
    $groups = mysql_real_escape_string(preg_replace('~(^|\s+)2(,|$)~', '', $row['groups']));
    //regex demo https://regex101.com/r/eX7qD1/1
    $id = (int)$row['id'];
    mysql_query("UPDATE `users` SET `groups` = '$groups' WHERE `id ` = $id ");
}

Your code is only getting one record because you aren't looping the fetch.

Also don't put data that comes from your DB back into a query directly this can lead to a SQL injection. Note I cast the ID here to an int and escaped the groups value. This should prevent the possibility of an injection.

You should switch drivers to PDO or MYSQLI. Once using one of those drivers you can use prepared statements.

This solution also will put an empty value in the groups field if 2 was the only value.

edited Jul 26, 2015 at 19:50

answered Jul 26, 2015 at 19:44

chris85

23.9k7 gold badges37 silver badges51 bronze badges

Sign up to request clarification or add additional context in comments.

10 Comments

Ben Over a year ago

Worked like a charm. Thank you!

Ben Over a year ago

I have a problem. When the group field is: "8,9,2" and I run the script the value it updates with is: "9,2". Any idea why this is happening?

chris85 Over a year ago

No space between the , and the 2. The regex checks for whitespace before the 2 or if it is the start of the string. Is there any other group with a 2 in it aside from 2? Example is 12, 22 etc groups?

chris85 Over a year ago

Use the regex101 link in the comments and you can see what is occurring. You could change the \s+ to \s* which makes the whitespace optional and would work for the instance you described but it would change 9,12,8 so that there was a straggling 1.

Ben Over a year ago

Currently theres no other group with 2 in it, just the group 2 itself. So if the field reads "8,9,2", without spaces, this solution won't work?

|

Collectives™ on Stack Overflow

PHP remove specific numbers from MySQL field

1 Answer 1

10 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

10 Comments

Your Answer

Sign up or log in

Post as a guest

Related