What's wrong with this code? =/

[eluser]emily12[/eluser]
Hi, I'm trying to get 4 article tables, 4 comments tables, 1 article type table, and a user table to get all of the comments in my project. But I can't seem to understand what's wrong with even the most basic code.

Code:

SELECT * FROM `entertainment` b LEFT OUTER JOIN `entertainment_comments` ON (0) WHERE  cme_articles_article = `earticleid`
UNION ALL
SELECT * FROM `articles` b RIGHT OUTER JOIN `articles_comments` ON (0) WHERE  cm_articles_article = `articleid`

This is the most recent version of the simplified mysql query and it gives me the following error:

#1222 - The used SELECT statements have a different number of columns


I had been advised to use http://dev.mysql.com/doc/refman/5.0/en/create-view.html as reference but I can't seem to understand how to use it. Can anyone please help me? I have until monday before this project is due and I already had my teacher extend it from friday so it will be interesting to see what happens until then

Thank you for your time!



EDIT: Here's the idea behind what I hope the final thing for that module will be

articles contains the article themselves for standard articles
categories contains the categories for the standard articles section
comments contains the comments that were published for these articles

entertainment contains the article themselves for the Entertainment section
entertainment_type is the article type for entertainment, this is used to know if it's information / review / etc. full urls are generated thanks to this
entertainment_comments contains the comments that were published for the entertainment section

users is the user list. it is joined by the comment author when needed. The standard articles uses the categories table to define the url similarly to what the entertainment_type does. Why is it this way? Because I wanted to learn something about migrations, and this was a little over my head lol

User
Comment body
Date (sorted by newest)
Category OR category type

[eluser]Abel A.[/eluser]
How does your database structure look like?

[eluser]emily12[/eluser]
look at the bottom of the previous post (bold text). That's the database structure for the tables in question. The column names don't really matter since they can be anything. I just want to know the logic behind it lol

Here's kinda where I want to be going with it:

Code:

function get_allcomments($cmlimit) {
            $this->db->select('*');
            $this->db->from ('entertainment_comments AS p, articles_comment AS g');
            $this->db->join('users AS b', 'p.cme_articles_author = b.user_id', 'left');
            $this->db->join('entertainment AS c', 'p.cme_articles_article = c.earticleid', 'left');
            $this->db->join('articles AS k' 'g.cm_articles_article = k.articleid', 'left');
            $this->db->order_by('p.cme_articles_date, g.cm_articles_date' 'desc');
            $this->db->limit($cmlimit);
            return $this->db->get();
}

Obviously this won't work but it's where I want to go with it.

[eluser]Abel A.[/eluser]
can you give an example on how the results should look like?

I don't see how you can put comments and articles in the same table result. Does the project require to get all the information in one query? Looks like you would need to split them in 2 queries, once for the articles and the other for the comments.

[eluser]emily12[/eluser]
You seemed to have replied before I could edit my previous post. Take a look now I just want to collect all of the comments around the selected categories then link them to those categories. The user need to be shown as the author of that comment as well.

For example, this would be the end result:

Username // username
Comment body // cm_articles_body and cme_articles_body
Date (sorted by newest) // cm_articles_date and cme_articles_date
Category OR category type // Determined by subcat or article_type (this is done by the controller)

[eluser]Abel A.[/eluser]
Can you try using regular queries?

my advice for complex queries is to not use active records because it can be hard to read at times and very difficult to analyze.

I'm still confused on what you're trying to do with your query. Comments and articles shouldn't go on the same query. You can query the comments and join it to the user table to get author, and run a SEPARATE query to get the articles and their author by joining the user table again. That would be the best approach.

What exactly do you want to display when the user loads the page?

[eluser]Abel A.[/eluser]
[quote author="emily12" date="1339014870"]You seemed to have replied before I could edit my previous post. Take a look now I just want to collect all of the comments around the selected categories then link them to those categories. The user need to be shown as the author of that comment as well.

For example, this would be the end result:

Username // username
Comment body // cm_articles_body and cme_articles_body
Date (sorted by newest) // cm_articles_date and cme_articles_date
Category OR category type // Determined by subcat or article_type (this is done by the controller)

[/quote]

Is that just for one comment? so you would have this for multiple comments? :

Username // username
Comment body // cm_articles_body and cme_articles_body
Date (sorted by newest) // cm_articles_date and cme_articles_date
Category OR category type // Determined by subcat or article_type (this is done by the controller)

