In this case, I really think the question is one of database design. In the domain
table you are creating to enumerate the types of degrees (a2), I would add a "rank"
column so I could evaluate on the rank column, and join back to this domain table in
queries where this evaluation needs to be made.
Once you have a numeric rank, see if you can then make your evaluation. As it is, the
rank is being determined by alphabetic order.
Steve H.
-----Original Message-----
From: About-tw.com ?????? [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, July 03, 2001 4:40 AM
To: Steve Howard; [EMAIL PROTECTED]
Subject: Re: Mysql DBI Select Syntax ?
Dear Steven,
Thanks for your opinion.
Here is my complete example ,
my $dbh = DBI->connect("DBI:mysql:xxxxx",,, { RaiseError => 1 });
my $sth = $dbh->prepare( qq{
CREATE TABLE a1 ( diploma
ENUM("junior_high","senior_high","junior_college","university","master","doc
tor")
});
$sth->execute;
$dbh->disconnect;
my $dbh = DBI->connect("DBI:mysql:xxxxx",,, { RaiseError => 1 });
my $sth = $dbh->prepare( qq{
CREATE TABLE a2 ( diploma
ENUM("junior_high","senior_high","junior_college","university","master","doc
tor")
});
$sth->execute;
$dbh->disconnect;
my $dbh = DBI->connect("DBI:mysql:xxxxx",,, { RaiseError => 1 });
my $sth =
repare( qq{
SELECT a1.diploma a2.diploma
FROM a1,a2
WHERE a1.diploma <= a2.diploma
});
$sth->execute;
$dbh->disconnect;
I'd like to compare a1.diploma and a2.diploma, and my ideal rule is doctor > master
> university > junior_college > .....
But the result is university > senior_high > junior_high > junior_college > master >
doctor
Is there any method let me get my ideal rule is doctor > master > university >
junior_college > senior_high > junior_high
Thanks in advance.
Sincerelly
Tom Wu
----- Original Message -----
From: "Steve Howard" <[EMAIL PROTECTED]>
To: "About-tw.com ??????" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>
Sent: Tuesday, July 03, 2001 10:59 AM
Subject: RE: Mysql DBI Select Syntax ?
I'm not completely sure I know what you're asking. If you're wanting to put a
numerical equivalent to the possible string values, in MySQL you can use a CASE
statement, like this:
SELECT case
WHEN diploma = 'junior_high' THEN 1
WHEN diploma = 'senior_high' THEN 2
WHEN diploma = 'junior_college' THEN 3
WHEN diploma = 'university' THEN 4
WHEN diploma = 'master' THEN 5
ELSE 6
END
AS DIPLOMA
FROM Tablename
You can embed some version of that to get a numerical return from a table enumerated
as you have said, however, it
still shouldn't return as you have put in your WHERE clause. You would still
have to use:
WHERE diploma = 'senior_high'
If you only wanted Senior high grads.
Is this what you are asking?
Steve Howard
-----Original Message-----
From: About-tw.com ?????? [mailto:[EMAIL PROTECTED]]
Sent: Monday, July 02, 2001 3:16 PM
To: [EMAIL PROTECTED]
Subject: Mysql DBI Select Syntax ?
Dear All,
my $dbh = DBI->connect("DBI:mysql:xxxxx",,, { RaiseError => 1 });
my $sth = $dbh->prepare( qq{
CREATE TABLE $table_name (
diploma
ENUM("junior_high","senior_high","junior_college","university","master","doc
tor")
});
When I do the following procedure
my $dbh = DBI->connect("DBI:mysql:xxxxx",,, { RaiseError => 1 });
my $sth = $dbh->prepare( qq{ SELECT * FROM $table_name WHERE
diploma = 2 });
$sth->execute;
my $diploma = $sth -> fetchrow_array ;
$dbh->disconnect;
I can get $diploma = "senior_high"
Now here is my problem , How could I get the value of the $diploma = 2 ?
I'll really appreciated if someone can help me.