perl and mysql column

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

Hi all, I am struggling to write a program in which i have to read 2 cloumns of a table in MySql and then have to put the values of the 2 columns in 2 respective arrays. The tables looks like:

Column1      Column2
apple        banana;
cat          dog;
elephant     flag;
dog          home
banane       kite
 and so on....
[download]

i want to store apple, cat , elephant,dog, banane in one array say as @array1 and banana, dog, flag, home, kite in another array as @array2. Then I have to compare each element of array2 with array1 element. If they are equal, then have to store in a row. I want my output as

Column1      Column2       Column3
apple        banana        kite
cat          dog           home
elephant     flag
 and so on....
[download]

my code looks like..

my $sth = $dbh->prepare("INSERT INTO Test3 values (?,?)");
my $order1 = $dbh->prepare("SELECT Column1 FROM Test3 ORDER BY Column1
+");
$sth->execute($words[0],$words[1]) or die $DBI::errstr;
            $order1->execute();
while(@row = $sth->fetchrow_array())
{
print qw($row[0]\n$row[1]\n$row[2]\n);}

$sth->finish();
$order1->finish();
[download]

Also, how can I split a table(Table T1) having two columns(Column1 and Column2) into two table(T2 and T3) having one column each. i.e. T2 has Column1 and T3 has Column2. But am unable to get the desired output. Many thanks in advance..

Comment on perl and mysql column Select or Download Code

Replies are listed 'Best First'.
Re: perl and mysql column by ELISHEVA (Prior) on Mar 03, 2009 at 14:39 UTC
First, if you want to extract and compare two columns, your query needs to select two columns. Your query is only extracting column 1. Second, looking at your output, it appears that the problem is to follow a chain of match-ups. Thus if you find the pair "apple-bannana" you need to match it up with the pair "bannana-kite" - is that right? You can't just do this by printing out three columns, like you are doing. Instead you are going to need to organize your data to make it easy to take the value in column 2 and search for it in column 1. How to do that? You will need two steps: Convert the rows of the table into a hash When you are done, with the conversion, search the hash for matching pairs and print them out. A hash is kind of like two array columns - but it lets you search easily on one of the columns. For a tutorial, see Hash Crash Course This looks a lot like a homework problem, so I'm not going to write out the code for you. Read the tutorial and update your post to reflect what you've learned. If you are having trouble using hashes, I'm sure many monks will be eager to help you if they see some code that shows what you've tried to do. The code makes it easier for us to see what you are stuck on. Note: it is also possible that that this is not a Perl problem at all. This kind of match up can also be done using an SQL query: you need to alias your table and then join the column 1 of the table to column 2 of the aliased table. Select column 1 and 2 from the table and set column 3 to column 2 of the aliased table. I'll leave the actual construction of the query to you. For a brief tutorial on self joins, see SQL Join- Self-Join Best, beth Update: May not be a Perl problem at all	[reply]
Re^2: perl and mysql column by Illuminatus (Curate) on Mar 03, 2009 at 15:22 UTC
First, if you want to extract and compare two columns, your query needs to select two columns. Your query is only extracting column 1. It's worse than this. You define 2 SQL operations, an insert and a select. You are doing the fetchrow on the insert, which is unlikely to do much of anything. Sure sounds like homework to me. If you want help: use mysql cli to display the table you want to parse, and cut/paste that to your post supply code that actually runs (but may not be doing what you want), instead of a snippet that references undefines (ie, @word), and clearly does nothing. There are many, many ways to accomplish the comparison/sorting you want. If you don't even know how to go about figuring out common elements in 2 arrays, then your issue is foundational.	[reply]
Re: perl and mysql column by bradcathey (Prior) on Mar 03, 2009 at 14:17 UTC
Yeah, what are you looking for and what are you getting now? BTW, recommended reading (will save you some typing, at least). —Brad "The important work of moving the world forward does not wait to be done by perfect men." George Eliot	[reply]
Re^2: perl and mysql column by sharan (Acolyte) on Mar 03, 2009 at 14:36 UTC
I want my output in another table as: `Column1 Column2 Column3 apple banana kite cat dog home elephant flag and so on....` [download]	[reply] [d/l]
Re: perl and mysql column by toolic (Bishop) on Mar 03, 2009 at 14:11 UTC
But am unable to get the desired output You never specified what your desired output is, but I will guess that you want to print out the contents of the `@row` array. If that is the case, then you are misusing qw because it does not interpolate variables. Maybe you want something like this instead: `print "$_\n" for @row;` [download]	[reply] [d/l] [select]