CODEWITHSUNDEEP
phpmysql
Jou
Jou

Reputation: 13

returning data from table using category_id from a category table from mysql using php

Hi everyone this is my first question here and i'll be very grateful if you could help me.

I have a table like this in mysql

//table items
id | item_name | description | link | category_id | is_active

And i have another table like this

//table categories
id  category_name | cat_description | is_active

I want to get all the data in category_name and get all the content from item_name if is_active column is on, and also if category is_active column is on.

I was trying to make a function that would retrieve all that data with html content and just if certain conditions are true.

My function is something like this:

    function getCatAndItems(){
    include "conn.php";
    $petition = mysqli_query($conn,"SELECT * FROM items,categories WHERE is_active=1");
    while ($row = mysqli_fetch_array($petition)) {
        $filename = $row['nombre'];
        $url       = $row['url_document'];

    echo "<a href=../docs/files/$url'><li>&nbsp;".$filename."</li></a>";
  }
}

My goal is to bring the categories and if the categories are active and if the items table in the category_id is the same as the category id and is_active it will bring me also the data in the items table that share the same number.

I hope you understand me and hope you could help me, thank's

Upvotes: 1

Views: 150

Answers (3)

1000111
1000111

Reputation: 13519

A simple inner join between these two tables would do the job done.

SELECT
      items.name,
      items.description,
      items.link,
      categories.category_name,
      categories.cat_description
FROM categories
INNER JOIN items ON categories.id = items.category_id
WHERE categories.is_active = 1
AND items.is_active = 1

Upvotes: 0

Vipin Jain
Vipin Jain

Reputation: 3756

You can get using JOIN

SELECT items.name, items.description, items.link, categories.category_name, categories.cat_description
FROM categories
JOIN items 
ON categories.id = items.category_id
WHERE categories.is_active = 1
AND items.is_active = 1;

Upvotes: 1

Mr. Engineer
Mr. Engineer

Reputation: 3515

Here is your query : 

select i.id,i.item_name,i.description,i.link,i.category_id,i.is_active,c.category_name 
               from items as i left join categories as c on i.category_id=c.id where 
               i.is_active=1 and c.is_active=1;

Upvotes: 0

Related Questions