Left Join

General PHP Help
#1

Ok, so I need to know how to query my database using php joining together some columns. Table “products” has 3 columns I need, product, category, subcategory. Table"categories" has 2 that I need, category, subcategory.
I would like for table “products” to display all the results for the specified category from “categories”

here is a visual representation

table"products"
product Category Subcategory
T-Shirt retail none
water retail none
uniform retail uniforms
seminar events none

table"categories"
category subcategory
Retail none
retail uniforms
events none

so when it displays, they will display in buttons
there will be two main category buttons: retail and events
then there is one subcategory for retail: uniforms, as the table shows
then there will be the two products that have a category of retail that show under the retail button along with the subcategory. then under the subcategory uniforms, the uniform would show
and under the category of events, seminar would show. so in the categories table, if the subcategory is none, it ignores it, but if the subcategory is not none, then it looks to the category as to where it should be placed. does this make some sense as to what I would like to do?

#2

ok, I can help but…

If table ‘products’ contains three columns products, category, subcategory, why do you need another table called ‘categories’ which contain the same info as in products?

I’ll admit I don’t see what you’re doing here but I’m going to jump off the pier and say…

This can be done in a single table. Build a table called ‘inventory’ (just to be different). Add the columns…

id, name, category, subcategory, on_hand, min, max, cost, price, supplier, description, imageid, rating

ok, I thought I’d add a boatload more columns, just for fun.

Then your query can be SELECT * FROM inventory WHERE category = ‘retail’ AND subcategory = ‘uniforms’

all rows that match that query will be returned. In your product table example, the row with the product name uniform would be returned.

The query SELECT * FROM inventory WHERE category = ‘events’ will return the row with the product name seminar.

and more for fun…

SELECT * FROM inventory WHERE supplier = ‘Acme’ AND cost < ‘9.95’

SELECT * FROM inventory WHERE on_hand < min (note: both are column names)

This could be used to generate a purchase order or simple auto order to replenish inventory.

Please let me know if I’m misinterpreting your question or purpose.

#3

my goal is that I can add categories and subcategories later, and they will be dynamically added. I don’t want to have to change the code for anyone that uses this system. I need it to be like select were category=“specified by code, not by me, category”

#4

Frankly, I didn’t understand your question and I didn’t understand your reply.

Both database design and complex querys are an art. Proper planning and implementation is crucial.

I understand you want to look up data from one table to look up data in another. With the information you have given I can’t help.

Anybody else?

#5


that is a link as to what I would like to make it look like. currently that uses spry tabbed panels that I have to manually put into the code myself. I would like all that to be able to be done dynamically by code. I am going to use javascript with show/hide functions with buttons instead of tabbed panels…does that make more sense hopefully…maybe the first post should be ignored…

#6

Ok, fuzzy as hell on this still, but let me take another stab at it…

Using your image as a reference; we will call the red row of items the ‘main menu’ and blue row of items the ‘submenu’ and the green rows ‘product’, for this discussion. I am assuming the main menu item ‘Retail’ is selected and the submenu items are categories of the products in product. The submenu item ‘Main Retail’ is also selected and the products shown all have the category Main Retail. I will further assume all menu and product items are clickable and some action is expected, when one is clicked. I’m going to narrow clickable actions to the submenu only for brevity, but I will explain how to dynamically build the submenu when the main menu item ‘Retail’ is clicked.

You don’t want to have users add categories, you want them to be able to add new products that have new categories, no? The category is associated with the product. Add a new product with a unique catagory, and you’ve just added a new category. Delete all products with a particular category and the category is deleted also.

Refer back to my previous post and see my description of a typical table I called inventory. Each product has one row on one table. ALL relevant criteria regarding that product is a distinct column in a single row.

Now let’s build the submenu…

$result = mysql_query(“SELECT category FROM inventory GROUP BY category”);

if (mysql_num_rows($result) > 0) {
while ($row = mysql_fetch_array($result)) {

$items[] = $row[0]; // $row[0] contains one category ie: Main Retail
// build one submenu item from each in the loop. Every submenu’s action should be to another mysql query to request all products that have the category associated with the submenu item.
}
}

// the submenu is built, one thing left to do!

showthegoods($items[0]’); // Display all products for the first submenu item…

And now…
each submenu item calls this function and passes it’s category to it.

