Identifying and connecting ID's between tables

[VanityCrush]

Me again..

 

I've struggled for the past 2 hours to insert article comments and link them to an existent article on the page.

 

Now, the function that is displaying both comments and articles looks like this:

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);
    
    $previous_blog_id = 0;
    
    while ($row = mysqli_fetch_array($result)) {
        if ($previous_blog_id != $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>
                <hr class='artline'>";
            $previous_blog_id = $row['content_id'];
        }
        if (!empty($row['comment_by']) && !empty($row['comments'])) {
       		 echo "<div class='commented_by'>Posted by: {$row['comment_by']} </div> 
                   <div class='comments'>Comments: {$row['comments']}</div>
               	   <hr class='artline2'>";
        }
    }
}

The function I'm running to insert comments into article_comments table

function insert_comments($comments, $comment_by, $blog_id) {
    include('core/db/db_connection.php');
    $comment_by = sanitize($comment_by);
    $comments = sanitize($comments);
    $sql = "INSERT INTO article_comments (comments, comment_by, blog_id)
            VALUES ('$comments', '$comment_by', '$blog_id')";
    mysqli_query($dbCon, $sql);
}

This works - it does the insertion, however I have no clue on how I could target the $blog_id variable when the user submits the post... The below is the form I use

<?php echo list_articles(); 
	if (!empty($_POST)) {
	    insert_comments($_POST['comments'], $_POST['username'], 11);
        }
?>
<form method='post' action='' class='comments_form'>
	<input type='text' name='username' placeholder='your name... *' id='name'>
	<textarea name='comments' id='textarea' placeholder='your comment... *' cols='30' rows='6'></textarea> 
	<input type='submit' name='submit' id='post' value='post'>
</form>

I bet you noticed that I've manually inserted 11 as a param for the last variable. This links to blog_id 11 (the foreign key) in my article_comments table. It is displaying the comment just fine.

 

Is there any way to target $blog_id without having to insert a number manually? Something like how I am targeting the $comments variable using $_POST['comments'] ?

 

Also, even if I can target that, how do I know which post is the user commenting to? Should I give them the option to choose in a drop-down list ? That seems awkward.. but it's the only solution I can think of. 

Edited August 15, 2015 by VanityCrush

[mac_gyver]

you would use a hidden field in the comment form with the blog_id as the the value.

 

where are your 'comment' forms being output on the page? aren't you outputting one following each blog/comment section?

blog...

any existing comments for this blog....

comment form...

[VanityCrush]

I am only outputting 1 comment form at the bottom of the page so far.

 

I understand how the hidden form field could work: something like this maybe

<input type="hidden" name="blog_id" value="11">

However, I do not want to manually insert the blog_id value, I want this to be automatically picked up from the database table. Once the value is picked up, the comments will point to the article they correspond. I'm not sure how can I accomplish that..

 

Here is what I'm thinking:

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);
    
    $previous_blog_id = 0;
    
    while ($row = mysqli_fetch_array($result)) {
        if ($previous_blog_id != $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>
                <hr class='artline'>
                <form method='post' action='' class='comments_form'>
		    <input type='text' name='username' placeholder='your name... *' id='name'>
		    <textarea name='comments' id='textarea' placeholder='your comment... *' cols='30' rows='6'></textarea> 
		    <input type='hidden' name='blog_id' value='{$row['blog_id']}'>
		    <input type='submit' name='submit' id='post' value='post'>
		</form>";
            $previous_blog_id = $row['content_id'];
        }
        if (!empty($row['comment_by']) && !empty($row['comments'])) {
       		 echo "<div class='commented_by'>Posted by: {$row['comment_by']} </div> 
                   <div class='comments'>Comment: {$row['comments']}</div>
               	   <hr class='artline2'>";
        }
    }
}

That's displaying a comment form for each article, but it doesn't seem to pick the value of the blog_id field

Edited August 15, 2015 by VanityCrush

[CroNiX]

What do you mean "do not want to manually insert the blog_id value". You'd use the same code that you are using to display the blog article, which presumably returns the blogs ID with the rest of the article data when retrieving from the database? So you'd just output the article_id in the hidden form input just like you are outputting the article text.

[mac_gyver]

the comment form for a blog/comment display section, is part of the blog/comment display section. it's not a separate thing. you should have a comment form following the display of each blog/existing-comment section.

