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

        $EMAIL1 = $q->param('Email1');
        $EMAIL2 = $q->param('Email2');        
        $EMAIL3 = $q->param('Email3');

        $sth = $dbh->prepare("select user from table1 where user = '$E
+MAIL1' or user = '$EMAIL2' or user = '$EMAIL3'");
        $rc = $sth->execute();
        while (my $row = $sth->fetchrow_hashref) {
        $GID = $$row{gid};
        $USER = $$row{user};
}

[download]

With the code above, I'm trying to check the table to see if any of the EMAIL names are available. I want to use the first EMAIL name that's available. How do I expand the code above to do that?

Edited by Corion : Added <CODE> tags.

Replies are listed 'Best First'.
Re: code help
by PsychoSpunk (Hermit) on Feb 06, 2001 at 05:35 UTC
[reply]
Re: code help
by runrig (Abbot) on Feb 06, 2001 at 05:38 UTC
    Why are you trying to do it in one select? Just (prepare one and) execute three selects and keep the first one which is not in the table. See the DBI docs (or Super Search this site) and use placeholders/bind values. Its as easy as:
    

my $new_name;
for my $name ($name1, $name2, $name3) {
 $new_name = $name;
 $sth->execute($name);
 $sth->fetch or last;
 $new_name = '';
}

print "New name: $new_name" if $new_name;

    [download]
[reply]
[d/l]
Re: code help
by dws (Chancellor) on Feb 06, 2001 at 06:29 UTC
    If your target RDBMS supports "union" queries, you can do this in a single query: 
    SELECT user FROM table1 WHERE user = ?
UNION
SELECT user FROM table1 WHERE user = ?
UNION
SELECT user FROM table1 WHERE user = ?

    [download]
[reply]
[d/l]
Re: code help
by kschwab (Vicar) on Feb 06, 2001 at 18:31 UTC
    Supposing this is a multi-user database, you'll have a race condition if you use the select statement to pick an unused email addr.

    Create a Used-Names table where EMAIL name is a unique index or constraint. Insert into the table in a loop where you are eval()ing $sth->execute() and checking DBI::err or DBI::errstr for your database vendor specific "duplicate key/unique field" error.

[reply]
Re: code help
by Anonymous Monk on Feb 06, 2001 at 09:59 UTC
    Sorry, I was trying to state the code tag. hehe :)
[reply]
Re: code help
by Anonymous Monk on Feb 06, 2001 at 09:57 UTC
    Yes, this is the same as "can I get a loop?". I previously tried to reply several time to the "can I get a loop?" but it didn't work. Anyway, I've used the <code> tags several times so I'll be sure to use them again on my next posting.

    Now, the database I'm using is MySQL. None of the replies to my first posting don't really help at all. I don't mean to sound angry or anything, but I'm really frustrated b/c I can't figure this one out :)

    Could someone expand on the code I've written so that it will grab the first EMAIL name available and then exit the routine? Or can someone tell me how to exit a routine safely?

    Thanks!

    Edit: 2001-03-03 by neshura

[reply]
A reply falls below the community's threshold of quality. You may see it by logging in.

Back to Seekers of Perl Wisdom