Trouble with syntax with SELECT

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

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
Comment on Trouble with syntax with SELECT
Select or Download Code

Replies are listed 'Best First'.
Re: Trouble with syntax with SELECT by tuxz0r (Pilgrim) on Nov 29, 2007 at 00:57 UTC
First, I don't think MySQL lets you do a "SELECT * FROM A INTO B", though you can do: `INSERT INTO A (col1,col2,col3) SELECT fld1,fld2,fld3 FROM B;` [download] Also, you only need one database connection to run both those queries since they both act on the same database. And, I would use $row[0] a bit differently, `my @pats = (); 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) { push @pats, $row[0]; } foreach (@pats) { # TODO: CREATE TABLE NAMED $_ HERE ? $dbh->do("INSERT INTO $_(col1,col2,col3) SELECT fld1,fld2,fld3 FR +OM patente WHERE pat = $_"); }` [download] However, from your post, it sounds like you'll also need to run a `CREATE` statement for each table in `@pats`, unless they already exist. --- `echo S 1 [ Y V U \| perl -ane 'print reverse map { $_ = chr(ord($_)-1) } @F;'` Warning: Any code posted by tuxz0r is untested, unless otherwise stated, and is used at your own risk.	[reply] [d/l] [select]
Re: Trouble with syntax with SELECT by nedals (Deacon) on Nov 29, 2007 at 00:38 UTC
You only need to connect to the database once. Your code will attempt to reconnect each time through the loop my $dbh = DBI->connect("DBI:mysql:database=proyecto;host=localhost", " +root", "xyz123", {'RaiseError' => 1}); # Pre-prepare using placeholders my $sth_a = $dbh->prepare("SELECT dept, course FROM final WHERE pat=?" +); my $sth = $dbh->prepare("SELECT DISTINCT PAT FROM patentes"); $sth->e +xecute(); while (my ($pat) = $sth->fetchrow_array()) { # You don't need an arra +y here $sth_a->execute($pat); while (my ($dept,$course) = $sth_a->fetchrow_array()) { print "$pat, $dept, $course\n"; # Here you can create and populate your new tables as needed } } $sth->finish() $sth_a-finish(); [download] Update Don't forget to check whether or not your new table exists in that inner loop. You don't want to repeatedly re-create the tables.	[reply] [d/l]