[VanityCrush]

What do you mean "do not want to manually insert the blog_id value". You'd use the same code that you are using to display the blog article, which presumably returns the blogs ID with the rest of the article data when retrieving from the database? So you'd just output the article_id in the hidden form input just like you are outputting the article text.

 

I've tried to display it in the same way as I'm displaying the articles, however I get an undefined index error for blog_id when I try to target it with $row['blog_id']

 

Therefore I have tried the below:

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);
    
    $previous_blog_id = 0;
    
    while ($row = mysqli_fetch_array($result)) {
        if ($previous_blog_id != $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>
                <hr class='artline'>";
            $previous_blog_id = $row['content_id'];
        }
        if (!empty($row['comment_by']) && !empty($row['comments'])) {
       		 echo "<div class='commented_by'>Posted by: {$row['comment_by']} </div> 
                   <div class='comments'>Comment: {$row['comments']}</div>
               	   <hr class='artline2'>";
        }
        $sql2 = "SELECT blog.content, article_comments.blog_id
			             FROM blog LEFT OUTER JOIN article_comments
			             ON blog.content_id = article_comments.blog_id
			             WHERE blog.content != ''"
		$result2 = mysqli_query($dbCon, $sql2);
		while ($row = mysqli_fetch_array($result2)) {
			echo "  <form method='post' action='' class='comments_form'>
						<input type='text' name='username' placeholder='your name... *' id='name'>
						<textarea name='comments' id='textarea' placeholder='your comment... *' cols='30' rows='6'></textarea> 
						<input type='hidden' name=blog_id' value='{$row['blog_id']}'>
						<input type='submit' name='submit' id='post' value='post'>
					</form>";
		}	             
    }
}

This doesn't seem to do the trick as I get syntax error, unexpected '$result2' (T_VARIABLE)

 

I've modiefied the second sql query to:

$sql2 = "SELECT FROM article_comments VALUES blog_id";
		$result2 = mysqli_query($dbCon, $sql2);
		while ($row = mysqli_fetch_assoc($result2)) {
			echo "  <form method='post' action='' class='comments_form'>
					<input type='text' name='username' placeholder='your name... *' id='name'>
					<textarea name='comments' id='textarea' placeholder='your comment... *' cols='30' rows='6'></textarea> 
					<input type='hidden' name=blog_id' value='{$row['blog_id']}'>
					<input type='submit' name='submit' id='post' value='post'>
				</form>";
		}	      

mysqli_fetch_assoc() expects parameter 1 to be mysqli_result, boolean given  - is the error i get in doing so. I do need the value of blog_id to be boolean though...

 

Tell me if what I'm doing doesn't make any sense, not sure it does either. I'm new to all this stuff. Trying to learn on the fly..

Edited August 15, 2015 by VanityCrush

[CroNiX]

In your query, you aren't selecting "blog_id", you're selecting "content_id", which I assume is what you're after for your ID.

[CroNiX]

And the reason for the error, "syntax error, unexpected '$result2' (T_VARIABLE)", is because you don't have a semicolon terminating your $sql2 string, just before you try to perform the 2nd query.

 

 

$sql2 = "SELECT blog.content, article_comments.blog_id
             FROM blog LEFT OUTER JOIN article_comments
             ON blog.content_id = article_comments.blog_id
             WHERE blog.content != ''" // need semicolon here!!

[VanityCrush]

Thanks, however I don't think the $sql2 is the correct approach. Code works fine now, but I'm back to square 1. For each comment inserted articles get duplicated.

<form method='post' action='' class='comments_form'>
	<input type='text' name='username' placeholder='your name... *' id='name'>
	<textarea name='comments' id='textarea' placeholder='your comment... *' cols='30' rows='6'></textarea> 
	<input type='hidden' name=blog_id' value='{$row['blog_id']}'>
	<input type='submit' name='submit' id='post' value='post'>
</form>";

is there any way to target the blog_id without calling while ($row = mysqli_fetch_array($result)) {} ? or at least, not calling it in the second while loop?

 

With the first piece of code I posted I get the following results:

Article title: LOREM IPSUM
Content: LOREM IPSUM DOLOR SIT AMET....
-------------------------------------- 
Name: DSK
Comment: Great article!
--------------------------------------
Name: DSK
Comment: Great article! - 2nd comment 

-- BEGIN SECOND ARTICLE ON WEBPAGE 

