Get foregin key value not id SQL

Question

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 :(

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 02 '19 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 02 '19 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 02 '19 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 02 '19 at 14:56
@ThomasG thank you, i will take a pic of the both tables & insert it here may it help — Romer, Jan 02 '19 at 15:03
@DrewT exactly that what im looking for, output i want it to be the value not the id , i will take pic of the both tables may it help, regarding to you & other's — Romer, Jan 02 '19 at 15:05
@ThomasG for the key's : ibb.co/tZ2B79v ibb.co/RCNJKLp ibb.co/xfLny6m for both tables : ibb.co/FYC8KwM ibb.co/3r7ySP4 — Romer, Jan 02 '19 at 15:38

score 0 · Answer 1 · answered Jan 02 '19 at 15:04

0

$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 02 '19 at 15:04

Twista

241
3
11

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 02 '19 at 15:39

score 0 · Accepted Answer · answered Jan 03 '19 at 12:14

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?

Get foregin key value not id SQL

2 Answers2