Displaying Data Two Mysql Tables

[badley]

I'm trying to display joined data from two tables. They are connected by a simple Product.ProviderID = ProductProvider.ID join. I can display a data from one table but how do I display columns from another table:

here is the sql:

drop table if exists Product;
create table Product
(
ID int unsigned not null auto_increment,
Name char(50) not null,
Description char(100) not null,
ProviderID int unsigned not null,
CategoryID int unsigned not null,
ClassID int unsigned not null,
EntryDate date not null,
Primary key (ID, ProviderID),
Index (ProviderID)

) type=innodb;

insert into product (id, name, description, providerid, categoryid, classid, entrydate) values (01, "Life Insurance 10", "Free for the first year", 01, 01, 01, "2005-01-05");
insert into product (id, name, description, providerid, categoryid, classid, entrydate) values (02, "Life Insurance 20", "Free for the first year", 01, 01, 01, "2005-01-05");
insert into product (id, name, description, providerid, categoryid, classid, entrydate) values (03, "Life Insurance 30", "Free for the first year", 01, 01, 01, "2005-01-05");
insert into product (id, name, description, providerid, categoryid, classid, entrydate) values (04, "Life Insurance 40", "Free for the first year", 01, 01, 01, "2005-01-05");
insert into product (id, name, description, providerid, categoryid, classid, entrydate) values (05, "Life Insurance 50", "Free for the first year", 01, 01, 01, "2005-01-05");
insert into product (id, name, description, providerid, categoryid, classid, entrydate) values (06, "Life Insurance 60", "Free for the first year", 01, 01, 01, "2005-01-05");
insert into product (id, name, description, providerid, categoryid, classid, entrydate) values (07, "Life Insurance 70", "Free for the first year", 01, 01, 01, "2005-01-05");
insert into product (id, name, description, providerid, categoryid, classid, entrydate) values (08, "Life Insurance 80", "Free for the first year", 01, 01, 01, "2005-01-05");
insert into product (id, name, description, providerid, categoryid, classid, entrydate) values (09, "Life Insurance 90", "Free for the first year", 01, 01, 01, "2005-01-05");
insert into product (id, name, description, providerid, categoryid, classid, entrydate) values (10, "Life Insurance 100", "Free for the first year", 01, 01, 01, "2005-01-05");
insert into product (id, name, description, providerid, categoryid, classid, entrydate) values (11, "Life Insurance 11", "Free for the first year", 02, 01, 01, "2005-01-06");

drop table if exists ProductProvider;
create table ProductProvider
(
ID int unsigned not null auto_increment primary key,
Name char(50) not null,
Address01 char(50) not null,
Address02 char(50) null,
Address03 char(50) null,
Address04 char(50) null,
County char(50) not null,
PostCode char(8) not null,
CountryID int not null,
AccountManagerID int not null references AccountManager.ID,
Index (ID)

) type=innodb;

insert into productprovider (id, name, address01, address02, address03, address04, county, postcode, countryid, accountmanagerid) values (01, "Norwich Union", "1 Sprout Square", "Norwich", null, null, "Norfolk", "NR1 8AD", 01, 01);
insert into productprovider (id, name, address01, address02, address03, address04, county, postcode, countryid, accountmanagerid) values (02, "Standard Life", "1 Green Road", "Norwich", null, null, "Norfolk", "NR1 8AD", 01, 02);



I have just the 'Product' access table here:

<?php require ('connect.php');

$Product = 'select * from product';
$result = $db->query($Product);

$num_results = $result->num_rows;
echo '<p> Number of Products found : ' .$num_results. '</p>' ;
?>
<table border = "1">
<tr>
<td>ID</td>
<td>Name</td>
<td>Description</td>
<td>Provider ID</td>
<td>Category ID</td>
<td>Class ID</td>
<td>Entry Date</td>
</tr>
<?php for ($i=0; $i <$num_results; $i++)
{
$row = $result->fetch_assoc();
?>
<tr>
<td><?php echo ($row[ 'ID' ]);?></td>
<td><?php echo ($row[ 'Name' ]);?></td>
<td><?php echo ($row[ 'Description' ]);?></td>
<td><?php echo ($row[ 'ProviderID' ]);?></td>
<td><?php echo ($row[ 'CategoryID' ]);?></td>
<td><?php echo ($row[ 'ClassID' ]);?></td>
<td><?php echo ($row[ 'EntryDate' ]);?></td>
</tr>
<?php
}
}
$result->free();
$db->close();?>

How do I access both tables in PHP?

Cheers

 

[Chacalinc]

Ejample:

select u.user_name, u.address, p.purchase_order from user u, purchase p ...

where purchase and user are 2 tables from the same database.

Comprendez vouz?

cheers.

 

[badley]

ok so how would I write it in the php table?

for example (without the other stuff):

select product.id, product.providerid, productprovider.name from product, productprovider
where product.providerid = productprovider.id

<?php echo ($row[ 'Product.ID' ]};?>
<?php echo ($row[ 'Product.ProviderID' ]};?>
<?php echo ($row[ 'ProductProvider.Name' ]};?>

 

[Chacalinc]

select product.id, product.providerid, productprovider.name from product, productprovider
where product.providerid = productprovider.id

should be:

select pd.id, pd.providerid, ppv.name from product pd, productprovider ppv
where pd.providerid = ppv.id

... then
<?php echo ($row[ 'pd.ID' ]};?>
<?php echo ($row[ 'pd.ProviderID' ]};?>
<?php echo ($row[ 'pd.Name' ]};?>

 

[Chacalinc]

Anyway, there are better solutions with join or something more else... take a look to mysql documentation in

http://www.mysql.com

or ask in the MySQL forum. This question is more related to mysql than php.

Cheers.

 

[badley]

Yeah I know, I've just started learning, as you can probably tell.

 

[badley]

Yeah I know, I've just started learning, as you can probably tell.

Thanks for the help though

 

[Chacalinc]

jeje, don't worry, I'm not a strong mysql'er, but I can do something.

How are you going so far?

 

[Chacalinc]

jeje = hehe ( =laughter, I speak spanish).

 

[badley]

So does it only work with aliases, pd rather than product?

 

[Chacalinc]

Yes, you need to put an alias (as I know, someone can correct me pls)

 

[badley]

Mmm webpage coming up with:

Undefined index: p.name

From:

<?php require ('connect.php');

$query= 'select p.name from product as p';

$result = $db->query($query);

$num_results = $result->num_rows;
echo '<p> Number of Products found : ' .$num_results. '</p>' ;

?>
<table border = "1">
<tr>
<td>Product Name</td>
<td>Product Provider</td>
</tr>
<?php for ($i=0; $i <$num_results; $i++)
{
$row = $result->fetch_assoc();
?>
<tr>
<td><?php echo ($row[ 'p.name' ]);?></td>
</tr>
<?php
}
$result->free();
$db->close();?>


'select p.name from product as p' works fine in MySQL

 

[badley]

The error occurs in the html table <?php echo ($row[ 'p.name' ]);?>

 

[badley]

Ha ha it works. Thank you all!!!!

So how come I can't use p.name in the variable?