Db question

tommyrulez

I have a db with 15 columns each with a different name. Each row may have a different value. What would be the best way to check comparisons and display the results?

Example:
Highest rated | Number of votes

Dr. Pepper 30

Coke 29

Select UNIQUECOUNT(*) AS something TABLE dinner or

SELECT COUNT(*) AS count TABLE dinner LIKE "%d%"

I am at a loss on how to code the dang thing or it could be just the pain pills.

laserlight

What do you mean by "check comparisons"?

tommyrulez

Checks the DB using unique or count and finds 30 instances of drpepper, 20 instances of coke and so on. How can I take that information and display it?

laserlight

You probably want a query that is something like this:

SELECT name, COUNT(name) AS name_count FROM tablename GROUP BY name

You would just run the query, then retrieve and display the results as usual (i.e., it is no different from the examples that you probably have already seen).

But hold on: what is the name of this table, and what are these 15 columns?

tommyrulez

It is a survey. They can enter 15 choices and not everyone's first, second...choice will be the same. I need to see if that person's choices are currently in the db and if it is then it adds to that field +1.
Example above
I like dr. pepper first and then coke.
You put pepsi and dr. pepper.
dr.pepper would get +1, coke +0 and pepsi +1.

Displayed:
Dr.Pepper 2
Coke 1
Pepsi 1

laserlight

Does the order of the choices matter?

EDIT:
If the order does not matter, then it is a simple case of a many to many relationship between participants of the survey and the survey choices.

Create a table of Participants, e.g., with id and name. Create a table of Choices, e.g., with id and name. Create a table of ParticipantChoices with participant_id and choice_id.

For every choice that a participant makes, add a row to the ParticipantChoices table with the participant's id and the choice's id.

Now, to list the choices the way you want to, you would run this query:

SELECT name, COUNT(choice_id) AS choice_count
    FROM ParticipantChoices JOIN Choices ON ParticipantChoices.choice_id=Choices.id
    GROUP BY name

tommyrulez

Sorry, I had to sleep.

foreach ($_POST as $key);
define ($something, $key);

"INSERT INTO choice (first, second,...)
VALUE('$something', .....)";

And my mind is still foggy from the pain pills and sleep.

laserlight

So, did you understand what I was trying to say?

tommyrulez

Not all of it.
I have 3 tables. One named users that has id, date and user. One named choices that has id and choice. One named p_choice that has id, p_id and c_id. I understand the concept, I just can't visualize the structure.

Would the table choices have 15 columns for user input?
How would that tie into p_choice?

laserlight

tommyrulez wrote:
Would the table choices have 15 columns for user input?

No, it would have 15 rows instead.

tommyrulez wrote:
How would that tie into p_choice?

This is a join table that connects the participants and their choices. In other words, the table of participants and the table of choices do not depend on user input. It is the contents of p_choice that reflects the user input.

tommyrulez

I am so trying to understand.
table users

column id | column date | column user

1 | 2010-16-03 | Tommy

       table choices

column id | column choice

1 -15 | 1-15

           table p_choice

column id | column p_id | column c_id
1 | p_1 | c_1

This what I am having a hard time understanding. Tying p_1 to column user and c_1 to column choice?

laserlight

Like:
table users
column id | column date | column user
1 | 2010-16-03 | Tommy

table choices
column id | column choice
1-15 | 1-15

table p_choice
column id | column p_id | column c_id
1 | 1 | 1

tommyrulez

laserlight;10946068 wrote:
You probably want a query that is something like this:
SELECT name, COUNT(name) AS name_count FROM tablename GROUP BY name
You would just run the query, then retrieve and display the results as usual (i.e., it is no different from the examples that you probably have already seen).

But hold on: what is the name of this table, and what are these 15 columns?

I like this better:

table users
column id | column date | column user
1 | 2010-16-03 | Tommy

Could I do something like:

SELECT *, COUNT(*) AS name_count FROM tablename  ORDER BY name_count DESC

laserlight

tommyrulez wrote:
I like this better:

I suggest that you read this article on database normalisation.

By doing what you like, you are just making life difficult for yourself. If you like to be a masochist, go ahead and figure out the painful way yourself 🙂