Minor problem with PDO / LIKE statement

I'm hitting a problem with a PDO LIKE statement.

The problem is that I'm dynamically creating part of the query, with a join()-statement.
Something like this:
$usergroup_list = join(' OR group_access LIKE ',$usergroup_new);

When I do the query with just the $usergroup_list variable, it works fine (of course) - but if I want to prepare that statement, and use the $usergroup_list as a prepared value, it doesn't work - which of course is also natural, since PDO escapes the input values - so I'm assuming it shows up something like this:
WHERE group_access LIKE '%3%' 'OR group_access LIKE '%6%'' which syntactically is correct, but doesn't actually work in the query. Anyone able to help with this one?
Something else I can do? I have to make the call dynamically, since the user can have multiple groups, and hence have several numbers which I somehow have to call separately - and I don't know how many the user has beforehand.
If you need more code, please tell me, and I can post the whole bit.

 

Is there some reason you aren't using prepared queries for this, and instead are blindly dumping values into a query string like you are still using the old mysql_ functions?

Also wondering why you're array joining an array since that's the functional equivalent of:
$usergroup_list = ' OR group_access LIKE ' . $usergroup_new;

Wait.. or is it... just what the blue blazes are you trying to do here?!? Oh, gluing together multiples? That seems... wrong. What's the content of $usergroup_new? Is it actually a list of names for prepare, or are you blindly dumping in UNSANITIZED values?

Which any way you look at it is NOT how one should be building queries in PDO. I'd have to see your ENTIRE query to weigh in more, but that's not working, that's not how you do it.

 

Not a solution, but I find it helpful to tail the queries send to MySQL to see how they look (assumptions usually end up wrong):

1) In my.ini, under the [mysqld] section, add a log command, like log="PATH/mysql.log"
2) Restart MySQL.

That file will grow quickly, so be sure to delete it and turn off logging when you're done testing.

Additionally the join statement (aka implode, I always like to not use aliases for easier documentation reach):

$usergroup_new = array ("'%3%'", "'%6%'");
$usergroup_list = implode(' OR group_access LIKE ',$usergroup_new);
echo $usergroup_list;
// OR group_access LIKE '%3%' OR group_access LIKE '%6%'

PHP:

Is the content of usergroup_new any different? What is the final LIKE you are trying to do?

 

WHERE group_access RLIKE '[36]'

Replace 3 and 6 with the numbers you need. The brackets are required.

Sample code:

<?php
$digits = '36';
$param = '[' . $digits . ']';

$sth = $pdo->prepare("SELECT whatever FROM table WHERE group_access RLIKE ?");
$sth->bindValue(1, $param);
$sth->execute();

PHP:

 

Thank you @xtmx - that worked perfectly.

 

Simply trying to get a variable set of values ($usergroups) to work in the query, and then fetch the records that match. Of course I could've done multiple queries, but I prefer having the results in one go. Since a user can be a member of several groups, or only one, I needed a way to do a comparison with multiple groups, hence the join() - but the solution xtmx provided worked perfectly.

 

Wouldn't his solution fail to work if you have more than 10 (0..9) 'groups'? It's digit based... Also, are these numbered? If so, why are you using 'like' since 3 should return 3, 33, 36, 31, 13, 23... Not much of a solution...

Is this what you are trying to do? (skipping those stupid % for like)

if ($userGroupCount = count($userGroupList)) {
	
	$statement = $db->prepare('
		SELECT * FROM userGroups
		WHERE group_access = ?' . (
			$userGroupCount > 1 ?
			str_repeat(' OR group_access = ?', $userGroupCount - 1) :
			''
		)
	);
	$statement->execute($userGroupList);
	
} else {

	// handle that the user is not a member of any groups.
	
}

Code (markup):

If not, then I'm just not getting it... possibly because you aren't telling us what the data is or the structure you are operating on is...

 

I'm pretty sure the value is simply a list of characters, i.e. 248 meaning the user is a member of groups 2, 4, and 8.

It violates 1NF, but we'll save that for another thread.

 

Why the hell would ANYONE ever do that?!? So... he can only ever have 10 groups? That's... wow. I lack the words in polite company.

I was unable to determine what the input values even were -- which is why I'm not really grasping what it is he's even trying to accomplish and guessing wildly. Best I could figure was he had an array:
['%3%', '%6%']

But it seems silly to assume he'd never have '%20%' or '%1293%' -- or to code it so having more than ten is impossible to support.

Though I'd drop the % and just use equals instead of like since, again, like would return false positives -- unless again, my wild guesses have nothing to do with the actual data in the array and the table being queried.

 

For the sake of understanding, let's say that each character represents a skill that a business needs. Like 0=PHP,1=JS,2=C++,3=Java

Someone who knows Java, PHP, and C++ could be 310, 031, or any other combination of those digits. RLIKE checks to see if the pattern matches anywhere in the string, not just the string itself (MySQL has special operators for this). Brackets represent a character class, and because there is no plus symbol or asterisk, RLIKE returns true if any of the characters in the brackets are also present in the row's value.

If an employee with PHP/JS skills was needed, they could use RLIKE '[01]' or RLIKE '[10]'. Either would do the exact thing.

______________________________________________

That being said, this is absolutely NOT how I would approach the problem if I was coding this. I'd have a table called "user_groups" that had the fields "user_id" and "group_id" so that you could expand if you needed more groups (without having to resort to alphabet letters).

 

These are good points. And this just means I've been way too tired while working on this
I was actually not 100% sure why the LIKE-bits were still in there - as @deathshadow said, it's pretty much just set values - then I discovered that what I'm actually trying to fetch this from is a one-field in a column with multiple values - ie the value could be 3 or it could be 3,6,8 - hey, don't kill me, I didn't design this crap.
Also very true about the "what, no groups beyond 10" (or, 0-9) - and I was... wow... true again. I need to do a talk with someone responsible for the db, and just ask if I can't just redo this tiny part... I don't really wanna look at the rest.