[KLUG Members] Inserting data into MySQL]

[bill]

Need to clarify what you're doing a bit.

You have a file or several files with similar data?  It's rather important to be clear on what is coming in.

Your looking for a "solution" to "format the text and precede it with insert into cards"?

If by format you mean get the text ready for a mysql insert here's how I would do it with php:

Break each file down until you get to the Card Name: and the Card #:  Those seem to be the beginning and end of each record.

For illustration, let's assume each record was a different file, each field and value are on a single line, and that you have a database with fields that match the fields in the records..

$lines=fopen (record.txt);
// we've opened the record and read each line into an element in an array.
// $lines[0] is line 1, $lines[1] is line 2, etc.

//walk through the record line by line (each line contains a field: value)
for ($i=0;$i<count($lines);$i++) {

     // separate each line on the colon
    $parts=explode (": ",$lines[$i]);
    // now $parts[0] is what's before the colon, $parts[1] is what's after the colon

    //build up the sql

    if ($fields) {

        $fields .= ", " . trim($parts[0]);
        $values .= ", " . addslashes(trim($parts[1]));

        // trim just removes whitespace from the beginning and end
        // addslashes escapes certain characters that ruin an SQL statement (like quotes).
        // We're -assuming- perfect input here (a mythological beast) just for illustration.
        // You should add other error checks for the values.  This is rather important.

    } else {

        // the first ones don't need a comma
        $fields  =    trim($parts[0]);
        $values.=  addslashes(trim($parts[1]));

    } // end if

} // end for

$sql="INSERT INTO
$mydatabase
($fields) VALUES ($values)";

$result=mysql_query($sql);

// do your MySQL error checking here, etc.


randy perry wrote:

> Yeah, I could post this to a SQL group, but I trust the noodledge in
> this group. (Besides, I don't belong to any SQL list).
>
> We need to be able to parse text files similar to the following:
>
> <text data>
> Judgment Spoiler List
> Wizards of the Coast, Inc.
> (Released May 21, 2002)
>
> 143 Cards Total
> Color: 16 Black, 26 Blue, 33 Green, 27 Red, 33 White, 3 Land,
> 4 Multicolored
>
> Rarity: 55 Common, 44 Uncommon, 44 Rare
>
> Card Name:      Ancestor's Chosen
> Card Color:     W
> Mana Cost:      5WW
> Type & Class:   Creature - Cleric
> Pow/Tou:        4/4
> Card Text:      "First strike When Ancestor's Chosen comes into play,
>                 you gain 1 life for each card in your graveyard."
> Flavor Text:    Empowered by generations of strength.
> Artist:         Pete Venters
> Rarity:         U
> Card #:         1/143
>
> Card Name:      Aven Warcraft
> Card Color:     W
> Mana Cost:      2W
> Type & Class:   Instant
> Pow/Tou:        n/a
> Card Text:      Creatures you control get +0/+2 until end of turn.
>                 Threshold - Creatures you control also gain protection
>                 from the color of your choice until end of turn. (You
>                 have threshold if seven or more cards are in your
>                 graveyard.)
> Flavor Text:    n/a
> Artist:         Roger Raupp
> Rarity:         U
> Card #:         2/143
> </text data>
>
> Ok, the tags aren't going to be there, just what is in between them.  I
> snipped it down to 2 to show you, but there are really hundreds.  There
> are also a lot of other lists that each have a hundred or so records.
>
> I know that it won't port directly, so I am looking for any solution of perl, php, whatever. The solution should format the text and precede it with insert into cards values(....whatever).  (php on webpage
> PHP is preferred, but I could set that up as a front end to any solution.
> Ideas??? Anyone?? Anyone?
>
> Randall Perry
>
> _______________________________________________
> Members mailing list
> Members@kalamazoolinux.org
>