Refreshing the page INSERTs into the database

**pbmods** · May 16 '07, 12:22 AM

Renamed the thread to better match contents.

Heya, rcmatt4321. Welcome to TSDN!

About the easiest way I can think of to put a stop to this is to add a unique key to your table. Log in to your MySQL server and type:

[code=sql]
ALTER TABLE `comments` ADD UNIQUE KEY `nameTime` (`name`, `time`);
[/code]

Substitute the actual names of your columns where appropriate (and you can change the name of the key from 'nameTime' to whatever you want).

This is really more of a failsafe than a fix, but it will prevent the script from inserting values for that User more than once a minute.

Incidentally, rather than figure the date manually, you can use NOW()

[code=php]
mysql_query("IN SERT INTO `comments` VALUES('$name', '$comment', NOW(), NOW())");
[/code]

**rcmatt4321** · May 16 '07, 12:24 AM

Originally posted by pbmods

Renamed the thread to better match contents.

Sorry! Thanks!

**rcmatt4321** · May 16 '07, 12:49 AM

Thank you so much! I'll do that when I finish my homework. :)

Matt

**rcmatt4321** · May 16 '07, 02:34 AM

When I try to query the db to mod the table it gives me the error
#1170 - BLOB/TEXT column 'name' used in key specification without a key length

I am using blobs for the comments and names, if this helps any. I have not used the Mod table before so I don't really understand what it does, this would be great help.

Thanks,
Matt

**pbmods** · May 16 '07, 03:31 AM

Originally posted by rcmatt4321

I am using blobs for the comments and names, if this helps any. I have not used the Mod table before so I don't really understand what it does, this would be great help.

blob is really not the proper data type for textual stuff; you'd want to use text, or better yet varchar for that.

Here's some info on string types in MySQL:

fw_error_www

http://dev.mysql.com/doc/refman/5.0/en/string-types.html

Since blobs and texts are so huge, it's generally not efficient (nor useful) to index them in their entireties. While you could create a fulltext index, this will probably not be in the best interests of your application.

Instead, I would change the `name` and `comment` fields to varchars:

[code=sql]
ALTER TABLE `comments` CHANGE `name` `name` varchar(100) not null;
ALTER TABLE `comments` CHANGE `comment` `comment` varchar(1000) not null;
ALTER TABLE `comments` ADD UNIQUE KEY `postLimiter` (`name`, `time`);
[/code]

This gives you 100 characters to work with (come on... whose name is REALLY that long?), and 1000 characters for a comment.

In addition to saving space on your server's hard drive, it will encourage your Users to post more meaningful comments; since they don't have nearly as much space to work with, they have to make it count!

You could probably even get away with smaller sizes; test it out and see what works best for you. You can always go back and make it larger/smaller.

**pbmods** · May 16 '07, 03:42 AM

