Simple update mysql script

chrisguk

Hi guys, I am having a little trouble with the following:

First of all I can't work out how to incorporate how to select the current data from the database first, then change the data and click update. In my script below the update part isnt working at all. I am not worried about security because this will not be visible to the web:

Here is my code:



<?php

if(isset($_POST['update']))
{
$dbhost = 'localhost';
$dbuser = 'root';
$dbpass = 'mypass';
$conn = mysql_connect($dbhost, $dbuser, $dbpass);
if(! $conn )
{
  die('Could not connect: ' . mysql_error());
}

$mon = $_POST['mon'];
$die = $_POST['die'];
$mit = $_POST['mit'];
$don = $_POST['don'];
$fre = $_POST['fre'];


$sql = "UPDATE essen SET mon='$mon', die='$die', mit='$mit', don='$don', fre='$fre'";

mysql_select_db('mydb');
$retval = mysql_query( $sql, $conn );
if(! $retval )
{
  die('Could not update data: ' . mysql_error());
}
echo "Updated data successfully\n";
mysql_close($conn);
}
else
{
?>   

<div id="content_wrapper"><span class="top"></span><span class="bottom"></span>
        <div id="content">

        <div class="cleaner_h40"></div>


<h2>What the world is saying:</h2>
<div id="login_box">
<form method="post" action="<?php $PHP_SELF; ?>">
  <input id="loginfield" type="text" name="mon" id="mon" value="<?php echo $mon; ?>" />
  <input id="loginfield" type="text" name="die" id="die" value="<?php echo $die; ?>" />
  <input id="loginfield" type="text" name="mit" id="mit" value="<?php echo $mit; ?>" />
  <input id="loginfield" type="text" name="don" id="don" value="<?php echo $don; ?>" />
  <input id="loginfield" type="text" name="fre" id="fre" value="<?php echo $fre; ?>" />

  <input id="loginbutton" type="submit" value="update" id="update" />
</form>
</div>






<?php

}

?>




    </div> <!-- end of content -->

Any help to fix the update and incorporate a select statement first will be great, hopefully I will learn something. 🙂

bradgrafelman

Standard disclaimer: Stop Using the MySQL Extension!

chrisguk;11036097 wrote:
First of all I can't work out how to incorporate how to select the current data from the database first, then change the data and click update.

The steps can vary depending upon how you want to present this editing interface. Are you wanting to display all records in the database and allow the user to select which ones need to be edited? Are you wanting to allow the user to select more than one record for editing at a time?

chrisguk;11036097 wrote:
I am not worried about security because this will not be visible to the web

Doesn't matter. The same data sanitization practices still apply (they prevent more than just malicious users, you know). As it stands now, a stray single quote in any of your submitted data will break your query since you failed to sanitize the data.

chrisguk;11036097 wrote:
Here is my code:

And here is a quick critique:

```
$sql = "UPDATE essen SET mon='$mon', die='$die', mit='$mit', don='$don', fre='$fre'";
```
Other than the aforementioned deprecation of the mysql extension, the lack of sanitization, and altogether ignoring the question of "What if one or more of those entities don't exist in the POST'ed data?")...

The first problem I see is a lack of a WHERE clause. Without one, this query is going to UPDATE every single record in the given table using the given data. I'm guessing your intent was to instead only update a single record, meaning you'd want to add a WHERE clause that utilizes the primary key of the table to single out that record for updating.
```
mysql_select_db('mydb');
```
What happens if this fails?
```
echo "Updated data successfully\n";
```
Of course, since you don't check something like [man]mysql_affected_rows/man, it may be the case that you have successfully updated nothing (or, with the lack of a WHERE clause, everything).
```
<?php $PHP_SELF; ?>
```
Note that this PHP statement does absolutely nothing. Perhaps you meant to instead [man]echo[/man] or [man]print[/man] the value of a variable? If that's the case, also note that $PHP_SELF appears to be an undefined variable.
```
<input id="loginfield" type="text" name="mon" id="mon" value="<?php echo $mon; ?>" />
```
(et al. for all other <input>s in the form)

Note that if any of the variables contained data with a double quote, you will have broken your HTML markup. Consider using a function like [man]htmlspecialchars/man to prevent this.

Also note that your HTML has some invalid markup as well. For one, you're specifying the "id" attribute multiple times in many elements, and the first time includes values that aren't unique (which is a requirement for the attribute). Furthermore, I doubt there's really a need (or even a desire, depending upon how you answer the two questions at the beginning of my post) to add many/all of those "id" attributes.

chrisguk

Sorry for the late reply.

Let me just give you an overview so the picture is a little clearer with regards to its function.

Basically all this is, is a food menu display which changes weekly. The menu will be displayed like so:

Monday:

Soup

Tuesday

Bread

Webnesday:

Soup

Thursday:

Cake

Friday:

Soup

The user will navigate to an update page which will use the php mysql select to populate the fields Monday to Friday. The the user will change the details and hit update.

Does that help?