Thanks Paul ,
It's just what I want.
Sincerelly
Tom Wu
----- Original Message -----
From: "Paul DuBois" <[EMAIL PROTECTED]>
To: "About-tw.com ??????" <[EMAIL PROTECTED]>
Sent: Wednesday, July 04, 2001 11:18 AM
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","do
c
> >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","do
c
> >tor")
> >});
> >$sth->execute;
> >$dbh->disconnect;
> >
> >
> >my $dbh = DBI->connect("DBI:mysql:xxxxx",,, { RaiseError => 1 });
> >my $sth =
>
e( 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
>
> I would have guessed that master would appear between senior_high and
> junior_high. But no matter. Tell MySQL to do the comparison using
> the underlying numeric values. Change your WHERE clause from:
>
> WHERE a1.diploma <= a2.diploma
>
> to:
>
> WHERE a1.diploma+0 <= a2.diploma+0
>
> The "+0" tells MySQL to convert the ENUM values to integers.
>
>
> >
> >
> >Is there any method let me get my ideal rule is doctor > master >
> >university > junior_college > senior_high > junior_high
> >
> >
> >Thanks in advanc
e.
> >
> >
> >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","do
c
> >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.
>
>
> --
> Paul DuBois, [EMAIL PROTECTED]
>
