Question about updating select variables in a db row

[badgoat]

Hi! I put this question at the end of my last thread, the original question which was resolved, so I thought it might bet answered if I made a new thread.

As I understand it, the below statement will update a db record.. My question, will it update only the variable that changes? Or will I have to re-set every variable and update the entire row? And, is the best way to use an HTML form with text boxes which with which the variable can be changed?

$query="UPDATE table SET variable='".$variable."'";

[devinemke]

that query will update the field "variable" to the value of $variable in every record in the table. if you want to specify only certain records to update then you must use a WHERE clause in your query.

[badgoat]

Is a WHERE clause capable of updating only fields that have changed? And if so, is the text box and HTML form the best solution for this?

[bpat1434]

It will update only the values that you specifically SET. You won't have to reset each value.

If you're going to be doing an update script, your best bet would be to run a query to get the current values, and then put them as the current value of the text-box.

<?php
// Connect to database

if(!empty($action) && $_GET['action'] === 'update')
{
    $id = $_REQUEST['id'];
    $query = "UPDATE table SET variable = '".$_REQUEST['variable']."'";
    $result = mysql_query($query);
    if(!$result)
    {
        echo 'Error processing request.';
    }
    else
    {
        echo 'Request processed successfully.';
    }
}

$id = $_GET['id'];
// The ID is passed through the URL to specify the row

$query = "SELECT * FROM table WHERE id = '$id'";
$result = mysql_query($query);
$row = mysql_fetch_array($result);

echo '<form name="update" action="post">
    <input type="hidden" value="update" name="action">
    <input type="hidden" name="id" value="'.$id.'">
    <input type="text" value="'.$row['variable'].'"><br>
    <input type="submit" value="Update">
</form>';

~Brett

[devinemke]

Originally posted by badgoat
Is a WHERE clause capable of updating only fields that have changed?

sure. but it's up to you (the porgrammer) to determine the criteria by which you say a record has "changed". changed since when? 5 seconds ago? 5 minutes ago? 5 years ago?

[badgoat]

bpat1434: Thank you! I am going through the script, trying to understand it. One question.. I pasted that into a page and altered certain parts to jive it with my scripts, and I get a funny error when I run it:

The requested URL /post was not found on this server.

http://localhost/post?action=update&id=35

The only reference with post in it is inthe form, and even when I change the action I can't get it to stop the error.

devinemke: By change I mean when the variable has changed, most of which are names.

[bpat1434]

Sorry, change the form tag from:
<form name="update" action="post">
to
<form name="update" method="post">

Sorry.

~Brett

[badgoat]

That did it, kinda! Now when I click the Update button after entering a different value, it refreshes back to the old value rather than kleeping the new one. Did I miss a step?
Code:

    <?php
    // Connect to database
$connect= mysql_connect("localhost","root") 
   or die("Could not connect to database in localhost !");
$result=mysql_select_db("testdiw") 
   or die("Could not select that database !");



if(!empty($action) && $_GET['action'] === 'update')
{
    $id = $_REQUEST['id'];
    $query = "UPDATE mktime SET variable = '".$_REQUEST['variable']."'";
    $result = mysql_query($query);
    if(!$result)
    {
        echo 'Error processing request.';
    }
    else
    {
        echo 'Request processed successfully.';
    }
}

$id = $_GET['id'];
// The ID is passed through the URL to specify the row

$query = "SELECT * FROM mktime WHERE id = '$id'";
$result = mysql_query($query);
$row = mysql_fetch_array($result);

echo '<form name="update" method="post">
    <input type="hidden" value="update" name="action">
    <input type="hidden" name="id" value="'.$id.'">
    <input type="text" value="'.$row['diwtitle'].'"><br>
    <input type="submit" value="Update">
</form>'; 

[bpat1434]

Well, you don't need to the $result = mysql_select_db(). You can just have:
mysql_select_db().

And I made another typo flub. It should be:
if(!empty($GET['action']) && $GET['action'] === 'update')

<?php
 // Connect to database
$connect= mysql_connect("localhost","-----") 
   or die("Could not connect to database in localhost !");
mysql_select_db("testdiw") 
   or die("Could not select that database !");

if(!empty($_GET['action']) && $_GET['action'] === 'update')
{
    $id = $_REQUEST['id'];
    $query = "UPDATE mktime SET variable = '".$_REQUEST['variable']."'";
    $result = mysql_query($query);
    if(!$result)
    {
        echo 'Error processing request.';
    }
    else
    {
        echo 'Request processed successfully.';
    }
}

$id = $_GET['id'];
// The ID is passed through the URL to specify the row

$query = "SELECT * FROM mktime WHERE id = '$id'";
$result = mysql_query($query);
$row = mysql_fetch_array($result);

echo '<form name="update" method="post">
    <input type="hidden" value="update" name="action">
    <input type="hidden" name="id" value="'.$id.'">
    <input type="text" value="'.$row['diwtitle'].'"><br>
    <input type="submit" value="Update">
</form>';
?>

[badgoat]

OK, last(hopefully) noob question..
In this line
$query = "UPDATE mktime SET variable = '".$_REQUEST['variable']."'";

