CODEWITHSUNDEEP
phpmysql
lisovaccaro
lisovaccaro

Reputation: 33946

Dynamic MySQL query?

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?

Upvotes: 0

Views: 728

Answers (3)

symcbean
symcbean

Reputation: 48357

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'))

Upvotes: 0

Sukumar
Sukumar

Reputation: 3577

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.

Upvotes: 1

Cody Caughlan
Cody Caughlan

Reputation: 32748

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

Upvotes: 0

Related Questions