Querying multiple tables

[pete317]

Hi,

 

I'm very new to PHP and have managed to get through most of the problems I've encountered so far, but I have got to one point that I can't seem to solve.

 

I have two mysql tables (Categories & Products), I have a page that shows the categories (generated from the categories table) and when a category is selected it lists the products in that category.

 

Due to stock etc. not all categories always have products associated to them.

 

The Category & Product Tables are automatically updated, so what I need to do is only show categories that have products associated to them, both tables have a column category_no, and nothing I've tried so far seems to work.

 

The code I have so far is:-

 

Require_once('config.php');

Mysql_connect(db_host, db_user, db_password);

@mysql_select_db(db_database) or die( "unable to select database");

$query="select * from categories";

$result=mysql_query($query);

$num=mysql_numrows($result);

Mysql_close();

Thanks

 

Pete

[Maq]

How do you know the query doesn't work?  You don't output anything.

 

(In the future, please use

tags around your code)

[Pikachu2000]

If all you need is a count, a SELECT COUNT() query against an indexed field  is much more efficient than selecting every field of every record like you're doing. If you must do it that way however, the function is mysql_num_rows(), not mysql_numrows().

[MatthewJ]

Are you thinking something like

 

SELECT categories.*, products.* FROM categories JOIN products ON categories.id = products.id WHERE products.numberinstockfield > 0

 

I'm sure there are ways to improve that, I just wanted to give you the idea.

[pete317]

MAQ - sorry, first post, didn't realize.  The Query does work, $num is then used for the output and what's displayed, filtering what's displayed is what I need to do..

 

Pikachu2000 - As I said, I'm very new to this and that may work, but it means nothing to me???

 

MatthewJ - I'll have a try at that and see how it goes, thanks.

 

Pete

[pete317]

TBH what I need is,

 

If category_no in tbl_categories doesn't match with any category_no's in tbl_products then remove from results.

 

I just cant work out how to do it.

 

Pete

[Maq]

If category_no in tbl_categories doesn't match with any category_no's in tbl_products then remove from results.

Then only match on that condition.  Something like:

SELECT * FROM tbl_categories tc LEFT JOIN tbl_products tp ON [whatever_field(s)_you_join_on] WHERE tc.category_no = tp.category_no

[pete317]

If category_no in tbl_categories doesn't match with any category_no's in tbl_products then remove from results.

Then only match on that condition.  Something like:

SELECT * FROM tbl_categories tc LEFT JOIN tbl_products tp ON [whatever_field(s)_you_join_on] WHERE tc.category_no = tp.category_no

 

Thanks, I'll have a play with that tomorrow and let you know how it goes.

 

Pete