Username // username
Comment body // cm_articles_body and cme_articles_body
Date (sorted by newest) // cm_articles_date and cme_articles_date
Category OR category type // Determined by subcat or article_type (this is done by the controller)

Username // username
Comment body // cm_articles_body and cme_articles_body
Date (sorted by newest) // cm_articles_date and cme_articles_date
Category OR category type // Determined by subcat or article_type (this is done by the controller)

Am I getting the gist of it?

[eluser]emily12[/eluser]
Yes, basically on the home page (or moderation panel) there would be a list of comments from the articles all around the website. I'm not trying to get the articles but just the url, article id, and article type. I used ('*') to make it easier to read and it's easier to debug this way (I think).


So let's pretend that we're going to be looking at the module (wherever it may be)...

LATEST 5 COMMENTS AROUND THE WEBSITE

Username // username
Comment body // cm_articles_body and cme_articles_body
Date (sorted by newest) // cm_articles_date and cme_articles_date
Category OR category type // Determined by subcat or article_type (this is done by the controller)

Username // username
Comment body // cm_articles_body and cme_articles_body
Date (sorted by newest) // cm_articles_date and cme_articles_date
Category OR category type // Determined by subcat or article_type (this is done by the controller)

Username // username
Comment body // cm_articles_body and cme_articles_body
Date (sorted by newest) // cm_articles_date and cme_articles_date
Category OR category type // Determined by subcat or article_type (this is done by the controller)

Username // username
Comment body // cm_articles_body and cme_articles_body
Date (sorted by newest) // cm_articles_date and cme_articles_date
Category OR category type // Determined by subcat or article_type (this is done by the controller)

Username // username
Comment body // cm_articles_body and cme_articles_body
Date (sorted by newest) // cm_articles_date and cme_articles_date
Category OR category type // Determined by subcat or article_type (this is done by the controller)

[eluser]Abel A.[/eluser]
Ok, that's pretty easy them.

You can either use the id or a timestamp as the index.

Code:

SELECT c.id, c.articleid, c.userid, c.commentbody, c.date, c.category, u.username, a.articleurl, a.articletype
FROM comments c
LEFT JOIN user u ON u.userid = c.userid
JOIN article a ON a.articleid = c.articleid
ORDER BY c.date DESC
LIMIT 5

Let me know if you have any questions with the above query.

[eluser]emily12[/eluser]
Well, that IS very easy and I've done that for just about every query relating to comments articles so far, but the problem is that we're looking at more than one table and each table has unique columns but they need to be treated the same.

ENTarticleid (from entertainment)
NEWSarticleid (from news)
NORMarticleid (from articles) //misc stuff

To name a few. Then we join...

entertainment_articles(entarticleid) with entertainment_comments(ent_articles_article) // joining id's

news_articles(narticleid) with news_comments(news_articles_article) // joining id's

articles(articleid) with articles_comments(articles_article) // joining id, see NORMarticleid further up

Okay so now we theoretically connected the comments from different sections(tables) to their own respective article. Now let's make sure they know their category so a url can be generated

entertainment_articles(type) -> entertainment_categories // category id
news_articles(subcat) -> news_categories // category id
articles(subcat) -> articles_categories // category id

Now comments should know what article they are from, and what category they're in, thanks to the article saying the (subcat) is x(int) in their given category. We can use a simple conditional to find out what category they're from to form a url.

The next step is to assign users to these comments, so we use the user's table and the comments table from each section.

entertainment_comments(author_id) -> users(id) // cross checks the comment's author id with users's id.
news_comments(author_id) -> users(id) // same thing
articles(author_id) -> users(id) // again

Okay so now our results should look like assuming the view file was created (if limited to two results):

Myusername
Hello world, this is a test post.
06 - 06 - 2012, 2:45 PM
urlgoeshere/

Myusername
Hello world, this is the second post
06 - 06 - 2012, 2:48 PM
urlgoeshere/

The last thing on this list is to determine how to order it by, and each comment has their own prefix like said in the beggining of this post (ENTarticleid, NEWSarticleid etc...).

The date would be similar:

entertainment_comments(cme_date_posted)
news_comments(ncm_date_posted)
article_comments(cm_date_posted)

Ridiculous isn't it? I figured that this problem would be an amazing learning experience but it's way over my head with the usage of mysql lol X.x I heard create view might be necessary, also join, union and alias might be used too. This should be an exam question to be honest lol!