0

How do you update a database with new info but keep old info aswell?

My aim is to have a favorites button that when clicked it will add the item id to a members favorites list so they could call something like fav.php?fav=janedoe and their favorites list be listed.

I know how to add the button and get the item id from database I just really need the mysql query I would run to update aswell as what the "type" would be when creating the database column I think.

info inside the row would then look like this

1,12,14,15,34,46,74

then if member wanted to add item 98 it would update to

1,12,14,15,34,46,74,98

adding 98 to the end.

I realise this is kind of a "can you do this for me" post, but I just haven't a clue where to start.

Mahmoud Gamal
  • 78,257
  • 17
  • 139
  • 164
Anna Riekic
  • 728
  • 1
  • 13
  • 30
  • Alright, this is not a normalized database. You need to have 3 tables. `user`, `items` and `user_items` the `user_items` table would link between the two, so adding and removing records is a breeze. `user_items` would have `user_id` and `item_id` columns. – wesside Oct 30 '12 at 13:39

4 Answers4

2

You should create a new table called something like:

MembersFavoritePage:

  • MemberId.
  • PageId.
  • ...

Then whenever the user mark a page as a favourite page do an insert into this table.

Mahmoud Gamal
  • 78,257
  • 17
  • 139
  • 164
  • 2
    Much better design than storing in a string, more flexible easier to maintain. ( I was in middle of writing an answer such as this). – Brian Oct 30 '12 at 13:37
  • @Mahmoud Gamal thx, I didn't think about that way but I'm really wanting to do it so te ids are stored in 1 row, (OP updated) – Anna Riekic Oct 30 '12 at 13:37
  • @Brian Oh I thought a string would be the better option being that it would save space etc only needing a new column rather then a new table thx – Anna Riekic Oct 30 '12 at 13:39
1

IF you do want to maintain the string you could do this in the query:

UPDATE `table`
SET `fav` = CONCAT(`fav`, ",98")
WHERE `id` = 1234;

Or do the concat in PHP and just update with that - i.e. get existing string first - but that means 2 queries.

But the separate table is best as suggested by @MahmoudGamal. As this is difficult to 'remove' items from... you'd need to do the manipulation PHP-side.

Brian
  • 8,418
  • 2
  • 25
  • 32
1

You should not save comma separated values in single column. It is a violation of First normal form

Check below answer for broader explanation:

Is storing a delimited list in a database column really that bad?

The best approach is to create separate table as suggested by @Mahmoud Gamal which is easy to maintain in long run.

Community
  • 1
  • 1
metalfight - user868766
  • 2,722
  • 1
  • 16
  • 20
  • There are situations it is OK - yes very wrong to do but depending on the specific app it can be valid... however I would store it as a JSON string so it can easily be manipulated int he code. – Brian Oct 30 '12 at 13:46
-1

What about creating a table "favs" in which you can store the id and the user who added it to his favs'list?

ID | USER_ID
------------
5  | user_1
6  | user_1
8  | user_2
6  | user_2

select ID from favs where USER_ID = "user_1"

this will get pages'id for user_1.

Remember you'll need to manage the insert/update/delete for this table, if the user needs to add/remove a fav page.

An easier solution could be having a favs pages field in the user's detail

USER_ID| FAVS
-------|-----
user_1 | 5*6
user_2 | 8*6

By using this solution, you will able to easily parse the favs field and get info about the pages (in this case you'll just need to parse the string).

When you'll need to update or remove any page from a user favs'list, you can just parse the string, remove the page id you want to delete or append the new page to the string.

The cons in this case will be you will not be able to find a fav page easily (you'll end up using LIKE construct).

sataniccrow
  • 372
  • 2
  • 7
  • How is this any different? You just added a step. – wesside Oct 30 '12 at 13:49
  • @bigman why are you policing this question? Just add an answer. You don't need to critique everyone else's response to the question, but if you feel you do then you should at least add some new information. – Kevin Bradshaw Oct 30 '12 at 14:30
  • Take it easy town cryer, I commented because it was warranted. I already posted a comment that was on the same track with what was marked correct. So take it easy and let people handle themselves. – wesside Oct 30 '12 at 14:33
  • "How is this any different? You just added a step. – bigman 53 mins ago" Just added a step to what? – Kevin Bradshaw Oct 30 '12 at 14:44
  • Bigman, mine is not a crusade to win a price, I just wrote down what came to my mind. If it was wrong, I deserve a -1... but It was not. TIP: Sometimes the way you edit your answers can make those easier to be understood. – sataniccrow Oct 30 '12 at 15:01