More than one value in a field?

[Punk Rock Geek]

I currently have a table set up so that every time a value is added to a field, it displays in this format:

 

|| value

 

So when I add three values, it looks like this:

 

|| value || value2 || value3

 

There is no limit to how long this list of values could get.  My question is, if I wanted to return each of these values separately, how would I go about doing that?  Thanks.

[Ken2k7]

Uh... let me guess, you have them stored as a string in a table row, am I right?

[corbin]

If you ever find your self thinking "How can I store multiple sets of data in 1 column per row" you should then think, "Oh wait... I shouldn't do that."

 

 

Instead of storing all of the values in 1 column, why not store them in multiple?

 

 

Example:

 

---users---

user_id|username|friends

1|bob|joe,john,peter

(I would use IDs there instead of names, but you get the point.)

 

Or:

 

---users---

user_id|username

1|bob

 

---user_friends---

user_id|friend_name (once again, I would use IDs)

1|joe

1|john

1|peter

 

 

 

I suggest reading the sticky on database normalization.

[Punk Rock Geek]

If you ever find your self thinking "How can I store multiple sets of data in 1 column per row" you should then think, "Oh wait... I shouldn't do that."

 

 

Instead of storing all of the values in 1 column, why not store them in multiple?

 

 

Example:

 

---users---

user_id|username|friends

1|bob|joe,john,peter

(I would use IDs there instead of names, but you get the point.)

 

Or:

 

---users---

user_id|username

1|bob

 

---user_friends---

user_id|friend_name (once again, I would use IDs)

1|joe

1|john

1|peter

 

 

 

I suggest reading the sticky on database normalization.

You wouldn't recommend it then?

 

Here's what I'm doing.  I have a list of users already.  Each user is going to choose a list of 20 cities.  I know then, it might seem like the obvious choice would be to set up the table like this:

 

user_id | city1 | city 2 | city 3 | etc

 

However, I need to be able to display the cities from most common to least common.  So if I did it this way, not only would I need to count the number of rows returned for every single city in the database (people can type in whatever they want for a city), but I need to count the number of rows for every single city in the database twenty times (once for each "city" field), and then add them together.  I'm sure PHP can do this, but I don't imagine it being less messy than doing it the other way.

 

Which would be a table like this:

 

City | Users | Numberofusers

 

Then I could have a graph showing the largest cities quite easily.  The only hard part of course is returning the data for who chose which city...

 

Of course, any simpler ideas are welcome.

[Mark Baker]

However, I need to be able to display the cities from most common to least common.  So if I did it this way, not only would I need to count the number of rows returned for every single city in the database (people can type in whatever they want for a city),

And? That's what databases are for. Why is it an issue?

 

but I need to count the number of rows for every single city in the database twenty times (once for each "city" field), and then add them together.

Surely not. Surely a single query can return everything you need here, perhaps if you explained what your really want to do.

[Daniel0]

Of course, any simpler ideas are welcome.

You were already given the simple way. This is a standard many-to-many relationship.

 

 

Schema:

