assistance using update method in php with mysql

Question

I have a simple option tag in html which lists all users held on the database when the user clicks donate i want to take the value entered and update the dontate_Total row relvant to that user with the value entered by the user in the donate label section.

here is my html

<table>

    <form>
    <tr><td><label>User:</label></td>
    <td>
    <select>
    <?php do{?>
    <option> <?php echo $rsNames['first_Name'];?> <?php echo $rsNames['last_Name'];?></option>
    <?php } while ( $rsNames= mysql_fetch_assoc($names_query))?>
    </select>
    </td>
    </tr>
    <tr><td><label>Donation £</label></td><td><input type="text" maxlength="9" value="0.00"/></td></tr>
    <tr><td><input id="submit" type="submit" value="DONATE" /></td></tr>
    </form>
</table>

the above works fine but now I want to run and update query when the user hits the DONATE input field. here is my sql statment which I believe wont work just after some advice

$donate_sql =UPDATE `donate` SET donate_Total=  donate_Total + $_GET['value'] WHERE first_Name = 'first_Name'   AND last_Name ='last_Name';

thanks

Answer 1

How are you?

Edited (more profound) answer:

In your HTML, you have to give names to your fields in order to retrieve them from your PHP script i.e. give your <select> element a name (attribute), like this:

<select name="fullname">

Also, the nested <option> elements should have a "value" attribute, this is because you almost always want the identification of a field to be a unique ID (so there's no mistake when refering to a table row) but you want to show useful information to the user, such as a full name.

If you plan on using an ID, then your code (in HTML) should be updated to:

<option value="<?php echo $rsNames['id']; ?>"> <?php echo $rsNames['first_Name'];?> <?php echo $rsNames['last_Name'];?></option>

This will be outputed as:

<option value="1">John Doe</option>
<option value="2">Jane Doe</option>

Then in your PHP file, it would be much easier to write a query that updates exactly the user that you want.

<?php

$id = $_GET['fullname'];
$donate_sql = "UPDATE `donate` SET donate_Total = donate_Total + {$_GET['value']} WHERE id = $id";
mysql_query($donate_sql);

?>

If you don't plan on using an ID, you have to face the fact that if 2 users are called John Doe, you'll be updating the "total" amount of donation for both of them, and it's going to be a bit more complicated for you to parse the information.

Say you DON'T want to use an ID and you want to stick with your first and last name, then you would write something like:

<option value="<?php echo $rsNames['first_Name'] . '||' . $rsNames['last_Name'];?>"><?php echo $rsNames['first_Name'];?> <?php echo $rsNames['last_Name'];?></option>

Which would produce an output similar to:

<option value="John||Doe">John Doe</option>
<option value="Jane||Doe">Jane Doe</option>
<option value="Mary Jane||Doe">Mary Jane Doe</option>

The third <option> tag is an example of why you can't rely on spaces to parse first and last name; a person can have double name or double last name -- or both, as it is in my case ;-)

The separator || is an arbitrary one, the idea is to use one or more characters that are not likely going to be used as part of the name or the last name.

This approach (which again, is a really bad one) would require you to parse the first name and last name individually afterwards, so your script would end up being:

<?php

$name = $_GET['fullname']; //'fullname' is the name of our <select>
$name = explode('||',$name); //this returns an array, with each position defined by the || token

//$name is now an array(0=>'John',1=>'Doe') - for example.

//Get the first name
$firstName = $name[0]; //John

//Get the last name
$lastName = $name[1]; //Doe

//Finally, your SQL would be updated to be:
$donate_sql = "UPDATE `donate` SET donate_Total= donate_Total + {$_GET['value']} WHERE first_Name = '$firstName' AND last_Name ='$lastName'";

mysql_query($donate_sql);
?>

Old answer

The quick answer is:

$donate_sql = "UPDATE `donate` SET donate_Total = donate_Total + {$_GET['value']} WHERE first_Name = 'first_Name' AND last_Name ='last_Name'";

And then using the mysql_query($donate_sql); function.

However as @hjpotter92 pointed out, you should (amongst other things) add an ID column to that table and set it as a primary key.

Also, you should keep in mind things like escaping values that come in your requests ($_POST and $_GET), so no SQL injection is applied to your query.

Hope this helps!

Cheers.

Answer 2

<?php
//code at the top of the document
if(isset($_POST['donation']) && $_POST['donation'] != '')
{
    $donation = mysql_real_escape_string($_POST['donation']);
    $fname = mysql_real_escape_string($_POST['first_name']);
    $lname = mysql_real_escape_string($_POST['last_name']);
    $donate_sql = "UPDATE `donate` SET donate_Total = donate_Total + '{$donation}' WHERE first_Name = '{$fname}' AND last_Name = '{$lname'";
}

?>

<table>

    <form method="POST" accept-charset="utf-8">

    <tr><td><label>User:</label></td>
    <td>
    <select>
    <?php do{?>
    <option> <?php echo $rsNames['first_Name'];?> <?php echo $rsNames['last_Name'];?></option>
    <?php } while ( $rsNames= mysql_fetch_assoc($names_query))?>
    </select>
    </td>
    </tr>
    <tr><td><label>Donation £</label></td><td><input name="donation" type="text" maxlength="9" value="0.00"/></td></tr>
    <tr><td><input id="submit" type="submit" value="DONATE" /></td></tr>
    </form>
</table>

Answer 3

$value = mysql_real_escape_string($_GET['value']);
$donate_sql = "UPDATE `donate` SET donate_Total = donate_Total + {$value}

adds a small layer of security to your query, but however give credit to @fsodano this is just an add on to his answer

Answer 4

Instead of directly using mysql_query function, try building the mysql statements, like $donate_sql built here by fsodano and then using an echo $donate_sql; to check, and then using the statement on a backup table. I prefer to do it this way, then messing up with the original work.

On a side note, your form consists of 'ALL' the first and last name combinations(I guess!), so there won't be any unique value for $_GET['first_name']

Do give the following statement a consideration!

$donate_sql = "UPDATE `donate` SET donate_Total = donate_Total + {$_GET['value']} WHERE first_Name = '{$_GET['first_Name']}' AND last_Name = '{$_GET['last_Name']}'";
echo $donate_sql;