By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,747 Members | 2,011 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,747 IT Pros & Developers. It's quick & easy.

PHP MySQL two table question...

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

Table 1:

Table 2:

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 align="center" valign="middle">
<?php echo $row_products['classification_id'] ; ?>

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

that is what i have so far. can anyone help me?
Feb 25 '08 #1
Share this Question
Share on Google+
7 Replies

Expert 100+
P: 490
you need an inner join my friend....

run this query...

Table 1: (tblProducts)

Table 2: (tblClassification)

Expand|Select|Wrap|Line Numbers
  1. select tblProducts.product_id, tblProducts.product_name, tblClassification.classification_name
  2. From tblProducts
  3. inner join tblClassification on tblProducts.classification_id = tblClassification.classification_id
  4. Where 1=1
I hope that helps
Feb 25 '08 #2

P: 5
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.
Feb 25 '08 #3

P: 14
You can try this way .
Expand|Select|Wrap|Line Numbers
  2. <?php
  3.         $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";
  5.         $result = mysql_query($sql);
  6.         while($row = mysql_fetch_assoc($result)){
  7.             echo $row['product_name']."<br>";
  8.             echo $row['classification_name'];
  9.         }
  10.       ?>
Feb 25 '08 #4

P: 5
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!


Feb 25 '08 #5

P: 5
i even got it working for another category too! finally im learning something!
Feb 25 '08 #6

P: 5
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!
Feb 25 '08 #7

Expert 2.5K+
P: 4,258
If the brand name is in your product table:[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
Feb 25 '08 #8

Post your reply

Sign in to post your reply or Sign up for a free account.