associate and post comments to a blog page and table

[nonspacial]

Hello people,

 

can someone help me to make this function work how i want it.

 

so it's a bit butchered from a tutorial and code i had already used before but i'm confusing myself with how to associate a posting with the original post that it is being commented on and therefore to make the number populate into the table.

 

my idea is to use the id of the original post in the table as the blogcomment column in the table so the auto incremented id can associate all comments with it and all completely new posts that are put up by admin have this value as 0.

 

i have a working DB connection in $dbc and a working page association in $pg that way i can associate material with different areas of the site. if you look at the last if statement you will see my table structure in the insert into query. i am stuck here without an association of the actual box to the original Post; where i get this value from or how i get it from the table directly i.e. the id.

 

i also have a working cookie system that i have saved userid and usertype so i can retrieve usernames from the users table directly.

 

i obviously need to populate every column in the table for new posts and i need to somehow retrieve them and place them with the original post they refer to... it's hurting my head atm . thanks for your help and input.

 

function blogs ($dbc, $pg) {// blogs retrieved and sorted from DB
 $q="SELECT * FROM abstrktmainblogs WHERE page_id='$pg' ORDER BY date_posted DESC";
 $r = mysqli_query($dbc, $q);
while ($blogPosts= mysqli_fetch_assoc ($r)) { // blogs displayed in table date descending
echo'<div class="blogPosts">
<table>
<tr>'.$blogPosts['title'].' by '.$blogPosts['username'].' on '.$blogPosts['date_posted'].'</tr>
<br></br>
<tr>'.$blogPosts['post'].'</tr>
</table></div> <br></br>';
if (isset($_COOKIE['userid'])) { //comment posting ability if logged in (display form)
 echo '<div id="respond">
 <form method="post" autocomplete="on">
 <label for="comment" class="required">Your message: </label>
    <textarea name="comment" id="comment" rows="1" width="90%" tabindex="4"  required="required"></textarea>
	 <input type="hidden" name="comment_post_ID" value="1" id="comment_post_ID" /><br></br>
	 <input name="submit" type="submit" value="Post" />
    </form></div>';

 print_r($_REQUEST);

if (isset ($_POST['Post'])==1) { // actions to take when post is made
 $q="SELECT * FROM `abstrktmainblogs` WHERE `blogcomment` = 0";
 $r = mysqli_query($dbc, $q);
 $q ="INSERT INTO `abstrktmainblogs`(`id`, `blogcomment`, `page_id`, `title`, `post`, `username`, `date_posted`) VALUES ([],[value-2],['".$pg."'],[value-4],[".$_POST['comment']."],[".$user['username']."],[value-7])";

} //end Post actions
} //end display comment form
} //end while loop
}   //end blogs function  

[PFMaBiSmAd]

Your comment form would need to supply the id of the original post as a hidden field.

[nonspacial]

yes that's what i'm doing now with others like username and title but i'm still unsure of how to get the id of the original post... how does the textarea box know it is different and associated with said post id?

 

currently i have 2 main posts and either field provides both with the same print on request ie both posts would display the comment.

[nonspacial]

it seems to be working i get different titles from different boxes in my print out but i'm trying to call a function as a value paramenter and it's now printing the function name in $_POST array but echoing the function above Your message: which is odd as it's a hidden field?

[PFMaBiSmAd]

I think you should slow down and fix one problem at a time.

 

To retrieve existing posts and comments, that have a parent child relationship, you will need to do a self join of the table to itself. The will get the parent (original blog post) information joined with all the child (comment) information. There are a large number of examples posted on the Internet of how to do this common type of query.

[nonspacial]

taking your advice i'm getting no rows from this in phpmyadmin or my site i need all rows to be sorted by title and then by blogcomment so that it will go 0,1,1,1, 0,1,1,1 where 0 is main post and 1 is comment...

page_id is also important as it's how different pages are referenced for each post

 

SELECT * FROM abstrktmainblogs f INNER JOIN abstrktmainblogs s ON f.title = s.title WHERE page_id=2 ORDER BY 'date_posted' DESC

[nonspacial]

this is the closest i've got and it produces triplicates in the query if i use < or > as it says in the manual i'm reading it comes back as no rows?

 

$q="SELECT *
FROM abstrktmainblogs f
INNER JOIN abstrktmainblogs s ON f.title = s.title
WHERE f.title = s.title
ORDER BY f.title, f.date_posted, f.blogcomment DESC
LIMIT 0 , 30"

[nonspacial]

i think i have cracked the query but it's now only showing the comments which is effectively the right side of the table here is the query:

