How can I show all the data in a certain category using an SQL query?

Question

Ok, I have 2 Mysql tables, the structure of which is below:

Task Table

job_id---job_cat_id---job_title---job_description---job_data----is_active ========================================================================= 1 1 title 1 description 1 2016-05-06 1 2 2 title 2 description 2 2016-05-06 0 3 2 title 3 description 3 2016-05-06 1 

Job_details table

 job_cat_id---job_cat_name ========================= 1 cat name 1 2 cat name 2 3 cat name 3 

Now I want to show all jobs for each category from the jobs table. for instance

What I need to show:

 Job Category 1 1. job 1 from category 1 2. Job 2 from category 1 Job Category 2 1. Job 3 from category 2 

So, for this I use the following sql query, but I can not get the correct result:

 $get_job = mysqli_query($conn, "SELECT jobs.job_id, jobs.job_title, job_category.job_cat_name FROM jobs LEFT JOIN job_category ON job_category.job_cat_id = jobs.job_cat_id WHERE jobs.is_active = '1' "); while($result = mysqli_fetch_array($get_job) ) { $job_id = (int) $result['job_id']; $job_title = htmlspecialchars($result['job_title']); $job_category = htmlspecialchars($result['job_cat_name']); echo "<h4>$job_category</h4>"; echo "<p>$job_title</p>"; } 

Now he shows me the whole category with all the tasks, but I want to show all the tasks for each category.

What is now displayed:

 Job Category 1 1. job 1 from category 1 Job Category 1 1. Job 2 from category 1 Job Category 2 1. Job 3 from category 2 

Answer 1

First, we must remember that the result of the SELECT query is a newly created table. This is not a multidimensional array. If it were a multidimensional array, then you could leave with printing the category of tasks at the beginning of each new array, which could group all the tasks in one category, however, since this is not the type of result that SQL SELECT QUERY received, you print the category tasks after each line:

 echo "<h4>$job_category</h4>"; echo "<p>$job_title</p>"; 

Decision:

The solution to your problem was to first use ORBER BY ASC in your sql query:

 $get_job = mysqli_query($conn, "SELECT jobs.job_id, jobs.job_title, job_category.job_cat_name FROM jobs LEFT JOIN job_category ON job_category.job_cat_id = jobs.job_cat_id WHERE jobs.is_active = '1' ORDER BY job_cat_id ASC"); 

From there, you know that tasks in each category should be at least grouped next to each other (from the lowest to the highest, like 1,1,1,1,2,2,3,3,3). Now you can conditionally print $job_category if AND ONLY IF it hasn't been printed before.

Change this line:

 echo "<h4>$job_category</h4>"; 

to this line:

 if ($previous_print != $job_category) { echo "<h4>$job_category</h4>"; $previous_print = $job_category; } 

Let me know if it works now.

Answer 2

Another solution might be if you simply run one request using the group by job_cat_id, and then write another request inside the loop to get the desired result with the where job_cat_id clause.

Answer 3

Here you need to make 2 requests. Here, the exmaple code may need some tweaks to suit your table column names:

 <?php $query = "SELECT `job_cat_id`, `job_cat_name`, COUNT(`jobs`.`id`) as `jobs` FROM `job_category` GROUP BY `job_cat_id`"; $get_cat = mysqli_query($conn, $query); $cats = []; while($result = mysqli_fetch_array($get_cat) ) { $result['jobs'] = []; $cats[$result['job_cat_id']] = $result; } $get_job = mysqli_query($conn, "SELECT jobs.job_id, jobs.job_title, jobs.job_cat_id FROM jobs WHERE jobs.is_active = '1' AND `job_cat_id` IN (" . implode(',', array_keys($cats)) . ")"); while($result = mysqli_fetch_array($get_job) ) { $cats[$result['job_cat_id']][] = $result; } foreach ($cats as $cat) { $job_category = htmlspecialchars($cat['job_cat_name']); echo "<h4>$job_category</h4>"; foreach ($cat['jobs'] as $job) { $job_title = htmlspecialchars($job['job_title']); echo "<p>$job_title</p>"; } }