Incidentally (and because I have to justify to myself why I haven't moved this thread to the MySQL forum), your problem illustrates a good reason why you should generally try to keep your display (or 'view') code separate from your data ('model') code.

Here's a simple example. Suppose we have a form that posts data to a database. You might be familiar with the concept :)

If we do this:
mypage.php
[code=html]

<?php if(! isset($_POST['data'])): ?>
<form action="mypage. php" method="post">
<textarea name="data"></textarea>
<input type="submit" value="Add to DB" />
</form>
<?php else:
mysql_connect( ... );
mysql_query("RE PLACE INTO `mytable` (`data`) VALUES('" . addslashes($_PO ST['data']) . "')");
endif;
?>
[/code]

We have a nice, compact little script that will make your life miserable if the User refreshes the page.

But what if we did this:

mypage.html
[code=html]

<form action="process .php" method="post">
<textarea name="data"></textarea>
<input type="submit" value="Add to DB" />
</form>
[/code]

process.php
[code=php]
<?php
// process.php

if(isset($_POST['data'])) {
try {
mysql_connect( ... );
mysql_query("RE PLACE INTO `mytable` (`data`) VALUES('" . addslashes($_PO ST['data']) . "')") || throw new Exception(mysql _error());
$message = 'SUCCESS';
} catch (Exception $e) {
$message = 'MYSQL_' . urlencode($e->getMessage() );
}
} else
$message = 'NO_DATA';

// Redirect to form.
header("Locatio n: mypage.html?mes sage=$message") ;
exit;
?>
[/code]

Once you redirect the User back to the form page, it doesn't matter how many times he refreshes the page. As long as you don't use up your bandwidth for the month, he can't do any damage.

**rcmatt4321** · May 16 '07, 12:29 PM

That is so much help, I think mine was so long because I tried to do the database and PHP on one page. Im printing all this stuff now, even though I have two books, so I can look at it at school when I have free class tine.

Thanks,
Matt

**rcmatt4321** · May 17 '07, 12:59 AM

O.K. I took the basic concept you had and simplified it a little bit and I wrote the code during literacy. So I put it on my server when I got home and worked most of the bugs out. It should work, I just have trouble redirecting and also some trouble out-putting the comments. I have simplified it so I don't post the time and date right now, Ill do that later.
Here is the Comments.php code
[PHP]<html>
<?php
include('loginS QL.php');
$connection = mysql_connect($ db_host, $db_username, $db_password);
if(!$connection ) {
die("Could Not Connect To The Database<br>");
};
$select = mysql_select_db ($db_database);
if(!$select) {
die("Could Not Select The Database");
};
$query = "SELECT * FROM comments";
$result = mysql_query($qu ery);
if(!result) {
die("Could Not Query The Database".mysql _error());
};
while($result_r ow = mysql_fetch_row (($result))) {
echo $result_row[1];
echo " posted this comment";
echo "<br>";
echo $result_row[2];
echo "<hr>";
};
?>
<font size = "5" color = "grey">Leav e A Comment!</font><br>
<a href = "http://www.ontheballte nnis.com/leaveComment.ph p"><Font size = "3" color = "grey">Clic k Here To Leave A Comment</Font></a>
</html>[/PHP]

And Here is the leaveComment.ph p code
[PHP]<html>
<?php
if(isset($_POST['name'])) {
include('loginS QL.php');
$connection = mysql_connect($ db_host, $db_username, $db_password);
if(!$connection ) {
die("Could Not Connect To The Database<br>");
};
$select = mysql_select_db ($db_database);
if(!$select) {
die("Could Not Select The Database");
};
$name = $_POST['name'];
$comment = $_POST['comment'];
$query = "INSERT INTO comments (name) VALUES ('$name');";
$result = mysql_query($qu ery);
if(!result) {
die("Could Not Query The Database".mysql _error());
};
$query = "INSERT INTO comments (comment) VALUES ('$comment');";
$result = mysql_query($qu ery);
if(!result) {
die("Could Not Query The Database".mysql _error());
};
header('Locatio n: http://www.ontheballte nnis.com/comments.php');
echo "</html>";
};
if(!isset($_POS T['name'])) {
echo "<font size = \"5\" color = \"grey\">Typ e your comment and name then press submit!</font><br>";
echo "<form action = \"leaveComment. php\" method = \"post\">";
echo "Name<br>";
echo "<input type = \"text\" name = \"name\"><br >";
echo "Comment <br>";
echo "<textarea cols = \"40\" rows = \"5\" name = \"comment\"> ";
echo "Type Your Comment Here! Be sure to delete this before you leave the comment.";
echo "</textarea><br>";
echo "<input type = \"submit\">" ;
echo "</form>";
echo "</html>";
};
?>[/PHP]

When it comes to the redirect part my server outputs this error

Warning: Cannot modify header information - headers already sent by (output started at /hsphere/local/home/rcmatt12/ontheballtennis .com/leaveComment.ph p:2) in /hsphere/local/home/rcmatt12/ontheballtennis .com/leaveComment.ph p on line 25

Now about the outputting part, when it outputs the comments it leaves the name and no comment then goes like it is going to post the next comment and posts no name and the comment like this

When I put in my name as Bob and comment as Brillant it posts it like this

Bob posted this comment
//// Comment Should Go Here//////

////Name Should Go Here///// posted this comment
Brilliant!

Thanks for the help,
Matt

**rcmatt4321** · May 17 '07, 03:39 AM

If it helps any, I my comments table has three cols

a post number (not used right now)
name
comment

Thanks,
Matt

**pbmods** · May 17 '07, 04:19 AM

Originally posted by rcmatt4321

header('Locatio n: http://www.ontheballte nnis.com/comments.php');
[/PHP]

When it comes to the redirect part my server outputs this error

Warning: Cannot modify header information - headers already sent by (output started at /hsphere/local/home/rcmatt12/ontheballtennis .com/leaveComment.ph p:2) in /hsphere/local/home/rcmatt12/ontheballtennis .com/leaveComment.ph p on line 25

When you redirect using header, you can't send any output to the browser, or else it won't work. As a general rule, I like to put an exit statement right after every redirect:

[code=php]
header('Locatio n: somepage.php');
exit;
[/code]

You also need to make sure that you don't output anything before you do the redirect, either. Note that spaces in front of the first <?php tag count!

If you want to have different layouts for entering comments vs. viewing comments, you might want to create three files: One that displays the form to submit the comments, one that displays the comments, and a third script that saves the comments.

When that last script finishes saving the comment, it could then redirect to the page that displays the comments (since you can redirect wherever you want o_O).

Originally posted by rcmatt4321

Now about the outputting part, when it outputs the comments it leaves the name and no comment then goes like it is going to post the next comment and posts no name and the comment like this

When I put in my name as Bob and comment as Brillant it posts it like this

Bob posted this comment
//// Comment Should Go Here//////

////Name Should Go Here///// posted this comment
Brilliant!

[code=php]
while($result_r ow = mysql_fetch_row (($result))) {
echo $result_row[1];
echo " posted this comment";
echo "<br>";
echo $result_row[2];
echo "<hr>";
};
[/code]

Try this. It won't solve the problem, but it should give you a better idea of what you're working with:
[code=php]
while($result_r ow = mysql_fetch_row (($result, MYSQL_ASSOC))) {
print_r($result _row);
print('<hr />');
};
[/code]

print_r will quickly become your best friend when working with arrays. Also, pass MYSQL_ASSOC as the second argument to mysql_fetch_row to retrieve a much more developer-friendly associative array that allows you to use column names instead of having to memorize the order :)

