• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 442
  • Last Modified:

Sorting values based on MySQL recordset

I don't know if this is possible, but I would like to use a single query to accomplish a couple of tasks.

What I have is a single query that has a list of FAQ questions that include an id, question, answer, and category (int) that is inner joined with another table to provide the category name.  Here is the query.

$query_rsFAQ = "SELECT FAQ.faqID, FAQ.faqName, FAQ.faqCat, FAQ.faqText, Categories.CatID, Categories.CatName, Categories.CatDescription FROM FAQ Inner Join Categories ON FAQ.faqCat = Categories.CatID ORDER BY Categories.CatName ASC";

What I want to do is create a menu like REGISTRATION / PLACING LISTING / MEMBERSHIP / ETC. based on the values from the query.

Now, after the menu, I want to just run the results of the query, separating the categories and provide a heading for each section.
For Example
Membership
     Question 1: Answer
     Question 2: Answer
Registration:
     Question 1: Answer
     Question 2:  Answer

Etc.

As I am kind of a newbie at arrays, I don't know how to pull out the unique values from an array column in order to make the headings/menu, without repeating the category over and over with a do/while loop.

I know how to do it by going back and hitting the database for the values, but thought it would be much faster to do it with a single query and manipulate the array instead.
0
dragboatrandy
Asked:
dragboatrandy
  • 2
  • 2
  • 2
1 Solution
 
dirknibleckCommented:
I think you'll want a second query for determining the headings:

ie:  $heading_query = "SELECT DISTINCT base.CatName FROM (" . $query_rsFAQ . ") as base"

0
 
dirknibleckCommented:
I would run it like:

$query_rsFAQ = "SELECT FAQ.faqID, FAQ.faqName, FAQ.faqCat, FAQ.faqText, Categories.CatID, Categories.CatName, Categories.CatDescription FROM FAQ Inner Join Categories ON FAQ.faqCat = Categories.CatID ORDER BY Categories.CatName ASC";

$heading_query = "SELECT DISTINCT base.CatName FROM (" . $query_rsFAQ . ") as base";
$heading_result = @mysql_query($result);

while ($heading_row = @mysql_fetch_array($heading_result, MYSQL_NUM)){
     echo $row[0];
     
     $query_rsFAQ = "SELECT FAQ.faqID, FAQ.faqName, FAQ.faqCat, FAQ.faqText, Categories.CatID, Categories.CatName, Categories.CatDescription FROM FAQ Inner Join Categories ON FAQ.faqCat = Categories.CatID WHERE Categories.CatName ='" . $row[0] . "' ORDER BY Categories.CatName ASC";

     $result_rsFAQ = @mysql_query($query_rsFAQ);
     while($rsFAQ_row = @mysql_fetch_array($result_rsFAQ, MYSQL_ASSOC)){
          echo //your columns
     }
}
0
 
carchitectCommented:
Select data by a single query and sort it in DB only, no need to do in array. Loop through the data and as soon as category changes, print accordingly.
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
dragboatrandyAuthor Commented:
Thanks for the help, could you give me an example?
0
 
carchitectCommented:
$query_rsFAQ = "SELECT FAQ.faqID, FAQ.faqName, FAQ.faqCat, FAQ.faqText, Categories.CatID, Categories.CatName, Categories.CatDescription FROM FAQ Inner Join Categories ON FAQ.faqCat = Categories.CatID ORDER BY Categories.CatName ASC";
$res=mysql_query($query_rsFAQ);

$category_id = 0;

while($row=mysql_fetch_array($res))
{

if($category_id != $row['Categories.CatID'])
{
$category_id = $row['Categories.CatID'];
echo "<br>";
echo Categories.CatName;
echo "<br>";
}
echo "{FAQ.faqID}. {FAQ.faqName} {FAQ.faqText}";
echo "<br>";

}

0
 
dragboatrandyAuthor Commented:
I did change a couple of things, but the basic structure worked like a champ!  Thanks - you're the best!

Here is what the final code looked like:

$category_id = 0;
while($row=mysql_fetch_array($res)) {
      if($category_id != $row['CatID'])
            {
            $category_id = $row['CatID'];
                  echo "<br>";
                  echo $row['CatName'];
                  echo "<br>";
            }
      echo $row['faqID']. $row['faqName']."-".$row['faqText'];
      echo "<br>";
}
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 2
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now