CODEWITHSUNDEEP
postgresqlselect
roberthuttinger
roberthuttinger

Reputation: 1194

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

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

Upvotes: 1

Views: 451

Answers (1)

krlmlr
krlmlr

Reputation: 25484

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.

Upvotes: 1

Related Questions