in reply to Re^2: Help with MySQL SELECT into multidimensional array
in thread Help with MySQL SELECT into multidimensional array

Well, see if you can get to "first base", connect to the DB and run the first query.

See some untested code below... Once you get connected and have a database handle, the sequence is : prepare query ->execute query -> retrieve results. There are several "flavors" of result retrieval. I show an easy way below.

I recommend the following book, what DB are you using?
Programming the Perl DBI, Database programming with Perl By Tim Bunce, Alligator Descartes.

use DBI;

my $database = 'somedatabase';  #your system specific
my $hostname = 'localhost';     ##your system specific
my $port     = 3306;            #default mysql port
my $user     = 'someuser';      #your system specific
my $password = 'somepassword';  #your system specific

my $dsn = "DBI:mysql:database=$database;host=$hostname;port=$port"
                or die "DB data set name failed $!\n";
my $dbh = DBI->connect($dsn, $user, $password, {RaiseError => 1}) 
                or die "DB connect failed $!\n";

my $query = $dbh->prepare("SELECT c2.id, c2.name as 'client' 
                          FROM client c2 
                          WHERE level = 50 and status = 1");
$query->execute();
while (my ($id, $name) = $query->fetchrow_array)
{
  print "$id \t $name\n";
}

__END__
To make a parameter a variable, use a placeholder:
my $query = $dbh->prepare("SELECT c2.id, c2.name as 'client' 
                          FROM client c2 
                          WHERE level = ? and status = ?");
$query->execute(50,1);

[download]
Update: added placeholder comment in code above. Also just tested a very similar query on my local MySQL installation. See if you can get this first part working.

Replies are listed 'Best First'.
Re^4: Help with MySQL SELECT into multidimensional array
by btongeorge (Initiate) on Dec 02, 2011 at 15:27 UTC

    Works like a charm, without any changes other than the obvious DB name/uid/pw etc. The code is far simpler than what I had previously cobbled together, for which I am thankful. I can read it now!

    Please excuse my ignorance but I don't understand the placeholder comment?

    Will look up the books mentioned.

[reply]
      In your statement, this is not needed, but I added this to just "show how its done". When you prepare the statement, you can put in a ? to mean that parameter will be supplied when you call execute.

      A complicated statement will take a while to prepare as the data base will figure out its strategy for executing it. You can keep "reusing" a prepared query with different values. This way of doing it is also better than interpolating a different Perl variable into the statement for security reasons. 

      To make a parameter a variable, use a placeholder:
my $query = $dbh->prepare("SELECT c2.id, c2.name as 'client' 
                          FROM client c2 
                          WHERE level = ? and status = ?");

my $level = 50;
$query->execute($level,1);
$level = 60;
$query->execute($level,1);

      [download]
      Update: you may find this feature useful in dealing with other of your queries. A common technique to run essentially the same query multiple times with different values is to create a data structure, an array, a hash table, etc and then make a loop to cycle through that structure, executing the query again and again. with different values. This can reduce the clutter of repeating the same SQL again and again.
[reply]
[d/l]

        Understood, thanks for the explanation. In this case I will need different queries for each column, but that's useful info nonetheless.

        The next step is for me to understand the best way to loop through the array and populate the next column - if I get that, I feel I can do the rest of the coding without too much more help - famous last words I know!

[reply]
Re^4: Help with MySQL SELECT into multidimensional array
by btongeorge (Initiate) on Dec 02, 2011 at 14:32 UTC
    Thanks Marshall, will give that a try. Am using MySQL on CentOS. We have an O'Reilly online subscription, can you recommend any books in their library at all?
[reply]
      Yes, Learning SQL by Alan Beaulieu,
      I can't find my copy at the moment, but if I remember right this book uses MySQL for the examples which would be perfect for you. Both of my recommendations are O'Reilly books.
[reply]

In Section Seekers of Perl Wisdom