Article title: LOREM IPSUM 2nd article
Content: LOREM IPSUM DOLOR SIT AMET....
-------------------------------------- 
Name: User0
Comment: Great article!
--------------------------------------
Name: User1
Comment: Great article! - 2nd comment 
--------------------------------------
Name: User2
Comment: Great article! - 3rd comment
-------------------------------------- 

Which is exactly what I'm looking for. However I can only insert comments via the phpmyadmin interface, manually selecting the foreign key(blog_id).

 

I would like to be able to get the same results through a form:

Article title: LOREM IPSUM
Content: LOREM IPSUM DOLOR SIT AMET....
-------------------------------------- //comments
Name: DSK
Comment: Great article!
--------------------------------------
Name: DSK
Comment: Great article! - 2nd comment 
-------------------------------------- // end comments

|-------------------------------------| // comments form
|Name: New User                       |
|Comment: New comment !               |
|                                     | 
|-------------------------------------|
[Submit]

When the user submits the form, his name and his comment gets submitted to the database into article_comments table. Also the foreign key (blog_id) should link to an existing article (which it does). I just need a way to target this blog_id in my function so I can use it as a param.

 

God, I'm so lost. Does that make any sense?....

Edited August 15, 2015 by VanityCrush

[Barand]

You don't need a second query. Your first query is to list the blog and existing comments. Save the comment_id from this query and that goes in the hidden field in the comment form at the bottom

<form method='post' action='' class='comments_form'>
    <input type='text' name='username' placeholder='your name... *' id='name'>
    <textarea name='comments' id='textarea' placeholder='your comment... *' cols='30' rows='6'></textarea>
    <input type='hidden' name=blog_id' value='$saved_comment_id'>
    <input type='submit' name='submit' id='post' value='post'>
</form>

[mac_gyver]

you are throwing too much code at this. you actually need to separate the concerns in your code. the code that knows how to query the database and contains all the database specific statements needs to be separate from the code that knows how to produce the html markup from that data.

 

you also need to NOT make a database connection every place you are running queries. your application should make one database connection and pass it into any function/class that is dependent on having a database connection.

 

i would have one function/class-method to run your query, fetch all the rows into an array, even if there are no matching rows, then return that array from the function/class-method.

 

another function would accept that array of data, even if it is an empty array, as a call time parameter, then loop over that data to produce the output. this second function should build the output in a variable and return that variable to the main calling code. the main calling code can do whatever it needs with that returned content - echo it, cache it, put it into an email, make a pdf file from it, ...

 

the place in your code to produce the comment form is actually right before you output the start of a new blog display, except before the very first blog display (you can test if the $previous_blog_id is a zero or not to determine if you are outputting the very first blog display). you would also output a final comment form after the last blog/comment display section, if there have been any blog/comment display sections (you can test if the $previous_blog_id is a zero or not to determine if there have been any blog/comment display sections.)

[mac_gyver]

the following example shows one way of separating the concerns in your code and should (untested) produce the result you are trying to achieve -

// create one database connection in your applcation
include 'core/db/db_connection.php'; // produces a mysqli connection in $dbCon

// retrieve the blog/comment data
function get_articles($dbCon) {
    $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); // you need some error handling (exceptions work best) so that query errors can be displayed during development, logged on a live-server and don't throw follow-on errors trying to access data from a query that never ran due to errors
    $rows = array(); // initialize in case the query matched no rows
    while($row = mysqli_fetch_assoc($result)){ // you could use a fetch all statement, provided it is present in your php installation/version
        $rows[] = $row;
    }
    return $rows;
}

// produce the comment form (called in multiple places)
function comment_form($id){
    return <<<EOT
    <form method='post' action='' class='comments_form'>
    <input type='text' name='username' placeholder='your name... *' id='name'>
    <textarea name='comments' id='textarea' placeholder='your comment... *' cols='30' rows='6'></textarea>
    <input type='hidden' name='blog_id' value='$id'>
    <input type='submit' name='submit' id='post' value='post'>
    </form>
EOT;
}

