Page 1 of 1

A few problems in my update query

Posted: Tue Dec 22, 2009 3:03 pm
by craigwg
Hi all,

Here is my basic problem. I have a blog and I am trying to allow my readers to make comments. I have the logic set up correctly in my db. I have two issues that I can't resolve though, mostly around my update query. I'm trying to add a few pieces of information into my database, some from a form on the page and some from data fed to the page (not sure what other term to use there). The variables are coming back blank and nothing is being inserted into the table.

I posted the entire page below for context, but I added notes to the section that I believe is causing my problems (line #97). Also here are the two table's structures I am making use of. I hope that helps answer my questions. What do I need to change to make this work? Am I doing something wrong here? This seems straight forward to me! Thanks for your help. Here is the tables and the code:

Table name: tblJournal
Columns: ID, entry, Title, Category, date

Table name: tblJournalComments
Columns: ID, email, VisitorName, EntryID, DateOfComment, Comment, approved

The join is on entry to EntryID. I think the rest speaks for itself.

Code: Select all

 
<?php  
 
$page_title = 'Journal';
include ('./includes/header.html');
 
require_once ('connect.php');
 
if (isset($_GET['id'])) {
    $id = $_GET['id'];
} else { 
    $query = "select Max(id) from tblJournal order by id ASC";
    $result = mysql_query ($query);
    $row = mysql_fetch_array ($result, MYSQL_NUM);
    $id = $row[0];
}
 
$query = "
SELECT j.id, date_format(j.date, '%M %e, %Y') AS date, j.title, j.entry
FROM tblJournal AS j
where j.id='".$id."'
";
$result = @mysql_query ($query); // Run the query.
// Table Header
echo '<table align="center" cellspacing="0" cellpadding="5" border="0">';
// Fetch and print all the records
while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
 
    echo '<tr bgcolor="#FFFFFF">
        <td align="left" colspan="2">
                     <h2>' . $row['title'] . '</h2>
                     <h5>' . $row['date'] . '</h5>
                </td>
    </tr>
    <tr>
        <td align="left" colspan="2">' . $row['entry'] . '</td>
    </tr>';
 
}
mysql_free_result ($result); // Free up the resources.
 
