Re: Yet Another Half Perl Half mySQL Question

I like creating multiple tables...


create table tbl_names (
id_name INT NOT NULL auto_increment,
str_name VARCHAR(255),
);

create table tbl_ipaddresses (
str_ipaddress VARCHAR(255),
id_name INT,
);
[download]

Then you can do schtuff like

@names = qw( firstname secondname );
@ips = ( "123.132.123.121" , "198.21.252.2" , "123.123.123.132"); 

$sql_statement = qq|INSERT INTO tbl_names (str_name) VALUES (?)|;
$sth = $dbh->prepare($sql_statement);

$sql_statement = qq|INSERT INTO tbl_ipaddresses (id_name, str_ipaddres
+s) VALUES (?,?)|;
$sth2 = $dbh->prepare($sql_statement);

foreach $name (@names){
    $rv = $sth->execute($name);
    my $id_name = $sth->{'insert_id'};
    
    foreach $ip (@ips){
        $rv = $sth2->execute($id_name, $ip);
    }
    
}
[download]

To get a name's worth of ips out you could then do..

$sql_statement = qq|select str_ipaddress 
    from tbl_ipaddresses, tbl_names
    where tbl_names.str_name = 'firstname'
    and tbl_names.id_name = tbl_ipaddresses.id_name|;
$sth = $dbh->prepare($sql_statement);
$rv = $sth->execute;

while (    ($ipaddress) = $sth->fetchrow_array ){
    # do whatever
}
[download]

EEjack

Comment on Re: Yet Another Half Perl Half mySQL Question Select or Download Code

Replies are listed 'Best First'.
Re: Re: Yet Another Half Perl Half mySQL Question by jreades (Friar) on Jul 06, 2001 at 22:08 UTC
This really isn't a great table design since you're not actually gaining anything by splitting the information this way. The lookups are slower than on a single table using a straighforward SELECT query along the lines of "SELECT ipaddress WHERE name=?", but you haven't gained any additional flexibility since everything is bound to the name_id., If you really want to use ids to track this information a better schema would be: `create table names ( name_id INT NOT NULL auto_increment, name_str VARCHAR(255) ); create table ipaddresses ( ipaddress_id INT NOT NULL auto_increment, ipaddress_value VARCHAT(255) ); create table ip_names ( name_id INT NOT NULL, ipaddress_id INT NOT NULL );` [download] This gives you a lot more flexibility -- reverse look ups on virtual hosts, for instance, and allows for extensibility at a later date (associating machine-specific information with an IP address id, and domain specific information with the name id).	[reply] [d/l]
Re: Re: Re: Yet Another Half Perl Half mySQL Question by eejack (Hermit) on Jul 06, 2001 at 23:15 UTC
I would tend to agree with you, but you really only need the two tables if the assumption is a one to many relationship. But assuming a many to many relationship your schema is nicer, and certainly if you expand the scope, having an id on both the ip address and name is a good thing (most probably a necessary thing). One minor point though, you can still do reverse lookups on my schema: `$sql_statement = qq\|select tbl_names.str_name from tbl_ipaddresses, tbl_names where tbl_ipaddresses.str_ipaddress = '201.2.23.2' and tbl_names.id_name = tbl_ipaddresses.id_name\|; $sth = $dbh->prepare($sql_statement); $rv = $sth->execute; while ( ($str_name) = $sth->fetchrow_array ){ # do whatever }` [download] But thanks for pointing out a nicer way of doing it than I did. Thanks, EEjack	[reply] [d/l]