sql update

[pagegen]

Hi

 

I am trying to do this

UPDATE table SET field1=field1 + ',20';

 

but it is not working

whats spose to happen is:

if field1 is 5

it should become 5,20

etc

 

Thank you

[Skewled]

Are you replacing the 5 with 20 or trying to append 20 to the value?

 

Edit: the database field that is getting the information needs to be vchar in order to store the comma I believe. So you'd have to also make sure that's setup correctly.

[pagegen]

Hi

I am trying to append 20 to the current value

and yes the field is var char

[Skewled]

ok so here's what I'm thinking

 

// Connection to database stuff here

$dbc = mysqli_connect('host', 'user', 'password', 'dbname');

 

 

$newvalue = ','.20;

$fields = Array("yourfieldtoupdatehere")

 

// Foreach loop to only update the field where the value is 5

 

foreach ($fields as $fld) {

 

$query = "UPDATE table SET $fld = $fld+$newvalue' WHERE fld = 5";

mysqli_query($dbc, $query);

 

}

 

mysqli_close($dbc);

?>

 

See if that does the trick for ya, if anything it's how I"m trying to append 20 to the value that may fail.

[void]

 

UPDATE table SET field1= CONCAT(field1, ',20');

[Skewled]

 

UPDATE table SET field1= CONCAT(field1, ',20');

 

Change:

$query = "UPDATE table SET $fld = $fld+$newvalue' WHERE fld = 5";

 

To:

UPDATE table SET $fld=CONCAT($fld, ',20') WHERE $fld = 5);

 

remove $newvalue

 

 

 

 

[pagegen]

Thank you very much guys

 

this worked like a charm

UPDATE table SET field1= CONCAT(field1, ',20');

[PFMaBiSmAd]

Assuming you are doing this to represent adding a fractional number, using the comma , as a decimal point separator character is a human convention that is only used in specific geographic locations. Computer programs uses a decimal point . as the decimal separator character.

 

If you want to use the comma , as a decimal point separator, you will need to convert the number into your local representation when you display it. It is not stored that way and you don't perform computer operations on it that way.

 

If you are doing this to store a list of comma separated values in one field, it is a bad idea.

[pagegen]

Hi PFMaBiSmAd

 

I am storeing a list of comma separated values in one field, so the field in the database looks like

5,20,44,46

 

if this is a bad idea, what would you advice insted please

 

Thank you

[PFMaBiSmAd]

If you are only storing/retrieving them as is and you are not planning on searching for any of the values (i.e. you don't need to find the 44 in your example list), then storing them as a comma separated list is ok. Searching for the values is a different matter and if you plan on needing to search, you should store the individual values as one per row in a different table that is tied to your main table using a key/id value.

 

And what threw me off about if this was adding a decimal (.20) to the value was your use of the + in the first query posted.