Between Query Not Working..

Question

0.00/5 (No votes)

See more:

Hi Friends,

I have created a table but when i am putting a search query then it now showing the result,

SQL

CREATE TABLE `matri_user_info` (
  `reg_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `email_id` varchar(45) NOT NULL,
  `pass` varchar(45) NOT NULL,
  `name` varchar(45) NOT NULL,
  `age` varchar(45) NOT NULL,
  `gender` varchar(45) NOT NULL,
  `height` varchar(45) NOT NULL,
  `religion` varchar(45) NOT NULL,
  `marital_status` varchar(45) NOT NULL,
   PRIMARY KEY (`reg_id`)
) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

Now using this Query

$query_for_result=mysql_query("select * from matri_user_info where (gender='$gender') and (age between $agemin and $agemax) and (height between $hmin and $hmax);");

But this is not showing any result even the data is there.
Is this a Problem of data type because
height coming like 5 , 5.5, 5.10, 5.6,

If any of you have any idea then pls let me know.
Thanks

Posted 7-Oct-12 21:45pm

Rajeshkrathor

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Sandip.Nascar · Answer 1 · 2012-10-07T21:57:00

Solution 1

You have declared height and age datatype as varchar, which is wrong.
Change the data type to numeric. Your problem will be solved.

cheers

Posted 7-Oct-12 21:57pm

Sandip.Nascar

v2

Comments

[no name] 8-Oct-12 4:47am

i changed age integer and height decimal (10,2)
age coming correctly but in height still not getting the right result.

Sandip.Nascar 8-Oct-12 4:56am

It should work. Check the data. You have used "AND" Operator, so check the data meets the filter criteria.

[no name] 8-Oct-12 5:45am

Yes i checked that i am filling data as if 5.5 then in data base it save as 5.50 thats why it not taking during search.

Is there any way by which i take data 5.5 and convert it 5.05 , if i put 0 while inserting data then when i will insert 5.11 then it will take 5.011.

I am not aware how to do this?
thanks

Sandip.Nascar 8-Oct-12 6:29am

I didn't get you. If you post 5.05, it will save as 5.05 and if you save 5.11, it will save as 5.11. Then what is the problem?

[no name] 8-Oct-12 9:04am

If i will take in text box 5.5, it will save as 5.05 and if you enter 5.11, it will save as 5.01. when we take decimal (10,2,
It will be a wrong data.
thanks

Patrick Wanjau · Answer 2 · 2012-10-09T21:28:00

I would recommend you try using this solution:

SQL

CREATE TABLE `matri_user_info` (
  `reg_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `email_id` varchar(45) NOT NULL,
  `pass` varchar(45) NOT NULL,
  `name` varchar(45) NOT NULL,
  `age` INT(3) NOT NULL,
  `gender` varchar(45) NOT NULL,
  `height` NUMERIC(6,3) NOT NULL,
  `religion` varchar(45) NOT NULL,
  `marital_status` varchar(45) NOT NULL,
   PRIMARY KEY (`reg_id`)
) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

Now using this Query

PHP

$query_for_result=mysql_query("SELECT * FROM matri_user_info WHERE gender='".$gender."' AND age BETWEEN ".$agemin." AND ".$agemax." AND height BETWEEN ".$hmin." AND ".$hmax);
?>

The corrections I've made in your SQL Code are: Change the height to a floating point number, and age to an integer...both from Varchar. In your PHP Code I've re-formatted your code for readability, and just following the Conventions used while writing SQL Statements.

If the problem persists, get back to me, I will assist. If it works, approve my answer by indicating solved and upvote.

Regards,
Patrick