CODEWITHSUNDEEP
mysqlsqldatabase
ajsie
ajsie

Reputation: 79796

wild expressions in mysql IN statement

I want to use a WILDCARD for everything in the statement beneath:

"SELECT threads.*
    FROM thread_tag_map, threads, tags
    WHERE thread_tag_map.thread_id = threads.id
    AND thread_tag_map.tag_id = tags.id

    AND (tags.name IN ('WILDCARD'))";

What should I replace the WILDCARD with to be able to select every entry? I have tried ('*') but that doesn't work.

I could drop the last row but the thing is that i am making this to a function. passing a value lets say $value, and the $value will replace WILDCARD. So when I pass 'computer' as $value it will get all rows for computer, but then I want to be able to pass nothing and the default $value will be a wildcard to select everything. So how can I make this possible?

Upvotes: 1

Views: 1943

Answers (6)

MatBailie
MatBailie

Reputation: 86775

First, I think you're possibly misunderstanding the IN clause. IN lets you give a list of matching items:

SELECT * FROM [table] WHERE name IN ('Joe','Moe','Larry)


As mentioned by other people LIKE will allow pattern matching, with % being the multi-character wildcard (* in other languages, % here).

SELECT * FROM [table] WHERE name LIKE '%oe'


I'm assuming that in your case the $value is populated by the users, and so can be blank. in which case you want "everything". In an over simplified example, this is the kind ogf thing I've done in the past:

SELECT * FROM [table] WHERE (name LIKE @pattern OR @pattern = '')


As you appear to be building the SQL string up in the application, the alternative would be to only include the final WHERE condition if [LEN($value) > 0].

Also note, if you ARE taking a string from the user and imbedding it in the SQL to execute, make certain to use whatever methods you have to make that string safe. You don't really want someone to seach for the following string...

  • ')); DELETE tags WHERE (name NOT IN ('

SQL Injection is baaad, mmmkay?

Upvotes: 1

ChssPly76
ChssPly76

Reputation: 100746

If you really want to select every entry, drop the last condition altogether:

SELECT threads.*
  FROM thread_tag_map, threads, tags
 WHERE thread_tag_map.thread_id = threads.id
   AND thread_tag_map.tag_id = tags.id

Update (based on question update). Since you seem to be constructing SQL dynamically, you can check whether passed in tag value is null (or whatever you designated to be your "wildcard") and only omit the "AND tags.name = 'value'" condition then. Or, if by "function" you've meant MySQL's stored procedure, take a look at OMG Ponies' solution.

Upvotes: 2

OMG Ponies
OMG Ponies

Reputation: 332691

I recommend using MySQL's Prepared Statements to dynamically construct the SQL statement you want. Here's an example:

SET @SQL := 'SELECT th.*
              FROM THREADS th
              JOIN THREAD_TAG_MAP ttm ON ttm.thread_id = th.id
              JOIN TAGS t ON t.id = ttm.tag_id '

IF @tag_name IS NULL THEN

  PREPARE stmt FROM @SQL;
  EXECUTE stmt;

ELSE

  SET @SQL := @SQL + ' WHERE t.name LIKE ''%'+ @tag_name +'%'''
  PREPARE stmt FROM @SQL;
  EXECUTE stmt USING @tag_name;

END IF;

Upvotes: 4

Rob
Rob

Reputation: 8195

SELECT threads.* is selecting every column. To select every row you need to drop the last where condition:

This will select all columns and rows:

SELECT threads.* FROM thread_tag_map, threads, tags
    WHERE thread_tag_map.thread_id = threads.id
    AND thread_tag_map.tag_id = tags.id

Upvotes: 0

meder omuraliev
meder omuraliev

Reputation: 186672

SELECT threads.*
FROM thread_tag_map, threads, tags
WHERE 1=1
AND thread_tag_map.thread_id = threads.id
AND thread_tag_map.tag_id = tags.id
AND tags.name != ''

Perhaps a query such as this, where you check if its not blank? That or drop the last AND.

Upvotes: 0

Dmitry
Dmitry

Reputation: 3780

You probably want to use LIKE which allows % and _ wildcards. IN tests inclusion into a set of values.

SELECT threads.*
        FROM thread_tag_map, threads, tags
        WHERE thread_tag_map.thread_id = threads.id
        AND thread_tag_map.tag_id = tags.id

        AND tags.name LIKE '%foo_bar%';

Upvotes: 0

Related Questions