Hi everyone. Here's a little SQL issue I'm wondering about--how to join my two tables showing members and their multiple languages

This is what I've got:

MEMBERS table design
============================================
memberid
fname
...
lang // primary language abbreviation
secondlang // 2nd language abbreviation
thirdlang // 3rd language abbreviation
============================================


(example MEMBERS data)
============================================
memid fname lang secondlang thirdlang
0 Frank eng rom rus
1 Azucena spa eng ita
2 Rashi hin eng por
3 Jo fre kre eng
4 Carlos spa eng gua
============================================

LANGUAGES table design
============================================
langid
lang // language abbreviation, used to join with MEMBERS
lang_eng // language as spelled in English
lang_spa // language as spelled in Spanish
============================================

(example LANGUAGES data)
============================================
langid lang lang_eng lang_spa
0 eng English inglés
1 fre French francés
2 hin Hindi hindi
3 kre Kreyol criollo
4 rom Romanian rumano
...
============================================

I would like to use just one query to output all members whose primary language is X (spa, say) , yet display their first, second, and third languages in English. For example

Query results for all members whose primary language is Spanish:

FNAME LANG1 LANG2 LANG3
Azucena Spanish English Italian
Carlos Spanish English Guarani


Any ideas?

Thanks a million,

samtediou

Hello samtediou,

I would have structured the table differently to optimize the query ultimately.

Would you consider doing that? Otherwise, my knowledge of MySQL is still limited and I don't think I am able to return that kind of results with just one query...

Dear JDS,

I would restructure the table in a heartbeat if I could figure out the best way to do it. Here's my problem:

I've got a users table that contains most of the personal information about each user. I need to store information on each user's languages (principal language, and one to two languages of interest). Normally, I would just add three fields in the user table (plang, langint1, langint2).

However, since in the future the languages will need to translated into about seven different languages (in other words, the site will be translated into 7 languages, so I'll need to store available each language and its 7 translations), I decided to make a separate language table and put references to each language's unique id in the main user table.

I'll want to be able to write an SQL query to return all the users whose principal language is Vietnamese, say, and still be able to display the languages of interest (from the language table) and not just their codes.

The query I've written joins my user and languages tables, but since it returns the user and language info for all users whose language code is X (id for Vietnamese, say), I don't have any other language info, which I need for the other languages represented in each user's languages of interest fields.


Thanks,

Samtediou

Sorry samtediou... it has been a busy week for me personally!

OK, I didn't really spend much time with this perhaps you can fine-tune it a bit more at a later stage:

What do you think of a MySQL database structured this way instead? :

The last table `members_lang` just holds the relationship data where `mid` and `lid` are joined primary keys.

Thank you for helping out with other questions on the board too -

Thanks JDS,

I restructured my DB design the way you suggested--it makes more sense, doesn't it?

I greatly appreciate it!

It did raise another issue, however.

I've been using the result returned by mysql_insert_id() to verify that I successfully inserted a record. When I insert data into the new table member_langs, however, since I didn't build it with an auto-increment member_langs id, the mysql_insert_id() returns a 0. I've added an auto-incrementing id column to this table and the queries do work the same way.

Is their another way to check for a successful insert on a table without an auto-incrementing column?


TABLE: member_langs
======================
mid| lid |expertise
---+-----+--------------
1 | eng | 1
1 | rom | 2
1 | fre | 3
2 | spa | 1
2 | kre | 2
2 | ita | 3
3 | hin | 1
3 | eng | 2
3 | fre | 3
4 | spa | 1
4 | kre | 2
4 | rom | 3



Thanks,

samtediou

I don't know why you would need the last insert id for anything else except [after] adding a new record to the `member` table.

Editing, updating or deleting `member_langs` records should be handled by a hidden value (referencing the member id) in your html form I'd suspect.

Well,

Since I've added an auto-incrementing id to the mem_langs table, I don't really need to know the answer to my question. What I am doing on this page is inserting records into 3 different tables:

members, passwords, mem_langs.

My script runs three different sql strings in order to insert into the tables. I was using the values returned by each mysql_insert_id() to verify that the insert did not fail--if each returned a number greater than 0, than the insert was successful.

I wanted to roll back my database, in case of a partially successful execution of this page. For example, members and passwords were being updated, but my sql for mem_langs was failing, so using the values returned by the previous mysql_insert_id(). I delete the new records.

Anyway, that's more than you wanted to hear, I'm sure. Thanks for your help.

samtediou