Okay, to start I'm dealing with the most poorly designed database I've ever seen. My goal is to cut certain records out of a 1.4 million record database, based on readability of the word. That part is easy!

Database Sampling:

| ID       | WORD               | FLAG | LWORD
| dc5943   | bears              | 0    | bears
| mu0286   | best summer tunes  | 0    | bestsummertunes
| mu0286   | bestsummertunes    | 1    |
| muz334   | chk chk chk        | 0    | chkchkchk
| ofn00861 | chuck e cheese.com | 0    | chuckecheesecom
| dcz013   | dc  restaurants    | 0    | dcrestaurants
| dcz0013  | dc  restaurants    | 0    | dcrestaurants
| dcz013   | dc  resturants     | 1    |
| dcz0013  | dc  resturants     | 1    |
| dcz013   | dc american dining | 0    | dcamericandining
| dcz0003  | elmira wv          | 0    | elmirawv
| dc0160   | elmira, c. a.      | 1    |
| tw0056   | elmira, n. y.      | 1    |

[download]

Okay, I'll just sort based on how the data comes in via the FLAG. Depending on what the last record was I can determine what to do with the current one... or so I thought.

I'm using DBI, binding the column names with $sth->bind_columns(...); and grabbing the rows with while($sth->fetch()) { ... }. The data, which perl outputs isn't in the same order as the database.

Output Sampling (first 10 records):

| ID          | WORD            | FLAG | LWORD
| wq12351y059 | hawkinschemical | 1    |
| wq12366y059 | healtheon corp. | 1    |
| wq12367y059 | healthgatedata. | 1    |
| wq12382y059 | helixtechnology | 1    |
| wq12385y059 | helpathome,inc. | 1    |
| wq12390y059 | henryschein,inc | 1    |
| wq12390y059 | henryscheininc. | 1    |
| wq12397y059 | hermanmillerinc | 1    |
| wq12404y059 | hffinancialcorp | 1    |
| wq12409y059 | hickorytechcorp | 1    |

[download]

Is there a way for me to get the data out of the DB as it appears OR will it return this way everytime (it always comes back this way).

Here's my code (exerpt), for reference:

$DSN = 'DBI:ODBC:SearchWords';
$USER= '';
$PASS= 'password';
$DBH = DBI->connect( $DSN, $USER, $USER ) || die "Connect Error: $DBI:
+:errstr\n";
my $SQL = "SELECT * FROM [Production Words]";
my $sth = $DBH->prepare($SQL);
$sth->execute || die "Could not execute SQL statement ... maybe invali
+d?\n$!";
$sth->bind_columns(\$aid,\$wd,\$we,\$df,\$lw,\$sw);
while($sth->fetch()) {
  print "$aid\t$wd\t$we\t$df\t$lw\t$sw\n";
}
$DBH->disconnect();

[download]

System Info: Win32, Perl 5.8.2 on Cygwin, standard CPAN DBI module.

--
paul

In reply to Database Record Order by vbrtrmn

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.