Beefy Boxes and Bandwidth Generously Provided by pair Networks Ovid
Keep It Simple, Stupid

Referencing MySQL Results by Column

by TheBiscuit (Initiate)
on May 26, 2010 at 18:08 UTC ( #841773=perlquestion: print w/ replies, xml ) Need Help??
TheBiscuit has asked for the wisdom of the Perl Monks concerning the following question:

I would like to reduce the number of MySQL queries executed by running a single SELECT statement then later referencing the results, preferably by a single column. Unfortunately, I'm not quite sure how and am hoping for a bit of help. The SELECT statement looks like this:

$sqlQuery = "SELECT username,email,phone FROM users"; $sqlResult = $connect->query($sqlQuery);

Is it possible to somehow reference a specific row by the username, to pull the email and phone values? If not, what is the best way to pull the specific values? This will be done within a foreach loop where the username is pulled from another source.


Comment on Referencing MySQL Results by Column
Download Code
Re: Referencing MySQL Results by Column
by halfcountplus (Hermit) on May 26, 2010 at 18:15 UTC
    This isn't at all a perl question, it's an SQL question. You should do a bit of reading about SQL syntax, there's tons of stuff online.

    Anyway, for example:
    $sqlQuery = "SELECT username,email,phone FROM users WHERE username='bo +b'";
    Always use the single quotes. WRT the perl API, you can also use placeholders like this:
    my $sql = $db->prepare("SELECT username,email,phone FROM users WHERE u +sername=?"); $sql->execute("bob");
    The point of using a placeholder is to prevent abuse of the single quote in SQL injection attacks on the database, if say "bob" where actually an $input and you are operating in an insecure environment.

      I apologize for not being clear enough, but this is a Perl question. The script I am trying to modify currently runs a SELECT query with a WHERE statement multiple times within a foreach loop. I would like to run the query once and pull the necessary data from an array instead. Below is the segement of code I'm working with:

      # Bind to LDAP Server $ldap = Net::LDAP->new( $ldapServer ) or die "$@"; $bind = $ldap->bind( $ldapUser, password => $ldapPass ); # Connect to SQL Server $connect = Mysql->connect($sqlServer, $sqlDB, $sqlUser, $sqlPass); $connect->selectdb($sqlDB); $ldapResult = $ldap->search( base => $ldapBaseOU, filter => "(&(usertype=Employee))", attrs => @ldapAttrs ); $sqlQuery = "SELECT username,email,phone FROM users"; $sqlResult = $connect->query($sqlQuery); print $sqlResult; my @entries = $ldapResult->entries; my $entry; foreach $entry ( @entries ) { my $username = $entry->get_value ( 'sAMAccountName' ); my $dn = $entry->dn; ### ***** Insert Match Here ***** ### $phone = ?? $email = ?? } # Unbind from LDAP Server $bind = $ldap->unbind;

      In the section noted as "***** Insert Match Here *****, I would like to be able to use the $username value pulled from LDAP and match it to the 'username' value pulled from MySQL to set the phone and email variables.


        Do not use Mysql, it is obsolete module and its own documentation recommends to use DBI. DBI has selectall_arrayref method which you can use to get all records from the table. If you want to be able to get phone and email by username you can store result into hash like this (not tested):

        my $res = $dbh->selectall_arrayref("SELECT username, phone, email FROM + users"); my %users = map { $_->[0] => [ $_->[1], $_->[2] ] } @$res; for (@usernames) { my ($phone, $email) = @{$users{$_}}; }

      Sorry, bad advise:

      1. "Always use single quotes" is too much work. There is no need to quote numbers, doing so makes the job harder for the database.
      2. "Always use single quotes" is just plain wrong. It makes people think "just add the magic quotes and everything will be well". Even with the quotes, the SQL is still vulnerable to SQL injection. You really want proper quoting here.
      3. While most databases use single quotes, some exotic ones may use other quotes. So, you first need to find out HOW to quote properly.

      Luckily, all this has already been done in DBI and the various DBDs, there is no need to reinvent the wheel. So, the last part should really read: "You SHOULD use placeholders". Unfortunately, DBI does not force you to use them, because that would be too much work. It really should, so you better read that advice as "You MUST use placeholders".

      Some technical background: DBI guarantees that you can use "?" placeholders with each and every database supported by DBI, even if the database itself does not support placeholders. DBI will automatically insert all bind values, properly quoted, into the SQL statement before it is passed to the database. Since most DBs do support placeholders, using them costs really nothing. Even better, because most DB APIs use seperate ways for the SQL statement and the bind values, there is absolutely no need to quote anything at the Perl or the DB API level. As a nice side effect, your code can work with every database that understands SQL (unless you use some DB-specific, non-standard SQL). And, for extra bonus points, the prepared SQL statement with placeholders can be cached.

      Imagine you need to run 20.000 queries (insert, select, update, whatever) against a database, that differ only in the values used. Using hardcoded SQL, you have to generate 20.000 SQL statements, pass them to the database, have the database parse them 20.000 times, and finally execute the one of the 20.000 parsed SQL statements, returning a result. With placeholders, you write ONE query containing placeholders, pass that query ONCE to the database, have the database parse that query ONCE, and finally you play ping-pong with the database: One set of values in, one result out, without any parsing or quoting, as fast as your Perl code can handle the data.


Re: Referencing MySQL Results by Column
by dineed (Scribe) on May 26, 2010 at 20:38 UTC

    I believe others have already given appropriate Perl advice. However, if you prefer not to select all rows from the table, which may make or may not make sense depending on the size of the table, you can use a for loop to build a list of user names, and then structure your SQL select statement to use "IN".

    $sqlQuery = "SELECT username,email,phone FROM users where username IN +("name1", "name2", ...)"; $sqlResult = $connect->query($sqlQuery);

    This should return all the rows you need and then you can process them as needed within your program.

    NOTE: I know the "IN" list capability exists in Oracle, so my suggestion is based on the assumption of similar functionality in MySQL.

Re: Referencing MySQL Results by Column
by CountZero (Chancellor) on May 27, 2010 at 06:14 UTC
    AS others already showed, it is possible to do so, but in most of the scenario's I can think of, this is not a good idea.

    • Unless you are guaranteed that the contents of the table will not change during the lifetime of your script, you are not going to pick up any changes to it and therefore may be working with stale data.
    • If the table is very big, you will download a lot of data to a variable structure in your script, of which you perhaps only use a very small part. This is wasteful of memory and will scale very badly.
    • Databases are made to be rather efficient to hand you back the answer to your queries. Over the aggregated lifetime of your script is likely that the database will do a better job than you can.



Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://841773]
Approved by Corion
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others scrutinizing the Monastery: (20)
As of 2014-03-13 18:38 GMT
Find Nodes?
    Voting Booth?

    Have you used a cryptocurrency?

    Results (289 votes), past polls