I would suggest that you read some of the tutorials here on DBI:

• Reading from a database
• Tricks with DBI

I would also suggest that you look into using placeholders in your query. Right now, you're interpolating the strings '$username' etc. into your insert statement. While this will work most of the time, someone could enter "T'Pau" as a username, and your insertion statement would be:

...Values ('T'Pau'...
[download]

 
$query = qq(insert into members (username, password, email, name, url,
+ date) Values (?, ?, ?, ?, ?, ?));
$sth = $dbh->prepare($query);
$sth->execute($username, $password, $email, $name, $url, $time) or die
+ "Error in execution: $DBI::errstr";
[download]

stephen

You need to get the information out with a custom sql statement and put it in some kind of data structure if it exists. There are a lot of ways to do this. a quick example.

#!/usr/bin/perl -w

use strict;
use DBI;
#db connect
my $driver = 'mysql';
my $database = 'usersdb';
my $dsn = "DBI:$driver:database=$database";
my $dbh = DBI->connect($dsn, undef, undef);
my $user ="bob";
my $user_test = user_exists($user);
if (defined $user_test){
    print "user $user is already in our database";
}else{
    #code to insert user
}

$dbh->disconnect;

sub user_exists{
    my $id_val = shift;
    my $statement = "select user_id from users where user_id = $id_val
+";
    my $ary_ref = $dbh->selectall_arrayref($statement);
    if (defined $ary_ref->[0]->[0]){
        return $ary_ref->[0]->[0];
    }else{
        return undef;
    }
    
}
[download]

select count(*) from user_table where username = $username

is simpler if you ask me ... that way you only return a number instead of matching some kind of string or checking to see how many rows you've got.

-mr.dunstan

# This is untested -- no perl here
my $sth = $dbh->prepare("SELECT name FROM members WHERE username = ?")
+;

$sth->execute($username);  # this replaces the '?' above

if ($sth->rows != 0) {
  my $user = $sth->fetchrow_array;
  print "This username is in use by $user.  Try another username.\n";
} else {
  $sth = $dbh->prepare("INSERT INTO member (username, password, email,
+ name, url, date) VALUES (?, ?, ?, ?, ?, ?)");
  $sth->execute($username, $password, $email, $name, $url, $time);
  print "User Created Normally";
}
[download]

Now you might ask why I handle queries that way. I use question marks for two primary reasons. One, I don't have to worry about punctuation so much. This helps me to avoid bugs. Second, I do this so that I can define all my queries at the top of a subroutine ($sth_user, $sth_articles, etc). I might define more than one, because I might try to nest some of my queries within while loops and if statements. It helps me to maintain clean code in the long run. Your method isn't incorrect...just that I find my method more logical for what I do. (Proof that there is 100 ways to do 1 thing).

Hope it helps.

--Coplan

Be careful: in a multi-user database, someone can insert the row between the select and insert statements. A better way to do this is to combine the existence test with the insert:

my $sql = qq{
if not exists
    (select 1 from members where username = ?)
insert into members
    (username, password, email, name, url, date)
values (?, ?, ?, ?, ?, ?)
};
my $sth = $dbh->prepare($sql);
$sth->execute($username, $username,
    $password, $email, $name, $url, $date)
[download]

Note that $username appears twice, the first one to bind to the existence test. (BTW, I haven't tested this, and I'm assuming MySQL supports prepared exists clauses.)

I'm only really familiar with mySQL. But is that a mySQL specific command? Or is that common among SQL in general? I your reasoning for doing this completely. And now that I've heard your views on the issue, I'd like to implement such a feature into my coding as well. But I'd like to keep my code generalized should I need to change the SQL database in the future (maybe to postgres, or something).

Thanks,

--Coplan

$query = 'INSERT INTO members 
    (username, password, email, name, url, date) 
    Values (' . 
        $dbh->quote($username) ', ' . 
        $dbh->quote($password) . ', ' . 
        $dbh->quote($email) . ', ' . 
        $dbh->quote($name) . ', ' . 
        $dbh->quote($url) . ', ' . 
        $dbh->quote($time) . 
    ')' ;
[download]

"?"-placeholders are a lot cleaner, and use the same quote() internally.



$sth = $dbh->prepare('SOME QUERY WITH (' .
    $dbh->quote($foo) . ', ' .
    $dbh->quote($bar) .
    ')';
$sth->execute();

###

$sth = $dbh->prepare('SOME QUERY WITH (?, ?)');
$sth->execute($foo, $bar);

### And if you really, really want to quote() yourself,
## at least use join and map to make easy additions
# possible:

$sth = $dbh->prepare('SOME QUERY WITH (' .
    join(', ', map $dbh->quote($_),
        $username, $password, $email, $name, $url, $time
    ) . ');';

# But still, placeholders ARE better!
[download]

++ vs lbh qrpbqrq guvf hfvat n ge va Crey :)
Nabgure bar vs lbh qvq fb jvgubhg ernqvat n znahny svefg.
-- vs lbh hfrq OFQ pnrfne ;)
    - Whreq