category search

[slevytam]

Hello,

I have categories n levels deep and am wondering how I can do a search on them.

category table looks like this

ndx - index
text - name of category
mom - parent category ndx

I would like the user to be able to select a category and have the search query search the category and all its children.

I know that this could probably done using a temporary table; however, can it be done without one?

I thought of getting all the ndxs of the children and making a really long query but I think this is probably bad form. Plus my query is really long already.

Thanks for any help offered.

--Shalom

[rrijnders]

Just use recursion:

// Define your search function
function SearchCat($ndx, $search, $results) 
{

  // first, do your actual search using 
  // $search and storing
  // the results in an array or other struct
  // that you passed into this function
  // as $results

  // Then get the children of this node
  // (one level only) by looking for nodes
  // that have this node as a parent:
  $SQL = "SELECT ndx FROM categoryTable where mom = " . $ndx;

  // execute the query
  $recordSet = pg_exec($conn, $SQL);

  // For each child, call this function again!
  while @pg_fetch_object($recordSet, $row) {
    SearchCat($recordSet->ndx, $search, $results);
  }

} // end search function

// Program Main Line

// get starting node somehow,
// let's say, from a form?
$startNode = $_POST['startndx'];

$searchCriteria = $_POST['searchstring'];

// kick off the search
SearchCat($startNode, $searchCriteria, $ResultStruct);

// When your done, $ResultStruct will have 
// all your results (whatever they are)
// do with it what you must

[slevytam]

Thank you for your reply.

Do you think storing results in array is better or worse then using a temp table.

Originally, I thought to create a function to check category. If category is good then just add row to temp table. Then I would display the rows in temp table and then destroy the temp table.

Thanks again,

Shalom

[rrijnders]

It depends on:

a) What the results are
b) How many results you expect, on average?
c) Do you need the results after the script is done executing? For other pages?

If the results are very large strings, documents, or binary files (like images) then you may consider a temp table.

If you expect thousands of results, even if they are small text strings (like file names) then you may consider a temp table.

If you need to have access to the result list from other scripts after the search script is done executing, then a temp table is a good idea.

Keep in mind that DBMS's are typically for persistent data. For this reason, the DBMS will commit your table and each one of it's changes to disk as soon as it can. You may not have control over when this happens. If you have to read data from the disk later in the script, it is much slower than reading from memory (RAM).

I would say if your needs don't really fall into one of the categories listed above, an array or other memory structure should suffice.

[slevytam]

Thanks for your reply.

This gives me something solid to work with. I will do some analysis and go from there.

--SL