pattern matching question

[chomedey]

Hi,

 

I have no idea what version of mysql I'm using but I don't think in this case it'll make much difference as it's probably a very basic question for anyone who is not a total novice like myself.

 

Basically I have a table full of data:

 

CREATE TABLE profiles (

profileID INT UNSIGNED NOT NULL AUTO_INCREMENT,

username VARCHAR(16) NOT NULL,

age VARCHAR (6) NOT NULL,

gender VARCHAR (6) NOT NULL,

education VARCHAR (16) NOT NULL,

country VARCHAR (16) NOT NULL,

state VARCHAR (16) NOT NULL,

pseudonym TEXT NOT NULL,

things TEXT NOT NULL,

books TEXT NOT NULL,

songs TEXT NOT NULL,

movies TEXT NOT NULL,

people TEXT NOT NULL,

core_beliefs TEXT NOT NULL,

core_values TEXT NOT NULL,

live TEXT NOT NULL,

sad TEXT NOT NULL,

change_world TEXT NOT NULL,

date_created DATETIME NOT NULL,

last_updated DATETIME NOT NULL,

PRIMARY KEY (profileID),

UNIQUE KEY (username)

)

 

and a search form used to query that data that users of the website can input into.

 

The search query looks like this, where the variables are derived from the $_post data from the form:

 

$query = "SELECT * from

PROFILES WHERE (age LIKE '$age')

AND (gender LIKE '$gender')

AND (education LIKE '$education')

AND (country LIKE '$country')

AND (state LIKE '$state')

ORDER BY last_updated DESC";

 

The problem is, if the user has left a field blank, I don't want that to exclude a profile.  In other words, when age in the db is matched to $age from the $_post data, I don't want that row of the db to be excluded if the variable $age is empty and the column in the db has a value.

 

Does anyone know how to get the LIKE command to ignore empty strings?

 

Does that make any sense?

 

Any help much appreciated.

 

Cheers.

 

Julian

[Maq]

If the value is empty then assign that variable to '%' which is a wild card and will match any value.

 

Of course you'll have to do this in PHP (or w/e you're using).

[chomedey]

I am using PHP but not sure how I would do that.

 

Something like

 

SET % = '';

 

?

 

Also not sure what to make of the error reporting code you included at the bottom of your post.  Was that for me?

 

Cheers.

 

Julian

If the value is empty then assign that variable to '%' which is a wild card and will match any value.

 

Of course you'll have to do this in PHP (or w/e you're using).

[chomedey]

Figured it out.  Thanks very much for your help.

 

Julian