// produce the blog/comment/comment-form display
function list_articles($rows) {
    if(empty($rows)){
        return "There are no Blogs to display";
    }
    
    $previous_blog_id = 0;
    $content = '';
    foreach($rows as $row){
        if ($previous_blog_id != $row['content_id']) { // the blog id changed
            if(previous_blog_id != 0){ // not the first section, close out the previous section
                $content .= comment_form($row['content_id']); // add the comment form html. you may want to add some html around this for styling...
            }
            // start a new blog section
            $content .= "<h5 class='posted_by'>Posted by {$row['posted_by']} on {$row['date']}</h5>
                <h1 class='content_headers'>{$row['title']}</h1>
                <article>{$row['content']}</article>
                <hr class='artline'>";
            $previous_blog_id = $row['content_id'];
        }
        if (!empty($row['comment_by']) && !empty($row['comments'])) {
             $content .= "<div class='commented_by'>Posted by: {$row['comment_by']} </div>
                   <div class='comments'>Comments: {$row['comments']}</div>
                   <hr class='artline2'>";
        }
    }
    // done looping over blog/comments. add the final comment form if there were any blogs
    if(previous_blog_id != 0){ // not the first section, close out the previous section
        $content .= comment_form($row['content_id']); // add the comment form html. you may want to add some html around this for styling...
    }
    return $content;
}

// call the code to get the article data, produce the blog/comment/comment-form display, and echo the result
echo list_articles(get_articles($dbCon));

Edited August 15, 2015 by mac_gyver

[VanityCrush]

@mac_gyver

 

 

the following example shows one way of separating the concerns in your code and should (untested) produce the result you are trying to achieve -

// create one database connection in your applcation
include 'core/db/db_connection.php'; // produces a mysqli connection in $dbCon

// retrieve the blog/comment data
function get_articles($dbCon) {
    $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); // you need some error handling (exceptions work best) so that query errors can be displayed during development, logged on a live-server and don't throw follow-on errors trying to access data from a query that never ran due to errors
    $rows = array(); // initialize in case the query matched no rows
    while($row = mysqli_fetch_assoc($result)){ // you could use a fetch all statement, provided it is present in your php installation/version
        $rows[] = $row;
    }
    return $rows;
}

// produce the comment form (called in multiple places)
function comment_form($id){
    return <<<EOT
    <form method='post' action='' class='comments_form'>
    <input type='text' name='username' placeholder='your name... *' id='name'>
    <textarea name='comments' id='textarea' placeholder='your comment... *' cols='30' rows='6'></textarea>
    <input type='hidden' name='blog_id' value='$id'>
    <input type='submit' name='submit' id='post' value='post'>
    </form>
EOT;
}

// produce the blog/comment/comment-form display
function list_articles($rows) {
    if(empty($rows)){
        return "There are no Blogs to display";
    }
    
    $previous_blog_id = 0;
    $content = '';
    foreach($rows as $row){
        if ($previous_blog_id != $row['content_id']) { // the blog id changed
            if(previous_blog_id != 0){ // not the first section, close out the previous section
                $content .= comment_form($row['content_id']); // add the comment form html. you may want to add some html around this for styling...
            }
            // start a new blog section
            $content .= "<h5 class='posted_by'>Posted by {$row['posted_by']} on {$row['date']}</h5>
                <h1 class='content_headers'>{$row['title']}</h1>
                <article>{$row['content']}</article>
                <hr class='artline'>";
            $previous_blog_id = $row['content_id'];
        }
        if (!empty($row['comment_by']) && !empty($row['comments'])) {
             $content .= "<div class='commented_by'>Posted by: {$row['comment_by']} </div>
                   <div class='comments'>Comments: {$row['comments']}</div>
                   <hr class='artline2'>";
        }
    }
    // done looping over blog/comments. add the final comment form if there were any blogs
    if(previous_blog_id != 0){ // not the first section, close out the previous section
        $content .= comment_form($row['content_id']); // add the comment form html. you may want to add some html around this for styling...
    }
    return $content;
}

// call the code to get the article data, produce the blog/comment/comment-form display, and echo the result
echo list_articles(get_articles($dbCon));

 

Thanks for the effort in trying to put this together.

 

I've tried to wrap my mind around that.. 

 

There seems to be an issue with this part of the code:

