I've been scratching my head for a bit now and I can't think of a way to make a mysql_query pull a set of data that matches a number in an array.


It's easier to explain what I want to do with code.. so here goes..

Then I need my mysql_query to be able to select rows from my database table where, lets say for example, id = any of the values in $test. like this:

$test = 1;
$new_result = mysql_query("SELECT * FROM something WHERE id = $test");

Now that's all great and dandy, BUT, the content of id is a set of numbers for example... lets say there are 20rows of data where id is of type text and contains 4 different random numbers..

Row 1... id= 4,1,5,9
Row 2... id= 6,7,3,1
Row 3... id= 9,0,4,5
Row 4... id= 6,1,2,9
etc...

. So what I need this to do, is for the mysql_query to pick up every row where one of the numbers in id is equal to $test.

So $new_result would end up pulling Row 1, 2 and 4

I assume you'd use the explode function, but I'm not quite sure how.

The only way I can think is like this:

$sql = "SELECT * FROM something WHERE id LIKE \"%,".$test.",%\" OR id LIKE \"".$test.",%\" OR id LIKE \"%,".$test."\"";

Try using like instead of = in the query.

$result = mysql_query("SELECT * FROM `some_table` WHERE `some_column` like '%some_value%'");

I was beaten by the post above :), that suggestion takes into account the commas too.

My suggestion will match anything with that number in, so if $test was 4 then it would also pull 42, 143, 204 etc..

Excellent replies. Thank you very much. I'll try that out and see how it goes.

I can't manage to get the last part of that code to work

OR id LIKE \"%,".$test."\"

(where there is a comma before a number but not after) any idea why?

Edit: Scratch the above issue.. Slightly stupid logic error.


On more problem I'm having is that I also have an AND statement before all of these and it's only only comparing the AND and the first OR statement. Example:

WHERE one = '_GET['something']' AND id LIKE something OR id LIKE something

is there a way to group all of the OR's together so that the statement is true if any one of the OR statements are true.

Thanks in advance.

is there a way to group all of the OR's together so that the statement is true if any one of the OR statements are true.

You can use brackets to group things together.

For example:

WHERE (a = b OR c = d OR d = e) AND (f = g)

or

WHERE (a = b AND c = d) OR (e = f)

These are just a couple of random examples do illustrate the point. By using brackets, everything inside brackets is evaluated first.

THe first example requires either a to be equal to b, or c to be equal to d, or e to be equal to e, and as well as at least one of those being true, f must equal g.

The second example requires that a is equal to be and b is equal to c, or, if not the case, then e must be equal to f.

Alright, sweet. I didn't think you could use brackets inside mysql query's for some reason

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.