Does the word variable need to change to something specific?

[bpat1434]

The syntax is:

UPDATE table_name SET column_name = 'new_value'

Depends on what column name you are updating. And the $_REQUEST[] array is the name of the value that is being updated.

So in your code, you need to assign a "name" value to the text input that is going to be updating.

<input type="text" name="title" value="'.$row['diwtitle'].'">

If you use that example, then you would use $REQUEST['title'] instead of $REQUEST['variable']

~Brett

[badgoat]

Ah, ok... So, if I have 5 variables on the page and only two need updating, I can update more than one, I would just need to append the line then.. Correct?

[bpat1434]

Then you would update each one (no matter what the value). But yes, you would append the lines to add to the form, and to update each value.

~Brett

[badgoat]

OK.. I don't think I will push the project that way.. I'd rather keep it simple..

I was wrong, there is one more noob question.. I have everything set as this thread has instructed, yet the variable doesn't change to the new one I enter in. Here is the code.. In the meanwhile, I am trying to figure it out on my own. :bemused:

    <?php
    // Connect to database
$connect= mysql_connect("localhost","root") 
   or die("Could not connect to database in localhost !");
$result=mysql_select_db("testdiw") 
   or die("Could not select that database !");



if(!empty($_GET['action']) && $_GET['action'] === 'update')
{
    $id = $_REQUEST['id'];
    $query = "UPDATE mktime SET diwtitle = '".$_REQUEST['newtitle']."'";
    $result = mysql_query($query);
    if(!$result)
    {
        echo 'Error processing request.';
    }
    else
    {
        echo 'Request processed successfully.';
    }
}

$id = $_GET['id'];
// The ID is passed through the URL to specify the row

$query = "SELECT * FROM mktime WHERE id = '$id'";
$result = mysql_query($query);
$row = mysql_fetch_array($result);

echo '<form name="update" method="post">
    <input type="hidden" value="update" name="action">
    <input type="hidden" name="id" value="'.$id.'">
    <input type="text" name="newtitle" value="'.$row['diwtitle'].'"><br>
    <input type="submit" value="Update">
</form>';
?> 

[bpat1434]

Not sure why. But try this:

<?php
    // Connect to database
$connect= mysql_connect("localhost","root") 
   or die("Could not connect to database in localhost !");
mysql_select_db("testdiw") 
   or die("Could not select that database !");



if(!empty($_GET['action']) && $_GET['action'] === 'update')
{
    $id = $_REQUEST['id'];
    $_GET['id'] = $id;
    $query = "UPDATE `mktime` SET diwtitle = '".$_REQUEST['newtitle']."'";
    $result = mysql_query($query) or die("<b>mySQL Error:</b>".mysql_errono()."<br>".mysql_error());
    if(!$result)
    {
        echo 'Error processing request.';
    }
    else
    {
        echo 'Request processed successfully.';
    }
}

$id = $_GET['id'];
// The ID is passed through the URL to specify the row,
// Or it is set in the previous script.

$query = "SELECT * FROM mktime WHERE id = '$id'";
$result = mysql_query($query);
$row = mysql_fetch_array($result);

echo '<form name="update" method="post">
    <input type="hidden" value="update" name="action">
    <input type="hidden" name="id" value="'.$id.'">
    <input type="text" name="newtitle" value="'.$row['diwtitle'].'"><br>
    <input type="submit" value="Update">
</form>';
?>

I added a check for an SQL error, and modified small items of code.

REMEMBER: You don't need $result = mysql_select_db(). You just need to select it, not set it to a variable.

~Brett

[badgoat]

Same problem.. In testing the script, the variable $diwtitle I am trying to alter is 'Craig is sleepy'. When I alter the variable name in the text box to 'Craig is NOT sleepy' and click UPDATE, it immediately refreshes to 'Craig is sleepy'. No errors, looking at the right ID # in the db shows the unchanged variable, and the page source shows 'Craig is sleepy' asa well.

[ShawnK]

This code you posted:

$query = "UPDATE mktime SET variable = '".$_REQUEST['variable']."'"; 

Is not secure at all because a user can put whatever they want in the URL and insert it into your db.

[bpat1434]

Okay, do you get either one of the outputs that the update script should show? (Request Processed Successfully)?

If not, then change
if(!empty($GET['action']) && $GET['action'] === 'update')
{
to
if(!empty($REQUEST['action']) && $REQUEST['action'] == 'update')
{

~Brett

His question was how to do it, had nothing to do with security. Once he gets the basic script down, he can add security as he sees fit.

[badgoat]

bpat: That did it! Changing it to "$_REQUEST['action'] == 'update')" made it work. Thank you very much for spending so much time giving me a hand!

Shawnk: This will be an intranet project and the only people with access to it will be salespeople.. Do you think it is still something to worry about? Of course I want this to be as secure as possible..

EDIT: bpat: It changed EVERY entry in the table to what I changed it to, not just that id.... Eek!

[ShawnK]

Yes you should still you some form of protection because even though they may not mean any harm, they could crash your script by putting slashes and such.