Create a list in php from two tables in mysql

Question

How could I create a list in php of categories and types of topics that are mixed together?

Meaning, the list should look like this:

Sports (this is a category)
    Soccer (this is a type)
    Golf (this is a type)
    Basketball (this is a type)
Science (this is a category)
    Biology (this is a type)
    Chemistry (this is a type)
    Anatomy (this is a type)

The difficult part is that sports and science come from the same table (called categories), and soccer, golf, basketball, biology, chemistry and anatomy all come from a different table (called types). In the MySQL table "types" there is a foreign key that points to the id of the entry in the categories table. The current setup in MySQL cannot easily be changed as the navigation of the site is dependent on it.

How could i use php to put the types in between the categories?

Currently the code i have is this:

<?php session_start ();

include 'connect.php';

$result = mysql_query("SELECT * FROM categories JOIN types ON categories.catid = types.typecat");


while($row = mysql_fetch_array($result))
{
echo $row["catname"];
}


?>

However, that outputs "SportsSportsSportsSportsSportsScienceScienceScience," which it obviously shouldnt. There are 5 types in sports, and 3 in science. So i am not sure what is happening there and i cant proceed on to the next step of adding in php to include the types.

Show us your current code, what you have tried, where you are stuck and someone will help. Knowing the complete shema for all the tables would certainly help understand your problem. — Jocelyn
– Jocelyn, Commented Jul 30, 2012 at 1:45

Vamsi · Accepted Answer · 2012-07-30 09:07:47Z

1

I think you need two lists with main category and sub-category, if so you can do as follows

fetch categories from db and display in list 1 with sub category list empty
on change of category submit the form using jquery or javascript
based on category posted fetch sub-categories from database and display in list 2 and select attribute of list 1 make it as selected based on posted category value.

You can use ajax too instead of submitting form.

answered Jul 30, 2012 at 9:07

Vamsi

8731 gold badge8 silver badges16 bronze badges

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

Comments

Jerska · Accepted Answer · 2012-07-30 01:52:25Z

1

I believe you have an id_cat in your types table. Then you should use SQL JOINs.

answered Jul 30, 2012 at 1:52

Jerska

12.1k4 gold badges38 silver badges54 bronze badges

Comments

DTecMeister · Accepted Answer · 2012-07-30 01:56:12Z

1

I don't have the code for you, but the query would be either:
select category, type from categories c, types t on c.id = t.id group by category;
or:
select distinct category from categories;
Then query each category individually.
I suspect the single query would be more efficient.

answered Jul 30, 2012 at 1:56

DTecMeister

764 bronze badges

Collectives™ on Stack Overflow

Create a list in php from two tables in mysql

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related