Associate 2 table values in perl

Question

Hi,

I have 2 tables in mysql database: CUSTOMER and GROUP

The CUSTOMER table:

  NAME |PHONE
  A    |222
  B    |333 
  C    |777
  D    |888
  E    |111
  F    |555

and so on.

The GROUP table has only 3 value:

  GN   | NUM
  NEW  |807
  OLD  |455 
  INT  |504

I would like to get the following result:

  A, NEW, 807
  B, OLD, 455
  C, INT, 504
  D, NEW, 807
  E, OLD, 455
  F, INT, 504

and so on..

The GROUP table must repeat until the end of CUSTOMER table.

Here is my code:

#!/usr/bin/perl

# PERL MODULES
use DBI;
use strict;
use warnings;


# MYSQL CONFIG VARIABLES
my $dsn = 'DBI:mysql:test:127.0.0.1';
my $tablename = "CUSTOMER";
my $user = "root";
my $pw = "xxxx";
# DEFINE A MySQL QUERY
my $myquery1 = "SELECT  NAME FROM $tablename";

# PERL CONNECT()
my $dbh = DBI->connect($dsn, $user, $pw);
# EXECUTE THE QUERY
my $getname = $dbh->prepare($myquery1); 
$getnum->execute(); 

my $getlogin = $dbh->prepare("select * from GROUP"); 
$getlogin->execute();
my($login, $password);
# FETCHROW ARRAY
while (my $name = $getname->fetchrow_array()) {

    while (my @row = $getlogin->fetchrow_array()) {
     my ($gn,$num) = @row;
        $login=$gn;
        $password=$num;

        print "$name\t\t $login \t\t $password \n";

    }


} 

When i execute my code i get:

  A NEW 807
  B OLD 455
  C INT 504

  DBD::mysql::st fetchrow_array failed: fetch() without execute() at ./main.pl line 29.

How can i do this? Any help would be appreciated.

Answer 1

First of all, you have a typo -- you are doing $getnum->execute() not $getname->execute(). Did you really run the exact code you pasted?

You are encountering an error after the third iteration because you only have three rows of data in the GROUP table. You need to either start the loop again with a fresh query (perform the execute() inside the first while loop, just before you start the second), or cache all its data into an array that you can loop over repeatedly:

my $getname = $dbh->prepare($myquery1); 
my $getlogin = $dbh->prepare("select * from GROUP"); 

# FETCHROW ARRAY
$getname->execute(); 
while (my $name = $getname->fetchrow_array())
{
    $getlogin->execute();
    while (my @row = $getlogin->fetchrow_array())
    {
        my ($gn,$num) = @row;
        my $login=$gn;
        my $password=$num;

        print "$name\t\t $login \t\t $password \n";
    }
} 

Answer 2

The problem is that you're calling $getlogin->fetchrow_array after all elements are processed. This happens when CUSTOMER loop doing second iteration. You should call $getlogin->execute just in the start of CUSTOMER loop. Like this:

while (my $name = $getname->fetchrow_array()) {
    ## start new query each time we want to loop GROUP table
    my $getlogin = $dbh->prepare("select * from GROUP"); 
    $getlogin->execute();
    while (my @row = $getlogin->fetchrow_array()) {

But this can kill performance of the script. I suggest you to select all GROUPs before CUSTOMERs loop into array and use it instead of loading data from DB each iteration.

Answer 3

It sounds like you just want the rows in the CUSTOMER table to be assigned alternating values, rotating through the GROUP table -- and you don't much care who gets what value (or you would have put ORDERs into your SELECTs).

What I'd do is: add a column to the GROUP table with the values 0, 1 and 2; give the CUSTOMER table an incrementing id; and join them on (CUSTOMER.id % 3 = GROUP.id). That rotates the GROUP values down the CUSTOMER table in what I think is exactly the way you want.

ALTER TABLE `GROUP` ADD COLUMN id INT UNSIGNED NOT NULL, ADD INDEX idx_id (id);
UPDATE GROUP SET id=0 WHERE GN='NEW';
UPDATE GROUP SET id=1 WHERE GN='OLD';
UPDATE GROUP SET id=2 WHERE GN='INT';
ALTER TABLE `CUSTOMER` ADD COLUMN
    id INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT;

Then it's just one SELECT to get the pairings you want:

SELECT NAME, GN, NUM FROM `GROUP`, CUSTOMER WHERE GROUP.id = CUSTOMER.id % 3;

(P.S. I suggest not naming a table an SQL keyword like "GROUP", you'll have to quote it every time you use it.)