Need help with array
am 28.11.2007 19:55:55 von Armando Gomez
------=_NextPart_000_0009_01C831BE.03E95630
Content-Type: text/plain;
charset="us-ascii"
Content-Transfer-Encoding: 7bit
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";
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]");
}
But every time I try it gives my an error message
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
Table : 0002
0002 WAR STRATEGIC PHILO.
0002 WAR COUNTERMEASURES
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
Thanks
------=_NextPart_000_0009_01C831BE.03E95630--
Re: Need help with array
am 28.11.2007 20:03:09 von Baron Schwartz
Hi,
On Nov 28, 2007 1:55 PM, Armando Gomez wrote:
> 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";
>
> 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]");
>
>
>
> }
>
>
> But every time I try it gives my an error message
FYI, it would be a good idea to include the error message. I think
your error is probably a syntax error in the SQL.
> 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
>
>
> Table : 0002
>
> 0002 WAR STRATEGIC PHILO.
> 0002 WAR COUNTERMEASURES
>
>
> 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
MySQL doesn't understand SELECT ... INTO. Use INSERT INTO... SELECT instead.
--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org
Re: Need help with array
am 28.11.2007 21:11:21 von Greg Meckes
Fundamentally, you should join these tables into one query:
SELECT P.PAT, F.DEPT, F.COURSE FROM PATENTES P, FINAL F WHERE P.PAT = F.PAT
....so you have to only loop once. Let the database handle the hard work.
Try that.
Greg
--- Armando Gomez wrote:
> 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";
>
> 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]");
>
>
>
> }
>
>
> But every time I try it gives my an error message
>
> 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
>
>
> Table : 0002
>
> 0002 WAR STRATEGIC PHILO.
> 0002 WAR COUNTERMEASURES
>
>
> 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
>
>
> Thanks
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org
RE: Need help with array
am 29.11.2007 01:03:03 von Armando Gomez
Actuallly I dont try to join=20
I am using the first query=20
"SELECT DISTINCT pat from patentes" as a guideline to get all the data =
from
the second table, but I want that the data being extracted from the =
second
table be save as another table and the table name must be the same as =
the
field "pat" from patentes
Please check on the bottom there I put an example how I need these data
Thank you for you email=20
-----Mensaje original-----
De: Greg Meckes [mailto:gregmeckes@yahoo.com]=20
Enviado el: Mi=E9rcoles, 28 de Noviembre de 2007 02:11 p.m.
Para: Armando Gomez; perl@lists.mysql.com
Asunto: Re: Need help with array
Fundamentally, you should join these tables into one query:
SELECT P.PAT, F.DEPT, F.COURSE FROM PATENTES P, FINAL F WHERE P.PAT =3D =
F.PAT
....so you have to only loop once. Let the database handle the hard work.
Try that.
Greg
--- Armando Gomez wrote:
> Hello all
>=20
> =20
>=20
> =20
>=20
> I have a problem=20
>=20
> =20
>=20
> 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=20
>=20
> So I do a query like this one:
>=20
> =20
>=20
> =20
>=20
> my $dbh =3D =
DBI->connect("DBI:mysql:database=3Dproyecto;host=3Dlocalhost ",
>=20
> "root", "xyz123",
>=20
> {'RaiseError' =3D> 1});
>=20
> my $sth =3D $dbh->prepare("SELECT DISTINCT PAT FROM patentes");
>=20
> =20
>=20
> $sth->execute();
> while ((my @row) =3D $sth->fetchrow_array()) {
> print "$row[0]\n";
> =20
> So at this point I have all the patente's number on an array=20
> Now, the other table call final I have all the data I need to work, =
what I
> need is to get all the=20
> Data from final but I need to extract the data by pat (both tables =
have
the
> same field) and at the same time=20
> Create tables for each set of data that I get from using the the query
> something like this :
> =20
> =20
>=20
> =20
>=20
> my $dbh =3D =
DBI->connect("DBI:mysql:database=3Dproyecto;host=3Dlocalhost ",
>=20
> "root", "xyz123",
>=20
> {'RaiseError' =3D> 1});
>=20
> my $sth =3D $dbh->prepare("SELECT DISTINCT PAT FROM patentes");
>=20
> =20
>=20
> $sth->execute();
> while ((my @row) =3D $sth->fetchrow_array()) {
> print "$row[0]\n";
> =20
> =20
>=20
> my $a_dbh =3D =
DBI->connect("DBI:mysql:database=3Dproyecto;host=3Dlocalhost ",
>=20
> "root", "xyz123",
>=20
> {'RaiseError' =3D> 1});
>=20
> my $a_sth =3D $a_dbh->prepare("SELECT * INTO $row[0] FROM final=20
>=20
> WHERE pat =3D $row[0]");
>=20
> =20
> =20
> }
> =20
> =20
> But every time I try it gives my an error message=20
> =20
> Can anyone help me to fix the problem thank you
> =20
> =20
> =20
> This is an example of what I need
> =20
> Table : patente Table : Final=20
> =20
> 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
> =20
> So for pat 0001:
> =20
> I need to create a table contains this:
> =20
> Table : 0001
> =20
> 0001 SCIFI MATH ADVANCE
> 0001 SCIFI Quark study
> 0001 SCIFI TIME STUDY
> =20
> =20
> Table : 0002
> =20
> 0002 WAR STRATEGIC PHILO.
> 0002 WAR COUNTERMEASURES
> =20
> =20
> Table : 0003
> =20
> 0003 POLITICS DIPLOMATIC RELATIONS
> 0003 POLITICS ADDRESS STUDY
> =20
> =20
> =20
> So at the end I have all these tables in the database
> =20
> Patente
> Final
> 0001
> 0002
> 0003
> =20
> =20
> Thanks
> =20
> =20
> =20
>=20
> =20
>=20
> =20
>=20
> =20
>=20
> =20
>=20
> =20
>=20
> =20
>=20
>=20
--=20
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe:
http://lists.mysql.com/perl?unsub=3Darmando.gomez@aaahmt.com
--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=3Dgcdmp-msql-mysql-modules @m.gmane.org
Re: Need help with array
am 29.11.2007 03:38:57 von Baron Schwartz
I think what you're trying to do is divide a table into many tables.
The problem may be that you're trying to name the table as a number.
If you must name it that way, try quoting the table name as an
identifier:
INSERT INTO `1234`.....
Your problem is fundamentally a SQL syntax problem, not a Perl
problem. It may help you if you don't try to do it through Perl, and
test out your method by typing the SQL manually into a mysql client
until you find something that works as you wish.
On Nov 28, 2007 7:03 PM, Armando Gomez wrote:
> Actuallly I dont try to join
>
> I am using the first query
>
> "SELECT DISTINCT pat from patentes" as a guideline to get all the data fr=
om
> the second table, but I want that the data being extracted from the secon=
d
> table be save as another table and the table name must be the same as the
> field "pat" from patentes
>
> Please check on the bottom there I put an example how I need these data
>
>
> Thank you for you email
>
> -----Mensaje original-----
> De: Greg Meckes [mailto:gregmeckes@yahoo.com]
> Enviado el: Mi=E9rcoles, 28 de Noviembre de 2007 02:11 p.m.
> Para: Armando Gomez; perl@lists.mysql.com
> Asunto: Re: Need help with array
>
>
> Fundamentally, you should join these tables into one query:
> SELECT P.PAT, F.DEPT, F.COURSE FROM PATENTES P, FINAL F WHERE P.PAT =3D F=
..PAT
>
> ...so you have to only loop once. Let the database handle the hard work.
>
> Try that.
>
> Greg
> --- Armando Gomez wrote:
>
> > 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 =3D DBI->connect("DBI:mysql:database=3Dproyecto;host=3Dlocalhos=
t",
> >
> > "root", "xyz123",
> >
> > {'RaiseError' =3D> 1});
> >
> > my $sth =3D $dbh->prepare("SELECT DISTINCT PAT FROM patentes");
> >
> >
> >
> > $sth->execute();
> > while ((my @row) =3D $sth->fetchrow_array()) {
> > print "$row[0]\n";
> >
> > 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, wha=
t 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 =3D DBI->connect("DBI:mysql:database=3Dproyecto;host=3Dlocalhos=
t",
> >
> > "root", "xyz123",
> >
> > {'RaiseError' =3D> 1});
> >
> > my $sth =3D $dbh->prepare("SELECT DISTINCT PAT FROM patentes");
> >
> >
> >
> > $sth->execute();
> > while ((my @row) =3D $sth->fetchrow_array()) {
> > print "$row[0]\n";
> >
> >
> >
> > my $a_dbh =3D DBI->connect("DBI:mysql:database=3Dproyecto;host=3Dlocalh=
ost",
> >
> > "root", "xyz123",
> >
> > {'RaiseError' =3D> 1});
> >
> > my $a_sth =3D $a_dbh->prepare("SELECT * INTO $row[0] FROM final
> >
> > WHERE pat =3D $row[0]");
> >
> >
> >
> > }
> >
> >
> > But every time I try it gives my an error message
> >
> > 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
> >
> >
> > Table : 0002
> >
> > 0002 WAR STRATEGIC PHILO.
> > 0002 WAR COUNTERMEASURES
> >
> >
> > 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
> >
> >
> > Thanks
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
>
>
> --
> MySQL Perl Mailing List
> For list archives: http://lists.mysql.com/perl
> To unsubscribe:
> http://lists.mysql.com/perl?unsub=3Darmando.gomez@aaahmt.com
>
>
>
> --
> MySQL Perl Mailing List
> For list archives: http://lists.mysql.com/perl
> To unsubscribe: http://lists.mysql.com/perl?unsub=3Dbaron@xaprb.com
>
>
--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=3Dgcdmp-msql-mysql-modules @m.gmane.org