CODEWITHSUNDEEP
mysqlsql
Kiran Badi
Kiran Badi

Reputation: 521

My sql Query returning null inspite of having data in the table

I have this query which I am building in my DAO Class after taking the input from te users.Its basic a search functionality with around 10 options users provide..I wrote the below query,

  1. Users give the range of date.
  2. Users provide range of rent.(This part is when it goes as null,I get 0 records.How do I handle null values and still make mysql return data.)
  3. Users select an option to see records which has images.
  4. Status flag for this query should be 1.
  5. Its search by Title.Which means that 1 to 4 points above should be met and keyword given by user should search the column title with like operator.
  6. Finally results are sorted by date.

.

select SQL_CALC_FOUND_ROWS * 
from `database`.`table` 
WHERE (Date(ctimestamp) BETWEEN '2011-12-02' and '2012-12-06') and (crent BETWEEN '' and '') 
   and (cimg1 IS NOT NULL or cimg2 IS NOT NULL or cimg3 IS NOT NULL or cimg4 IS NOT NULL or cimg5 IS NOT NULL or cimg6 IS NOT NULL or cimg7 IS NOT NULL or cimg8 IS NOT NULL)
   and cflag = 1 and ctitle LIKE '%Testing%' 
order  by Date(ctimestamp) desc Limit 0, 100

All the parameters are coming from front end.Now for this query I have close to 13 records with same parameters and conditions,but some how when I run it mysql client it gives me 0 records written.

This query gives me the data correctly,

select SQL_CALC_FOUND_ROWS * 
from `database`.`table`
WHERE (Date(ctimestamp) BETWEEN '2011-12-02' and '2012-12-06') 
   and (cimg1 IS NOT NULL or cimg2 IS NOT NULL or cimg3 IS NOT NULL or cimg4 IS NOT NULL or cimg5 IS NOT NULL or cimg6 IS NOT NULL or cimg7 IS NOT NULL or cimg8 IS NOT NULL)
   and cflag = 1 and ctitle LIKE '%Testing%' 
order  by Date(ctimestamp) desc Limit 0, 100

The difference between the two is this part 

and (crent BETWEEN '' and '')

Now my doubt is how do I handle this situation when users plans to skip these fields.Appreciate some insights.

Upvotes: 1

Views: 1150

Answers (2)

Joel Coehoorn
Joel Coehoorn

Reputation: 415820

crent is likely a numeric value. You're asking whether it's between two text fields. That condition will never be true.

Upvotes: 1

Crystal
Crystal

Reputation: 72

Add validation of the input parameters in your program. Do not put it in the sql when it's NULL.

Upvotes: 0

Related Questions