I am currently working with a MyBB set up where I am trying to do an SQL query which can check if a user is part of a certain additional group. However, I am having difficulties.
MyBB stores additional group IDs in one VARCHAR field separated by commas, such as 1,23,27,30,49 etc. I know that in PHP I can explode strings using the comma as a delimiter. I understand that MySQL cannot explode, but I can use substring_index to achieve a similar goal. Point to note each user could have a random list of additional groups, so not all users would be the same.
I am trying this
$stmt = $conn->query('SELECT username, additionalgroups FROM my_users WHERE SUBSTRING_INDEX(30, ',', 1)');
foreach ($stmt as $row)
{
print '<div class="name"><a href="#">' . $row['username'] . '</a></div>';
}
This does not seem to work though. Looking at https://dev.mysql.com/doc/refman/8.0/en/string-functions.html#function_substring-index I seem to be following their way of constructing the condition, but I get the following error:
Fatal error: Uncaught PDOException: SQLSTATE[HY000]: General error: mode must be an integer in E:\xampp\htdocs\11thinfantrybrigade.co.uk\orbat.php:180 Stack trace: #0 E:\xampp\htdocs\11thinfantrybrigade.co.uk\orbat.php(180): PDO->query('SELECT username...', ', 1)') #1 {main} thrown in E:\xampp\htdocs\11thinfantrybrigade.co.uk\orbat.php on line 180
Any advice please?
The end goal is to print a list of users that meet that criteria.
Thank you.
$query = 'SELECT username FROM my_users WHERE CONCAT(",",additionalgroups,",") LIKE "%,30,%"' ;
$stmt = $conn->query($query);
foreach ($stmt as $row)
{
print '<div class="name"><a href="#">' . $row['username'] . '</a></div>';
}