[RESOLVED] Extra rows in mySQL database

[aksival]

I have a form that sends information to another page. This page handles the form data by inserting a row into the database and returning a success/error message depending on success/failure.

The problem is that the data gets written to the table, but two additional rows also get written, where everything is blank except for the date, time and IP fields.

I have been troubleshooting this all night and can't seem to pin-point the problem. If I tell the script to exit() immediately after the query is sent, only one row is written. If I send the exit() further down the page, even before any other PHP is executed, I get the blank rows again.

When I tested it in another browser (Konqueror) I only got one extra row instead of two. This makes me wonder if it is a browser issue and not a scripting issue. The site's using sessions with the IE6 fix:

	session_start();
	header("Cache-control: private"); //IE 6 Fix

But that's never hurt anything before. Weird...if I comment out the session code I get three extra rows using Firefox as opposed to two.

Using: PHP5, Apache 2, MySQL, Firefox 1.5

Here is the code snippet for the database operation (very simple!):

// Gather comment date
$name = strip_tags(addslashes($_POST['comment-name']));
$email = strip_tags(addslashes($_POST['comment-email']));
$comment_entry = strip_tags(addslashes($_POST['comment-entry']));
$blog_id = $_POST['blog_id'];
$comment_time = date("H:i:s");
$comment_date = date("Y-m-d");
$ip = $_SERVER['REMOTE_ADDR'];

// Connect to database
require_once($_SERVER['DOCUMENT_ROOT'] . "/includes/database.inc.php");
$link = mysql_connect($dbhost, $dbuser, $dbpass);
mysql_select_db($dbname, $link) or die("Unable to connect to database");
// Send query
$query = "INSERT INTO blog_comments SET blog_id='$blog_id', name='$name', email='$email', comment='$comment_entry', comment_date='$comment_date', comment_time='$comment_time', ip='$ip'";
$result = mysql_query($query);
// Close the database
mysql_close($link);

No clue on this one, and my best friend Google couldn't even help me 🙁

Any ideas? 😕

[bradgrafelman]

Try doing your query like this:

    $query = "INSERT INTO blog_comments (`blog_id`, `name`, `email`, `comment`, `comment_date`, `comment_time`, `ip`) VALUES ($blog_id, '$name', '$email', '$comment_entry', '$comment_date', '$comment_time', '$ip')";

I'm guessing that blog_id is a numeric column, so I removed the quotes around its value (numeric values such as integers and whatnot shouldn't be treated as strings).

Also, why have two fields of comment_date and comment_time, and not just store the Unix timestamp in one field?

[aksival]

Aha! The query works fine the way it is - with one exception:

I'm guessing that blog_id is a numeric column, so I removed the quotes around its value (numeric values such as integers and whatnot shouldn't be treated as strings).

Indeed, blog_id is an integer and the quotes, for some reason, were causing the extra blank entries.

If anyone knows why this happens please post to clarify...I'd much rather receive a warning or error message than extra rows of data appearing mysteriously!

Thanks, bradgrafelman.

[bradgrafelman]

I don't think it was the quotes, but then again I'm not sure exactly what was causing it. I just know that I don't use the "INSERT INTO.. SET .." syntax and prefer the VALUES() method. It should work either way, though, but I'm glad it works now nonetheless.

[aksival]

I don't see why it would be the quotes either, but after extensive testing I find that to be the definate cause of the problem. I can't seem to get it to "break" now.

Oh well, seems fine now. Thanks again.