function showthegoods($mycategory); {

$result = mysql_query(“SELECT * FROM inventory WHERE category = ‘$mycategory’”);
if (mysql_num_rows($result) > 0) {
while ($row = mysql_fetch_array($result)) {

// $row is an array of all columns in the table inventory for each product that matched
// build each product item here
}
}
}

Now, you will have to build an ‘Add Product’ page, and most likely a delete product page and an edit product page too.

All this was typed freehand and nothing was copied or pasted, don’t expect to copy and paste any of the above and have it work without a little cleanup. I’ve left the building of your menus and products for you to implement. There is much error checking and additional finesse that can and should be added also.

I really hope I got this right and this helped…

#7

just one question.
where would I put in the things to echo? I just want to make sure I get this right…

#8

so it is saying that the function needs to be called. then I put function infront of the show the goods, and it then says that it can’t have the $items[0] inside the function. what do I do about this? i don’t work with php functions often…

#9
where would I put in the things to echo? I just want to make sure I get this right....

I’ve put a comment in each while loop where you need to construct each of your submenu items and the product items.

so it is saying that the function needs to be called. then I put function infront of the show the goods, and it then says that it can't have the $items[0] inside the function. what do I do about this? i don't work with php functions often....

The function is called once after building the submenu to populate products from the first submenu item. It is also called each time you select (click) any submenu item to. Pass the function a category and it returns all products with that category.

#10

ok, thanks, I will try. I think I got the function figured out too :slight_smile:

#11

ok, here is what I have and it just keep returning array, array, array…

<?php $result = mysql_query("SELECT category FROM products GROUP BY category"); if (mysql_num_rows($result) > 0) { while ($row = mysql_fetch_array($result)) { $items[] = $row[0]; // $row[0] contains one category ie: Main Retail or if I set it to $row['category'], it does the same thing..... echo $items;} } // the submenu is built, one thing left to do! showthegoods($items[0]);// Display all products for the first submenu item... function showthegoods($mycategory) { $result = mysql_query("SELECT * FROM products WHERE category = '$mycategory'"); if (mysql_num_rows($result) > 0) { while ($row = mysql_fetch_array($result)) { $cat=$row['category']; echo $mycategorycat; } } } ?>
#12

$items IS and array! Use print_r $items; to ‘echo’ an array…

use $row[0] for the category…

if you want to use $items[$i], you will need to add a counter var…

#13

ok, thanks. I am not good at working with arrays, I know what they are, but I am not well at using them…

#14

I am getting this now:
Parse error: syntax error, unexpected T_VARIABLE in /home/tkdpost1/public_html/index2.php on line 44.

any help?

here is piece of code in question:

<?php $result = mysql_query("SELECT category FROM products GROUP BY category"); if (mysql_num_rows($result) > 0) { while ($row = mysql_fetch_array($result)) { $items[] = $row[0]; // $row[0] contains one category ie: Main Retail print_r $items;} }
#15

The error is most likely on line 44 but sometimes is on the previous line.

This is usually caused by garbage non-printing characters from a copy and paste job.

Try deleting every space between words and then add a space back to clean up. Remove any trailing spaces also and remember your semicolons…

#16

I tried that, and I am still getting the same error.

<?php $result = mysql_query("SELECT DISTINCT category FROM products"); if (mysql_num_rows($result) > 0){ while ($row = mysql_fetch_array($result)){ $items[]=$row[0]; print_r $items; } } ?>

the line is the print_r $items;
if I replace it with echo $items, it doesn’t give an error, but returns the array, array,…
would the print_r do this?

#17

I don’t know what line 44 is!

please supply lines 40 trough 45 so I can inspect.

This is a notorious error introduce by coping from a web page and pasting into your editor.

#18

Wait,wait,wait!

DON’T USE PRINT_R $items!!!

What I said was…

use $row[0] for the category...
#19

I will try that, I think I already did, but I will try again. thanks for bearing with me on this…

#20

I don’t mind leading someone to a solution, but I prefer they walk the last mile alone. Just so they learn something. This would have been a shorter exercise for me if I’d written the entire thing for you…

Here you go…

<?php $result = mysql_query("SELECT DISTINCT category FROM products"); if (mysql_num_rows($result) > 0){ while ($row = mysql_fetch_array($result)){ $items[]=$row[0]; echo ''; } } ?>
Sponsor our Newsletter | Privacy Policy | Terms of Service