$q="SELECT * FROM abstrktmainblogs f INNER JOIN abstrktmainblogs s ON f.title = s.titleWHERE f.blogcomment < s.blogcomment ORDER BY f.title, f.date_posted, f.blogcomment DESC";

 

i now need to find a way to make the while loop table display the main article and comments cascading from eachother:

 

$q="SELECT * FROM abstrktmainblogs f INNER JOIN abstrktmainblogs s ON f.title = s.title
WHERE f.blogcomment < s.blogcomment ORDER BY f.title, f.date_posted, f.blogcomment DESC";
$r = mysqli_query($dbc, $q);
while ($blogPosts= mysqli_fetch_assoc ($r)) { // blogs displayed in table date descending
echo'<div class="blogPosts">
<table>
<tr>'.$blogPosts['title'].' by '.$blogPosts['username'].' on '.$blogPosts['date_posted'].'</tr>
<br></br>
<tr>'.$blogPosts['post'].'</tr>
</table></div> <br></br>';

if (isset($_COOKIE['userid'])) { //comment posting ability if logged in (display form)
echo '<div id="respond">
<form method="post" autocomplete="on">
<label for="comment" class="required">Leave us a comment: </label>
 <textarea name="comment" id="comment" rows="1" width="90%" tabindex="4" required="required"></textarea>
	 <input type="hidden" name="blogcomment" value="1" id="blogcomment" /><br></br>
<input type="hidden" name="title" value="'.$blogPosts['title'].'" id="title" />
<input type="hidden" name="userid" value="'.$user ['username'].'" id="userid" />
	 <input name="submit" type="submit" value="Post Comment" id="submit" />
 </form></div>';

 

Dumping data for table abstrktmainblogs

 

id

blogcomment

page_id

title

post

username

date_posted

id

blogcomment

page_id

title

post

username

date_posted

1

0

2

testing

testing 123 testing post display

Robbie

2013-01-12 00:00:00

3

1

2

testing

comment 1

Robbie

2013-01-25 22:35:00

1

0

2

testing

testing 123 testing post display

Robbie

2013-01-12 00:00:00

5

1

2

testing

comment 3

Robbie

2013-01-26 06:31:00

2

0

2

testing 2

testing testing work mutha fucker work

Robbie

2013-01-12 00:00:20

4

1

2

testing 2

comment 2

Robbie

2013-01-25 22:35:00

Edited January 26, 2013 by nonspacial

[nonspacial]

here is the queried table:

 

id blogcomment page_id title post username date_posted id blogcomment page_id title post username date_posted

1 0 2 testing testing 123 testing post display Robbie 2013-01-12 00:00:00 3 1 2 testing comment 1 Robbie 2013-01-25 22:35:00

1 0 2 testing testing 123 testing post display Robbie 2013-01-12 00:00:00 5 1 2 testing comment 3 Robbie 2013-01-26 06:31:00

2 0 2 testing 2 testing testing work mutha fucker work Robbie 2013-01-12 00:00:20 4 1 2 testing 2 comment 2 Robbie 2013-01-25 22:35:00

[nonspacial]

---

a few more hours and i am here:

 

function blogs ($dbc, $pg) {// blogs retrieved and sorted from DB
 //setup user variable
 $user[]=
 $q ="SELECT * FROM users WHERE id= '$_COOKIE[userid]' LIMIT 1";
 $r = mysqli_query ($dbc, $q);
 if (mysqli_num_rows ($r) >0) {
 $user = mysqli_fetch_assoc($r);
 };

 $q="SELECT * FROM abstrktmainblogs f INNER JOIN abstrktmainblogs s ON f.title = s.title WHERE f.page_id ='$pg' AND s.blogcomment < f.blogcomment ORDER BY f.date_posted, f.title, f.blogcomment  DESC";
 $r = mysqli_query($dbc, $q);
while ($blogPosts= mysqli_fetch_assoc ($r)) { // blogs displayed in table date descending while loop 1
 foreach ($blogPosts as $value) {
 echo'<div id="blogPosts">
 <table>
 <tr><h3>'.$blogPosts['title'].' by '.$blogPosts['username'].' on '.$blogPosts['date_posted'].'</h3></tr>
 <br></br>
 <tr>'.$blogPosts['post'].'</tr>
 </table> <br></br>';

 //Comments retrieve and display
 $q="SELECT * FROM abstrktmainblogs f INNER JOIN abstrktmainblogs s ON f.title = s.title WHERE f.page_id ='$pg' AND s.blogcomment > f.blogcomment AND f.title=s.title ORDER BY f.title, f.date_posted,  f.blogcomment  DESC";
 $r = mysqli_query($dbc, $q);
 while ($blogComments=mysqli_fetch_assoc ($r)) { //comments while loop 2
   if ($blogPosts['title'] == $blogComments['title']) { //if statement to create comments div
   echo '<div id="blogComments" >
   <table>
   <tr><h4>'.$blogComments['title'].' by '.$blogComments['username'].' on '.$blogComments['date_posted'].'</h4></tr>
   <br></br>
   <tr>'.$blogComments['post'].'</tr>
   </table></div> <br></br>'; } //End comments loop 2
   } //End if statement
 if (isset($_COOKIE['userid'])) { //comment posting ability if logged in (display form)
  echo '<div id="respond">
  <form method="post" autocomplete="on">
  <label for="comment" class="required">Leave us a comment: </label>
  <textarea name="comment" id="comment" rows="1" width="90%" tabindex="4"  required="required"></textarea>
   <input type="hidden" name="blogcomment" value="1" id="blogcomment" /><br></br>
   <input type="hidden" name="title" value="'.$blogPosts['title'].'" id="title" />
   <input type="hidden" name="userid" value="'.$user ['username'].'" id="userid" />
   <input name="submit" type="submit" value="Post Comment" id="submit" />
  </form></div>';

  //print_r($_REQUEST);

   if (isset ($_POST['Post'])==1) { // actions to take when post is made
  $q ="INSERT INTO abstrktmainblogs (blogcomment, page_id, title, post, username, date_posted) VALUES ( '$_POST[blogcomment]','$_GET

---

','$_POST[title]','$_POST[post]','$_POST[userid]', now()";
  $r=mysqli_query($dbc, $q);
} //end Post actions
} //end display comment form
} //END foreach
} //end while loop 1
}   //end blogs function
echo '</div>'  // end post div

 

What this now does is display the 1st post with it's children 7 times. if i remove the foreach it only displays the 1st post once so where am i losing the 2nd Post and it's children?

[PFMaBiSmAd]

I'm not sure how your first - SELECT * FROM abstrktmainblogs f ... query is even matching one row, because by your own definition, blogcomment is a zero for the main blog posts, so s.blogcomment < f.blogcomment (this side is a zero) won't ever be true.

 

Your foreach(){} loop is just iterating over the columns of the row that your query fetched, so you are getting the contents of the inner loop repeated for each column that the * selected.

 

Speaking of using SELECT * in a self joined query, since the left and right columns have identical names, that is not giving you the values you want/need. You need to specifically list out the columns you want from the left and right parts of the join.

 

By your own definition, the blogcomment column for comments is the id of the main blog post. I don't know why you didn't use that for the join condition or as the value passed through the form. Also, by using the title as the join condition, your queries will be slower and you cannot ever repeat a title. Your join condition should be ON the main blog id = comment blogcomment and you need to pass the main blog id through the form to associate the comment with the blog post. Id's are unique, titles are not guaranteed to be.

 

Using an inner join will only return results that have comments. Any main blog entry that doesn't have a comment won't be present in your first SELECT * FROM abstrktmainblogs f ... query, assuming the rest of that query is doing what you expect.

 

Your inner loop is reusing the $r variable, so there's no way the outer loop will do what you expect.

 

Your use of isset($_COOKIE['userid']) to detect if someone is logged in and can post a comment will allow anyone to do so because anyone (or a bot script) can create and send a cookie to your web page. Also, by using the users id column as the cookie value, someone can just cycle through values and impersonate anyone in your users table. At a minimum, you need to generate a unique and hard to guess and hard to duplicate value and store that in the cookie and store that in a separate column in your users table. You would then use the value from the cookie to match up the user and his data in the users table. You would also use the fact that your query on the users table matches a row to determine if the visitor is logged in and the comment form is displayed and the form submission is processed. By setting up your log in system this way you can prevent users from posting (i.e. suspend or ban them) by having a status column in the users table that you check to see what the user is allowed to do.

 

Lastly, you should not run queries inside of loops. Your goal should be one query that gets the information you want in the order that you want it.

 

I'm not sure what ORDER you want the results, but the following query will get the information that you want (the alias names are b = blog, c = comment) -

 

$query = "SELECT b.id, b.title, b.username, b.date_posted, b.post, c.username as c_username,
c.date_posted as c_date_posted, c.post as c_post
FROM your_table as b LEFT JOIN your_table as c
ON b.id = c.blogcomment WHERE b.blogcomment = 0 AND b.page_id = $pg ORDER BY b.id, c.id";

[PFMaBiSmAd]

P.S. By having your form processing code AFTER the point where you are retrieving the data, you won't see any comment that is added until the page gets loaded again.

 

Form processing code needs to be near the top of the code on the page.