CODEWITHSUNDEEP
phpmysql
user3313444
user3313444

Reputation: 19

condition in SELECT command

I have user table with fields username,student name, class where it stores values as 

username      studentname     class
--------      -----------     ------ 
ABC           Prudhvi           1
XYZ           Vishwa            2
SSS           Priya             3

I'm displaying the database records from this user table by using grid table.

SELECT * FROM user.

Now, here if class=1 i want to display class name in grid as Nursery & if class=2 it should display as pre-school....

I want the displaying grid table as follows

username      studentname     class
--------      -----------     ------ 
ABC           Prudhvi           Nursery
XYZ           Vishwa            Pre-school
SSS           Priya             Kindergarten

But i want to display the class names differently while displaying without changing it's values in database.

I tried using switch codition,but didn't worked.

Please help me.

Upvotes: 0

Views: 107

Answers (5)

user3313444
user3313444

Reputation: 19

Thank you all for posting the reply..

The below code is working fine..

SELECT username, password, studentname, parentname, timestamp, 
CASE class
    WHEN  '1' THEN  'Pre_nursery M - Gold fish'
    WHEN  '2' THEN  'Pre-nursery LMP - Ducklings'
    WHEN  '3' THEN  'Nursery M - Doves'
    WHEN  '4' THEN  'Nursery M Sejal - Parrots'
    WHEN  '5' THEN  'Nursery LMP - Koels'
    WHEN  '6' THEN  'K1 - Cuckoos'
    WHEN  '7' THEN  'K1 LMP Blue jays'
    WHEN  '8' THEN  'K2 - White doves'
    WHEN  '9' THEN  'K2 LMP - Penguins'
    WHEN  '10' THEN  'Extended - Peacocks'
END AS class
FROM user_profile

Upvotes: 0

paragm
paragm

Reputation: 54

You can Use Case in select query. e.g.

SELECT username, studentname,     
CASE(class)
    WHEN 1 THEN 'Nursery'
    WHEN 2 THEN 'Pre-school'
    WHEN 3 THEN 'Kindergarten'
END AS 'className'
FROM `user`;

Upvotes: 0

user3524542
user3524542

Reputation:

Try this:

SELECT username,
   CASE class
    WHEN 1 THEN 'Nursury'
    WHEN 2 THEN 'Pre-school'
    WHEN 3 THEN 'Kindergarden'
   END CASE AS 'classname'
FROM user

You can learn more about case on http://dev.mysql.com/doc/refman/5.0/en/case.html and also I agree with others in normalizing your tables

Upvotes: 0

SEB BINFIELD
SEB BINFIELD

Reputation: 410

I think you might want to reconsider your table structure. Not only will this make your query simpler but will conform to database normalisation. http://databases.about.com/od/specificproducts/a/normalization.htm

Create another table which contains the class names and then do a simple INNER JOIN, to join these two tables.

Upvotes: 0

john
john

Reputation: 613

Easiest way is to make a second table that contains the class mappings:

ID|Class
1 |Nursery
2 |Preschool
...

Then you join your first table on the new classTable and select the values you need.

Upvotes: 4

Related Questions