Need help with array

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