comment on

Hello all I have a problem I have 2 tables on MySQL in the first one call patentes I just need to get the patente’s number that’s a field call pat So I do a query like this one:

my $dbh = DBI->connect("DBI:mysql:database=proyecto;host=localhost",
                         "root", "xyz123",
                         {'RaiseError' => 1});
   my $sth = $dbh->prepare("SELECT DISTINCT PAT FROM patentes");

$sth->execute();
   while ((my @row) = $sth->fetchrow_array()) {
   print "$row[0]\n";
[download]

So at this point I have all the patente’s number on an array Now, the other table call final I have all the data I need to work, what I need is to get all the Data from final but I need to extract the data by pat (both tables have the same field) and at the same time Create tables for each set of data that I get from using the the query something like this :

my $dbh = DBI->connect("DBI:mysql:database=proyecto;host=localhost",
                         "root", "xyz123",
                         {'RaiseError' => 1});
   my $sth = $dbh->prepare("SELECT DISTINCT PAT FROM patentes");

$sth->execute();
   while ((my @row) = $sth->fetchrow_array()) {
   print "$row[0]\n";


my $a_dbh = DBI->connect("DBI:mysql:database=proyecto;host=localhost",
                         "root", "xyz123",
                         {'RaiseError' => 1});
   my $a_sth = $a_dbh->prepare("SELECT * INTO $row[0] FROM final 
                                         WHERE pat = $row[0]");


}
[download]

But every time I try it gives my an error message this one :

DBD::mysql::st execute failed: You have an error in your SQL syntax; c
+heck the manual that corresponds to your MySQL server version for the
+ right syntax to use near '0447 from final
                                          where pat = 0447' at line 1 
+at finaltest.pl line 19.
DBD::mysql::st execute failed: You have an error in your SQL syntax; c
+heck the manual that corresponds to your MySQL server version for the
+ right syntax to use near '0447 from final
                                          where pat = 0447' at line 1 
+at finaltest.pl line 19.
[download]

Can anyone help me to fix the problem thank you This is an example of what I need

Table :  patente               Table : Final 

Pat    Name                    pat     dept      course
0001   John                    0001    SCIFI     MATH ADVANCE
0002   Jack                    0001    SCIFI     Quark study
0003   Peter                   0001    SCIFI     TIME STUDY
                               0002    WAR       STRATEGIC PHILO.
                               0002    WAR       COUNTERMEASURES
                               0003    POLITICS  DIPLOMATIC RELATIONS
                               0003    POLITICS  ADDRESS STUDY

So for pat 0001:

I need to create a table contains this:

Table : 0001
 
0001    SCIFI     MATH ADVANCE
0001    SCIFI     Quark study
0001    SCIFI     TIME STUDY

and likewise with this
Table : 0002

0002    WAR       STRATEGIC PHILO.
0002    WAR       COUNTERMEASURES

and ofcourse 
Table : 0003

0003    POLITICS  DIPLOMATIC RELATIONS
0003    POLITICS  ADDRESS STUDY



So at the end I have all these tables in the database


Patente
Final
0001
0002
0003
[download]

I am not trying to JOIN, the field pat from patentes is the guideline to get all the data from FINAL and to name the new tables created. Thanks

In reply to Trouble with syntax with SELECT by ArmandoG

Title:
Use: <p> text here (a paragraph) </p>
and: <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":

Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!

Titles consisting of a single word are discouraged, and in most cases are disallowed outright.

Read Where should I post X? if you're not absolutely sure you're posting in the right place.

Please read these before you post! —

How do I compose an effective node title?

How do I post a question effectively?

Markup in the Monastery

Posts may use any of the Perl Monks Approved HTML tags:
a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr

You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)

For: Use:

& &

< <

> >

[ [

] ]

Link using PerlMonks shortcuts! What shortcuts can I use for linking?

See Writeup Formatting Tips and other pages linked from there for more info.