CODEWITHSUNDEEP
phpmysqlsql
ryno
ryno

Reputation: 374

Create a list in php from two tables in mysql

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.

Upvotes: 0

Views: 306

Answers (3)

Vamsi
Vamsi

Reputation: 873

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

  1. fetch categories from db and display in list 1 with sub category list empty
  2. on change of category submit the form using jquery or javascript
  3. 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.

Upvotes: 1

DTecMeister
DTecMeister

Reputation: 76

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.

Upvotes: 1

Jerska
Jerska

Reputation: 12002

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

Upvotes: 1

Related Questions