CODEWITHSUNDEEP
phpmysql
Boopathi V
Boopathi V

Reputation: 37

fetch and display values from multiple mysql tables using php

hi i need to display values from multiple tables by a single mysql query by userid. i'm having 6 tables followings...

country: countryid, country

state: stateid, statename, countryid

city: cityid, city, stateid.

categories: categoryid, category_name.

sub_categories: sub_category_id, sub_category_name.

users: userid, username, countryid, stateid, city, category_id and sub_category_id.

Now i want to display all details by userid. i written query and after it displays only id's for country, state, category, and sub_category and not display their names. i'm using select statement and as well as join statement. but not get exact output. i'm have basic knowledge in joining tables query. please give idea or query to display my output.

Upvotes: 0

Views: 790

Answers (2)

automaticAllDramatic
automaticAllDramatic

Reputation: 2063

The database structure could be better, also, always have uniform nomenclature across your tables, it is easier writing queries.

Something like this should suffice:

SELECT c.country, s.statename, ci.city, ca.category_name, sc.sub_category_name FROM country c, state s, city ci, categories ca, sub_categories sc, users u WHERE u.country.id = c.countryid, u.stateid = s.stateid, u.city = ci.city, u.category_id = ca.categoryid AND u.sub_category_id = sc.sub_category_id ORDER BY u.userid DESC;

Upvotes: 0

Fluffeh
Fluffeh

Reputation: 33512

You can use a join like this:

select
    a.username,
    b.country,
    c.statename,
    d.city,
    e.category_name,
    f.sub_category_name
from
    users a
        join country b
            on a.countryid=b.countryid
        join state c
            on a.stateid=c.stateid
            and a.countryid=c.countryid
        join city d
            on a.city=d.cityid
            and a.stateid=d.stateid
        join categories e
            on a.category_id=e.categoryid
        join sub_categories f
            on a.sub_category_id=f.sub_category_id

I am using the users.city column name from your question here, is it really cityid though - that would match the rest of your column naming convention more.

Upvotes: 1

Related Questions