Usng enum in drupal

Question

I have a mysql table id,name,gender,age religion( enum('HIN','CHR','MUS') ,category(enum('IND','AMR','SPA') where last 2 are enum datatype and my code in drupal was

  $sql="SELECT * FROM {emp} WHERE age=".$age." and religion=".$rel." and category=".$categ;

  $result=db_query_range($sql,0,10);

   while($data=db_fetch_object($result))
    {
      print $data->id." ".$data->name."<br>";
    }

I get no result or error . I'm trying different query with each field and all are fine except using enum.

 for ex: $sql='SELECT * FROM {emp} WHERE religion="'.$rel.'"';

Is there any problem in using enum datatype in drupal

Answer 1

Enum is not something that I believe drupal can make with the schema API, which is what you in most cases want to use for modules and stuff. Also you are lacking an ending ) in your reference to it, but I'm sure you did it right when your made the table.

Enum is only a constraint that is built into the database when inserting values. So if you try to insert an invalid value, you will insert an empty string instead. So it wont have any effect on Drupal querying to get data. It also wont have any effect when Drupal insert values, other than converting invalid values to empty strings. You might want to check the your data, to see if it is as expected. You might just get no results because your query doesn't match anything.

Another thing is the way you construct your queries is a big NO NO, as it's very insecure. What you should do is this:

db_query("SELECT ... '%s' ...", $var);

Drupal will replace %s with your var and make sure there is no SQL injection and other nasty things. %s indicates the var is a string, use %d for ints and there are a few others I can't remember just now. You can have several placeholders like this, and they will be insert in order, much like the t function.

Answer 2

Seconding Googletorps advise on using parameterized queries (+1). That would not only be more secure, but also make it easier to spot the errors ;)

Your original query misses some quotes around your (String) comparison values. The following should work (Note the added single quotes):

$sql = "SELECT * FROM {emp} WHERE age='" . $age . "' and religion='" . $rel . "' and category='" . $categ . "'";

The right way to do it would be something like this:

$sql = "SELECT * FROM {emp} WHERE age='%s' and religion='%s' and category='%s'";
$args = array($age, $rel, $categ);
$result = db_query_range($sql, $args ,0 , 10);
// ...