VanityCrush Posted August 14, 2015 Share Posted August 14, 2015 Is it possible to link column_id from table A with column_id from table B? For example: If column_id A has a value of 6, column_id B should not be able allow entries if the column_id B is more than the value of column id A. I am running the below function to extract data from these tables: function list_articles() { include('core/db/db_connection.php'); $sql = "SELECT blog.title, blog.content, blog.posted_by, blog.date, article_comments.comments, article_comments.comment_by FROM blog LEFT OUTER JOIN article_comments ON blog.content_id = article_comments.comment_id WHERE blog.content != '' ORDER BY blog.content_id DESC"; $result = mysqli_query($dbCon, $sql); while ($row = mysqli_fetch_array($result)) { echo "<h5 class='posted_by'>Posted by " . $posted_by = $row['posted_by'] . " on " . $row['date'] . "</h5>" . "<h1 class='content_headers'>" . $title = $row['title'] . "</h1>" . "<article>" . $content = $row['content'] . "</article>" . "<hr class='artline'>" . "<div class='commented_by'>" . $row['comment_by'] . "</div>" . "<div class='comments'>" . $row['comments'] . "</div>"; } } Thanks. Quote Link to comment Share on other sites More sharing options...
Barand Posted August 14, 2015 Share Posted August 14, 2015 (edited) Do you mean SELECT A.column_id , B.column_id FROM tableA as A INNER JOIN tableB as B ON B.column_id > A.column_id Edited August 14, 2015 by Barand 1 Quote Link to comment Share on other sites More sharing options...
VanityCrush Posted August 14, 2015 Author Share Posted August 14, 2015 (edited) Sorry, I should have also posted the code that's trying to insert the comments in the database. This is where I want to check if column_id A is = with column_id B. Before I try what you suggested, can you please let me know what's wrong with my code? function insert_comments($comment_by, $comments) { include('core/db/db_connection.php'); $sql = "INSERT INTO article_comments (comments, comment_by) VALUES ('$comments', '$comment_by') WHERE article_comments.comment_id = blog.content_id"; mysqli_query($dbCon, $sql); } EDIT: Does this look ok? How can I use the below to actually insert the comments in my database now? function insert_comments($comment_by, $comments) { include('core/db/db_connection.php'); $sql = "SELECT blog.content_id, article_comments.comment_id FROM blog AS blog INNER JOIN article_comments AS article_comments ON article_comments.comment_id > blog.content_id"; mysqli_query($dbCon, $sql); } Edited August 14, 2015 by VanityCrush Quote Link to comment Share on other sites More sharing options...
Barand Posted August 14, 2015 Share Posted August 14, 2015 1. Don't connect to the database in side the function. You don't want to be connecting every time the function is called (connecting is slow). Connect once when you open your script and pass the connection as a parameter to your function. 2. INSERT queries do not have WHERE clauses, you would write the id in the insert with the other data. Are you sure you don't mean to update an existing record? Quote Link to comment Share on other sites More sharing options...
VanityCrush Posted August 14, 2015 Author Share Posted August 14, 2015 Thanks for the suggestion regarding the connection, I'll update my code. No, I have tried using UPDATE but every time I insert a new comment it deletes the previous one. I would like to be able to store comments and display them uniquely for each article they are linked to. I was thinking of creating a foreign key in table A and link it with table B. I'll do some reading on it and get back with my results. Quote Link to comment Share on other sites More sharing options...
VanityCrush Posted August 14, 2015 Author Share Posted August 14, 2015 I have tried creating a foreign key: ALTER TABLE article_comments ADD CONSTRAINT comment_blog_fk FOREIGN KEY (blog_id) REFERENCES wt.blog(content_id) ON DELETE NO ACTION ON UPDATE CASCADE; but I get the error #1452 - Cannot add or update a child row: a foreign key constraint fails (worldtour.#sql-22a4_47b, CONSTRAINT comment_blog_fk FOREIGN KEY (blog_id) REFERENCES blog (content_id) ON DELETE NO ACTION ON UPDATE CASCADE). Any idea why? Quote Link to comment Share on other sites More sharing options...
Barand Posted August 14, 2015 Share Posted August 14, 2015 Do you have any values in the comment table's blog_id field that do not match an id in the blog table? 1 Quote Link to comment Share on other sites More sharing options...
VanityCrush Posted August 14, 2015 Author Share Posted August 14, 2015 (edited) Duh, nope.. all values were set automatically to 0 while the content_id on the blog table was auto incremented to 8. I have changed these to match and everything works ok now. Thanks for all your help. I'll go test how I can implement this in a new query now Edited August 14, 2015 by VanityCrush Quote Link to comment Share on other sites More sharing options...
VanityCrush Posted August 15, 2015 Author Share Posted August 15, 2015 (edited) So, I have modified my code and it now looks like this: function list_articles() { include('core/db/db_connection.php'); $sql = "SELECT blog.title, blog.content, blog.posted_by, blog.date, article_comments.comments, article_comments.comment_by FROM blog LEFT OUTER JOIN article_comments ON blog.content_id = article_comments.content_id WHERE blog.content != '' ORDER BY blog.content_id DESC"; $result = mysqli_query($dbCon, $sql); while ($row = mysqli_fetch_array($result)) { echo "<h5 class='posted_by'>Posted by " . $posted_by = $row['posted_by'] . " on " . $row['date'] . "</h5>" . "<h1 class='content_headers'>" . $title = $row['title'] . "</h1>" . "<article>" . $content = $row['content'] . "</article>" . "<div class='commented_by'>Posted by: " . $row['comment_by'] . "</div>" . "<div class='comments'>Comments: " . $row['comments'] . "</div>"; } } And this is how I'm now inserting comments in the database: function insert_comments($comment_by, $comments) { include('core/db/db_connection.php'); $sql = "SELECT blog.content_id, article_comments.blog_id FROM blog AS blog INNER JOIN article_comments AS article_comments ON article_comments.blog_id > blog.content_id"; mysqli_query($dbCon, $sql); } IN PHPMyAdmin the foreign key works alright and the comments are linked to a specific article. I want to transpose this on a web page. When I insert a new article on the page it works alright, but when I try to insert a comment for that article it will not display it. If I change ON blog.content_id = article_comments.content_id to ON blog.content_id = article_comments.blog_id (blog_id is the field name for the foreign key) - it will display all the comments for an article - but it duplicates that article for each comment associated with it. I'm not looking for someone to actually go ahead and give me the code, I just want to be pointed in the right direction as I feel a bit lost and I would appreciate some help. EDIT: Just to clarify - For now, I am inserting data directly through the database interface until I figure out how to display it properly, hence no INSERT statement in my code. So, my problem lays in displaying it. In the database, comments are associated with articles correctly. The way I display them on the page seems to be the issue. More than likely there's an issue with the way I am selecting either the comments or the articles. Edited August 15, 2015 by VanityCrush Quote Link to comment Share on other sites More sharing options...
Barand Posted August 15, 2015 Share Posted August 15, 2015 ON blog.content_id = article_comments.blog_id that is the correct join. That's why you added the foreign key. As for the duplication - that is how joins work. You get the data from A with matching data from B. The trick is to output the article data only when it changes. Without knowing exactly how you want to display the data I cannot offer much help other than this pseudocode. prev_blog_id = 0 while (get next result row) if (blog_id != prev_blog_id) output article data prev_blog_id = blog_id endif output comment data endwhile Quote Link to comment Share on other sites More sharing options...
VanityCrush Posted August 15, 2015 Author Share Posted August 15, 2015 (edited) Hi Barand, thank you once more for trying to help me out. Please see below the issues I get with ON blog.content_id = article_comments.blog_id Results on the web page: Article title: LOREM IPSUM Content: LOREM IPSUM DOLOR SIT AMET.... -------------------------------------- Name: DSK Comment: Great article! -- HERE IT DUPLICATES THE ARTICLE TO DISPLAY A NEWLY INSERTED COMMENT -- Article title: LOREM IPSUM Content: LOREM IPSUM DOLOR SIT AMET.... -------------------------------------- Name: DSK Comment: Great article! - 2nd comment As you can see, it duplicates the article for every comment inserted. So I end up with two duplicate articles that hold different comments. If If I'll have 100 comments, the article will get replicated 100 times The behavior I am expecting: Article title: LOREM IPSUM Content: LOREM IPSUM DOLOR SIT AMET.... -------------------------------------- \\ COMMENTS \\ Name: DSK Comment: Great article! -------------------------------------- Name: DSK Comment: Great article! - 2nd comment Does this help clarify what I'm trying to accomplish ? Edited August 15, 2015 by VanityCrush Quote Link to comment Share on other sites More sharing options...
Solution Barand Posted August 15, 2015 Solution Share Posted August 15, 2015 (edited) The pseudocode I gave you will map directly on to your function. Try it yourself. If you get stuck you can have the spoonfed answer by clicking the spoiler function list_articles() { include('core/db/db_connection.php'); $sql = "SELECT blog.content_id, blog.title, blog.content, blog.posted_by, blog.date, article_comments.comments, article_comments.comment_by FROM blog LEFT OUTER JOIN article_comments ON blog.content_id = article_comments.blog_id WHERE blog.content != '' ORDER BY blog.content_id DESC"; $result = mysqli_query($dbCon, $sql); $prev = 0; while ($row = mysqli_fetch_array($result)) { if ($prev != $row['content_id']) { echo "<h5 class='posted_by'>Posted by {$row['posted_by']} on {$row['date']}</h5> <h1 class='content_headers'>{$row['title']}</h1> <article>{$row['content']}</article>" ; $prev = $row['content_id']; } echo "<div class='commented_by'>Posted by: {$row['comment_by']} </div> <div class='comments'>Comments: {$row['comments']}</div>"; } } Edited August 15, 2015 by Barand 1 Quote Link to comment Share on other sites More sharing options...
VanityCrush Posted August 15, 2015 Author Share Posted August 15, 2015 Amazing, thank you ! Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.