MySql Fetch Array manipulation

Question

Ok..I know how to get a data record from a MySql table...and I want to change data in that record and update the table.

My question is...can you actually manipulate that data from the result row, and subsequently use those in the update statement?

For example.

Let's say the table rows have 2 fields: Name, YearlyEarn.

And once a month I want to add that month's income to the YearlyEarn field for each person.

Assume we already did the Select statement for someone who's name is in $CurrentName. And we then get their record.

$DataRow = mysql_fetch_array($result):

Can you do this:

$DataRow["YearlyEarn"] = $DataRow["YearlyEarn"] + $MonthEarn;

$query = "UPDATE EarnTable SET YearlyEarn = '$DataRow["YearlyEarn"]'    
`WHERE Name = '$CurrentName'" ;

$UpdResult = mysql_query($query) or die(mysql_error());

OR.....should I put the data into intermediate fields, manipulate it..and then use those fields in the update statement?

Bjørn Bråthen · Accepted Answer · 2013-01-17 01:26:00Z

1

You should use prepared statements, like PDO. The mysql_* is outdated. But if not doing so, you should consider changing your query from:

$query = "UPDATE EarnTable SET YearlyEarn = '$DataRow["YearlyEarn"]'`WHERE Name = '$CurrentName'" ;

to:

$query = "UPDATE EarnTable SET YearlyEarn = `" . $DataRow['YearlyEarn'] . "` WHERE Name = `$CurrentName`" ;

answered Jan 17, 2013 at 1:26

Bjørn Bråthen

4428 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Zardiw Over a year ago

Ahhh...use the double quotes so the variable is evaluated?

Zardiw Over a year ago

I'm thinking that I'll use intermediate variables....I think the parser will get confused with using array elements from a previous query....at any rate..MySql is obtuse to use....I'm actually converting a Pascal program to PHP....and the Pascal program uses a regular Database which lets you manipulate the record data itself and then post the record.........z

Zardiw Over a year ago

Also.....while PDO looks interesting....I'm not prepared for a steep learningn curve using something like that.......does that help save overhead processing?...And if so how much?....z

Barmar Over a year ago

You could also use mysqli, which is more like mysql, but supports prepared statements. There's a little less overhead when you use prepared statements, but that's not the reason to use them, safety is.

Bjørn Bråthen Over a year ago

It has a great portability. PDO provides single interface across multiple database types, like mysql, MS sql etc, in an object oriented way. You can easily use prepared statements, which help protecting you from MySQL injections. It also has excellent error trapping methods.

|

eggyal · Accepted Answer · 2013-01-17 01:43:24Z

1

Yes, you can:

UPDATE EarnTable
SET    YearlyEarn = YearlyEarn + 123
WHERE  Name = 'abc'

answered Jan 17, 2013 at 1:43

eggyal

127k18 gold badges220 silver badges244 bronze badges

3 Comments

Zardiw Over a year ago

Unfortunately I am doing much more complex data manipulation..I just provided a simple example to show what I wanted to do....If all I wanted to do is add a number to it, of course I would have done that.....z

eggyal Over a year ago

@Zardiw: Then it's not at all clear (to me) from your question exactly what manipulation you are seeking to perform. Can you clarify?

Zardiw Over a year ago

Like I said...it's complex....issue is moot now anyway...using the KISS method now......z

Barmar · Accepted Answer · 2013-01-17 02:11:11Z

0

You can use:

$query = "UPDATE EarnTable SET YearlyEarn = '$DataRow[YearlyEarn]' WHERE Name = '$CurrentName'" ;

When you're interpolating an array reference, the key is automatically quoted.

or:

$query = "UPDATE EarnTable SET YearlyEarn = '{$DataRow["YearlyEarn"]}' WHERE Name = '$CurrentName'" ;

Inside {...}, you can put any variable expression and it will be evaluated and interpolated.

answered Jan 17, 2013 at 2:11

Barmar

789k57 gold badges554 silver badges669 bronze badges

4 Comments

Zardiw Over a year ago

Yes but can you actually add something to the $DataRow['YearlyEarn'] before you use it to update the row?

Barmar Over a year ago

Not sure I understand the question. It's just a variable being interpolated into the string. Whatever its value is, it will be inserted.

Zardiw Over a year ago

The question was if you could treat that variable like any other variable....Could you add a value to it?....and then use it in an update statement........z

Barmar Over a year ago

It is just like any other variable. What makes you think it's special?

Collectives™ on Stack Overflow

MySql Fetch Array manipulation

3 Answers 3

6 Comments

3 Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

6 Comments

3 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related