[RESOLVED] return list of distinct items from multiple columns

Dereksdontrun

Thanks to all who have offered me help and advice....I am learning a lot but still need your help as i cant figure out the best way to do this not sure if the problem is my database and i should redesign


example table and fields

fruit_1            fruit_2           fruit_3         fruit_4

apples            oranges            pears

oranges                                               melon


                                                  apples

Is this database design suitable?

How do I go from this to a string of distinct fruit? using SQL and/or PHP

apples, oranges, pears, melon

thanks for all your help

dagon

as soon as your adding 1 2 _3 etc to a field name you can be almost certain this is the wrong approach

Dereksdontrun

Can you tell us more about the table schema and the data it stores?

The database doesnt involve fruit, the actual data is quite specialised so i thought this analogy would be easier to illustrate. But the idea and data type is the same,...so i'll stick with the fruit
think of it as trying to capture user preference for fruit

hope that helps

as soon as your adding 1 2 _3 etc to a field name you can be almost certain this is the wrong approach

Im new to this if you could point out what would be the best strategy to take in this scenario that would be really appreciated..

Thanks everyone all your time and expertise in this matter

vivek151189

It would be possible but its wrong approach of database design
you can fire four select distict (column name) for each column
now you have the four array
compare with each array using array_diff, array_intersect
to get the distinct value from the four array

Weedpacket

Dereksdontrun wrote:
Im new to this if you could point out what would be the best strategy to take in this scenario that would be really appreciated..

bradgrafelman has suggested you look up the topic of database normalization.

Dereksdontrun

Thanks everyone will read up more around this....

Brad...using your example..order is not an issue but understand how it can be achieved from your edit

If i wanted to record multiple choices per user, say on a particular day... i would still need to multiple columns for fruit... fruit_1, fruit_2 etc, is that correct? would this be classed as normalized or is this something to be avoided? If to be avoided how would I capture multiple choices on for example a daily basis

Then would i need to do as vivek151189 recommends

you can fire four select distict (column name) for each column
now you have the four array
compare with each array using array_diff, array_intersect
to get the distinct value from the four array

to create the comma delimited string?

Thanks again

Dereksdontrun

OK Brad, i get what you're saying and can appreciate the wisdom in your advice.
If i have to capture three preferences, wont that mean the form has to be submitted 3 times so that a new row is inserted each time

In my case i want to record 3 preferences on a form submitted once, surely this is not possible with your normalized example?

Thanks for all this Brad, it is really helping me to get this thing clearer in my mind.🙂

Dereksdontrun

You could have a single form that contains 3 different inputs.

So i would have 3 separate inputs whose contents were written to the same field?

I would then have to execute 3 insert statements for each form submission...is that correct?

Dereksdontrun

Ahh i now see what you mean...I didn't know that you could insert multiple rows from a single insert statement.....will look into your suggestion about dynamically adding removing fields as that will be useful

Again, many thanks for all your help....really appreciated..

cheers

dalecosp

If I might be so bold; look for a book on SQL ... perhaps even a specific work on MySQL ... or perhaps a good set of tutorials on the web. Definitely read something regarding database design. Install the mysql client and server locally somewhere and do some practice and testing.

It may not seem important to your present goals (what course of education does?) ... but it will reward you later.

One of the best things I did when I moved from general consulting to specific programming was read as much as possible as quickly as I could with the goal of conceptualizing SQL concepts. It has helped a good deal in my current work, although there's still much to be learned....

vivek151189

Do need some more practice on sql

Weedpacket

Dereksdontrun wrote:
I didn't know that you could insert multiple rows from a single insert statement

Not that that is critical: you could use multiple insert statements (indeed, optimising such multiple operations are what prepared statements were designed for).