**rcmatt4321** · May 17 '07, 11:32 AM

Hey Im working on it, my server is down so I cannot put it up to test it out. Ill check back with you later on how it goes.

**rcmatt4321** · May 18 '07, 08:49 PM

I got it! You helped me so much! If you want to see it click here

Here is my code if you are interested. I figured out the redirect thing also. Feel free to copy it and use it.

comments.php
[PHP]<html>
<?php
include('loginS QL.php');
$connection = mysql_connect($ db_host, $db_username, $db_password);
if(!$connection ) {
die("Could Not Connect To The Database<br>");
};
$select = mysql_select_db ($db_database);
if(!$select) {
die("Could Not Select The Database");
};
$query = "SELECT * FROM comments";
$result = mysql_query($qu ery);
if(!result) {
die("Could Not Query The Database".mysql _error());
};
while($result_r ow = mysql_fetch_row (($result))) {
echo $result_row[1];
echo " posted this comment";
echo "<br>";
echo $result_row[2];
echo "<hr>";
};
?>
<font size = "5" color = "gray">Leav e A Comment!</font><br>
<a href = "http://www.ontheballte nnis.com/comment_leaving/working/leaveComment.ht m"><Font size = "3" color = "black">Cli ck Here To Leave A Comment</Font></a>
</html>[/PHP]

leaveComment.ht m
[HTML]<html>
<body bgcolor = "Navy">
<font size = "5" color = "white">Typ e your comment and name then press submit!</font><br>
<form action = "processComment .php" method = "post">
<font color = "white"> Name </font><br>
<input type = "text" name = "name"><br>
<font color = "white">Com ment </font><br>
<textarea cols = "40" rows = "5" name = "comment">
Type Your Comment Here! Be sure to delete this before you leave the comment.
</textarea>
<br>
<input type = "submit">
</form>
</html>[/HTML]

processComment. php
[PHP]<?php
include('loginS QL.php');
$connection = mysql_connect($ db_host, $db_username, $db_password);
if(!$connection ) {
die("Could Not Connect To The Database<br>");
};
$select = mysql_select_db ($db_database);
if(!$select) {
die("Could Not Select The Database");
};
$name = $_POST['name'];
$comment = $_POST['comment'];
$query = "INSERT INTO comments VALUES (\"\", '$name', '$comment');";
$result = mysql_query($qu ery);
if(!$result) {
die("Could Not Query The Database".mysql _error());
};
header('Locatio n: http://www.ontheballte nnis.com/comment_leaving/working/comments.php');
exit();
?>
[/PHP]

If you are wondering what loginSQL.php is it is a file containing my database URL, password, username and database.

Here is what it would look like.

loginSQL

<?php
$db_host = '///Put Host URL Here///';
$db_database = '///Put Database Name Here///;
$db_username = '///Put Username Here///';
$db_password = '///Put Password Here///';
?>

List Of What Does What

Comments.php

Outputs all comments in database and provides a link to leaveComment.ht m where you can type in your name and comment

leaveComment.ht m

Provides input areas for your name and comment when you press the submit button it goes to the processComment. php page

Process Comment.php

Inputs the comment into the database and redirects to the comments.php page where you would see your comment that you just posted.

List of things left to do

Make a way to post a title (Should Be Easy!)
Make it look better! Way better!
Replies (The hardest one!)
Other cosmetic stuff
More data points like e-mail and website
Spam prevention
No posting the defualt message
No posting long names or comments
No bots posting thousands of coments (ie:a random generated image to enter text in)
Time and date
Other stuff!

Thanks for the Help!,
Matt

**pbmods** · May 18 '07, 10:02 PM

Glad to hear you got it working!

Good luck with your project, and post back anytime if you get stuck.

pbmods