What's wrong with this code? =/ - Printable Version +- CodeIgniter Forums (https://forum.codeigniter.com) +-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20) +--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forumdisplay.php?fid=23) +--- Thread: What's wrong with this code? =/ (/showthread.php?tid=52331) Pages:
1
2
|
What's wrong with this code? =/ - El Forum - 06-06-2012 [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` 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 Quote:articles contains the article themselves for standard articles Quote:User What's wrong with this code? =/ - El Forum - 06-06-2012 [eluser]Abel A.[/eluser] How does your database structure look like? What's wrong with this code? =/ - El Forum - 06-06-2012 [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) { Obviously this won't work but it's where I want to go with it. What's wrong with this code? =/ - El Forum - 06-06-2012 [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. What's wrong with this code? =/ - El Forum - 06-06-2012 [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: Quote:Username // username What's wrong with this code? =/ - El Forum - 06-06-2012 [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? What's wrong with this code? =/ - El Forum - 06-06-2012 [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: Quote:Username // username[/quote] Is that just for one comment? so you would have this for multiple comments? : Quote:Username // username Am I getting the gist of it? What's wrong with this code? =/ - El Forum - 06-06-2012 [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 Quote:Username // username Quote:Username // username Quote:Username // username Quote:Username // username Quote:Username // username What's wrong with this code? =/ - El Forum - 06-06-2012 [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 Let me know if you have any questions with the above query. What's wrong with this code? =/ - El Forum - 06-06-2012 [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. Quote:ENTarticleid (from entertainment) To name a few. Then we join... Quote:entertainment_articles(entarticleid) with entertainment_comments(ent_articles_article) // joining id's 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 Quote:entertainment_articles(type) -> entertainment_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. Quote:entertainment_comments(author_id) -> users(id) // cross checks the comment's author id with users's id. Okay so now our results should look like assuming the view file was created (if limited to two results): Quote:Myusername 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: Quote:entertainment_comments(cme_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! |