Page 1 of 1

Echoing out data from a different table?

Posted: Tue Dec 01, 2009 3:28 pm
by kraft_dinner
In my database in MyPHPAdmin, I have data that is identified with ID numbers through seperate tables, in my case my the column in my table is called 'Company' and it then leads through the 'tbl_Company' and all the proper data along with its corresponding ID is stored in that table.

When I echo out the data however, it shows up as it's ID number.

When i call out the table it looks like this:

Code: Select all

    
$query = "SELECT * FROM tbl_merchandise";
    $result = mysqli_query($dbc, $query);
 
and these are what my echos look like, in my case Company and Type are appearing as their ID numbers:

Code: Select all

    
 
while ($row = mysqli_fetch_assoc($result)) {
        echo ('<a href="'. $row['Product_URL'] . '">' . '<img src=' . '"' . $row["Thumb_URL"] . '"' . '">' . '</a>' . '<br />');
        echo $row["Name"] , ('<br />');
        echo ('by: ') , $row["Company"] , ('<br />');
        echo $row['Type'] , ('<br />');
        echo ('In Stock: ') , $row["Stock"] , ('<br />');
        echo $row["Price"] , (' CDN'), ('<br />');
        echo ('<br />');
What I'd like to know is how do i have that data show up as the data from the external table "tbl_Company' rather than the "tbl_merchandise"

Many Thanks!
Should i be putting another level

Re: Echoing out data from a different table?

Posted: Tue Dec 01, 2009 4:14 pm
by AlanG
Took a few attempts to try and understand the problem there. lol I think the problem was "and it then leads through the". Probably should phrase it as "and it relates to the". :) (Assuming you have a relationship set up.) If the below solution doesn't work, post the SQL code of the two tables.

There are 2 solutions. You can set up a relationship and grab all the data you want in the one query. Or you can do what you are doing and fetch the data from one table, you can then use the id's in the result to query the database a second time and return the data you need. I'll show you the first solution, while it is longer, in my opinion it is the better way to do it.

Ok, so what I gather from your post is the following:

Note: * denotes a foreign key, underline denotes a primary key, { } denotes an assumption by me

tbl_Merchandise (Product_URL, Thumb_URL, Name, Company*, Type, Stock, Price)
tbl_Company ({company_id}, {Name}, {Type})
There's not enough information to provide a working solution, so i'll fill in the blanks and you can substitute them with your own values yourself.

Note: you can actually remove the Type field from the tbl_Merchandise as the relationship can be used to fetch it through the Company field.

----------
Step one: Setting up a relationship.
If you have a relationship already set up then you can skip this section. You will need to set the Company field as an index to allow it to participate in a relationship. Both fields will have to be of the same data type (eg. varchar, int, mediumint etc...). Also, you will need to change the table storage engine to INNODB. (The MyISAM storage engine doesn't support relationships).

Add the Company index to the tbl_merchandise table:

Code: Select all

Alter TABLE `tbl_merchandise` ADD INDEX ( `Company` );
Set up the relationship (assuming the primary key (tbl_company) is `company_id`):

Code: Select all

ALTER TABLE `tbl_merchandise` ADD FOREIGN KEY ( `Company` ) REFERENCES `tbl_company`.`relationship` (`company_id`) ON DELETE NO ACTION ON UPDATE CASCADE;
That's the relationship taken care of.

Step 2: Querying the relationship

Code: Select all

 
$query = "SELECT m.Product_Url AS Product_URL, m.Thumb_Url AS Thumb_Url, m.Name AS Name, c.Name AS Company, c.Type AS Type, m.Stock As Stock, m.Price AS Price
              FROM tbl_merchandise AS m, tbl_company AS c
              WHERE tbl_merchandise.Company = tbl_company.company_id";
 
$result = mysqli_query($dbc, $query);
 
Substituting that query with your own should allow the other code to work without any updates.