CODEWITHSUNDEEP
phpsql
Bift
Bift

Reputation: 73

Check if PHP variable is contained in SQL field

Is the following possible?

field in DB contains: 1,2,3,4

$a=1;

$selqry="select * from table where field={contains $a}";

Thanks, B.

Upvotes: 0

Views: 313

Answers (4)

OIS
OIS

Reputation: 10033

If field only contain numbers from 1-8 you can do it simple

$selqry="select * from table where field like '%" . intval($a) . "%'";

You "should" probably move field to another table and use a query joining the tables based on id.

Upvotes: 0

VolkerK
VolkerK

Reputation: 96159

MySQL's FIND_IN_SET(str,strlist) function can help you there (edit: oops.... in case you're using MySQL)

Returns a value in the range of 1 to N if the string str is in the string list strlist consisting of N substrings. A string list is a string composed of substrings separated by “,” characters.
SELECT
  x,y,z
FROM
  tablename
WHERE
  FIND_IN_SET(':id', field)

(If you're not using pdo/prepared statements, replace :id by the value of $a but make sure it encoded/escaped properly)

It's not perfectly index-friendly though... you'll end up with a full-table scan having this function in your WHERE-clause.

Upvotes: 1

Evan Carroll
Evan Carroll

Reputation: 1

I'm not 100% sure what you're asking but why not just do a LIKE, it should work on numeric fields too:

SELECT * FROM foo WHERE id like '%1%';

This will match 001, 100, and 101, 010, etc.

Upvotes: 0

Max Shawabkeh
Max Shawabkeh

Reputation: 38603

Sure, it's possible.

  1. If you don't mind "1" matching "2,3,11,4". You can do it using INSTR or LIKE. Example:

    SELECT *
FROM tbl
WHERE INSTR(field, '1') != 0

  2. If you don't want "1" to match "2,3,11,4", you'll have to use a (slow) regex if your DBMS supports it (MySQL, for example, does):

    SELECT *
FROM tbl
WHERE field REGEXP '[[:<:]]1[[:>:]]'

However, a better question is whether this is a good idea. And the answer to that is a resounding no. You should not store multiple values in a single field. A case like this would really benefit from normalization.

Upvotes: 0

Related Questions