select problem

[pradeepjain]

I have a table like this

+---------------------------+--------------------------+------+-----+---------+----------------+
| Field                     | Type                     | Null | Key | Default | Extra          |
+---------------------------+--------------------------+------+-----+---------+----------------+
| patient_id                | int(5) unsigned zerofill | NO   | PRI | NULL    | auto_increment | 
| Surname                   | varchar(100)             | YES  |     | NULL    |                | 
| Firstname                 | varchar(100)             | YES  |     | NULL    |                | 
| Gender                    | enum('Male','Female')    | YES  |     | NULL    |                | 
| Dob                       | date                     | YES  |     | NULL    |                | 
| Age                       | int(3)                   | YES  |     | NULL    |                | 
| Address                   | text                     | YES  |     | NULL    |                | 
| City                      | varchar(100)             | YES  |     | NULL    |                | 
| State                     | varchar(100)             | YES  |     | NULL    |                | 
| Pincode                   | int(10)                  | YES  |     | NULL    |                | 
| Address_landmark          | varchar(100)             | YES  |     | NULL    |                | 
| Landline                  | varchar(20)              | YES  |     | NULL    |                | 
| Mobile                    | varchar(20)              | YES  |     | NULL    |                | 
| Email                     | varchar(30)              | YES  |     | NULL    |                | 
| Smoker                    | enum('Yes','No')         | YES  |     | NULL    |                | 
| Alchohol                  | enum('Yes','No')         | YES  |     | NULL    |                | 
| Discover_diabetic         | text                     | YES  |     | NULL    |                | 
| Emergency_contact_name    | varchar(100)             | YES  |     | NULL    |                | 
| Emergency_telephone       | varchar(20)              | YES  |     | NULL    |                | 
| Physician_id              | int(10) unsigned         | NO   | MUL | NULL    |                | 
| Physician_name            | varchar(100)             | YES  |     | NULL    |                | 
| Physician_phone           | varchar(20)              | YES  |     | NULL    |                | 
| Physician_Mobile          | varchar(20)              | YES  |     | NULL    |                | 
| Physician_after_office    | varchar(20)              | YES  |     | NULL    |                | 
| consultation              | enum('Yes','No')         | YES  |     | NULL    |                | 
| Diabetologist_name        | varchar(100)             | YES  |     | NULL    |                | 
| Diabetologist_number      | varchar(20)              | YES  |     | NULL    |                | 
| Chiropodist_name          | varchar(100)             | YES  |     | NULL    |                | 
| Chiropodist_number        | varchar(20)              | YES  |     | NULL    |                | 
| Hospital_name             | varchar(100)             | YES  |     | NULL    |                | 
| Hospital_number           | varchar(20)              | YES  |     | NULL    |                | 
| Diabetes_nurse_name       | varchar(100)             | YES  |     | NULL    |                | 
| Diabetes_nurse_number     | varchar(20)              | YES  |     | NULL    |                | 
| Type_of_diabetes          | varchar(100)             | YES  |     | NULL    |                | 
| Date_of_diagnosis         | date                     | YES  |     | NULL    |                | 
| Symptoms_at_diagnosis     | text                     | YES  |     | NULL    |                | 
| Weight_at_diagnosis       | int(3)                   | YES  |     | NULL    |                | 
| Past_family_history       | text                     | YES  |     | NULL    |                | 
| Past_medical_history      | text                     | YES  |     | NULL    |                | 
| Allergies                 | text                     | YES  |     | NULL    |                | 
| Doctors_observation       | text                     | YES  |     | NULL    |                | 
| Alternate_medicine        | enum('Yes','No')         | YES  |     | NULL    |                | 
| Alternate_medicine_reason | text                     | YES  |     | NULL    |                | 
| addedon                   | varchar(50)              | NO   |     | NULL    |                | 
| addedip                   | varchar(50)              | NO   |     | NULL    |                | 
| addedby                   | varchar(100)             | NO   |     | NULL    |                | 
| updatedon                 | varchar(50)              | NO   |     | NULL    |                | 
| updatedip                 | varchar(50)              | NO   |     | NULL    |                | 
| updatedby                 | varchar(100)             | NO   |     | NULL    |                | 
| userdetails               | text                     | YES  |     | NULL    |                | 
+---------------------------+--------------------------+------+-----+---------+----------------+
 

and i have values in table like

 select * from viioforms_form1;
