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

I just need some advice on how this would work for now, I could get the user_ID based on first + last then update but I just wanting it to work this way for now — mitchnufc
– mitchnufc, Commented Mar 18, 2012 at 0:47

fsodano · Accepted Answer · 2012-03-18 17:38:02Z

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.

I know im just building my knowledge step at a time now would this get the value i want as I have a 2 value fields also do i just call the like this action = mysql_query($donate_sql); to my input type
I edited my answer to be a bit more precise. However, I do recommend you read a PHP tutorial, such as the one provided by w3schools: w3schools.com/php/php_intro.asp this question could be easily answered with the examples there! I hope it works out for you.

hjpotter92 · Accepted Answer · 2012-03-18 02:21:44Z

1

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;

answered Mar 18, 2012 at 2:21

hjpotter92

81.1k36 gold badges148 silver badges188 bronze badges

Comments

gastonfartek · Accepted Answer · 2012-03-18 14:29:50Z

1

<?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>

answered Mar 18, 2012 at 14:29

gastonfartek

3481 silver badge10 bronze badges

1 Comment

mitchnufc Over a year ago

these answers are still not doing what i set to do

gastonfartek · Accepted Answer · 2012-03-18 02:35:59Z

0

$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

answered Mar 18, 2012 at 2:35

gastonfartek

3481 silver badge10 bronze badges

4 Comments

mitchnufc Over a year ago

Thanks for the Advice but I make use of value twice in this form so how do I know which one the SQL will fetch?

gastonfartek Over a year ago

i did not completely understand your question, but from what i understand is that you have a $value variable declared twice in your code?

mitchnufc Over a year ago

I'm trying to get the value entered by the user on the page under the label named donate which has a value attached to it which the user enters, the code is in the first post by myself.

gastonfartek Over a year ago

you should add a name to the input field where the user enters the desired donation amount, i will post another answer with code example

Collectives™ on Stack Overflow

assistance using update method in php with mysql

4 Answers 4

Edited (more profound) answer:

Old answer

2 Comments

Comments

1 Comment

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Edited (more profound) answer:

Old answer

2 Comments

Comments

1 Comment

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related