mySQL query -- multiple possible specific values

Question

I am currently querying our database using a rather lengthy statement, given how little it does.

fieldname like 'expected_result_up' AND `result_text` not like 'obsolete' and
AK47_testsection_id like 436 and ( `resultsfields_value` like 240 or
`resultsfields_value` like 846 or `resultsfields_value` like 1000 )

I'm trying to get a value from "resultsfields_value" such that it can be either 240, 846, or 1000. The current query works, it returns every one of the values. But it only works in a manual query through the heidiSQL interface.

When I run the same query with different syntax through php I only get the last "or" (1000) returned. The php DB query is:

"SELECT point_id FROM v_ak47_test_point WHERE ak47_testsection_id= $secID
and result_text != 'obsolete' and fieldname like 'expected_result_up' and 
(resultsfields_value = 240 or resultsfields_value = 846 or resultsfields_value =
1000)"

Is there a more elegant way to specify multiple unique possibilities?

Those parentheses around the "OR'ed" fields definitely mean something... — Wonko the Sane
– Wonko the Sane, Commented Jul 11, 2011 at 17:17

webbiedave · Accepted Answer · 2011-07-11 17:16:42Z

2

You can use an IN clause:

SELECT point_id 
FROM v_ak47_test_point 
WHERE ak47_testsection_id= $secID
AND result_text != 'obsolete'
AND fieldname = 'expected_result_up'
AND resultsfields_value IN (240, 846, 1000)

answered Jul 11, 2011 at 17:16

webbiedave

49k8 gold badges92 silver badges104 bronze badges

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

2 Comments

Joshua Over a year ago

certainly works on hedisql, checking it in my php now. Either way, thanks!

prodigitalson Over a year ago

Aldo one would assume the field names are predetermined so it doesnr make much sense to use a LIKE you would probably be better off using =

Michael Berkowski · Accepted Answer · 2011-07-11 17:17:31Z

1

Use the IN () clause. Because you haven't put parens around the last three OR conditions, the last of them, if true, will override everything else in the WHERE clause.

SELECT point_id FROM v_ak47_test_point WHERE ak47_testsection_id= $secID
  and result_text != 'obsolete' and fieldname like 'expected_result_up' and 
  resultsfields_value = IN (140, 846, 1000)

Also, since you have no % wildcard with fieldname like 'expected_result_up', don't use LIKE where a simple fieldname = 'expected_result_up' will do. (Unless that's just placeholder data, and you actually intend to use something more complicated)

answered Jul 11, 2011 at 17:17

Michael Berkowski

271k47 gold badges450 silver badges395 bronze badges

1 Comment

Joshua Over a year ago

It's actually the reverse, it was something more complex that I copied and haven't cleaned up yet. The actual php call uses =

Collectives™ on Stack Overflow

mySQL query -- multiple possible specific values

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related