$query = "
SELECT c.VisitorName, date_format(c.DateOfComment, '%M %e, %Y' ) AS dateofcomment, c.Comment, j.date, j.Title
FROM tblJournal AS j
LEFT JOIN tblJournalComments AS c on j.id = c.entryid
where c.approved=1 and j.id='".$id."'
";
$result = @mysql_query ($query); // Run the query.
 
    while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
    
    if (empty($result)) { //If the query above is blank...
    echo '<tr>
                <td align="right" colspan="2"><hr width="80%"></td>
        </tr>
        <tr>
                <td><h2>Be the first to leave a comment!  Use the form below.</h2></td>
        </tr>';
    } else { 
        echo '<tr>
                <td align="right" colspan="2"><hr width="80%"></td>
            </tr>
            <tr>
                <td align="left" valign="top" width="200"><b>           
                ' . $row['VisitorName'].'</b>&nbsp;said:<p></td>
                <td align="left">
                ' . $row['Comment'].'</p><font size="2" color="#666666">Posted on:
                ' . $row['dateofcomment'].'</font>
                </a></td>
            </tr>';
    }
}
echo '</center></table>';
// Handle the comment form
if (isset($_POST['submitted'])) {
    $email = ($_POST['email']);
    $errors = array(); // Initialize the error array.
    if(empty($_POST['name'])) { // Check for a name.
        $errors[] = 'You forgot to enter your name.'; // no name
    }
    if(empty($_POST['email'])) { // Check for a name.
        $errors[] = 'You forgot to enter your email address.'; // no email address
    }
    if(empty($_POST['comment'])) { // Check for a message
        $errors[] = 'You forgot to enter a comment.'; // no comment
    }
    if(strlen($_POST['comment']) > 500) { // If characters in message is over 200
        $errors[] = 'Your comment is too long (over 500 characters).'; // message is too long
    }
    if(!eregi("^[_a-z0-9-]+(\.[_a-z0-9-]+)*@[a-z0-9-]+(\.[a-z0-9-]+)*(\.[a-z]{2,3})$", $email)) { // Validate email address
        $errors[] = 'Your email address is not valid.';     
    }
if (empty($errors)) { // If everything is ok
 
 
 
##########THIS IS THE START OF THE PROBLEM SECTION #########
 
#I try to load the variables here from the query on line #43which will hold values to insert into the database later in the code
 
    $title = ($_GET['j.Title']);
    $entrydate= ($_GET['j.date']);
    $name = ($_POST['name']);
    $email = ($_POST['email']);
    $eid = ($_GET['j.id']);
    $comment= stripslashes($_POST['comment']);
 
#This line is a test, printing the values so I can see the values.  The POSTs work fine, the GETs get nothing!  I thik this is 50% of my issue
 
    print 'Variables: $title='.$title.', $entrydate='.$entrydate.', 
    $name='.$name.', $email='.$email.', $eid='.$eid.', $comment='.$comment.'';
 
# Suffice to say, this query fails and all I get is the message 'Error Updating database'.  No other comments for now but I posted the whole page in case I am missing something that you might be able to see.
 
    $query="INSERT INTO tblJournalComments (email, VisitorName, EntryID, DateOfComment, Comment) 
    values ('".$email."','".$name."','".$eid."',getdate(),'".$comment."')";
    mysql_query($query) or die ('Error updating database');
    $body = "On $entrydate someone made a comment on the journal entry titled $title.  Their name was given as {$_POST['name']}.  Their email address was entered as  {$_POST['email']}\n\nHere is the comment:\n\n$comment\n\n";
    mail('craig@craiggreenwood.com', 'Journal Comment on your Journal!',$body); //Send message to Craig
    echo '<h1>Thank you!</h1><p>Your comment will appear after approval.  For now go back to the <a href="http://www.craiggreenwood.com/journal.php">Journal</a> again.</p><p><br /></p>';
} else { // Report the errors
    echo '<h1>Error</h1><p><h2>Looks like you may have forgot to fill out some of the fields:</h2></p>';
    foreach ($errors as $msg) { // Print each error
        echo "<p> - $msg<br />\n";
    }
    echo '</p><p>Please <a href="http://www.craiggreenwood.com/journalcomment.php?id='.$id.'">try again</a>.</p><p><br /></p>';
    }
} else {
 
// Beginning of the comment form
echo '
<form action="http://www.craiggreenwood.com/journalcomment.php?id='.$id.'" method="post" name="addcomment" id="addcomment" align="center">
  <fieldset>
  <legend><font color="#000000">Add Your Comment Here:</font></legend>
  <table cellpadding="5">
     <tr>
      <td align="center"><p align="right">Name:</p><td>
          <p align="center"><input type="text" name="name" size="25"></field>
        </p></td>
      <td align="center"><p align="right">Email Address:</p><td>
          <p align="center"><input type="text" name="email" size="25"></field>
        </p></td>                             
</p></td>
    </tr>
    <tr>
      <td align="center"><p align="right">Comment:</p><td colspan="3">
          <p align="center"><textarea name="comment" rows="5" cols="75"></textarea>
        </p></td>
    </tr>
    <tr>
      <td align="right" colspan="3"><input type="submit" name="submit" value="Leave Comment" />
      <input type="hidden" name="submitted" value="TRUE" />
      </td>
    </tr>
  </table>
  </fieldset>
</form>';
mysql_free_result ($result); // Free up the resources.
 
mysql_close(); // Close the database connection
 
echo '<a href="http://www.craiggreenwood.com/journal.php">Back to the journal...</a>';
include ('./includes/footer.html');
}
?>