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_ass oc($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: (tblClassificat ion)
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($_GE T['brand']);
// setup you MySQL
// select all products from requested brand
$sql="SELECT * FROM table_name WHERE brand_name='$br and'";
// execute statement
// retrieve and display all rows from result set
}
?>[/php]Ronald