Help with MySQL JOIN please.

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
ianhull
Forum Contributor
Posts: 310
Joined: Tue Jun 14, 2005 10:04 am
Location: Hull England UK

Help with MySQL JOIN please.

Post by ianhull »

Hi all, I am trying tolink sub categories with master categories with just one query.

my table structure is.

categoryID | masterCategoryID | categoryName | categoryLink

I would like to select all categories and sub categories and join them somehow.
I know it can be done, i just don't know how and i can't find any good examples on the net.

thanks in advance.
ianhull
Forum Contributor
Posts: 310
Joined: Tue Jun 14, 2005 10:04 am
Location: Hull England UK

Re: Help with MySQL JOIN please.

Post by ianhull »

Code: Select all

 
 
$q = mysql_query("SELECT
        categories.masterCategoryID,
        categories.categoryID,
        categories.categoryName,
        categories.categoryLink,
 
        (SELECT count(*) FROM items
        WHERE items.categoryID = categories.categoryID 
        ) AS itemCount
        
        (SELECT
        masterCategoryID AS subMasterCategoryID,
        categoryID AS subCategoryID,
        categoryName AS subCategoryName,
        categoryLink AS subCategoryLink FROM categories
        WHERE subMasterCategoryID = categories.categoryID 
        ) AS subCategories
        
        FROM categories ORDER BY
        categories.categoryName ASC")or die(mysql_error());
 
 
is what i have tried but i cant get it to work.

i need to join them somehow.

please help
User avatar
AbraCadaver
DevNet Master
Posts: 2572
Joined: Mon Feb 24, 2003 10:12 am
Location: The Republic of Texas
Contact:

Re: Help with MySQL JOIN please.

Post by AbraCadaver »

What do you mean by join them somehow? What is the desired result? Your second post shows 2 different tables in your query, not just the 1 categories table.
mysql_function(): WARNING: This extension is deprecated as of PHP 5.5.0, and will be removed in the future. Instead, the MySQLi or PDO_MySQLextension should be used. See also MySQL: choosing an API guide and related FAQ for more information.
User avatar
social_experiment
DevNet Master
Posts: 2793
Joined: Sun Feb 15, 2009 11:08 am
Location: .za

Re: Help with MySQL JOIN please.

Post by social_experiment »

From the code you posted let's assume you want to use 2 tables (you can't join one table). To inner join them you need a field from each table that will have a similar value . For the example i used 'categoryid'.

Code: Select all

 
<?php
 $ijq = mysql_query("SELECT * FROM item INNER JOIN categories WHERE categories.categoryid = item.categoryid")
  
  /*
  $query = mysql_query("SELECT * FROM table1 INNER JOIN table2 WHERE table2.fieldWithValue = table1.fieldWithValue")
 */
?>
 
Hope this helps
“Don’t worry if it doesn’t work right. If everything did, you’d be out of a job.” - Mosher’s Law of Software Engineering
User avatar
AbraCadaver
DevNet Master
Posts: 2572
Joined: Mon Feb 24, 2003 10:12 am
Location: The Republic of Texas
Contact:

Re: Help with MySQL JOIN please.

Post by AbraCadaver »

social_experiment wrote:(you can't join one table).
Why not (just an example, may not apply to the OP)?

Code: Select all

SELECT c1.categoryID, c1.masterCategoryID, c1.categoryName, c1.categoryLink
    FROM categories c1
    LEFT JOIN categories c2 ON  c1.categoryID = c2.masterCategoryID
mysql_function(): WARNING: This extension is deprecated as of PHP 5.5.0, and will be removed in the future. Instead, the MySQLi or PDO_MySQLextension should be used. See also MySQL: choosing an API guide and related FAQ for more information.
User avatar
social_experiment
DevNet Master
Posts: 2793
Joined: Sun Feb 15, 2009 11:08 am
Location: .za

Re: Help with MySQL JOIN please.

Post by social_experiment »

An SQL JOIN clause combines records from two or more tables in a database
http://en.wikipedia.org/wiki/Join_(SQL)

I think if you could join one table onto itself it would be a bit counter-productive.

Code: Select all

 
<?php
 $query = mysql_query("SELECT * FROM table1");
?>
 
The above code lets you access all the information in said table.
“Don’t worry if it doesn’t work right. If everything did, you’d be out of a job.” - Mosher’s Law of Software Engineering
User avatar
AbraCadaver
DevNet Master
Posts: 2572
Joined: Mon Feb 24, 2003 10:12 am
Location: The Republic of Texas
Contact:

Re: Help with MySQL JOIN please.

Post by AbraCadaver »

You can take a look at the article that you posted :wink:

http://en.wikipedia.org/wiki/Join_(SQL)#Self-join
mysql_function(): WARNING: This extension is deprecated as of PHP 5.5.0, and will be removed in the future. Instead, the MySQLi or PDO_MySQLextension should be used. See also MySQL: choosing an API guide and related FAQ for more information.
User avatar
social_experiment
DevNet Master
Posts: 2793
Joined: Sun Feb 15, 2009 11:08 am
Location: .za

Re: Help with MySQL JOIN please.

Post by social_experiment »

:oops: Valid point. I should have read further. :)
“Don’t worry if it doesn’t work right. If everything did, you’d be out of a job.” - Mosher’s Law of Software Engineering
Post Reply