add to a mysql column instead of replacing?

Question

I want to add an extra value to a mysql table column, e.g:

the field 'photos' already has an image name in, but i want to add another image name into the field using php.

I thought INSERT would just add it in on top but it seems to replace the existing image name. I want to avoid selecting the current field values, adding the new value and re-inserting if possible.

thanks!

code:

php:

$query = "INSERT INTO `listings-rent` (photos) VALUES ('$fileName') WHERE id = '$insertID'";
mysqli_query($mysqli, $query);

the problem is this replaces the existing value in the 'photos' column, rather than adding to it

how come there's a where clause in your insert statement? Please revise your query — Sebas, Feb 15 '13 at 23:50
because it needs to insert the data into the row where the id of said row equals a defined value, i.e i update the right row and not every single row in the table — rpsep2, Feb 15 '13 at 23:53

score 0 · Accepted Answer · answered Feb 15 '13 at 23:52

0

It is highly discouraged, to keep multiple values in the same column. But it is, of course possible.

UPDATE `photos` SET imagename = CONCAT(imagename, ",", $second_name) WHERE id=$insertID'

You will then use explode in PHP to get an array of strings like:

array("image1.jpg", "image2.jpg")

answered Feb 15 '13 at 23:52

Tomáš Zato

50,171
52
268
778

or you could store json to easy decoding, but yeah, this is a SQL anti pattern – Brad Bonkoski Feb 15 '13 at 23:54
OMG why json? There are no "," characters in paths, and JSON would **make it slower**. You remind me of those guys who [use JSON to compare arrays](http://stackoverflow.com/questions/7837456/comparing-two-arrays-in-javascript). – Tomáš Zato Feb 15 '13 at 23:57
can i ask why its not advised to store multiple values in the same column? – rpsep2 Feb 16 '13 at 00:06
Mysql's concept is to make many different data acessible using tables. For each value, there is one cell in one collumn. Each time you use cell for multiple values, you get closer to using a simple text file instead of database. Aside, it decreases code performance - both string contacnation and exploding to array takes some time. On small site, you don't notice that, but it is a bad habit to learn to write faulty codes. – Tomáš Zato Feb 16 '13 at 00:10
I said this is an ANTI PATTERN meaning you should *NOT* do it. JSON is, however, a common pattern for representing such structured data, and how do you know what types of data the fields might contain? – Brad Bonkoski Feb 28 '13 at 16:01

score 0 · Answer 2 · answered Feb 15 '13 at 23:53

0

You insert with a WHERE statement. This will not work.

try:

 $query = "INSERT INTO `listings-rent` (photos,id) VALUES ('$fileName', $insertID)";
mysqli_query($mysqli, $query);

answered Feb 15 '13 at 23:53

Green Black

5,037
1
17
29

add to a mysql column instead of replacing?

2 Answers2