PHP MySQL two table question...

[dr9553]

Here is my dilemma. I have two tables that i am wanting to read data from.

Table 1:
product_id
product_name
classification_id

Table 2:
classification_id
classification_name

What i want to be able to do is list all of the products in Table 1 but have the classification_name from Table 2 be used instead of the classification_id from Table 1.

As of right now i have it so it lists the classification_id for each product, but when i try to list the products with the classification_name it just shows the first classification_name for all of the products regardless of their classification_id

[PHP]<?php do { ?>
<tr align="left">
<td><!--//<?php echo $row_products['product_id']; ?>//-->
<?php echo $row_products['product_name'] ; ?>
</td>
<td align="center" valign="middle">
<?php echo $row_products['classification_id'] ; ?>

</td>
<?php } while ($row_products = mysql_fetch_assoc($products)); ?>[/PHP]

that is what i have so far. can anyone help me?

[harshmaul]

you need an inner join my friend....

run this query...

Table 1: (tblProducts)
product_id
product_name
classification_id

Table 2: (tblClassification)
classification_id
classification_name

select tblProducts.product_id, tblProducts.product_name, tblClassification.classification_name
From tblProducts
inner join tblClassification on tblProducts.classification_id = tblClassification.classification_id
Where 1=1

I hope that helps

[dr9553]

Ok but what do i put in my do while loop to have it display the correct information. Sorry i am new to some of this stuff.

[dheeraj857]

You can try this way .

 
<?php
        $sql = "select tblProducts.product_id, tblProducts.product_name, tblClassification.classification_name From tblProducts inner join tblClassification on tblProducts.classification_id = tblClassification.classification_id Where 1=1";
 
        $result = mysql_query($sql);
        while($row = mysql_fetch_assoc($result)){
            echo $row['product_name']."<br>";
            echo $row['classification_name'];
        }
      ?>
 

[dr9553]

OMG! You are a genius! thank you so very very much for the help. I was ready to punch my monitor on friday cause nothing i tried was working!

THANK YOU!

Donald

[dr9553]

i even got it working for another category too! finally im learning something!

[dr9553]

Ok one last little thing with this. I have a drop down that i want to be able to select a brand from. this brand would then give me the list of products that i was asking about earlier. So when you select your brand this is the code i have:

[PHP]<?php if(isset($_GET['brand_id'])){ ?>[/PHP]
that then creates the table with all of the product data in it.

Right now the table that is created contains all of the products (which was what i was asking how to do before) but now i can not figure out how to get it to only list the products belonging to that brand.

Can anyone help. Also thank you again for all the help i have already recieved!

[ronverdonk]

If the brand name is in your product table:[php]
<?php
if(isset($_GET['brand_id'])) {
$brand = strip_tags($_GET['brand']);
// setup you MySQL
// select all products from requested brand
$sql="SELECT * FROM table_name WHERE brand_name='$brand'";
// execute statement
// retrieve and display all rows from result set
}
?>[/php]Ronald