Dynamic MySQL query?

Question

I have 3 individual FULLTEXT indexes on columns: City, Text, Group and 3 corresponding values $city, $text and $group.

What I want to do is basically have multiple WHERE conditions that only run if the variable is defined. E.G if $city and $text are defined a WHERE for $city in column City and a WHERE for $text in column Text.

This is my current MySQL query:

$result = mysql_query("
SELECT * FROM Posts 
WHERE MATCH (City) AGAINST ('$city') 
ORDER by Date DESC LIMIT $limit_posts OFFSET $first_post
");

I don't know how to do it without messing the query.

How can I do it?

Sukumar · Accepted Answer · 2011-08-03 08:15:30Z

1

You could do something like this:

Warning: Hacky code ahead

// Column name => column value
$filter = array(
    'city' => $city,
    'text' => $text,
    'group' => $group,
);

$filter_sql = 'WHERE ';
foreach ($filter as $key=>$value)
    $filter[$key] = "MATCH ($key) AGAINST ('$value') ";

$filter_sql .= implode($filter, ' AND ');

$query = "SELECT * FROM Posts 
$filter_sql
ORDER by Date DESC LIMIT $limit_posts OFFSET $first_post";

Just make sure you properly populate $filter array based on what columns you want to filter on.

answered Aug 3, 2011 at 8:15

Sukumar

3,5873 gold badges28 silver badges29 bronze badges

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

Comments

Cody Caughlan · Accepted Answer · 2011-08-03 04:49:24Z

0

Probably easiest to build the query dynamically in your application code.

answered Aug 3, 2011 at 4:49

Cody Caughlan

32.8k5 gold badges67 silver badges68 bronze badges

Comments

symcbean · Accepted Answer · 2011-08-03 09:23:32Z

0

The best solution is to compose the query dynamically as indicated elsewhere. But an ugly hack where this is not possible would be:

SELECT * FROM Posts 
WHERE ('$city'='' OR MATCH (City) AGAINST ('$city'))
  AND ('$text'='' OR MATCH (text) AGAINST ('$text')) 
  AND ('$group'='' OR MATCH (group) AGAINT ('$group'))

answered Aug 3, 2011 at 9:23

symcbean

48.4k6 gold badges64 silver badges99 bronze badges

Collectives™ on Stack Overflow

Dynamic MySQL query?

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related