MYSQL Update and add a value into cell

Question

hello friends I want to update a cell in a table whit new content, but retaining the content that has. I need to add the new data separated whit comma or line break (is'nt relevant), preserving the data that have inside.

|id | title | referer|
|--------------------|
| 1 | post1 | google |
| 2 | post2 | yahoo  |
| 3 | post3 | bing   |
| 4 | post4 | google |

The table should look like this with new content added.

|id | title | referer           |
|-------------------------------|
| 1 | post1 | google,yahoo,bing |
| 2 | post2 | yahoo,bing,etc    |
| 3 | post3 | bing              |
| 4 | post4 | google,google     |

this is my code to insert new rows in my Database:

mysql_query("INSERT IGNORE INTO posts (id,title,referer) VALUES (null,'$q','$referer')");

What would be the code to add information in the cell "referer" whit Comma separated or line break ?

thanks in advance

I would create a new table for the referrers. As you add each referrer, use the id of the title from the 1st table as the ID for the referrers. Then join both tables to get all referrers for post 1 and use explode to display them — Brad
– Brad, Commented Jul 29, 2013 at 3:07
How can you tell from your table schema (where title has duplicates) to which row you want to add a referrer? — peterm
– peterm, Commented Jul 29, 2013 at 3:11
@Brad can you give me a Link with more information about method that you suggest to me? thanks — jose sanchez
– jose sanchez, Commented Jul 29, 2013 at 3:57
Jose, You want to create 2 databases. One will have the posts in them(1,2,3,4) along with an referrers ID from table 2, and the 2nd will have a list of referrers.(google, yahoo etc) each with an ID that will go into the first table.. lets use post1. everytime you get a new referrer to post1, you would add a new line in the first table with the new referrer id. That way eventually you can add them up as in Google-20 referrers instead of displaying a line with many referrers. Read up on mysql table join, php explode, foreach. — Brad
– Brad, Commented Jul 29, 2013 at 20:05

deviloper · Accepted Answer · 2013-07-29 03:46:49Z

1

I am not sure about what you might do with how separate them with commas unless you will want to create an array of variables and concat them into a single variable and use mysql's CONCAT function to update your table in order to keep your last field data:

$referes_list = '';
foreach ($referers as $referer) {
    $referes_list .= $referer . ", ";
} 
UPDATE referers SET referer=CONCAT(referer,'$referes_list') WHERE id='$id' ;

edited Jul 29, 2013 at 3:46

answered Jul 29, 2013 at 3:10

deviloper

7,25210 gold badges52 silver badges79 bronze badges

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

4 Comments

jose sanchez Over a year ago

mysql_query('UPDATE posts SET referer = concat(referer,',',$refkey) WHERE id='$id'"); this code sends error: Parse error: syntax error, unexpected T_VARIABLE

jose sanchez Over a year ago

thanks, your new update says: Invalid argument supplied for foreach()

deviloper Over a year ago

Well you are going to need to manage that by your self and see into what you have to put in the flaw! :)

jose sanchez Over a year ago

mysql_query("UPDATE posts SET referer = CONCAT(referer,',','$refkey') WHERE id='$id'"); it works! thanks

Paul · Accepted Answer · 2013-07-29 03:04:38Z

0

You'll have to forgive me if my MySQL skills aren't the greatest, but what I would do here is simply get the current value, append a comma and new value to it, then update the cell.

...Or did I misinterpret your question?

answered Jul 29, 2013 at 3:04

Paul

3,9741 gold badge20 silver badges23 bronze badges

1 Comment

jose sanchez Over a year ago

yes, is what I need, but this comand not works for me mysql_query("update posts set referer = referer + '$newref' where id ='$id'");

levi · Accepted Answer · 2013-07-29 03:07:57Z

0

In mysql there isnt "comma separated type", that is just a simple string or VARCHAR, so, you will need get previous data and concatenate it like string in php and then update it.

answered Jul 29, 2013 at 3:07

levi

22.8k7 gold badges79 silver badges76 bronze badges

Comments

Gordon Linoff · Accepted Answer · 2013-07-29 03:08:02Z

0

I think you are looking for something like:

update t
    set referrer = (case when referer is null or referer = ''
                         then @newvalue
                         else concat(referer, ',', @newvalue)
                    end)
    where id = @id;

answered Jul 29, 2013 at 3:08

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Comments

Soundz · Accepted Answer · 2013-07-29 03:13:09Z

0

mysql_query('UPDATE tablename SET referer = concat(referer,", '.$value.'") where id="'.$id.'")'; this should do it, it's untested though.

answered Jul 29, 2013 at 3:13

Soundz

1,30010 silver badges17 bronze badges

Collectives™ on Stack Overflow

MYSQL Update and add a value into cell

5 Answers 5

4 Comments

1 Comment

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

4 Comments

1 Comment

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related