Help with strange database problem

[Paul15679]

I've written a script to process user comments on a blog that grabs the post_id from the URL and passes it to a database query along with the other parameters necessary to insert the comment in the database. The problem is that the post_id isn't being passed to the query.

 

The comments are being created in the database, but they all have a post_id of 0. I've echoed the $post_id variable in the script, and it echoes correctly. When I echo the query, it shows

 

INSERT INTO comments VALUES (NULL,'2','','Test comment ','I hope this works!',NULL)

 

with a blank between '2' and 'Test comment', which is where the post_id should be. Can anyone suggest why the post_id might be being grabbed from the URL correctly but not passed to the query? The full code of my script is below. I'd be really grateful for any help

 

<?php
session_start();

require_once('config.php'); 
require_once('c:\wamp\www\db_login.php'); 
require_once('DB.php');

// Display the page header
$smarty->assign('blog_title',$blog_title);
$smarty->display('header.tpl');


// Check for valid login
if (!isset($_SESSION["username"])) 
{ 
echo "Please <a href='login.php'>Login</a>.";
exit;
}

//Connect to the database
$connection = DB::connect("mysql://$db_username:$db_password@$db_host/$db_database");

	if (DB::isError($connection))
	{
	die ("Could not connect to the database: <br />". DB::errorMessage($connection));
	}

$stop=FALSE;

// grab submission variables
$post_id=$_GET[post_id];
$title=$_POST['title'];
$body=$_POST['body'];
$action=$_POST['action'];
$category_id=$_POST['category_id'];
$user_id=$_SESSION["user_id"];
$comment_id=$_POST['comment_id'];

echo $post_id;


if ($_GET['action']=="delete" AND !$stop)
{
$comment_id=$_GET["comment_id"];
$comment_id=mysql_real_escape_string(get_magic_quotes_gpc() ? stripslashes($comment_id): 
$comment_id);
$query= "DELETE FROM comments WHERE comment_id='".$comment_id."' AND 
user_id='".$user_id."'";
$result=$connection->query($query);
if (DB::isError($result))
{
	die ("Could not query the database: <br />".$query. " ".DB::errorMessage($result));
}
echo ("Deleted successfully.<br />");
$stop=TRUE; 
}

// We are editing an entry, explicitly grab the id from the url
if ($_GET["comment_id"] AND !$stop)
{
$comment_id=$_GET["comment_id"];
$query= "SELECT * FROM comments NATURAL JOIN users WHERE comment_id =".$_GET["comment_id"];
$result=$connection->query($query);
if (DB::isError($result))
{
	die ("Could not query the database: <br />". $query. " ".DB::errorMessage($result));
}
while ($result_row= $result->fetchRow(DB_FETCHMODE_ASSOC))
{
$comments[]=array('title'=>htmlentities($result_row['title']),
									'body'=>htmlentities($result_row['body']),
									'comment_id'=>$result_row['comment_id']);
}
$post_id=$_GET["post_id"];
$smarty->assign('action','edit');
$smarty->assign('comments',$comments);
$smarty->assign('post_id',htmlentities($post_id));
$smarty->display('comment_form.tpl');


//Display the footer
$smarty->display('footer.tpl');
exit;
}


echo $post_id;


