page_id in database

[Javil]

Hi all,

 

In a tutorial of making a comment system using php/ database, is written:

To make comments appear on different pages of your website (without getting mixed up), you’ll need to add a new column to the database, something like ‘page_id’.

 

    After this you will need to assign each page a unique ID number by which you will distinguish it from the others (the best option is to use the same ID your CMS has assigned to the page).

 

    When you insert a comment, you will have to add this ID as well. To show the comments for this particular page, just add a WHERE clause to the SELECT query in your file.php:

$comments = array();
    $result = mysql_query("SELECT * FROM comments
    WHERE page_id = ".$theIDofThePage." ORDER BY id ASC");

 

I created a database, now after days of searching and a lot of trouble i still not able to get it working..

So i will be glad to get some help.

 

Ok, i created a database.., it is working.

I put this in my webpage:

 

<?php

// Error reporting:
error_reporting(E_ALL^E_NOTICE);

include "connect.php"; // conection details in here
include "comment.class.php";


/*
/
Select all the comments and populate the $comments array with objects
*/

$comments = array();
$result = mysql_query("SELECT * FROM comments
WHERE page_id = ".$theIDofThePage." ORDER BY id ASC");

while($row = mysql_fetch_assoc($result))
{
$comments[] = new Comment($row);
}

?> 

 

I created the database like this:

 

--
-- Table structure for table `comments`
--

CREATE TABLE `comments` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `name` varchar(128) collate utf8_unicode_ci NOT NULL default '',
  `url` varchar(255) collate utf8_unicode_ci NOT NULL default '',
  `email` varchar(255) collate utf8_unicode_ci NOT NULL default '',
`page_id` varchar(50) collate utf8_unicode_ci NOT NULL default '',
  `body` text collate utf8_unicode_ci NOT NULL,
  `dt` timestamp NOT NULL default CURRENT_TIMESTAMP,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

 

Now, my page_id is in the database, but where must i put the ".theIDofThePage."?

 

Hope someone understand want i'm doing here..

[gizmola]

This assumes that you have some system where "pages" of the site come out of a database and have an id number associated with them.  Is that the case for your system?

[Javil]

Ehm, maybe i need to be more specific,

 

What i want is to make a comment system.

 

I put this on one of my webpages where i want to add a commentsystem:

 

<?php

// Error reporting:
error_reporting(E_ALL^E_NOTICE);

include "connect.php"; //my connection details are in here
include "comment.class.php";


/*
/	Select all the comments and populate the $comments array with objects
*/

$comments = array();
$result = mysql_query("SELECT * FROM comments ORDER BY id ASC");

while($row = mysql_fetch_assoc($result))
{
$comments[] = new Comment($row);
}

?>

 

And this, on the same page:

 

<div class="commentBox">
    <?php

/*
/	Output the comments one by one:
*/

foreach($comments as $c){
echo $c->markup();
}

?>

<div id="addCommentContainer">
<p>Add a Comment</p>
<form id="addCommentForm" method="post" action="">
    	<div>
        	<label for="name">Your Name</label>
        	<input type="text" name="name" id="name" />
            
            <label for="email">Your Email</label>
            <input type="text" name="email" id="email" />
            
            <label for="url">Website (not required)</label>
            <input type="text" name="url" id="url" />
            
            <label for="body">Comment Body</label>
            <textarea name="body" id="body" cols="20" rows="5"></textarea>
            
            <input type="submit" id="submit" value="Submit" />
        </div>
    </form>
</div> 

</div>

 

What i did is, i execute a *.sql file.

 

--
-- Table structure for table `comments`
--

CREATE TABLE `comments` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `name` varchar(128) collate utf8_unicode_ci NOT NULL default '',
  `url` varchar(255) collate utf8_unicode_ci NOT NULL default '',
  `email` varchar(255) collate utf8_unicode_ci NOT NULL default '',
`page_id` varchar(50) collate utf8_unicode_ci NOT NULL default '',
  `body` text collate utf8_unicode_ci NOT NULL,
  `dt` timestamp NOT NULL default CURRENT_TIMESTAMP,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

 

So my database is made.

This all is working! BUT, if i want to make another comment system for another topic, i get in trouble because

if i copy this all over in another webpage, i will load the same comments. So i need to make it unique..

 

The trouble is that i don't know how to do this...

 

[gizmola]

I understood your original question.  What allows you to have multiple "groups" of comments and to keep them seperate is the use of the page_id value in the rows.  As in your original code, you then need to have WHERE page_id = $pageid in your query.    If you want to just do this manually, you can hardwire the page_id value into each page.  You haven't shown the code that will actually INSERT a row into the comments table.

[Javil]

<?php

// Error reporting:
error_reporting(E_ALL^E_NOTICE);

include "connect.php";
include "comment.class.php";

/*
/	This array is going to be populated with either
/	the data that was sent to the script, or the
/	error messages.
/*/

$arr = array();
$validates = Comment::validate($arr);

if($validates)
{
/* Everything is OK, insert to database: */

mysql_query("	INSERT INTO comments(name,url,email,body)
				VALUES (
					'".$arr['name']."',
					'".$arr['url']."',
					'".$arr['email']."',
					'".$arr['body']."'
				)");

$arr['dt'] = date('r',time());
$arr['id'] = mysql_insert_id();

/*
/	The data in $arr is escaped for the mysql query,
/	but we need the unescaped variables, so we apply,
/	stripslashes to all the elements in the array:
/*/

$arr = array_map('stripslashes',$arr);

$insertedComment = new Comment($arr);

/* Outputting the markup of the just-inserted comment: */

echo json_encode(array('status'=>1,'html'=>$insertedComment->markup()));

}
else
{
/* Outputtng the error messages */
echo '{"status":0,"errors":'.json_encode($arr).'}';
}

?>

 

This is the submit.php which is a part of the comment system.

My trouble is that i have experience of php /html, but database and phpadmin is really a struggle for me..

If i can manage to create a system where i can put unique comment system to every page i like, and how to setup that

in phpadmin, i would be really glad, but for now, i really dont know how to realize that.

So if i knew the correct setup...

 

I really appreciate your help!

[gizmola]

Your insert query does not even include page_id.  It also seems that this is part of some ajax system.  I'm not willing to get into all that with you, but at minimum change your query:

 

mysql_query("	INSERT INTO comments(name,url,page_id,email,body)
				VALUES (
					'".$arr['name']."',
					'".$arr['url']."',
                                                '".$arr['pageid']."',
					'".$arr['email']."',
					'".$arr['body']."'
				)");

 

In your comment form add a hidden field:

 

 

For each different page you have you will need to use a different value, of course.

[Javil]

I adjusted this, but is this the correct id? ".$pageid." ?

$comments = array();
$result = mysql_query("SELECT * FROM comments
WHERE page_id = ".$pageid." ORDER BY id ASC"); 

while($row = mysql_fetch_assoc($result))
{
$comments[] = new Comment($row);
}
?>

 

And in my submit.php i adjust, like you told:

 

mysql_query("	INSERT INTO comments(name,url,email,body,page_id)
				VALUES (
					'".$arr['name']."',
					'".$arr['url']."',
					'".$arr['email']."',
					'".$arr['body']."',
					'".$arr['pageid']."'
				)"); 

 

Error :

 

Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in /webadres/

Yes it is an ajax system. I dont think the problem is really big, i is just that i don't know the right syntax..

[gizmola]

Where you query for the results, you need to have $pageid set to something.  So before the query you need to have:

 

$pageid = 1;

 

You would change that value for each seperate page, so that the form and the value of $pageid variable match.

[Javil]

Sorry, i'm really stuck.

 

There are so many variable's now..

<input type="hidden" id="pageid" name="pageid" value="1" />

on my form i have pageid, with a value of 1..

$comments = array();
$result = mysql_query("SELECT * FROM comments
WHERE page_id = ".$pageid." ORDER BY id ASC");

also on my form webpage, i have this ".$pageid." <- is this correct ??

mysql_query("	INSERT INTO comments(name,url,email,body,page_id)
				VALUES (
					'".$arr['name']."',
					'".$arr['url']."',
					'".$arr['email']."',
					'".$arr['body']."',
					'".$arr['pageid']."'
				)");

and is this correct?

 

Maybe you can have a a look here, for the complete tutorial.

http://tutorialzine.com/2010/06/simple-ajax-commenting-system/

The only thing i want, is to make this comment system unique..

 

I'll hope you have some patience left for me..

 

[gizmola]

As far as I can tell based on the information you provided you have all the pieces.  I don't have time to look at the tutorial, and even if I did that wouldn't fix problems in your version of the code.  You have to try things out, and debug it using things like $echo in your code to see what the value of variables are.