Page 1 of 1

Trouble updating SQL table

Posted: Thu Jan 28, 2010 1:25 pm
by Parmenion
I have created a simple blog and can show the comments on screen fine from the database, as well as being able to delete each one from a link. However, I am having problems updating the table and wonder if anyone has an idea of what is going wrong.

Under the blog comment I have a link called 'modify' that when clicked takes the user to a page to modify the post and update the database. My code for this is as follows:

Code: Select all

        
$connection = mysql_connect('localhost', 'user', 'password') or die ('Unable to connect!');
    
mysql_select_db('database') or die ('Unable to select database!');
 
$result = mysql_query("SELECT * FROM blog ORDER BY date DESC");
            
while ($row = mysql_fetch_array($result))
    {
    $date = $row['date'];
    $name = $row['name'];
    $comment = $row['comment'];
    echo '<table width="300px" border="1" cellpadding="2">';
    echo '<tr>';
    echo '<td colspan="2" bgcolor="#00A820">' . '<font color="#FFFFFF">' . 'Comment by ' .$row['name']. '</font>' . '</td>';
    echo '</tr>';
    echo '<tr>';
    echo '<td colspan="2">' . 'Posted ' .$row['date']. '</td>';
    echo '</tr>';
    echo '<tr>';
    echo '<td colspan="2">' .$row['comment']. '</td>';
    echo '</tr>';
    echo '</table>';
            
                    
    echo "<a href='blog_delete.php?blog=delete&date=$date&name=$name&comment=$comment'>Delete</a>";
    echo "|";
    echo "<a href='blog_modify.php?modify&date=$date&name=$name&comment=$comment'>Modify</a>";
    }
    echo '<br />';
    
mysql_close($connection);
 
That bit works and takes the user to the next page, which has the following code:

Code: Select all

 
$oldDate = $_GET['date'];
$oldName = $_GET['name'];
$oldComment = $_GET['comment'];
 
if (isset($_POST['post']))
    {
    
    $newName = $_POST['name'];
    $newComment = $_POST['comment'];
 
    $connection = mysql_connect('localhost', 'user', 'password') or die ('Unable to connect!');
    
    mysql_select_db('database') or die ('Unable to select database!');
 
    mysql_query("UPDATE blog SET name = '$newName' WHERE name = '$oldName' LIMIT 1");
    mysql_query("UPDATE blog SET comment = '$newComment' WHERE comment = '$oldComment' LIMIT 1");
        
    mysql_close($connection);
 
    echo '<meta http-equiv="refresh" content="0; URL=blog.php">';
    }
   
I get to the page to edit the post, which has the old name and old comment filled in, so I know it is getting the values. However, when I submit the form to itself to run the update code it tells me the three $_GET variables are undefined and seems to have forgotten them. Is there a way around this as I don't know why it's gone wrong?

Thanks

EDIT: Forgot to post the form code for what it matters:

Code: Select all

 
<h2>Modify post:</h2>
<form action="blog_modify.php" method="post">
<p><input type="text" value="<?php echo $oldName; ?>" size="45" name="name"></p>
<p><textarea name="comment" rows="8" cols="40" wrap="virtual" /><?php echo $oldComment; ?></textarea></p>
<input type="submit" name="post" value="Post">
</form>
 

Re: Trouble updating SQL table

Posted: Thu Jan 28, 2010 1:58 pm
by AbraCadaver
First you should move the assignment of the get vars to an else statement of the if(isset()) or wherever you display your form. On the post operation the previous get vars are lost. The easiest fix would be to include hidden inputs in the form and then inside the if you would get the vars from $_POST:

Code: Select all

<input type="hidden" value="<?php echo $oldName; ?>" name="name">
<input type="hidden" value="<?php echo $oldComment; ?>" name="name">
Really though, I would be using the ID of the post or comment to be updating or deleting. What if you have two of the same comments or two things with the same name? You should be using WHERE comment_id = $comment_id or something similar.

In closing, run all vars that you get from post or get through mysql_real_escape_string() to prevent SQL injection attacks.

Re: Trouble updating SQL table

Posted: Thu Jan 28, 2010 5:06 pm
by Parmenion
Thanks very much, AbraCadaver! It now all works and I have added an Id field, so it updates from the Id.

The only thing I don't like is that it updates the date posted, which I haven't told it to do. Will it do that if it's set to TIMESTAMP?

Oh, also can the TIMESTAMP be converted to something like '28/01/2010 23:04'? Any idea how I'd go about doing it?

Re: Trouble updating SQL table

Posted: Thu Jan 28, 2010 5:34 pm
by AbraCadaver
Parmenion wrote:Thanks very much, AbraCadaver! It now all works and I have added an Id field, so it updates from the Id.

The only thing I don't like is that it updates the date posted, which I haven't told it to do. Will it do that if it's set to TIMESTAMP?

Oh, also can the TIMESTAMP be converted to something like '28/01/2010 23:04'? Any idea how I'd go about doing it?
With both DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP clauses, the column has the current timestamp for its default value, and is automatically updated.
With neither DEFAULT nor ON UPDATE clauses, it is the same as DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP.
With a DEFAULT CURRENT_TIMESTAMP clause and no ON UPDATE clause, the column has the current timestamp for its default value but is not automatically updated.

So you want DEFAULT CURRENT_TIMESTAMP for the timestamp field in your table definition.

To convert the format, you can do it in the query with the mysql DATE_FORMAT() or after the query with a combination of the PHP date() and strtotime(). I'd probably go with DATE_FORMAT().

Re: Trouble updating SQL table

Posted: Fri Jan 29, 2010 10:26 am
by Parmenion
Thanks for the fast reply, AbraCadaver. From that I've managed to change the TIMESTAMP so it no longer updates when a post modification is made.

I'm going to have a go at the DATE_FORMAT() next. I better look up how it works as I have never used it before.

Must say it's nice to have somewhere to get PHP help as I'm quite new to PHP.

Re: Trouble updating SQL table

Posted: Sat Jan 30, 2010 9:20 am
by Parmenion
I'm encountering another problem now. I wasn't able to add apostrophes into the comment box so added the following code that solved the issue:

Code: Select all

 
$char = "'";
$comment = str_replace ($char, "''", $comment);
 
It now goes into the database fine and displays on screen fine when put into a table. However, I have a delete and modify link that uses $_GET to receive the variables on the next page and it will only display the text before the apostrophe.

For example:

He'll win the match

comes out as:

He

Any idea why it does this sending the variable to another page?

EDIT:

I just had a thought straight after posting and tried changing the apostrophe to a quotation mark before sending it across and converted it back to an apostrophe on the next page and it seems to display now. Is there a simpler way of doing this or do I need to convert the apostrophe before and after sending the variable?

Re: Trouble updating SQL table

Posted: Sat Jan 30, 2010 10:18 am
by AbraCadaver
You need to prep the var before inserting into the db:

Code: Select all

if(magic_quotes_gpc()) {
   $var = stripslashes($var);
}
$var = mysql_real_escape_string($var);
Also, unless you're displaying HTML, you should run the var through htmlentities() before display.

Re: Trouble updating SQL table

Posted: Sat Jan 30, 2010 12:05 pm
by Parmenion
Thanks for the information, AbraCadaver.