How can I show all data under specific category using sql query?

Question

well, I have 2 Mysql table which structure is bellow :

Table Jobs

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

Table job_details

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 under each category from jobs table. E.g

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 to do this I am using following sql query but can't 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, It's showing me all category with all jobs but I want to show all jobs under each category.

What is showing now :

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 have to remember that the result from a SELECT query is a newly generated table. It is not a multi dimensional array. If it were a multidimensional array, then you could get away with printing the job category at the beginning of each new array which could be grouping up all the jobs in a single category, however since this is not the type of result obtained by the SQL SELECT QUERY, you are printing the job category after each line:

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

Solution:

A solution to your problem would be to first use the 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 the jobs in each category should at least be grouped up next to each other (from lowest to highest like 1,1,1,1,2,2,3,3,3). What you can now do is have a conditional print the $job_category if AND ONLY IF it hasn't been printed already previously.

Change this line:

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

into this line:

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

Let me know if it works now.