Separate tables or in one. Which way is better?

[vinsb]

Hello,

First I want to apologize if this post is in wrong place.

 

So I started to learn php&mysql recently and decided to make one small web site. Is 2 pages - index.php and upload.php. So far I upload successfully image into folder and store name and path in database.

 

Now I want to make categories. In my index page I will have few categories. So I've made <select> options on my upload form and one more row in database 'categoris' where to store which category is selected.

 

My question is which way is beter to store categories? In separate table or as now in same table?

Also when I click on desired category, how to show images only from this category?

 

 

[Ch0cu3r]

 

My question is which way is beter to store categories? In separate table or as now in same table?

You'd store the categories in a separate table. You'd add an extra field to your images table to store the category id. That way you only need to update category name in one place, the categories table. Any image that references that category will also be affected. This is called a 1 to many relationship.

 

 

how to show images only from this category?

You add a WHERE clause condition. Example

SELECT c.cat_name,                          # get category name from category table (c)
       i.img_path                           # get the image path from image table (i)
FROM images i                               
LEFT JOIN categories c ON c.id = i.cat_id   # get category name where the cat id matches in both tables
WHERE i.cat_id = $cat_id                    # only get images that matches this category

Edited January 27, 2014 by Ch0cu3r

[vinsb]

So if I'm understand you correct I need something like this:

id, image, name, filepath ---> after filepath I will make one more field 'categories' where I will save what is selected from <select> in form. 

 

Then I will make another table where I will have - id, name_of_categori.. Then whit where and join clause I'll point them. Hm is a little bit more for me since I'm beginner .. but I'm gues there is no other way to learn it

 

Thank's for help!

[Barand]

If images can belong to multiple categories then you need three tables, the third defining which categories the image belongs to

+----+----------------+-------------+                    +-----+---------------------+
| id | image          | path        |                    | id  | categoryName        |
+----+----------------+-------------+                    +-----+---------------------+
|  1 | image_1.jpg    | /images     |                    |  1  | People              |
|  2 | image_2.jpg    | /images/xx  |                    |  2  | Landscape           |
+----+----------------+-------------+                    |  3  | Sport               |
   |                                                     |  4  | Animals             |
   |                                                     |  5  | Friends and family  |
   |                                                     +-----+---------------------+
   |                                                        |
   +---------------------------+         +------------------+                                                                     |         |
                               |         |                 
                               |         |                  
                     +-----+--------+---------+
                     | id  | img_id | cat_id  |
                     +-----+--------+---------+
                     |  1  |    1   |    1    |                 
                     |  2  |    1   |    5    |                 
                     |  3  |    2   |    2    |                 
                     |  4  |    2   |    4    |
                     +-----+--------+---------+