fetch and display values from multiple mysql tables using php

Question

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.

Fluffeh · Accepted Answer · 2012-09-08 08:35:21Z

1

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.

edited Sep 8, 2012 at 8:35

answered Sep 8, 2012 at 8:30

Fluffeh

33.6k16 gold badges69 silver badges80 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

automaticAllDramatic · Accepted Answer · 2012-09-08 08:39:58Z

0

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;

answered Sep 8, 2012 at 8:39

automaticAllDramatic

2,0831 gold badge21 silver badges25 bronze badges

Collectives™ on Stack Overflow

fetch and display values from multiple mysql tables using php

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related