Query only returning one column

[Andy17]

Hey guys,

 

 

I am building a new website and have a query that I want to return multiple columns, but it only returns one. Before I start explaining too much, here is the relevant part of my database diagram:

 

 

What I want to do is to select the Name, Description and CategoryNum from the ContentCategory table that corresponds to a given ContentType row. For example,  let's say that I have a ContentType row with the name "News". I would then want to find the categories associated with that type in the ContentCategory table. So, there are a number of categories associated with different types of content.

 

Here is my query:

 

SELECT cc.Name, cc.Description, cc.CategoryNum
FROM ContentCategory cc
INNER JOIN TypeCategory ON cc.CategoryId = TypeCategory.CategoryId
INNER JOIN ContentType ON ContentType.ContentTypeId = TypeCategory.ContentTypeId
WHERE ContentType.ContentTypeId = 'News'

 

The Name column in ContentType is unique. I have some test data in my tables. I get the correct row, but it only selects the Name column from ContentCategory as shown below.

 

 

I am pretty new to inner joins and using many-to-many relations, so I would really appreciate if anyone can tell what I am doing wrong. If it helps anyone, here is my table structure:

 

       CREATE TABLE ContentType (
	ContentTypeId 	        int	                NOT NULL	      auto_increment,
	Name			VARCHAR(20)		NOT NULL,
	Description		VARCHAR(150)	        NOT NULL,

	PRIMARY KEY (ContentTypeId),
	UNIQUE (Name)
)

        CREATE TABLE TypeCategory (
	ContentTypeId		int			NOT NULL,
	CategoryId		int			NOT NULL,

	PRIMARY KEY (ContentTypeId, CategoryId),
	FOREIGN KEY (ContentTypeId) REFERENCES ContentType(ContentTypeId),
	FOREIGN KEY (CategoryId) REFERENCES ContentCategory(CategoryId)
)

        CREATE TABLE ContentCategory (
	CategoryId			int		NOT NULL		auto_increment,
	Name				VARCHAR(50)	NOT NULL,
	Description			VARCHAR(500)	NOT NULL,
	CategoryNum			int		NOT NULL,

	PRIMARY KEY (CategoryId),
	UNIQUE (Name, CategoryNum)
)

 

Thanks a lot in advance!

 

Andy

[fenway]

Sorry, I don't follow.

[Andy17]

(Just so you don't have to scroll)

 

Let's say that I have the following ContentTypes with some descriptions:

 

News

Videos

Pictures

 

For each of those ContentTypes, I have associated ContentCategories (which is done in the many-to-many relation named TypeCategory). So, the ContentType "News" could have the following categories:

 

Sports

Crime

Celebrities

Gaming

 

... and the "pictures" ContentType could have different categories associated with it. So on a page I want to display the categories a given ContentType has associated with it. If this ContentType was "News", I would want the query to return the rows in ContentCategory that are associated with it. In this example, I would want it to return the categories given above (sports, crime, celebrities, gaming - and their Description and CategoryNum).

 

My query finds the correct categories associated with the ContentType I provide, but it only returns the category's Name and not Description and CategoryNum, which I also need (notice the orange border around the Name column in the phpMyAdmin screenshot in the first post).

 

I hope that helps.

[Andy17]

Bump

[Andy17]

bump

[PFMaBiSmAd]

No one here knows why you are posting. The image you posted in the first post shows that your query does return a Name, Description, and CategoryNum -

 

Name: TestCategoryName

Description: TestCategoryDescription

CategoryNum: 101

 

If those are not the correct values, you failed to simply state what the problem is and show what the expected results should have been and what the test data is.

 

[Andy17]

No one here knows why you are posting. The image you posted in the first post shows that your query does return a Name, Description, and CategoryNum -

 

Name: TestCategoryName

Description: TestCategoryDescription

CategoryNum: 101

 

If those are not the correct values, you failed to simply state what the problem is and show what the expected results should have been and what the test data is.

 

 

I already said to notice the orange border in that screenshot. It finds the correct values, but for some reason, I only have access to the Name column. When I try to echo out the other columns in PHP, nothing is displayed.

[PFMaBiSmAd]

That would indicate that your php code is incorrect or that the resulting HTML is incorrect.

[EchoFool]

I agree with PFMaBiSmAd

 

And to further confirm these things you should have a error reporting on so it would say something is not set.