7 Replies - 3520 Views - Last Post: 30 June 2012 - 02:03 PM Rate Topic: -----

#1 polska03  Icon User is offline

  • D.I.C Regular

Reputation: 5
  • View blog
  • Posts: 297
  • Joined: 28-November 09

parse

Posted 30 June 2012 - 11:39 AM

Hello I have a table which stores values as key and value in a text field (e.g 0,male \n 1,female). I want to just want to retrieve the male or female texts according to the key. I want to do this is mySql opposed to using php in the website to do it later.So essentially I have another table that stores the sex ( 0 or 1) and I want the query to reference to the table that contains 0,male \n 1,female and get the appropriate label(male or female) based on the value I give (0 or 1). Is this possible? I am using MySQL

This post has been edited by polska03: 30 June 2012 - 11:39 AM


Is This A Good Question/Topic? 0
  • +

Replies To: parse

#2 macosxnerd101  Icon User is online

  • Self-Trained Economist
  • member icon




Reputation: 10440
  • View blog
  • Posts: 38,666
  • Joined: 27-December 08

Re: parse

Posted 30 June 2012 - 11:51 AM

Why use numbers to store the sex? Use an enum type, defining 'MALE' and 'FEMALE' as constants.
Was This Post Helpful? 0
  • +
  • -

#3 polska03  Icon User is offline

  • D.I.C Regular

Reputation: 5
  • View blog
  • Posts: 297
  • Joined: 28-November 09

Re: parse

Posted 30 June 2012 - 11:59 AM

I know that is the smart way, but this is they way this program stores it. Is it possible?
Was This Post Helpful? 0
  • +
  • -

#4 macosxnerd101  Icon User is online

  • Self-Trained Economist
  • member icon




Reputation: 10440
  • View blog
  • Posts: 38,666
  • Joined: 27-December 08

Re: parse

Posted 30 June 2012 - 12:01 PM

Yes. Just use a basic SELECT query.
SELECT col2
FROM table_name
WHERE col1 = 'some_value'


Was This Post Helpful? 1
  • +
  • -

#5 polska03  Icon User is offline

  • D.I.C Regular

Reputation: 5
  • View blog
  • Posts: 297
  • Joined: 28-November 09

Re: parse

Posted 30 June 2012 - 12:05 PM

Sorry don't understand. So if I have a table (person) that stores person_id and sex (where sex is either 0 or 1) and I want to retrieve what the sex is in terms of male or female which is stored in another table that keeps it as (0,male \n 1,female) which php is used later to parse it, how would I retrieve male or female from that command?
Was This Post Helpful? 0
  • +
  • -

#6 JackOfAllTrades  Icon User is offline

  • Saucy!
  • member icon

Reputation: 6048
  • View blog
  • Posts: 23,473
  • Joined: 23-August 08

Re: parse

Posted 30 June 2012 - 12:55 PM

Persons:

CREATE TABLE `persons` (
 `id` bigint(20) NOT NULL AUTO_INCREMENT,
 `first_name` varchar(64) NOT NULL,
 `last_name` varchar(64) NOT NULL,
 `sex_id` tinyint(1) NOT NULL,
 PRIMARY KEY (`id`),
 KEY `sex` (`sex_id`),
 CONSTRAINT `persons_ibfk_1` FOREIGN KEY (`sex_id`) REFERENCES `sexes` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=latin1




Sexes table:

CREATE TABLE `sexes` (
 `id` tinyint(1) NOT NULL AUTO_INCREMENT,
 `sex` varchar(6) NOT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1


Example data:

Sexes:
INSERT INTO `sexes` VALUES(1, 'FEMALE');
INSERT INTO `sexes` VALUES(2, 'MALE');


Persons:
INSERT INTO `persons` VALUES(3, 'Joe', 'Bloggs', 2);
INSERT INTO `persons` VALUES(4, 'Jane', 'Bloggs', 1);


Query to retrieve data:
SELECT p.first_name, p.last_name, s.sex from persons p inner join sexes s on p.sex_id=s.id


Retrieval results:
|Jane|Bloggs|FEMALE
|Joe|Bloggs|MALE

Was This Post Helpful? 2
  • +
  • -

#7 baavgai  Icon User is offline

  • Dreaming Coder
  • member icon

Reputation: 5795
  • View blog
  • Posts: 12,628
  • Joined: 16-October 07

Re: parse

Posted 30 June 2012 - 01:54 PM

Exactly!

Though, um, I'd go with a gender table, and gender_id, gender_desc. "sexes" just sounds like an act that could be illegal in some states. Plurality in table names general end of sounding a little off in practice.

Note, for something as trivial as a value pair, you might just opt for a simple decode.
SELECT first_name, last_name, 
   (case gender when 0 then 'Male' when 1 then 'Female' else 'NA' end) as gender_desc
   from person


Was This Post Helpful? 0
  • +
  • -

#8 JackOfAllTrades  Icon User is offline

  • Saucy!
  • member icon

Reputation: 6048
  • View blog
  • Posts: 23,473
  • Joined: 23-August 08

Re: parse

Posted 30 June 2012 - 02:03 PM

You're right about gender vs. sex in these PC times.

And the table's names? Habit from my recent experience with CakePHP, which uses a plural table name convention. :)
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1