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 = F.PAT

...so you have to only loop once. Let the database handle the hard work.

Try that.

Greg
--- Armando Gomez <[email protected]> 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/[email protected]