MySQL column with multiple values

Question

I have a question about adding values into a table. Right now I am working on an application in which you can maintain the stock of, for example, a tool shop.

<?php
include 'dbconnect.php';

// Stock query
$sql = "SELECT * FROM product WHERE locationcode = 'loc1'";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
     // output data of each row
     while($row = $result->fetch_assoc()) {
         echo "<tr>";
         echo "<td>".$row['locationcode']."</td>";
         echo "<td>".$row['product_id']."</td>";
         echo "<td>".$row['amount']."</td>";
     }
} 
else {
     echo "0 results";
}

$conn->close();

The table that I made for the products available consists of the following columns:
- Product ID
- Product Type
- Brand
- Model
- Cost Price
- Selling Price
- Locationcode
- Amount

A lot of products from this tool shop are available on multiple locations (there are several physical stores located around the country).

Is it possible to add more than 1 value into a column? This is because the client should be able to filter on products and the corresponding locations (IE. A product can be available both in location 1 and location 2).

Thanks in advance!

See normalization.

Strawberry
– Strawberry

2017-01-13 01:36:15 +00:00
Commented Jan 13, 2017 at 1:36 — Strawberry
– Strawberry, Commented Jan 13, 2017 at 1:36

shmosel · Accepted Answer · 2017-01-13 01:42:28Z

4

I would strongly recommend against adding more than one value into a column, e.g. using CSV here. Instead, create a new table, say product_locations which has one column for the Product ID and another column for the location ID.

For example, if product 1 only appeared in location 1, and product 2 appeared in both locations 1 and 2, the table would look like this:

ProductID | LocationID
    1     |     1
    2     |     1
    2     |     2

Note that if you have the need to display all locations of a given product, you could always aggregate the above table using GROUP_CONCAT, e.g.

SELECT ProductID, GROUP_CONCAT(LocationID) AS locations
FROM product_locations
GROUP BY ProductID

edited Jan 13, 2017 at 1:42

shmosel

51.1k7 gold badges81 silver badges148 bronze badges

answered Jan 13, 2017 at 1:28

Tim Biegeleisen

526k32 gold badges323 silver badges399 bronze badges

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

Collectives™ on Stack Overflow

MySQL column with multiple values

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related