0

I want to create an api for android. The query menu below is not displaying everything. How to fix this? I want category,subscatery in android api. But for me, only left join in category is displayed.I have tried many times. If you know let me know i am php devloper

menu->category

INSERT INTO `menu` (`mid`, `menu_Name`, `menu_icon`) VALUES
(15, 'v', '9673-2022-08-24.png'),
(21, 'dgh', '9088-2022-08-24.jpg'),
(22, 'fg', '5378-2022-08-24.png'),
(24, 'ft', '7245-2022-08-24.png');

tbl_category->subcategory

  INSERT INTO `tbl_category` (`cid`, `category_name`, `category_image`, `menu_id`) VALUES
    (31, 'fgj', '2048-2022-08-24.jpg', 15),
    (32, 'fgh', '0644-2022-08-24.png', 16);

this tbl_recipes->tbl_recipes

INSERT INTO `tbl_recipes` (`recipe_id`, `cat_id`, `recipe_title`, `menu_id`, `recipe_description`, `recipe_image`, `video_url`, `video_id`, `content_type`, `size`, `featured`, `tags`, `total_views`, `last_update`) VALUES
(47, 0, 'dsg', 0, '<p>sg</p>\r\n', '1661254132_Screenshot (6).png', '', 'cda11up', 'Post', '', 0, '0', 0, '2022-08-23 11:28:52'),
(48, 14, 'sad', 0, '<p>asdasd</p>\r\n', '1661254498_Ent-card-1.jpg', '', 'cda11up', 'Post', '', 0, '0', 0, '2022-08-23 18:57:49'),
(49, 25, 'fgh', 13, '<p>fghfgh</p>\r\n', '1661280418_Screenshot (5).png', '', 'cda11up', 'Post', '', 0, '0', 0, '2022-08-23 18:57:44'),
(50, 25, 'try', 13, '<p>tyt</p>\r\n', '1661281058_Screenshot (5).png', '', 'cda11up', 'Post', '', 1, '0', 0, '2022-08-23 18:57:54');

this query android

  $query_category = "SELECT DISTINCT m.mid, m.menu_Name, m.menu_icon, c.cid, c.category_name, c.category_image, COUNT(DISTINCT r.recipe_id) as recipes_count FROM tbl_recipes r LEFT JOIN tbl_category c ON r.cat_id = c.cid left join menu m on m.mid=r.menu_id GROUP BY c.cid ORDER BY c.cid DESC LIMIT $limit";

i need

menu
   tbl_category
           tbl_recipes
atozcodes
  • 1
  • 2
  • 13
  • share the json structure which you needed – Shibon Aug 25 '22 at 04:18
  • @Shibon {"status":"ok","featured":[],"category":[{"mid":15,"menu_Name":"v","menu_icon":"9673-2022-08-24.png","cid":null,"category_name":null,"category_image":null,"recipes_count":1}],"recent":[{"recipe_id":51,"recipe_title":"wer","cat_id":27,"recipe_image":"1661329035_test.png","recipe_description":"

    ssfdfds<\/p>\r\n","video_url":"","video_id":"cda11up","content_type":"Post","featured":0,"tags":0,"total_views":0,"category_name":null}],"videos":[]}

     – atozcodes Aug 25 '22 at 04:20
  • menu tbl_category tbl_recipes i need android api – atozcodes Aug 25 '22 at 04:21
  • How many rows need to come from the sample data provided by you – Shibon Aug 25 '22 at 05:11
  • @Shibon No matter how much it comes, I am ok – atozcodes Aug 25 '22 at 05:15
  • you facing issue with query or php code ? – Shibon Aug 25 '22 at 05:16
  • @Shibon query how to set category,subcategory – atozcodes Aug 25 '22 at 05:29
  • @Shibon $query_category = "SELECT DISTINCT m.mid, m.menu_Name, m.menu_icon, c.cid, c.category_name, c.category_image, COUNT(DISTINCT r.recipe_id) as recipes_count FROM tbl_recipes r LEFT JOIN tbl_category c ON r.cat_id = c.cid left join menu m on m.mid=r.menu_id GROUP BY c.cid ORDER BY c.cid DESC LIMIT $limit"; I have already tried, but I am getting left join in the menu. I want to see all the categories that I am posting – atozcodes Aug 25 '22 at 05:32
  • @Shibon This api is a query going to android – atozcodes Aug 25 '22 at 05:34
  • r u using any framework or core php? – Shibon Aug 25 '22 at 05:34
  • @Shibon s core php this api to android – atozcodes Aug 25 '22 at 05:35
  • Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/247539/discussion-between-jay-raman-and-shibon). – atozcodes Aug 25 '22 at 06:08

1 Answers1

1

We can use header as

header('Content-Type: application/json; charset=utf-8');

Example:

$data['category'] = []; //your query result
$data['recent'] = []; //another result
header('Content-Type: application/json; charset=utf-8');
echo json_encode($data);
die;
Shibon
  • 1,552
  • 2
  • 9
  • 20
  • how to write query $query_category = "SELECT DISTINCT m.mid, m.menu_Name, m.menu_icon, c.cid, c.category_name, c.category_image, COUNT(DISTINCT r.recipe_id) as recipes_count FROM tbl_recipes r LEFT JOIN tbl_category c ON r.cat_id = c.cid left join menu m on m.mid=r.menu_id GROUP BY c.cid ORDER BY c.cid DESC LIMIT $limit"; – atozcodes Aug 25 '22 at 06:05
  • i need query category->subcategory->table_recipes – atozcodes Aug 25 '22 at 06:09
  • I can't able to understand what you need – Shibon Aug 25 '22 at 07:12
  • subcategory to merge "SELECT DISTINCT m.mid, m.menu_Name, m.menu_icon, c.cid, c.category_name, c.category_image, COUNT(DISTINCT r.recipe_id) as recipes_count FROM tbl_recipes r LEFT JOIN tbl_category c ON r.cat_id = c.cid left join menu m on m.mid=r.menu_id GROUP BY c.cid ORDER BY c.cid DESC LIMIT $limit"; – atozcodes Aug 25 '22 at 08:17