Trouble updating SQL table

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
Parmenion
Forum Commoner
Posts: 35
Joined: Sat Dec 12, 2009 8:29 am

Trouble updating SQL table

Post 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>
 
User avatar
AbraCadaver
DevNet Master
Posts: 2572
Joined: Mon Feb 24, 2003 10:12 am
Location: The Republic of Texas
Contact:

Re: Trouble updating SQL table

Post 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.
mysql_function(): WARNING: This extension is deprecated as of PHP 5.5.0, and will be removed in the future. Instead, the MySQLi or PDO_MySQLextension should be used. See also MySQL: choosing an API guide and related FAQ for more information.
Parmenion
Forum Commoner
Posts: 35
Joined: Sat Dec 12, 2009 8:29 am

Re: Trouble updating SQL table

Post 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?
User avatar
AbraCadaver
DevNet Master
Posts: 2572
Joined: Mon Feb 24, 2003 10:12 am
Location: The Republic of Texas
Contact:

Re: Trouble updating SQL table

Post 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().
mysql_function(): WARNING: This extension is deprecated as of PHP 5.5.0, and will be removed in the future. Instead, the MySQLi or PDO_MySQLextension should be used. See also MySQL: choosing an API guide and related FAQ for more information.
Parmenion
Forum Commoner
Posts: 35
Joined: Sat Dec 12, 2009 8:29 am

Re: Trouble updating SQL table

Post 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.
Parmenion
Forum Commoner
Posts: 35
Joined: Sat Dec 12, 2009 8:29 am

Re: Trouble updating SQL table

Post 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?
User avatar
AbraCadaver
DevNet Master
Posts: 2572
Joined: Mon Feb 24, 2003 10:12 am
Location: The Republic of Texas
Contact:

Re: Trouble updating SQL table

Post 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.
mysql_function(): WARNING: This extension is deprecated as of PHP 5.5.0, and will be removed in the future. Instead, the MySQLi or PDO_MySQLextension should be used. See also MySQL: choosing an API guide and related FAQ for more information.
Parmenion
Forum Commoner
Posts: 35
Joined: Sat Dec 12, 2009 8:29 am

Re: Trouble updating SQL table

Post by Parmenion »

Thanks for the information, AbraCadaver.
Post Reply