CODEWITHSUNDEEP
phpdatabaselaravel
Andrew
Andrew

Reputation: 404

How to get name of country based on id?

I have a table where I'm saving my records id of languages (language_skills_selected). Now, I need to get name for every item. I saved names in table language_skills.

Here is my variables and dd for everyone:

$data['user_test'] = DB::table('language_skills_selected')->where('user_id', $id)->get(); 

Language: 22 - Level: Intermediate 

Language: 41 - Level: Beginner 

Language: 13 - Level: Expert 

Language: 35 - Level: Expert 

Language: 23 - Level: Intermediate

And here I have the variable with names : $data['language_skills'] = \App\LanguageSkill::lists('language_skill','id'); 

{"1":"Albanian","2":"Arabic","3":"Bengali","4":"Belarus","5":"Bulgarian","6":"Czech","7":"Chinese","8":"Korean","9":"Croatian","10":"Danish","11":"Hebraic","12":"English","13":"Estonian","14":"Finnish","15":"French","16":"German","17":"Greek","18":"Hindi","19":"Icelandic","20":"Italian","21":"Japanese","22":"Latvian","23":"Lithuanian","24":"Macedonian","25":"Hungarian","26":"Norwegian","27":"Dutch","28":"Persian","29":"Polish","30":"Portuguese","31":"Romanian","32":"Russian","33":"Serbian","34":"Slovak","35":"Slovene","36":"Spanish","37":"Swedish","38":"Thai","39":"Turkish","40":"Ukrainian","41":"Other"}

So, instead of these id, I need to have Name of language - ex: Language: Bulgarian - Level : Expert

Upvotes: 1

Views: 1199

Answers (3)

kelvin kantaria
kelvin kantaria

Reputation: 1448

Please try this

$data['user_test'] = DB::table('language_skills_selected')
     ->select("language_skills.language_skill")
     ->join("language_skills","language_skills_selected.language_skills","=","language_skills.id")
     ->where('language_skills_selected.user_id', $id)->get();

Upvotes: 1

hosein in jast
hosein in jast

Reputation: 370

you can use this code

1 -> query for Get All skills

$data['user_test'] = DB::table('language_skills_selected')->where('user_id', $id)->get();

2 -> Query For language_skills Name
$data['language_skills'] = \App\LanguageSkill::where('id',$data['user_test']['language_skills])->get('language_skills');

3 -> Set Name instead of ID
$data['user_test']['language_skills'] = $data['language_skills'];

You can use foreach Loop; I suggest to change the names of variable

Upvotes: 1

Matthias S
Matthias S

Reputation: 3553

Naming conventions

First of all, I would change the strucuture to use conventional Laravel naming. It also helps others to better understand relations in your table.

You already use id as your auto-increment primary key, which is good. If you then reference another table through a foreign key, use the singular table name, followed by _id. So your language_skills_selected table structure becomes

language_skills_selected
id int primary
user_id int foreign key (users table)
language_skill_id int foreign key (language_skills table)

language_skills
id int primary
name string (the name, singular, as it will only have one name in it)

I would rename the column language_skills to name, as the table name already suggests that each entry in the table will be a language_skill. The column name should be more descriptive of what it actually holds, in your case language_skills column holds the name. So then you might as well name the column that way.

Joining tables

In Laravel, you can use the Query Builder (see here) to build queries, or you can use Eloquent Models (see here). I highly suggest you read through both parts of the documentation extensively. It is a vital part of the framework and choosing when to use what depends on your use case.

Now finally to your solution. If you have changed the column names, you can write a join statement between two tables like this:

$data['user_test'] = DB::table('language_skills_selected')
    ->join('language_skills', 'language_skills_selected.language_skill_id', '=', 'language_skills.id')
    ->where('user_id', $id)
    ->get();

You will then have a joined result, where you can access the name of the language_skill as well.

I would also suggest you read in general more about MySQL, as it is good basis know how before diving into the framework. Lookup MySQL joins, and read material like this.

Upvotes: 1

Related Questions