CREATE TABLE cities (
  city_id int(10) unsigned NOT NULL auto_increment,
  name varchar(100) NOT NULL,
  PRIMARY KEY (city_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE users (
  user_id int(10) unsigned NOT NULL auto_increment,
  username varchar(50) NOT NULL,
  PRIMARY KEY (user_id)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

CREATE TABLE user_city_choices (
  user_id int(10) unsigned NOT NULL,
  city_id int(10) unsigned NOT NULL,
  PRIMARY KEY (user_id,city_id),
  KEY city_id (city_id),
  CONSTRAINT fk_users__user_id FOREIGN KEY (user_id) REFERENCES users (user_id) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT fk_cities__city_id FOREIGN KEY (city_id) REFERENCES cities (city_id) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

 

Data:

INSERT INTO `cities` (`city_id`, `name`) VALUES
(1, 'Foo'), (2, 'Bar'), (3, 'Baz'), (4, 'Another city'),
(5, 'Hello World');

INSERT INTO `users` (`user_id`, `username`) VALUES
(1, 'Daniel'), (2, 'John Doe'), (3, 'Someone Else');

INSERT INTO `user_city_choices` (`user_id`, `city_id`) VALUES
(1, 1), (2, 1), (3, 1), (1, 2), (2, 5), (3, 5);

 

 

To get the popularity of each city:

SELECT c.name, IF(u.user_id IS NULL, 0, COUNT(*)) AS count
  FROM cities AS c
  LEFT JOIN user_city_choices AS x
    ON x.city_id = c.city_id
  LEFT JOIN users AS u
    ON x.user_id = u.user_id
  GROUP BY c.city_id
  ORDER BY count DESC;

 

Result:

+--------------+-------+
| name         | count |
+--------------+-------+
| Foo          |     3 |
| Hello World  |     2 |
| Bar          |     1 |
| Baz          |     0 |
| Another city |     0 |
+--------------+-------+

 

To get the things that Daniel chose:

SELECT c.city_id, c.name
  FROM user_city_choices AS x
  INNER JOIN cities AS c
    ON x.city_id = c.city_id
  INNER JOIN users AS u
    ON x.user_id = u.user_id
  WHERE u.username = 'Daniel';

 

Result:

+---------+------+
| city_id | name |
+---------+------+
|       1 | Foo  |
|       2 | Bar  |
+---------+------+

[Punk Rock Geek]

Surely not. Surely a single query can return everything you need here, perhaps if you explained what your really want to do.

 

Well, suppose I had a table like this

 

 

User | City1 | City2 | City3 | City4

1    |  X    | Y      |    Z  |  A

2    | D      |  N    |    X    |  B

3    | Y      | X      |  C    |  N

4    | N      |  O      |  G    |  X

 

How would I arrange these cities in order from the ones that appear most to the ones that appear least?  (I have just used alphabet letters to simplify, but in the actual table, the number of different city names would be potentially infinite.) 

 

Daniel0, while I was awaiting a reply, I actually settled for something very similar to what you have done.  (Using multiple tables.)  You're right -- it is much simpler this way, but I've been trying to limit mysql queries and database space usage.

 

One table would be preferable, but thanks everyone for your help.

[Daniel0]

One table would be preferable, but thanks everyone for your help.

 

Well, evidently it would not be preferable. As you have found out yourself, getting any meaningful data out of a such setup would be much more difficult. You are in no way the only person who has ever needed a many-to-many relationship. It happens very often. As corbin said, it would be a good idea to look into database normalization.

 

Less does not automatically mean better. Often quite the contrary in fact.

 

The most challenging of these to maintain and rework suffer from what one author described as the "Spreadsheet Syndrome": a tendency for the developer to lump every possible piece of information into as few table as possible, often into a single table.

 

A schema that suffers from the "Spreadsheet Syndrome" is subject to data redundancies, data anomalies, and various inefficiencies. The cure for "Spreadsheet Syndrome" is database normalization.

[Punk Rock Geek]

One table would be preferable, but thanks everyone for your help.

 

Well, evidently it would not be preferable. As you have found out yourself, getting any meaningful data out of a such setup would be much more difficult. You are in no way the only person who has ever needed a many-to-many relationship. It happens very often. As corbin said, it would be a good idea to look into database normalization.

 

Sorry, I should explain.  I'm modifying existing software, and a lot of the stress of it this comes from working from within the constrictions of the software itself.  Half of the queries I perform that would work anywhere else do not work here, and so it takes a bit of creativity.  The easiest solutions don't always work for me, and if they do, by the time I make them compatible with the software, they no longer look so simple.  It's entirely possible that there are ways around this, but the software is still in its Release Candidate phase, so I won't get much help with it at the moment.  I don't like to mention this stuff, because I feel it overcomplicates things.

 

I know what you're saying and I understand where you're coming from.  It's really just a software issue.

[corbin]

Well... the short answer is that you should either change the layout or just run away. 

 

 

 

But really, pretty much your only option is a pretty crappy one.

 

 

The best thing I can think of is:

 

 

SELECT city1, COUNT(user_id) FROM users GROUP BY city1;

SELECT city2, COUNT(user_id) FROM users GROUP BY city2;

SELECT city3, COUNT(user_id) FROM users GROUP BY city3;

 

Then you would have to add the data sets (or do sub queries or what ever) and it would get ugly very, very quickly.

[Daniel0]

And even then you run into the common pitfalls of non-normalized database schemas. You risk that someone makes a type such that it says New Yok instead of New York. You'll then have anomalies in your code. Searching for New York won't get the one with the typo. The reason why you call something good practice is because it's the good way of doing it.

[kickstart]

Hi

 

While I agree with all the above points about changing the database design, I decided to have a play and see if it is possible.

 

While this is pretty nasty and can probably be cleaned up, it does actually work.

 

SELECT Cityname, Count(Name) AS CityCount
FROM (
SELECT DISTINCT Name, CityName
FROM (
SELECT Name, SUBSTRING_INDEX( SUBSTRING_INDEX( Cities, "||", b.ournumbers ) , "||", -1 ) AS CityName
FROM UsersCities a, (
SELECT hundreds.i *100 + tens.i *10 + units.i AS ournumbers
FROM integers AS hundreds
CROSS JOIN integers AS tens
CROSS JOIN integers AS units
)b
) AS Deriv1
WHERE CityName != "") Deriv2
GROUP BY CityName
ORDER BY CityCount DESC

 

This is against a table called UsersCities with a column of the users name (Name) and a column containing the list of cities seperate by ||.

 

The other table it uses is a simple table called integers containing 1 integer column called i with 10 rows with the values 0 to 9. Basically it is used to generate a number between 0 and 999.

 

This will work with up to 999 cities in any users list of cities.

 

This was just done for amusement, and if you think you are having a hard time now if you do something like this then I really pity whoever has to fiddle with it in the future.

 

All the best

 

Keith