php list and select data using join tables

Question

I have this database table structure for posts, categories and post_categories

posts:

| id | title | details
| 1  | test  | test details

categories:

| id |     name     | parent_id
|  1 |   cat one    |     0
|  2 |   cat two    |     0
|  3 |   cat three  |     0

post_categories

|  category_id  |  post_id
|      1        |     1
|      2        |     1

I insert multiple categories using input checkbox for each post in post_categories table. Now in update page(domain.com/admin/posts/1/edit) i need to show categories list and checked input checkbox.can i generate this output using join two table(categories and post_categories) Or I need to separate two query(first from post_category table and second from categories table) like this output?! (my choice is join method so how to generate use join method?)

<input type="checkbox" value="1" checked> cat one
<input type="checkbox" value="2" checked> cat two
<input type="checkbox" value="3"> cat three  //unchecked (not in post_categories table)

update: for list categories query:

$query = $this->db->table('categories')
    ->select('id, name, parent_id')
    ->get()
    ->getResultObject();

data in print_r:

Array
(
    [0] => stdClass Object
        (
            [id] => 1
            [name] => cat one
            [parent_id] => 0
        )

    [1] => stdClass Object
        (
            [id] => 2
            [name] => cat two
            [parent_id] => 0
        )

    [2] => stdClass Object
        (
            [id] => 3
            [name] => cat three
            [parent_id] => 0
        )

    [3] => stdClass Object
        (
            [id] => 4
            [name] => cat4
            [parent_id] => 2
        )

    [4] => stdClass Object
        (
            [id] => 5
            [name] => cat5
            [parent_id] => 0
        )

    [5] => stdClass Object
        (
            [id] => 6
            [name] => cat6
            [parent_id] => 0
        )

)

after join:

$query = $this->db->table('categories')
    ->select('id, name, parent_id')
    ->join('post_categories', 'post_categories.category_id = id','left outer')
    ->where('post_categories.post_id', $id)
    ->get()
    ->getResultObject();

and data is:

Array
(
    [0] => stdClass Object
        (
            [id] => 1
            [name] => cat one
            [parent_id] => 0
        )

    [1] => stdClass Object
        (
            [id] => 4
            [name] => cat two
            [parent_id] => 2
        )

)

result after join is false.

@ZaheerAhmad: my choice is one query using join method. can u tell me how to? — H3llC0d3r
– H3llC0d3r, Commented Apr 9, 2020 at 8:13

Zaheer Ahmad · Accepted Answer · 2020-04-12 11:14:19Z

1

You can do this with one query but for this, you need one extra column in query for checking category is using or not.

SELECT id,  name, if(pc.cat_id IS NULL,0,1) as `value` from categories as ct   LEFT JOIN post_categories as pc on pc.cat_id = ct.id

demo link

I hope this query will fulfill your requirements.

If you need more detail about this you can visit this StackOverflow thread MySQL Join and create new column value

answered Apr 12, 2020 at 11:14

Zaheer Ahmad

1762 silver badges11 bronze badges

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

Comments

Randika · Accepted Answer · 2020-04-09 08:39:42Z

0

You can run below query to get the categories selected:

select post_categories.category_id from posts 
left join post_categories on post_categories.post_id=posts.id
left join categories on post_categories.category_id=categories.id;

get these ids to an array and within your category loop check if the ids are in array and make them checked.

quite easy !

answered Apr 9, 2020 at 8:39

Randika

1271 silver badge2 bronze badges

3 Comments

H3llC0d3r Over a year ago

your code show only categories for posts like this output:

Array (     [0] => stdClass Object         (             [category_id] => 1         )      [1] => stdClass Object         (             [category_id] => 2         )  )

I need to: first list all categories then select categories for posts.

Randika Over a year ago

First you need to run above query to have an array with the necessary category ids. Then run select * from categories; and loop the output in a foreach and within that loop use your previously created array to check if the category ids are present. if yes, make the input checkbox checked.

H3llC0d3r Over a year ago

Sure your method is for two query so without join method(one query). if right, please update your answer for your idea

Collectives™ on Stack Overflow

php list and select data using join tables

2 Answers 2

Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related