How to get page identifer?

chandler · July 20, 2011

Hello again

The subject may be wrong I am not sure of the technical term.

I have a comment script on several pages, and on the main index.php I have the last 5 updated comments from those pages.

How can I tell from what page a comment was made, I want to have a link for the user, to take them to the page.

For example: The last 5 comment: Name: Mr Jones Message: Bla bla bla <a href="page3.php "> read more</a>

comment code:

<?php
require_once('config.php');

if (isset($_POST['message']))
{

if (isset($_SESSION['token']) && $_POST['token'] == $_SESSION['token'])
{


ini_set('date.timezone', 'Europe/London');





function make_safe($variable) { 
	$variable = mysql_real_escape_string(trim($variable)); 
	return $variable; }

$message = make_safe($_POST['message']); $message2 = make_safe($_POST['message2']); 




// Insert a row of information into the table "example"
mysql_query("INSERT INTO comments 
(message, message2) VALUES('$message', '$message2' ) ") 
or die(mysql_error());  


}
}

$token = md5(uniqid(rand(), true));
$_SESSION['token'] = $token;



?>


<form id="contFrm" method="POST">
<input type="hidden" name="token" value="<?php echo $token;?>" />
<label><span class="required">*</span> Full Name:</label>
<input type="text" class="box"  name="message"><br />
<label><span class="required">*</span> Message: </label>
<textarea name="message2" id="message" cols="25" rows="8"></textarea><br />
<input type="submit" class="button" value="Submit">
</form> 



<?php


// Retrieve all the data from the "example" table
$result = mysql_query("SELECT * FROM comments ORDER BY id DESC")
or die(mysql_error()); 

while($row = mysql_fetch_assoc( $result ))
{
    $message  = $row['message'];
    $message2 = $row['message2'];

$message = stripslashes($message);
$message2 = stripslashes($message2);


    // Print out the contents of the entry 
    echo "<div id=\"census41_messages\">";
    echo "<div id=\"comments_box\"><div id=\"comment_name\"><p>$message<em> Says: </em></div><div id=\"comment_date\">" . date ("D, M d, Y, g:i a") . "</div><br />$message2</p></div>";
echo "</div>";
}
?>

last 5 comments made code (index.php):

<?php
require_once('config.php');

// Retrieve all the data from the "example" table
$result = mysql_query("SELECT * FROM comments ORDER BY id DESC LIMIT 5")
or die(mysql_error()); 

while($row = mysql_fetch_assoc( $result ))
{
    $message  = $row['message'];
    $message2 = $row['message2'];

$message = stripslashes($message);
$message2 = stripslashes($message2);


    // Print out the contents of the entry 
    echo "<div id=\"census41_messages_index\">";
    echo "<div id=\"comments_box_index\"><div id=\"comment_name\"><p>$message<em> Says: </em></div><div id=\"comment_date\">" . date ("D, M d, Y, g:i a") . "</div><br />$message2</p></div>";
echo "</div>";
}
?>

Many Thanks for your help.

btherl · July 20, 2011

How do you know (from your database) which page a comment was made on? The same information tells you which page to link to. I'm wondering if your comments table is supposed to have another column to say which page that comment is for?

chandler · July 20, 2011

I only have the columns id, message, message2..do I need to add another column? if so does it matter what its called or what data type it is, I am new to all this.

btherl · July 20, 2011

If you're going to have a comment system with comments on each pages, I would have a structure like this:

Table pages:

Column id

Column name

And any other columns you might want. You could put columns for access control (such as "only admin can view this page") in here if you wanted. For a CMS you could store who is allowed to edit the page. And so on. But for static pages just id and name is enough.

Table comments

Column id (this is the comment id)

Column page_id (this links each comment to a specific page)

Column full_name (don't call this "message", it'll be confusing)

Column message (This column is what you called "message2" before)

Good naming of columns is important, or you'll regret it later

Now all you have to do to link to the page is look at the page id when you get the most recent 5 comments. And you might need to look up the page name from the page id as well, which can be done from the pages table.

chandler · July 20, 2011

how would I get the page id, is there a tut around or some thing in the manual referring to this?

btherl · July 20, 2011

The page id is something that you make up yourself. Or you let the database make it for you. The number itself is meaningless, as long as there's one id for each page and it never changes.

Usually with mysql you use this feature: http://dev.mysql.com/doc/refman/5.0/en/example-auto-increment.html

That will create a unique id for each item you add to the table. So you would create the page table with an auto increment id column, and then insert each page into the table. You would end up with something like this:

id name

1 main

2 national news

3 local news

If you then added another entry "sports news", mysql will assign id number 4 to it. The next entry will get id number 5.

chandler · July 20, 2011

would I create a new table for this, I am trying to add a column with page_id AUTO_INCREMENT, but I keep getting errors as there is already a column with AUTO_INCREMENT.

error msg:

"there can be only one auto column and it must be defined as a key "

Like I said I am new to all this and its a bit confusing.

Thanks again for you help

btherl · July 20, 2011

Yes you would create a new table. Each table is for one type of "thing". Table "comments" stores comments, table "pages" stores pages. Other common tables would be "users" to store users and "orders" to store orders (for online transactions).

Each table has just one auto_increment id, and that id number uniquely identifies a row in that table.

You might want to do a tutorial on MySQL before continuing with the site you're making now.

chandler · July 20, 2011

ok thanks, I will do some reading on this today.

chandler · July 20, 2011

Hi, I was changing the column names like you suggested, I don't see any mistakes in the code but I am not getting the message displayed, I am only getting the name displayed in the name field and in the comments field.

can you see where its gone wrong?

<?php
require_once('config.php');

if (isset($_POST['message']))
{

if (isset($_SESSION['token']) && $_POST['token'] == $_SESSION['token'])
{


// Create a MySQL table in the selected database
mysql_query("CREATE TABLE comments(
id INT NOT NULL AUTO_INCREMENT, 
PRIMARY KEY(id),
name VARCHAR(30), 
message MEDIUMBLOB)")
or die(mysql_error());  

echo "Table Created!";




ini_set('date.timezone', 'Europe/London');





function make_safe($variable) { 
	$variable = mysql_real_escape_string(trim($variable)); 
	return $variable; }

$name = make_safe($_POST['name']); $message = make_safe($_POST['message']); 




// Insert a row of information into the table "example"
mysql_query("INSERT INTO comments 
(name, message) VALUES('$name', '$message' ) ") 
or die(mysql_error());  


}
}

$token = md5(uniqid(rand(), true));
$_SESSION['token'] = $token;



?>


<form id="contFrm" method="POST">
<input type="hidden" name="token" value="<?php echo $token;?>" />
<label><span class="required">*</span> Full Name:</label>
<input type="text" class="box"  name="name"><br />
<label><span class="required">*</span> Message: </label>
<textarea name="message" id="message" cols="25" rows="8"></textarea><br />
<input type="submit" class="button" value="Submit">
</form> 



<?php


// Retrieve all the data from the "example" table
$result = mysql_query("SELECT * FROM comments ORDER BY id DESC")
or die(mysql_error()); 

while($row = mysql_fetch_assoc( $result ))
{
    $message  = $row['name'];
    $message2 = $row['message'];

$message = stripslashes($name);
$message2 = stripslashes($message);


    // Print out the contents of the entry 
    echo "<div id=\"census41_messages\">";
    echo "<div id=\"comments_box\"><div id=\"comment_name\"><p>$name<em> Says: </em></div><div id=\"comment_date\">" . date ("D, M d, Y, g:i a") . "</div><br />$message</p></div>";
echo "</div>";
}
?>

Thanks

chandler · July 20, 2011

I see it now FIXED

chandler · July 20, 2011

Hi again, with the new mysql table should I have a column for the url or is that not needed? if so what datatype should be used for url's, will MEDIUMBLOB work?

like this?

// Create a MySQL table in the selected database
mysql_query("CREATE TABLE pages(
page_id INT NOT NULL AUTO_INCREMENT, 
PRIMARY KEY(id),
page_name VARCHAR(30), 
url MEDIUMBLOB)")
or die(mysql_error());  

echo "Table Created!";

I have been looking on google however I'm not finding any examples for this.

chandler · July 20, 2011

I have a problem with the date and time, it's echoing out the current time on each comment, do I need to put date and time into the db column too? or is there another way?

	<?php
require_once('config.php');

if (isset($_POST['message']))
{

if (isset($_SESSION['token']) && $_POST['token'] == $_SESSION['token'])
{



function make_safe($variable) { 
	$variable = mysql_real_escape_string(trim($variable)); 
	return $variable; }

$name = make_safe($_POST['name']); $message = make_safe($_POST['message']); 




// Insert a row of information into the table "example"
mysql_query("INSERT INTO comments 
(name, message) VALUES('$name', '$message' ) ") 
or die(mysql_error());  


}
}

$token = md5(uniqid(rand(), true));
$_SESSION['token'] = $token;



?>


<form id="contFrm" method="POST">
<input type="hidden" name="token" value="<?php echo $token;?>" />
<label><span class="required">*</span> Full Name:</label>
<input type="text" class="box"  name="name"><br />
<label><span class="required">*</span> Message: </label>
<textarea name="message" id="message" cols="25" rows="8"></textarea><br />
<input type="submit" class="button" value="Submit">
</form> 



<?php

ini_set('date.timezone', 'Europe/London');

// Retrieve all the data from the "example" table
$result = mysql_query("SELECT * FROM comments ORDER BY id DESC")
or die(mysql_error()); 

while($row = mysql_fetch_assoc( $result ))
{
    $name  = $row['name'];
    $message = $row['message'];

$name = stripslashes($name);
$message = stripslashes($message);


    // Print out the contents of the entry 
    echo "<div id=\"census41_messages\">";
    echo "<div id=\"comments_box\"><div id=\"comment_name\"><p>$name<em> Says: </em></div><div id=\"comment_date\">" . date ("D, M d, Y, g:i a") . "</div><br />$message</p></div>";
echo "</div>";
}
?>

thanks

chandler · July 20, 2011

Hi

I have made this comment script from reading a few tut's, it started off using a text file, then I was advised to switch to using MySQL db. I am completely rubbish at both PHP/MySQL and I am stuck on a few things could some one help me along with a few problems.

problem #1

The date and time system is echoing out the current time, how do I get this to show the date/time the comment was made?

problem #2

In the index.php (main page) I am showing the last 5 comments made from a number of pages, I have made a new table in the db, page_id, page_name, and page_url, in each of the 5 comments on the index.php (main page) I would like a link to the relevant page that the comment was made on. If you can give some tips on how to accomplish this please.

Here is the script

	<?php
require_once('config.php');

if (isset($_POST['message']))
{

if (isset($_SESSION['token']) && $_POST['token'] == $_SESSION['token'])
{



function make_safe($variable) { 
	$variable = mysql_real_escape_string(trim($variable)); 
	return $variable; }

$name = make_safe($_POST['name']); $message = make_safe($_POST['message']); 




// Insert a row of information into the table "example"
mysql_query("INSERT INTO comments 
(name, message) VALUES('$name', '$message' ) ") 
or die(mysql_error());  


}
}

$token = md5(uniqid(rand(), true));
$_SESSION['token'] = $token;



?>


<form id="contFrm" method="POST">
<input type="hidden" name="token" value="<?php echo $token;?>" />
<label><span class="required">*</span> Full Name:</label>
<input type="text" class="box"  name="name"><br />
<label><span class="required">*</span> Message: </label>
<textarea name="message" id="message" cols="25" rows="8"></textarea><br />
<input type="submit" class="button" value="Submit">
</form> 



<?php

ini_set('date.timezone', 'Europe/London');

// Retrieve all the data from the "example" table
$result = mysql_query("SELECT * FROM comments ORDER BY id DESC")
or die(mysql_error()); 

while($row = mysql_fetch_assoc( $result ))
{
    $name  = $row['name'];
    $message = $row['message'];

$name = stripslashes($name);
$message = stripslashes($message);


    // Print out the contents of the entry 
    echo "<div id=\"census41_messages\">";
    echo "<div id=\"comments_box\"><div id=\"comment_name\"><p>$name<em> Says: </em></div><div id=\"comment_date\">" . date ("D, M d, Y, g:i a") . "</div><br />$message</p></div>";
echo "</div>";
}
?>

Thanks for all your help, much appreciated!

TeNDoLLA · July 20, 2011

#1 You would add a field for the date in the comments table say e.g. type of DATETIME, and when you add the comment to the database you add in this field the time when the comment was added.

#2 You should probably have an another field in the comments table say e.g named 'page_id' and when adding the comment for some page you insert into this field the id of page (which will be the same as the id in the table pages for the page). So that this 'page_id' field in comments table will reference to the 'page_id' field in the pages table. When getting the comments for certaing page you join these two tables with this id and get the comments related to some page.

chandler · July 20, 2011

Wow sounds complicated, but I think I understand what you mean.

TeNDoLLA · July 20, 2011

Simple example tables could be like this

CREATE TABLE `pages` (
`page_id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`page_name` VARCHAR(255) NOT NULL,
`page_url` VARCHAR(255) NOT NULL,
PRIMARY KEY  (`page_id`)
) ENGINE=InnoDB;

CREATE TABLE `comments` (
`comment_id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`comment` TEXT NOT NULL,
`date_added` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
`page_id` INT UNSIGNED NOT NULL,
PRIMARY KEY (`comment_id`),
FOREIGN KEY (`page_id`) REFERENCES pages(`page_id`)
) ENGINE=InnoDB;

Then get comments for page with ID 1

SELECT `comment`, comment_id, date_added
FROM comments c
JOIN pages p ON p.page_id = c.page_id
WHERE c.page_id = 1 ORDER BY c.date_added

chandler · July 20, 2011

Hmm, I must be doing something very wrong, nothing is working. its so frustrating

comment script:

<?php
require_once('config.php');

if (isset($_POST['message']))
{

if (isset($_SESSION['token']) && $_POST['token'] == $_SESSION['token'])
{



function make_safe($variable) { 
	$variable = mysql_real_escape_string(trim($variable)); 
	return $variable; }

$name = make_safe($_POST['name']); $comment = make_safe($_POST['comment']); 





// Insert a row of information into the table "example"
mysql_query("INSERT INTO comments 
(name, comment) VALUES('$name', '$comment') ") 
or die(mysql_error());  


}
}

$token = md5(uniqid(rand(), true));
$_SESSION['token'] = $token;



?>


<form id="contFrm" method="POST">
<input type="hidden" name="token" value="<?php echo $token;?>" />
<label><span class="required">*</span> Full Name:</label>
<input type="text" class="box"  name="name"><br />
<label><span class="required">*</span> Message: </label>
<textarea name="comment" id="message" cols="25" rows="8"></textarea><br />
<input type="submit" class="button" value="Submit">
</form> 



<?php

ini_set('date.timezone', 'Europe/London');

// Retrieve all the data from the "example" table
$result = mysql_query("SELECT `comment`, comment_id, date_added
FROM comments c
JOIN pages p ON p.page_id = c.page_id
WHERE c.page_id = 1 ORDER BY c.date_added")
or die(mysql_error()); 

while($row = mysql_fetch_assoc( $result ))
{
    $name  = $row['name'];
    $comment = $row['comment'];

$name = stripslashes($name);
$comment = stripslashes($comment);


    // Print out the contents of the entry 
    echo "<div id=\"census41_messages\">";
    echo "<div id=\"comments_box\"><div id=\"comment_name\"><p>$name<em> Says: </em></div><div id=\"comment_date\">" . date ("D, M d, Y, g:i a") . "</div><br />$comment</p></div>";
echo "</div>";
}


?>

main page where last 5 comments are displayed:

<?php
require_once('config.php');

// Retrieve all the data from the "example" table
$result = mysql_query("SELECT `comment`, comment_id, date_added
FROM comments c
JOIN pages p ON p.page_id = c.page_id
WHERE c.page_id = 1 ORDER BY c.date_added")
or die(mysql_error()); 



while($row = mysql_fetch_assoc( $result ))
{
    $name  = $row['name'];
    $comment = $row['comment'];

$name = stripslashes($name);
$comment = stripslashes($comment);

ini_set('date.timezone', 'Europe/London');

    // Print out the contents of the entry 
    echo "<div id=\"census41_messages_index\">";
    echo "<div id=\"comments_box_index\"><div id=\"comment_name\"><p>$name<em> Says: </em></div><div id=\"comment_date\">" . date ("D, M d, Y, g:i a") . "</div><br />$comment</p></div>";
echo "</div>";
}
?>

Thanks for your help TeNDoLLA

chandler · July 20, 2011

Made some changes to comment script, giving error "Column count doesn't match value count at row 1" all columns in the db seem to be getting the data accept for comment.

	<?php
require_once('config.php');

if (isset($_POST['comment']))
{

if (isset($_SESSION['token']) && $_POST['token'] == $_SESSION['token'])
{



function make_safe($variable) { 
	$variable = mysql_real_escape_string(trim($variable)); 
	return $variable; }

$name = make_safe($_POST['name']); $comment = make_safe($_POST['comment']); 

ini_set('date.timezone', 'Europe/London');


// Insert a row of information into the table "example"
mysql_query("INSERT INTO comments 
(name, comment, date_added) VALUES('$name', '$comment' '$date_added' ) ") 
or die(mysql_error());  


}
}

$token = md5(uniqid(rand(), true));
$_SESSION['token'] = $token;



?>


<form id="contFrm" method="POST">
<input type="hidden" name="token" value="<?php echo $token;?>" />
<label><span class="required">*</span> Full Name:</label>
<input type="text" class="box"  name="name"><br />
<label><span class="required">*</span> Message: </label>
<textarea name="comment" id="message" cols="25" rows="8"></textarea><br />
<input type="submit" class="button" value="Submit">
</form> 



<?php


// Retrieve all the data from the "example" table
$result = mysql_query("SELECT `comment`, comment_id, date_added
FROM comments c
JOIN pages p ON p.page_id = c.page_id
WHERE c.page_id = 1 ORDER BY c.date_added")
or die(mysql_error()); 

while($row = mysql_fetch_assoc( $result ))
{
    $name  = $row['name'];
    $comment = $row['comment'];
$date_added = $row['date_added'];

$name = stripslashes($name);
$comment = stripslashes($comment);



    // Print out the contents of the entry 
    echo "<div id=\"census41_messages\">";
    echo "<div id=\"comments_box\"><div id=\"comment_name\"><p>$name<em> Says: </em></div><div id=\"comment_date\">" . date ("D, M d, Y, g:i a") . "</div><br />$comment</p></div>";
echo "</div>";
}
?>

TeNDoLLA · July 20, 2011

If you used the exact codes to create tables that I gave you then there is not such a field in my code as 'name'. Other thing could be that theres a column named 'comment' which is a preserved word in SQL syntax. You should put this kind of fields in the query inside the `comment` marks, dunno what they are called (backticks?).

chandler · July 20, 2011

I added the column name myself, for the name of the person leaving the comment.

TeNDoLLA · July 20, 2011

Well did you try adding the backticks or whatever they are called around the comment field in the query you run from php?

chandler · July 20, 2011

Like this?

<?php
require_once('config.php');

if (isset($_POST['comment']))
{

if (isset($_SESSION['token']) && $_POST['token'] == $_SESSION['token'])
{



function make_safe($variable) { 
	$variable = mysql_real_escape_string(trim($variable)); 
	return $variable; }

$name = make_safe($_POST['name']); $comment = make_safe($_POST['comment']); 

ini_set('date.timezone', 'Europe/London');


// Insert a row of information into the table "example"
mysql_query("INSERT INTO comments 
(name, `comment`, date_added) VALUES('$name', '$comment' '$date_added' ) ") 
or die(mysql_error());  


}
}

$token = md5(uniqid(rand(), true));
$_SESSION['token'] = $token;



?>


<form id="contFrm" method="POST">
<input type="hidden" name="token" value="<?php echo $token;?>" />
<label><span class="required">*</span> Full Name:</label>
<input type="text" class="box"  name="name"><br />
<label><span class="required">*</span> Message: </label>
<textarea name="comment" id="message" cols="25" rows="8"></textarea><br />
<input type="submit" class="button" value="Submit">
</form> 



<?php


// Retrieve all the data from the "example" table
$result = mysql_query("SELECT `comment`, name, comment_id, date_added
FROM comments c
JOIN pages p ON p.page_id = c.page_id
WHERE c.page_id = 1 ORDER BY c.date_added")
or die(mysql_error()); 

while($row = mysql_fetch_assoc( $result ))
{
    $name  = $row['name'];
    $comment = $row['comment'];
$date_added = $row['date_added'];

$name = stripslashes($name);
$comment = stripslashes($comment);



    // Print out the contents of the entry 
    echo "<div id=\"census41_messages\">";
    echo "<div id=\"comments_box\"><div id=\"comment_name\"><p>$name<em> Says: </em></div><div id=\"comment_date\">" . date ("D, M d, Y, g:i a") . "</div><br />$comment</p></div>";
echo "</div>";
}
?>

tested it and still get error "Column count doesn't match value count at row 1"

teynon · July 20, 2011

$sql="INSERT INTO comments (name, `comment`, date_added) VALUES('$name', '$comment' '$date_added' ) ";
mysql_query($sql) or die($sql ." / ".mysql_error());

Whats the SQL / Error say if you change your insert to this?

chandler · July 20, 2011

INSERT INTO comments (name, `comment`, date_added) VALUES('john doe', 'test' '' ) / Column count doesn't match value count at row 1

Sign In

How to get page identifer?

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information