Re: Weird Sorting Question. REPOST (Modified by carreraSC)

[Michael Stassen]

carreraSC wrote:

No one has any ideas?

Hi,

I've got a sorting question that I can't figure out.  The page in  
question is here:
http://toolstudios.com/intera/careers/index.php

I'd like it to show up like this:
Accounting
    job 1
    job 2
Engineer
    job 1
    job 2
IT
    job 1
    job 2
Basically, I'd like the categories of employment to be alphabetical.   
Here's the SQL stuff now:

<?php
include("common.inc");
$connection = @mysql_connect("$db_host","$db_user","$db_pass") or  
die("Couldn't Connect.");
$db = @mysql_select_db($db_name, $connection) or die("Couldn't select  
database.");

$sql ="SELECT  
id,status,title,summary,description,post_date,hidden,job_id,contact_emai l
                                    FROM careers
                                    WHERE hidden = '2'
                                    ORDER BY job_id,title ";
Here you retrieve all jobs ordered by job_id, which, I assume, is the 
key to the category table.  So, your categories are in id order, rather 
than alphabetical by name (category.category).

$result = @mysql_query($sql, $connection) or die("Error #".  
mysql_errno() . ": " . mysql_error());

while ($row = mysql_fetch_array($result)) {
    
$id=$row['id'];
$status=$row['status'];
$title=$row['title'];
$summary=$row['summary'];
$description=$row['description'];
$post_date=$row['post_date'];
$hidden=$row['hidden'];
$job_id=$row['job_id'];
$contact_email=$row['contact_email'];
    
    
// lookup sub_cat name and display, only if it's new   
if ($job_id != $old_job_cat){   
       
           
 // New Connection and selection from Sub Category db
 $connection = @mysql_connect("$db_host","$db_user","$db_pass") 
> or  die("Couldn't Connect.");

There's no need to reconnect.  In fact, this adds a lot of overhead. 
You are reconnecting for every job, when your original connection is 
still good.

 $db = @mysql_select_db($db_name, $connection) or 
die("Couldn't  select database.");
Same here.  You aren't changing dbs, so don't reselect the same db.

            $cat_sql ="SELECT job_id, category FROM career_categories 
WHERE  job_id = '$job_id' ORDER BY category ASC";
You only need to select category here, as you already have $job_id.

            $cat_result = @mysql_query($cat_sql, $connection) or 
die("Error #".  mysql_errno() . ": " . mysql_error());

 while ($cat_row = mysql_fetch_array($cat_result)) {
           
 $job_id=$cat_row['job_id'];
Redundant.  This sets $job_id = $job_id.

 $category=$cat_row['category'];
           
 // We then print out the RESULT row   
                $job_heading = "      <br>\n   <FONT  
SIZE=\"4\"><B>$category</B></FONT>\n";
               
                $display_block .= "$job_heading      <br>\n";
            }   
        }
          
        // This ends the Sub Cat Row, and continues the Job Listings  
Row                               
       
            $old_job_cat = $job_id;

           
            $display_block .= "          <a  
href=\"detail_action.php?id=$id\">$title</a> - $summary<br>\n";
           
       

    }    



$num = @mysql_num_rows($result);

if ($num != 0)  {

include("list_results.php");

} else {

include("list_no_rslt.php");
}
?>

The problem is, when the script looks for "job_ID" and attempts to see  
if it matches anything, it doesn't return an array of results...just 1.  
 So, can't really alphabetize one result.  Any workarounds?
Exactly, you are querying the db for the job category once for each job. 
 You would be better off using a join to get the category for each job 
in the original query, ordered by the category so you get the sorting 
you want, then print the header row each time the category changes.  See 
the sample code below.

Michael

<?php
include("common.inc");
$connection = @mysql_connect("$db_host","$db_user","$db_pass")
or die("Couldn't Connect.");
$db = @mysql_select_db($db_name, $connection)
or die("Couldn't select  database.");
$sql ="SELECT c.id, c.status, c.title, c.summary, c.description,
              c.post_date, c.hidden, c.job_id, c.contact_email,
              cat.category
       FROM careers c, career_categories cat
       WHERE c.job_id=cat.job_id AND hidden = '2'
       ORDER BY cat.category, c.title";
$result = @mysql_query($sql, $connection)
or die("Error #".  mysql_errno() . ": " . mysql_error());
$old_cat = ''; //make sure we get a header for the first job
while ($row = mysql_fetch_array($result))
{
  $id=$row['id'];
  $status=$row['status'];
  $title=$row['title'];
  $summary=$row['summary'];
  $description=$row['description'];
  $post_date=$row['post_date'];
  $hidden=$row['hidden'];
  $job_id=$row['job_id'];
  $contact_email=$row['contact_email'];
  $category=$row['category'];
  // display category name if it's new
  if ($category != $old_cat)
  {
    $job_heading = "  <br>\n   <FONT SIZE=\"4\"><B>$category</B></FONT>\n";
    $display_block .= "$job_heading  <br>\n";
  }
  // display the job listing row
  $display_block .= "    <a 
href=\"detail_action.php?id=$id\">$title</a> - $summary<br>\n";

  $old_cat = $category; //update $old_cat
}
$num = @mysql_num_rows($result);
if ($num != 0)
{
  include("list_results.php");
}
else
{
  include("list_no_rslt.php");
}
?>


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]