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

I have 2 questions

Q 1: I wanna check $username to make sure that it only contains letters and numbers and a underscore, anythig else giver error();

Q 2: Is there in way in SQL to select a row by random, so if it havd 1000 rows it picked row 200.

Replies are listed 'Best First'.
Re: Username regex, MYSQL random
by broquaint (Abbot) on Nov 23, 2002 at 15:36 UTC
    I wanna check $username to make sure that it only contains letters and numbers and a underscore, anythig else giver error(); 
    error() unless $username =~ m< ^ \w+ \z >x;

    [download]
    Is there in way in SQL to select a row by random, so if it havd 1000 rows it picked row 200.
    There isn't a generic way to pick a random row in MySQL, but if you've got a primary key which is an integer and you know the amount of rows you've got then you can just pick a random number and SELECT from that e.g 
    my $sth = $dbh->prepare(
               'SELECT some,rows FROM your_table WHERE id = ?'
          );
$sth->execute( 
    int rand $dbh->selectrow_array('SELECT MAX(*) FROM your_table')
);

    [download]

    HTH

    _________
    broquaint

    update: changed COUNT to MAX (thanks to UnderMine for that one)

[reply]
[d/l]
[select]
      Sorry to do this to you broquaint ;)

      @rand_row = $dbh->selectrow_array('SELECT some,rows, 
                                          rand() as orderno
                                     FROM your_table 
                                 ORDER BY orderno
                                    LIMIT 1');

      [download]
      Now that is nicer..

      Hope it helps
      UnderMine

[reply]
[d/l]
      thanks for your help
[reply]
Re: Username regex, MYSQL random
by Cody Pendant (Prior) on Nov 24, 2002 at 02:08 UTC
    Is there in way in SQL to select a row by random, so if it havd 1000 rows it picked row 200.

    There is, in MySQL, the option to ORDER BY RANDOM, but only in recent versions, from memory 3.23, because I tried it, got an error, and then found that my host was using 3.22.
    -- 
    ($_='jjjuuusssttt annootthheer
     pppeeerrrlll haaaccckkeer')=~y/a-z//s;print;

    [download]
[reply]
[d/l]
      That's the best way to do it, though it is platform dependent. 
      my $row = $dbh->selectrow_arrayref("
                      SELECT ...
                      FROM ...
                      WHERE ...
                      ORDER BY RANDOM
                      LIMIT 1");

      [download]
      That should return a single row as an array reference, chosen at random from all possible rows that meet your WHERE criteria.
[reply]
[d/l]
Re: Username regex, MYSQL random
by andrew (Acolyte) on Nov 23, 2002 at 19:19 UTC
    None of these work, how would i pull the information after I did it
[reply]
      This is based on a working example.... 
      use strict;
use DBI;

my $database='db';
my $username='user';
my $password='pass';

my $dbh=DBI->connect('DBI:mysql:database='.$database,$username,$passwo
+rd);
print 'dbh error' if (!$dbh);
my $sth=$dbh->prepare('SELECT *, 
                              rand() as orderno
                         FROM table_name
                     ORDER BY orderno
                        LIMIT 1');

print 'sth error' if (!$sth);
my $rv=$sth->execute();
print 'rv error' if (!$rv);

my @rand_row = $sth->fetchrow_array();
$sth->finish();

print join(',',@rand_row),"\n";

print "finished\n";

      [download]
      Replace the database, username, password (or for non mysql remove LIMIT 1 and change DBI driver name).
      Change table_name and you should change * to only the fields you need

      Hope this helps
      UnderMine Hope this helps

[reply]
[d/l]

Back to Seekers of Perl Wisdom