Postgres Conditional Query - Best Practice for comparing a string with several other strings

Question

I am currently using this to get my result:

pg_prepare($tableConnection, "", "SELECT * FROM star_wars_action_figures WHERE cool = 't' AND weapon ILIKE $1 OR weapon ILIKE $2 OR weapon ILIKE $3 OR weapon ILIKE $4 OR weapon ILIKE $5") or die( $tableDataRetrieved = false );

$actionFigureTables = pg_fetch_all(pg_execute('', array("%gun%","%".$tagArray[0]."%","%".$tagArray[1]."%","%".$tagArray[2]."%","%".$tagArray[3]."%")));

In English I am going for this: Look in star_wars_action_figures, return only the ones that are cool and then only if the weapon contains gun AND any of these other weapons (lightsaber|fist|knife|wit)

There has to be a more efficient and reliable way to do this right?

I think this s a core simple question and need to get this logic down before I can progress.

Cheers!

Bo

I wonder if it's possible to pass arrays to a Postgres connection in Perl. — krlmlr
– krlmlr, Commented Mar 7, 2012 at 14:30

krlmlr · Accepted Answer · 2012-03-07 14:04:23Z

1

Postgres knows POSIX regular expressions. Your query could be formulated as

...AND weapon ~* $1

if you pass gun|lightsaber|fist|knife|wit to the first parameter.

edited Mar 7, 2012 at 14:04

answered Mar 7, 2012 at 13:58

krlmlr

25.6k14 gold badges127 silver badges231 bronze badges

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

3 Comments

roberthuttinger Over a year ago

SELECT * FROM star_wars_action_figures WHERE cool = 't' AND weapon ~* $1 Then pass in: "gun|lightsaber|fist|knife|wit" ? I could have used regex so many times!

krlmlr Over a year ago

Edited. I missed that the 't' was a constant in your question. Plus the syntax was wrong.

krlmlr Over a year ago

Yes. See the link I already posted.

Collectives™ on Stack Overflow

Postgres Conditional Query - Best Practice for comparing a string with several other strings

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related