jrp370 has asked for the wisdom of the Perl Monks concerning the following question:

im working with a database to do a simple phonebook most of functions work perfectly however im running into a problem with the add entry function it is intended to stop a user from entering the same name twice and it works fine while youre running the program however once you close the program down and start it up again it will let you enter in a name that is already in the database. can anyone point me in the right direction for modifying my code to prevent this?

sub addEntry {

    

               

    print"Please enter their name: \n";

    chomp (my $name = <STDIN>);

    $name = lc($name);

    if (exists $phonebook{$name}){

        print"$name is already in your phonebook\n";

    } else {

        print"Please enter their phone number (123 4567): \n";

        chomp (my $phone = <STDIN>);
        

        $phonebook{$name} = $phone;

        my $sth; # statement handle

        $sth = $dbh->prepare('INSERT INTO Phonebook (name, Phone)

                      VALUES (?, ?)'

                    )

                   or die "Can't prepare SQL: " . $dbh->errstr();

        $sth->execute("$name", "$phone")

                   or die "Can't execute SQL: " . $sth->errstr();

               $sth->finish();

        

    }

}

[download]

Replies are listed 'Best First'.
Re: working with a database
by GrandFather (Saint) on Feb 10, 2012 at 00:20 UTC
    "can anyone point me in the right direction for modifying my code to prevent this?"

    Probably not, because you don't show us enough code. How is %phonebook populated? My guess is that it gets updated each time a name is added, but that you don't populate it with the names already in the database.

    Note that it is often a dumb idea to have the same information in two places because if it gets out of sync you can end up with very hard to find bugs. The main exception is where you cache data for fast access, which may be the case here. However if the updates are infrequent I'd get rid of %phonebook and check the database first to see if the name is there already.

    True laziness is hard work
[reply]
Re: working with a database
by JavaFan (Canon) on Feb 10, 2012 at 00:22 UTC
    If you make name a primary key in your database, you cannot enter a second entry for the same name. Instead of querying the database, you can then just run the insert query, and inspect the error (if any). Or you could make your query: 
        -- MySQL syntax
    INSERT INTO Pnonebook (name, Phone)
    VALUES (?, ?)
    ON DUPLICATE KEY UPDATE Phone=VALUES(Phone)

    [download]
    which stores the most recent phone number.
[reply]
[d/l]
[select]
Re: working with a database
by Anonymous Monk on Feb 10, 2012 at 00:13 UTC
[reply]
[d/l]

Back to Seekers of Perl Wisdom