//The form was submitted, was it an add or an edit
if ($_POST['submit'] AND !$stop)
{
	//validate fields
	if ($title == "")
	{
	echo 'Title must not be null.<br />';
	$found_error= TRUE;
	$stop= TRUE;
	}
	if($body == "")
	{
	echo "Body must not be null.<br />";
	$found_error= TRUE;
	$stop= TRUE;
	}


	//validated ok lets hit the database
	if ($_POST['action'] == "add" AND !$stop)
	{
	$title=mysql_real_escape_string(get_magic_quotes_gpc() ? stripslashes($title): 
$title); 	 
	$body=mysql_real_escape_string(get_magic_quotes_gpc() ? stripslashes($body): 
$body);
	$post_id=mysql_real_escape_string(get_magic_quotes_gpc() ? stripslashes($post_id): 
$post_id);
	$user_id=mysql_real_escape_string(get_magic_quotes_gpc() ? stripslashes($user_id): 
$user_id);
	$query= "INSERT INTO comments VALUES 
(NULL,'".$user_id."','".$post_id."','".$title."','".$body."', NULL)";
	$result=$connection->query($query);
if (DB::isError($result))
{
	die ("Could not query the database: <br />".$query." ".DB::errorMessage($result));
}
	echo "Posted Successfully.<br />";
	$stop=TRUE;
}
echo $query;
}
if ($_POST['action']=="edit" AND !$stop)
	{			 
	$title=mysql_real_escape_string(get_magic_quotes_gpc() ? stripslashes($title): 
$title); 	 
	$body=mysql_real_escape_string(get_magic_quotes_gpc() ? stripslashes($body): 
$body);
	$comment_id=mysql_real_escape_string(get_magic_quotes_gpc() ? stripslashes($comment_id): 
$comment_id);
	$user_id=mysql_real_escape_string(get_magic_quotes_gpc() ? stripslashes($user_id): 
$user_id);
	$query= "UPDATE comments SET title ='".$title."',body= '".$body."' 
	WHERE comment_id= '".$comment_id."' AND user_id= '".$user_id."'";
	$result=$connection->query($query);
if (DB::isError($result))
{
	die ("Could not query the database: <br />". $query." ".DB::errorMessage($result));
}
	echo "Updated Successfully.<br />";
	$stop=TRUE;
}

if (!$stop)
{
//Display the blank form
//Create an empty entry
$post_id=$_GET["post_id"];
$result_row=array('title'=>NULL,'body'=>NULL,'comment_id'=>NULL);
$comments[]=$result_row;
//Get the categories
$smarty->assign('post_id',htmlentities($post_id));
$smarty->assign('comments',$comments);
$smarty->assign('action','add');
$smarty->display('comment_form.tpl');
}

if ($found_error)
{
//assign old values
//redisplay form
$post_id=$_GET["post_id"];
$result_row=array('title'=>htmlentities($title),'body'=>htmlentities($body),
'comment_id'=>htmlentities($comment_id));
$comments[]=$result_row;
$smarty->assign('action',htmlentities($action));
$smarty->assign('post_id',htmlentities($post_id));
$smarty->assign('comments',$comments);
$smarty->display('comment_form.tpl');
}

//Display the footer
$smarty->display('footer.tpl');	


?>

[phpdragon]

your missing the quotes ''

 

$post_id=$_GET[post_id];

 

should be

 

$post_id=$_GET['post_id'];

 

[Paul15679]

Thanks, but I've tried that and it didn't make any difference.

[sloth456]

Hmm the query looks fine to me, I would suggest you do an

 

echo $query;

 

just after you define it, to see if it looks right.

 

Then the only thing I can think of is that you're inserting into the wrong column or something :/

 

[Yesideez]

Can you show the structure of your table? You can export it from within cPanel

[Paul15679]

Hmm the query looks fine to me, I would suggest you do an

 

echo $query;

 

just after you define it, to see if it looks right.

 

Then the only thing I can think of is that you're inserting into the wrong column or something :/

 

When I echo the query, I get

INSERT INTO comments VALUES (NULL,'2','','Test comment ','I hope this works!',NULL)

with no value between the '2' and the 'Test comment' values. This is where the post_id value should be being inserted into the table but isn't, despite being succesfully grabbed from the URL.

 

Can you show the structure of your table? You can export it from within cPanel

 

This is the structure of the comments table:

CREATE TABLE `comments` (
  `comment_id` int(11) NOT NULL auto_increment,
  `user_id` int(11) NOT NULL,
  `post_id` int(11) NOT NULL,
  `title` varchar(150) NOT NULL,
  `body` text NOT NULL,
  `posted` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  PRIMARY KEY  (`comment_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;

 

Thanks for your help guys