Re: Mysql DBI Select Syntax ?

[About-tw.com 免費人力銀行]

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.