function list_articles($rows) {
    // if(empty($rows)){
    //     return "There are no Blogs to display";
    // }
    $previous_blog_id = 0;
    $content = '';
    foreach($rows as $row){

The rows always return empty. If I comment them out to try and execute the other part of the code - the following error occurs:  Warning: Invalid argument supplied for foreach()  this must be because the $rows variable is null, and I don't understand why..

 

Note that I am calling the echo from another page, and I'm doing it like so:

<?php  
       echo list_articles(get_articles($dbCon));
	if (!empty($_POST)) {
	   insert_comments($_POST['comments'], $_POST['username'], $_POST['blog_id']);
	}
?>

@Barand

 

 

You don't need a second query. Your first query is to list the blog and existing comments. Save the comment_id from this query and that goes in the hidden field in the comment form at the bottom

<form method='post' action='' class='comments_form'>
    <input type='text' name='username' placeholder='your name... *' id='name'>
    <textarea name='comments' id='textarea' placeholder='your comment... *' cols='30' rows='6'></textarea>
    <input type='hidden' name=blog_id' value='$saved_comment_id'>
    <input type='submit' name='submit' id='post' value='post'>
</form>

 

I have tried implementing the above like so:

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, article_comments.comment_id
            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);
    
    $previous_blog_id = 0;
    
    while ($row = mysqli_fetch_array($result)) {
    	$saved_comment_id = $row['comment_id'];
        if ($previous_blog_id != $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>
                <hr class='artline'> 
                <form method='post' action='' class='comments_form'>
			<input type='text' name='username' placeholder='your name... *' id='name'>
			<textarea name='comments' id='textarea' placeholder='your comment... *' cols='30' rows='6'></textarea> 
			<input type='hidden' name='blog_id' value='$saved_comment_id'>
			<input type='submit' id='post' value='post'>
		</form>";
            $previous_blog_id = $row['content_id'];
        }
        if (!empty($row['comment_by']) && !empty($row['comments'])) {
       		 echo "<div class='commented_by'>Posted by: {$row['comment_by']} </div> 
                   <div class='comments'>Comment: {$row['comments']}</div>
               	   <hr class='artline2'>";
        }
    }
}

function insert_comments($comments, $comment_by, $blog_id) {
    include('core/db/db_connection.php');
    $comment_by = sanitize($comment_by);
    $comments = sanitize($comments);
    $sql = "INSERT INTO article_comments (comments, comment_by, blog_id)
            VALUES ('$comments', '$comment_by', '$blog_id')";
    mysqli_query($dbCon, $sql);
}

And this is how I'm calling the functions:

<?php echo list_articles(); 
	if (!empty($_POST)) {
	   insert_comments($_POST['comments'], $_POST['username'], $_POST['blog_id']);
        }
?>

All works well, no errors, comments are displaying, forms are displaying.. However, I am only able to add comments (through the forms) only for article 1. I submit the form, and the comment gets submitted in the database correctly and gets displayed on the page. If I try to do the same thing for article 2, the form submission will not work, nothing gets inserted in the database. I have to assume that the variable I'm using $saved_comment_id is always 1? Even if so, comments should still be posting to article 1...

 

I'm using 

error_reporting(E_ALL);
ini_set('display_errors', '1');

but I get no errors.

Edited August 15, 2015 by VanityCrush

[VanityCrush]

@mac_gyver

 

Ok, so I fixed it - however the behavior is not the one I'm expecting.

 

I have 3 articles with the id of 1, 11, and 12.

 

Each article has now it's own comment form.

 

If I comment on article 1, all good, the comment gets inserted with the correct blog_id_[fk] and displayed on the page.

However, If I comment on article 11, the comment goes to article 1.

When I comment on article 12, the comment goes to article 11.

 

The comment form seems to be 1 article behind... any clue?

Edited August 15, 2015 by VanityCrush

[mac_gyver]

The comment form seems to be 1 article behind... any clue?

 

 

yes, the untested example i posted is using the next/current id in the section of code that's outputting the comment-form for the previous section. change the first occurrence of - $content .= comment_form($row['content_id']); to this -

$content .= comment_form($previous_blog_id);

you can actually change the second occurrence of - $content .= comment_form($row['content_id']); too, for consistency, since $previous_blog_id will be the same as $row['content_id'] after the end of the loop.

 

edit: i also see another problem, a missing $. change this (in two places) - if(previous_blog_id != 0){, to this -

if($previous_blog_id != 0){

you should have been getting php runtime errors about an undefined constant if your error reporting and the execution path takes you through those lines of code.

Edited August 15, 2015 by mac_gyver

[VanityCrush]

Works fine, thanks, everyone.

Edited August 15, 2015 by VanityCrush