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;
    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') );

    HTH

    _________
    broquaint

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

      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');
      Now that is nicer..

      Hope it helps
      UnderMine

      thanks for your help
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;
      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");
      That should return a single row as an array reference, chosen at random from all possible rows that meet your WHERE criteria.
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
      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";
      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