How to search value from stored from as array to Database

#1

Hi,

i am using implode function to upload multiple values to database like

Lanuage

Kannada, English, Hindi
English, Hindi
Kannada

In that Language is table field…, and three row values what i mentioned So i want to find Language only in kannada Is that Possible…?

thanks,

How to fetch array values from mysql database
#2

Wrong database design, use Normalization

#3

But How to store multiple values to a single field from Database

for ex: languages known : obviously select multiple languages right…?

#4

You just don’t do that. That leads to your problem.

#5

I’ll explain my situation :

languages is the field name of database,

so i want to insert multiple Values to insert database so that i am using multiple selection drop down and storing values through Implode statement…

so i want to search languages who are know only particular languages is that possible …?

#6

just stop this implode stuff and use normalization - problem solved.

#7

can u guide me…?

thanks,

#8
#9

The last time I did a multi-language system, there was a table of languages. Each column stored a different language and the secondary key was a tag. So when the server prepared the page, you would query for the section that needed to be displayed and based on the language preference, it would select the column needed.

#10

my table structure is :

tbl_lang
lang_id     lang_known
1               Kannada
2               Kannada
3               Hindi

tbl_user_reg
user_id    user_name    lang_id
101         demo0             1,2
102         demo1             1,3
102         demo2             1,2,3

so, i am using implode() to store lang_known values …

Now my point is

  1. can i search lang_known only in perticular language …
  2. I cant fetch the values from database lang_known.

any suggestion please…

thanks,

#11

That is a disaster waiting to happen. You should not be storing a delimited list in a relational database. To continue with that style of design, you would need a linking table to relate users to languages. Something like,

user_language:
-id
-user_id
-lang_id

#12

Sorry that is my mistake actually i linked both the table
Updated question kindly check…

thanks,

#13

any suggestion please…

thanks,

#14

You need to store the user’s language known data in a 3rd table as has already been stated. The user_id and lang_id columns in that table would need to be defined as a composite unique index, in order to prevent duplicate data for each user.

You would JOIN the user table (I don’t know why people need to name tables with a variation of ‘table’ in the table name) with the user_language table.

// to search for users with a specific language id
SELECT u.user_name, other columns you want ...
 FROM tbl_user_reg u
 JOIN user_language ul ON u.user_id = ul.user_id
 WHERE ul.lang_id = ?
 
// to search for users with a specific language name
SELECT u.user_name, other columns you want ...
 FROM tbl_user_reg u
 JOIN user_language ul ON u.user_id = ul.user_id
 JOIN tbl_lang l ON ul.lang_id = l.lang_id
 WHERE l.lang_known = ?
#15

Yes but lang_id from tbl_user_reg is stored multiple values…

and how to fetch the lang_name …?

#16

phdr

It is not working…

#17

Dear All,

tbl_languages
lang_id    lang_known
1            Kannada
2             English
3             Hindi

tbl_user_reg
user_id              user_name              lang_id
101                     demo0                     1,2
102                     demo1                     1,3
103                    demo2                     1,2,3

I want to Show language name instead of ID I tried like this but it is not working…

SELECT * FROM tbl_user_reg
INNER JOIN tbl_languages ON tbl_user_reg.lang_id=tbl_languages.lang_id

My expected Output is :

ID: 101
Name: demo0
Language known: Kannada, English

But Above query is not working…
any suggestion please…

thanks,

#18

for relations with multiple entries you need another table. Im quite sure this was explained in another thread here.

user_languages
user_id, language_id

#19

So we have to do separate table…?

#20

you dont have to, but instead of hacking it with some backwards solution why not just do it the right way?