+------------+---------+-----------+--------+------------+------+-----------------------------+-------------+-------------+---------+------------------+------------+--------------+--------------------------+--------+----------+-------------------+------------------------+---------------------+--------------+----------------+-----------------+------------------+------------------------+--------------+--------------------+----------------------+------------------+--------------------+---------------+-----------------+---------------------+-----------------------+------------------+-------------------+-----------------------+---------------------+---------------------+----------------------+-----------+---------------------+--------------------+---------------------------+--------------+-----------------+---------+-----------+-----------+-----------+-------------------------------------------------------------------------------------------------------------------+
| patient_id | Surname | Firstname | Gender | Dob        | Age  | Address                     | City        | State       | Pincode | Address_landmark | Landline   | Mobile       | Email                    | Smoker | Alchohol | Discover_diabetic | Emergency_contact_name | Emergency_telephone | Physician_id | Physician_name | Physician_phone | Physician_Mobile | Physician_after_office | consultation | Diabetologist_name | Diabetologist_number | Chiropodist_name | Chiropodist_number | Hospital_name | Hospital_number | Diabetes_nurse_name | Diabetes_nurse_number | Type_of_diabetes | Date_of_diagnosis | Symptoms_at_diagnosis | Weight_at_diagnosis | Past_family_history | Past_medical_history | Allergies | Doctors_observation | Alternate_medicine | Alternate_medicine_reason | addedon      | addedip         | addedby | updatedon | updatedip | updatedby | userdetails                                                                                                       |
+------------+---------+-----------+--------+------------+------+-----------------------------+-------------+-------------+---------+------------------+------------+--------------+--------------------------+--------+----------+-------------------+------------------------+---------------------+--------------+----------------+-----------------+------------------+------------------------+--------------+--------------------+----------------------+------------------+--------------------+---------------+-----------------+---------------------+-----------------------+------------------+-------------------+-----------------------+---------------------+---------------------+----------------------+-----------+---------------------+--------------------+---------------------------+--------------+-----------------+---------+-----------+-----------+-----------+-------------------------------------------------------------------------------------------------------------------+
|      00001 |  m      |  pradeep  |        | 1984-08-19 |   25 |  #287,2nd block,2nd cross,  |  bangalore  |  karnataka  |  560085 |  pillar          |  26792042  |  9980572765  |  [e-mail]  |        |          |  yesterday        |  pradeep               |  26792042           |            1 |  ashok         |  26792042       |  9980572765      |  9980572765            |              |  naveen1           |  9980572761          |  naveen2         |  9980572762        |  naveen3      |  9980572763     |  naveen4            |  9980572764           |  not known       | 2009-09-09        |  unknown              |                  78 |  none               |  none1               |  nope     |  not yet done       |                    |  no reason                |  1258714681  |  192.168.2.112  |  admin  |           |           |           |  Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.9.1.3) Gecko/20090909 Fedora/3.5.3-1.fc11 Firefox/3.5.3 FirePHP/0.3 | 
|      00002 |  m      |  pradeep  |        | 1984-08-19 |   25 |  #287,2nd block,2nd cross,  |  bangalore  |  karnataka  |  560085 |  pillar          |  26792042  |  9980572765  |  [e-mail]  |        |          |  yesterday        |  pradeep               |  26792042           |            1 |  ashok         |  26792042       |  9980572765      |  9980572765            |              |  naveen1           |  9980572761          |  naveen2         |  9980572762        |  naveen3      |  9980572763     |  naveen4            |  9980572764           |  not known       | 2009-09-09        |  unknown              |                  78 |  none               |  none1               |  nope     |  not yet done       |                    |  no reason                |  1258714684  |  192.168.2.112  |  admin  |           |           |           |  Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.9.1.3) Gecko/20090909 Fedora/3.5.3-1.fc11 Firefox/3.5.3 FirePHP/0.3 | 
|      00003 |  m      |  pradeep  |        | 1984-08-19 |   25 |  #287,2nd block,2nd cross,  |  bangalore  |  karnataka  |  560085 |  pillar          |  26792042  |  9980572765  |  [e-mail]  |        |          |  yesterday        |  pradeep               |  26792042           |            1 |  ashok         |  26792042       |  9980572765      |  9980572765            |              |  naveen1           |  9980572761          |  naveen2         |  9980572762        |  naveen3      |  9980572763     |  naveen4            |  9980572764           |  not known       | 2009-09-09        |  unknown              |                  78 |  none               |  none1               |  nope     |  not yet done       |                    |  no reason                |  1258715003  |  192.168.2.112  |  admin  |           |           |           |  Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.9.1.3) Gecko/20090909 Fedora/3.5.3-1.fc11 Firefox/3.5.3 FirePHP/0.3 | 
+------------+---------+-----------+--------+------------+------+-----------------------------+-------------+-------------+---------+------------------+------------+--------------+--------------------------+--------+----------+-------------------+------------------------+---------------------+--------------+----------------+-----------------+------------------+------------------------+--------------+--------------------+----------------------+------------------+--------------------+---------------+-----------------+---------------------+-----------------------+------------------+-------------------+-----------------------+---------------------+---------------------+----------------------+-----------+---------------------+--------------------+---------------------------+--------------+-----------------+---------+-----------+-----------+-----------+-------------------------------------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)

but when i give query like

select * from viioforms_form1 where Surname='m' and Firstname='pradeep';
Empty set (0.00 sec)
 

it should give th result . but its not ....i am not knowing the reason.

[ssnaik84]

while inserting values to table, have u checked for extra spaces?
e.g. 'm' might be entered as 'm '
try using 'like' keyword

[Atli]

Hey.

Yea, I think ssnaik is right there. From what you posted, it looks like there are extra spaces before the values in both Surname and Firstname.

You can try the TRIM function.

SELECT stuff FROM tpl WHERE TRIM(Surname) = 'm' etc..

Ideally, you should trim stuff before it is inserted into the table, to prevent problems like these.