CODEWITHSUNDEEP
phpmysql
z0mbieKale
z0mbieKale

Reputation: 1028

How to update specific row in MYSQL

I have a table, where I store user uploaded files. There can be 5 different file types: profile picture, cpr file, degree file, video file, background check file.

Table structure is this:

file_id, user_id, file_type, file_size, file_name, file_new_name, file_path, file_cat, date_created

My questions:

  1. Is this structure efficient or should I create 5 different tables?
  2. If I would like to update, lets say user profile picture row, then what would be the best way to do it? --- I came up with a solution that probably is not be the best one- I update the row where file_cat = "profile_picture" and user_id=:user_id. Would that put a lot of load in the system?
  3. First when user signs up, he doesn't have any files. Should I user insert into ... VALUES ... on duplicate key update with a hidden value in a form?

Thank you in advance.

Upvotes: 2

Views: 597

Answers (2)

e4c5
e4c5

Reputation: 53734

This is three questions not one.

Is this structure efficient or should I create 5 different tables?

One table is good enough

If I would like to update, lets say user profile picture row, then what would be the best way to do it? --- I came up with a solution that probably is not be the best one- I update the row where file_cat = "profile_picture" and user_id=:user_id. Would that put a lot of load in the system?

Not if you have an index on file_cat, user_id (composite index on both fields). If you want to make things a bit leaner you can store constants instead of 'profile_picture' etc. eg

profile_picture = 1
cpr = 2
....
background = 6

This would make the tables and indexes a bit smaller. It might make the queries slightly faster.

First when user signs up, he doesn't have any files. Should I user insert into ... VALUES ... on duplicate key update with a hidden value in a form?

No need for that. not having a record for new users actually makes things easier. You can do an COUNT(*) = 0 query or better still an EXISTS query without having to fetch rows and examine them.

Update: These EXISTS queries are really usefull when you are dealing with JOINs or Sub Queries for example to quickly find if a user has uploaded a profile picc

SELECT * from users WHERE exists (SELECT * from pictures where pictures.user_id = users.id)

Upvotes: 5

jeffery_the_wind
jeffery_the_wind

Reputation: 18158

If you use the primary key properly then your insert ... on duplicate key update ... query will do everything for you.

For your table you need to define a primary key column. In this case I would say it is your file_id column. So if you do your insert, the MySQL server will check to see if your file_id column is defined already for that value, if so it will update with the new values, other wise it will add a new row of data with the new file_id.

I should be easy enough to separate it though, make 1 script for creating new rows and another for updating. Usually you will know when you are creating as opposed to updating in an application. Again using a primary key correctly will help you out a lot. Using a primary key in your where clause I am pretty sure is one of the most efficient ways to update.

https://dev.mysql.com/doc/refman/5.5/en/optimizing-primary-keys.html

Upvotes: 1

Related Questions