Get foregin key value not id SQL
guys php beginner is here :)
i know my question has been asked a lot, but i read more than 20 answers & most of them not working with my query, and my query is simple but i don't know why didn't work with me :(,
i have 2 TABLE :
1- USERS : (id,username,country,status) primary key is id & foregin key is country
2- countries (country_id,country_en,country_code) primary key is country_id
when i insert data into countries table & inserting data into users everything FINE & CONNECTED to each other!
but when i came to query part here 's my problem :
everything here is working! but the country output value is ID & i want it to be the name of it because the country_en has name values
$sql = "SELECT * FROM `users`
WHERE `users`.`status` = 'active'
ORDER BY RAND() LIMIT 10 ";
after i read many question's answers i tried to add UNION, LEFT JOIN, etc.. but still the same & i know the problem from my & i will learn from my mistakes
final query that i tried to solve this issue
$sql = "SELECT *
FROM `users` WHERE `users`.`status` = 'active'
INNER JOIN `countries` ON `users`.`country` = `countries`.`country_en`
ORDER BY RAND() LIMIT 10 ";
could you guys help me with it :(
php sql mysqli inner-join
edited Jan 2 at 14:45
Thomas G
7,45871932
asked Jan 2 at 14:40
RomerRomer
87
|
show 2 more comments
guys php beginner is here :)
i know my question has been asked a lot, but i read more than 20 answers & most of them not working with my query, and my query is simple but i don't know why didn't work with me :(,
i have 2 TABLE :
1- USERS : (id,username,country,status) primary key is id & foregin key is country
2- countries (country_id,country_en,country_code) primary key is country_id
when i insert data into countries table & inserting data into users everything FINE & CONNECTED to each other!
but when i came to query part here 's my problem :
everything here is working! but the country output value is ID & i want it to be the name of it because the country_en has name values
$sql = "SELECT * FROM `users`
WHERE `users`.`status` = 'active'
ORDER BY RAND() LIMIT 10 ";
after i read many question's answers i tried to add UNION, LEFT JOIN, etc.. but still the same & i know the problem from my & i will learn from my mistakes
final query that i tried to solve this issue
$sql = "SELECT *
FROM `users` WHERE `users`.`status` = 'active'
INNER JOIN `countries` ON `users`.`country` = `countries`.`country_en`
ORDER BY RAND() LIMIT 10 ";
could you guys help me with it :(
php sql mysqli inner-join
edited Jan 2 at 14:45
Thomas G
7,45871932
asked Jan 2 at 14:40
RomerRomer
87
1
What iscountry_enfield ? you JOIN using this field, I guess it must be :INNER JOIN countries ON users.country = countries.country_id
– Vincent Decaux
Jan 2 at 14:43
@VincentDecaux country_en is (varchar), when i tried your query is giving me (: mysqli_num_rows() expects parameter 1 to be mysqli_result, boolean given in on line )
– Romer
Jan 2 at 14:47
shouldnt you JOIN oncountries.country_idinstead ofcountries.country_en? You should show some data to help us visualize the issue, its a bit unclear atm.
– Thomas G
Jan 2 at 14:48
Give us an example of table data from both the target table and the joined table. If you want to return the data ofcountryby name and not by id, we are going to need to see a table row from each table so that we know how to help you formulate your query
– DrewT
Jan 2 at 14:56
@ThomasG thank you, i will take a pic of the both tables & insert it here may it help 🌹
– Romer
Jan 2 at 15:03
|
show 2 more comments
guys php beginner is here :)
i know my question has been asked a lot, but i read more than 20 answers & most of them not working with my query, and my query is simple but i don't know why didn't work with me :(,
i have 2 TABLE :
1- USERS : (id,username,country,status) primary key is id & foregin key is country
2- countries (country_id,country_en,country_code) primary key is country_id
when i insert data into countries table & inserting data into users everything FINE & CONNECTED to each other!
but when i came to query part here 's my problem :
everything here is working! but the country output value is ID & i want it to be the name of it because the country_en has name values
$sql = "SELECT * FROM `users`
WHERE `users`.`status` = 'active'
ORDER BY RAND() LIMIT 10 ";
after i read many question's answers i tried to add UNION, LEFT JOIN, etc.. but still the same & i know the problem from my & i will learn from my mistakes
final query that i tried to solve this issue
$sql = "SELECT *
FROM `users` WHERE `users`.`status` = 'active'
INNER JOIN `countries` ON `users`.`country` = `countries`.`country_en`
ORDER BY RAND() LIMIT 10 ";
could you guys help me with it :(
php sql mysqli inner-join
edited Jan 2 at 14:45
Thomas G
7,45871932
asked Jan 2 at 14:40
RomerRomer
87
guys php beginner is here :)
i know my question has been asked a lot, but i read more than 20 answers & most of them not working with my query, and my query is simple but i don't know why didn't work with me :(,
i have 2 TABLE :
1- USERS : (id,username,country,status) primary key is id & foregin key is country
2- countries (country_id,country_en,country_code) primary key is country_id
when i insert data into countries table & inserting data into users everything FINE & CONNECTED to each other!
but when i came to query part here 's my problem :
everything here is working! but the country output value is ID & i want it to be the name of it because the country_en has name values
$sql = "SELECT * FROM `users`
WHERE `users`.`status` = 'active'
ORDER BY RAND() LIMIT 10 ";
after i read many question's answers i tried to add UNION, LEFT JOIN, etc.. but still the same & i know the problem from my & i will learn from my mistakes
final query that i tried to solve this issue
$sql = "SELECT *
FROM `users` WHERE `users`.`status` = 'active'
INNER JOIN `countries` ON `users`.`country` = `countries`.`country_en`
ORDER BY RAND() LIMIT 10 ";
could you guys help me with it :(
php sql mysqli inner-join
php sql mysqli inner-join
edited Jan 2 at 14:45
Thomas G
7,45871932
asked Jan 2 at 14:40
RomerRomer
87
edited Jan 2 at 14:45
Thomas G
7,45871932
asked Jan 2 at 14:40
RomerRomer
87
edited Jan 2 at 14:45
Thomas G
7,45871932
edited Jan 2 at 14:45
Thomas G
7,45871932
edited Jan 2 at 14:45
Thomas G
7,45871932
7,45871932
asked Jan 2 at 14:40
RomerRomer
87
asked Jan 2 at 14:40
RomerRomer
87
asked Jan 2 at 14:40
RomerRomer
87
87
1
What iscountry_enfield ? you JOIN using this field, I guess it must be :INNER JOIN countries ON users.country = countries.country_id
– Vincent Decaux
Jan 2 at 14:43
@VincentDecaux country_en is (varchar), when i tried your query is giving me (: mysqli_num_rows() expects parameter 1 to be mysqli_result, boolean given in on line )
– Romer
Jan 2 at 14:47
shouldnt you JOIN oncountries.country_idinstead ofcountries.country_en? You should show some data to help us visualize the issue, its a bit unclear atm.
– Thomas G
Jan 2 at 14:48
Give us an example of table data from both the target table and the joined table. If you want to return the data ofcountryby name and not by id, we are going to need to see a table row from each table so that we know how to help you formulate your query
– DrewT
Jan 2 at 14:56
@ThomasG thank you, i will take a pic of the both tables & insert it here may it help 🌹
– Romer
Jan 2 at 15:03
|
show 2 more comments
1
What iscountry_enfield ? you JOIN using this field, I guess it must be :INNER JOIN countries ON users.country = countries.country_id
– Vincent Decaux
Jan 2 at 14:43
@VincentDecaux country_en is (varchar), when i tried your query is giving me (: mysqli_num_rows() expects parameter 1 to be mysqli_result, boolean given in on line )
– Romer
Jan 2 at 14:47
shouldnt you JOIN oncountries.country_idinstead ofcountries.country_en? You should show some data to help us visualize the issue, its a bit unclear atm.
– Thomas G
Jan 2 at 14:48
Give us an example of table data from both the target table and the joined table. If you want to return the data ofcountryby name and not by id, we are going to need to see a table row from each table so that we know how to help you formulate your query
– DrewT
Jan 2 at 14:56
@ThomasG thank you, i will take a pic of the both tables & insert it here may it help 🌹
– Romer
Jan 2 at 15:03
1
1
What is
country_en field ? you JOIN using this field, I guess it must be : INNER JOIN countries ON users.country = countries.country_id– Vincent Decaux
Jan 2 at 14:43
What is
country_en field ? you JOIN using this field, I guess it must be : INNER JOIN countries ON users.country = countries.country_id– Vincent Decaux
Jan 2 at 14:43
@VincentDecaux country_en is (varchar), when i tried your query is giving me (: mysqli_num_rows() expects parameter 1 to be mysqli_result, boolean given in on line )
– Romer
Jan 2 at 14:47
@VincentDecaux country_en is (varchar), when i tried your query is giving me (: mysqli_num_rows() expects parameter 1 to be mysqli_result, boolean given in on line )
– Romer
Jan 2 at 14:47
shouldnt you JOIN on
countries.country_id instead of countries.country_en? You should show some data to help us visualize the issue, its a bit unclear atm.– Thomas G
Jan 2 at 14:48
shouldnt you JOIN on
countries.country_id instead of countries.country_en? You should show some data to help us visualize the issue, its a bit unclear atm.– Thomas G
Jan 2 at 14:48
Give us an example of table data from both the target table and the joined table. If you want to return the data of
country by name and not by id, we are going to need to see a table row from each table so that we know how to help you formulate your query– DrewT
Jan 2 at 14:56
Give us an example of table data from both the target table and the joined table. If you want to return the data of
country by name and not by id, we are going to need to see a table row from each table so that we know how to help you formulate your query– DrewT
Jan 2 at 14:56
@ThomasG thank you, i will take a pic of the both tables & insert it here may it help 🌹
– Romer
Jan 2 at 15:03
@ThomasG thank you, i will take a pic of the both tables & insert it here may it help 🌹
– Romer
Jan 2 at 15:03
|
show 2 more comments
2 Answers
2
active
oldest
votes
$sql = "SELECT users.username,users.country,user.status,countries.country_en
FROM users
INNER JOIN countries ON users.country = countries.country_id
WHERE users.status = 'active'
ORDER BY RAND() LIMIT 10 ";
You are however open to sql injection so do look at prepared statements
answered Jan 2 at 15:04
TwistaTwista
226110
thanks for your alert, i will convert it at all but first i start with simple query to get understanding with how work with it , much love 🌹
– Romer
Jan 2 at 15:39
add a comment |
Thank to each of you for trying helping me,
i would like to share with all of you the answer of my question with the best method to fetch the data of foregin key not the ID only, the method use "JOIN"
minitauros says: JOIN works if tables have different column names. BELOW THE FINAL QUERY
SELECT `users`.`id`, `users`.`username`, `users`.`country`, `users`.`status`, `countries`.`country_id`, `countries`.`country_en` FROM `users` JOIN `countries` ON `users`.`id` = `countries`.`country_id` WHERE `users`.`id` = 1
In the ON part of the query you tell the query which column in the first table matches the which column in the second table.
credit to minitauros
The used SELECT statements have a different number of columns? 👍
answered Jan 3 at 12:14
RomerRomer
87
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54008274%2fget-foregin-key-value-not-id-sql%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
$sql = "SELECT users.username,users.country,user.status,countries.country_en
FROM users
INNER JOIN countries ON users.country = countries.country_id
WHERE users.status = 'active'
ORDER BY RAND() LIMIT 10 ";
You are however open to sql injection so do look at prepared statements
answered Jan 2 at 15:04
TwistaTwista
226110
thanks for your alert, i will convert it at all but first i start with simple query to get understanding with how work with it , much love 🌹
– Romer
Jan 2 at 15:39
add a comment |
$sql = "SELECT users.username,users.country,user.status,countries.country_en
FROM users
INNER JOIN countries ON users.country = countries.country_id
WHERE users.status = 'active'
ORDER BY RAND() LIMIT 10 ";
You are however open to sql injection so do look at prepared statements
answered Jan 2 at 15:04
TwistaTwista
226110
thanks for your alert, i will convert it at all but first i start with simple query to get understanding with how work with it , much love 🌹
– Romer
Jan 2 at 15:39
add a comment |
$sql = "SELECT users.username,users.country,user.status,countries.country_en
FROM users
INNER JOIN countries ON users.country = countries.country_id
WHERE users.status = 'active'
ORDER BY RAND() LIMIT 10 ";
You are however open to sql injection so do look at prepared statements
answered Jan 2 at 15:04
TwistaTwista
226110
$sql = "SELECT users.username,users.country,user.status,countries.country_en
FROM users
INNER JOIN countries ON users.country = countries.country_id
WHERE users.status = 'active'
ORDER BY RAND() LIMIT 10 ";
You are however open to sql injection so do look at prepared statements
answered Jan 2 at 15:04
TwistaTwista
226110
answered Jan 2 at 15:04
TwistaTwista
226110
answered Jan 2 at 15:04
TwistaTwista
226110
answered Jan 2 at 15:04
TwistaTwista
226110
226110
thanks for your alert, i will convert it at all but first i start with simple query to get understanding with how work with it , much love 🌹
– Romer
Jan 2 at 15:39
add a comment |
thanks for your alert, i will convert it at all but first i start with simple query to get understanding with how work with it , much love 🌹
– Romer
Jan 2 at 15:39
thanks for your alert, i will convert it at all but first i start with simple query to get understanding with how work with it , much love 🌹
– Romer
Jan 2 at 15:39
thanks for your alert, i will convert it at all but first i start with simple query to get understanding with how work with it , much love 🌹
– Romer
Jan 2 at 15:39
add a comment |
Thank to each of you for trying helping me,
i would like to share with all of you the answer of my question with the best method to fetch the data of foregin key not the ID only, the method use "JOIN"
minitauros says: JOIN works if tables have different column names. BELOW THE FINAL QUERY
SELECT `users`.`id`, `users`.`username`, `users`.`country`, `users`.`status`, `countries`.`country_id`, `countries`.`country_en` FROM `users` JOIN `countries` ON `users`.`id` = `countries`.`country_id` WHERE `users`.`id` = 1
In the ON part of the query you tell the query which column in the first table matches the which column in the second table.
credit to minitauros
The used SELECT statements have a different number of columns? 👍
answered Jan 3 at 12:14
RomerRomer
87
add a comment |
Thank to each of you for trying helping me,
i would like to share with all of you the answer of my question with the best method to fetch the data of foregin key not the ID only, the method use "JOIN"
minitauros says: JOIN works if tables have different column names. BELOW THE FINAL QUERY
SELECT `users`.`id`, `users`.`username`, `users`.`country`, `users`.`status`, `countries`.`country_id`, `countries`.`country_en` FROM `users` JOIN `countries` ON `users`.`id` = `countries`.`country_id` WHERE `users`.`id` = 1
In the ON part of the query you tell the query which column in the first table matches the which column in the second table.
credit to minitauros
The used SELECT statements have a different number of columns? 👍
answered Jan 3 at 12:14
RomerRomer
87
add a comment |
Thank to each of you for trying helping me,
i would like to share with all of you the answer of my question with the best method to fetch the data of foregin key not the ID only, the method use "JOIN"
minitauros says: JOIN works if tables have different column names. BELOW THE FINAL QUERY
SELECT `users`.`id`, `users`.`username`, `users`.`country`, `users`.`status`, `countries`.`country_id`, `countries`.`country_en` FROM `users` JOIN `countries` ON `users`.`id` = `countries`.`country_id` WHERE `users`.`id` = 1
In the ON part of the query you tell the query which column in the first table matches the which column in the second table.
credit to minitauros
The used SELECT statements have a different number of columns? 👍
answered Jan 3 at 12:14
RomerRomer
87
Thank to each of you for trying helping me,
i would like to share with all of you the answer of my question with the best method to fetch the data of foregin key not the ID only, the method use "JOIN"
minitauros says: JOIN works if tables have different column names. BELOW THE FINAL QUERY
SELECT `users`.`id`, `users`.`username`, `users`.`country`, `users`.`status`, `countries`.`country_id`, `countries`.`country_en` FROM `users` JOIN `countries` ON `users`.`id` = `countries`.`country_id` WHERE `users`.`id` = 1
In the ON part of the query you tell the query which column in the first table matches the which column in the second table.
credit to minitauros
The used SELECT statements have a different number of columns? 👍
answered Jan 3 at 12:14
RomerRomer
87
answered Jan 3 at 12:14
RomerRomer
87
answered Jan 3 at 12:14
RomerRomer
87
answered Jan 3 at 12:14
RomerRomer
87
87
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54008274%2fget-foregin-key-value-not-id-sql%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
1
What is
country_enfield ? you JOIN using this field, I guess it must be :INNER JOIN countries ON users.country = countries.country_id– Vincent Decaux
Jan 2 at 14:43
@VincentDecaux country_en is (varchar), when i tried your query is giving me (: mysqli_num_rows() expects parameter 1 to be mysqli_result, boolean given in on line )
– Romer
Jan 2 at 14:47
shouldnt you JOIN on
countries.country_idinstead ofcountries.country_en? You should show some data to help us visualize the issue, its a bit unclear atm.– Thomas G
Jan 2 at 14:48
Give us an example of table data from both the target table and the joined table. If you want to return the data of
countryby name and not by id, we are going to need to see a table row from each table so that we know how to help you formulate your query– DrewT
Jan 2 at 14:56
@ThomasG thank you, i will take a pic of the both tables & insert it here may it help 🌹
– Romer
Jan 2 at 15:03