Need random list by category

[Harkster]

  $tpl = new Template;
  $sp_expire = date('Y-m-d H:i:s');
	$query = "
	select * from idx_link 
  left join idx_paid_listing on (idx_link.link_id = idx_paid_listing.link_id) 
	where sponsored = 1 and expire > '$sp_expire'
    order by rand() limit 5";
  $sp_links = $dbConn->FetchArray($query);

is from clock.sponsor and works nicely but not what I want.

Since I am not good with sql I am stuck.

For top level categories only I would like to select rand some number of sponsor, featured or basic. This list needs to consist of only those resources which exist in the subcategories of the top level category.

Since the parent list is basically a ; separated list is this going to have to be a 2 or more step process?

Anyone please?

[FSGDAG]

This one appears to be above my pay grade ( Incase anyone was wondering, pay grade = $0.00 ).

Hopefully pungky or maulana can chime in on this

[dody]

Pungky is working on it now.

[Harkster]

Pungky is working on it now.

Thanks, I think I could get it done but can't figure a good way to deal with that idx_categories_path parents field structure. Can't find an SQL thing for existence of a 'substring'; but then I am an SQL dummy.

[pungky]

Hi, please download this attachment file.

[Harkster]

That was quick. I am having a health issue to work through but can't wait to try it out.

2 small questions please.
1. best way to test for top level category in browse?
2. I thought there was a way to disallow submissions in top categories. Yes/No?

[Harkster]

Works good but I need it to only execute for top level category.

After bumming around here I thought the following would work but...

Added $parent as global in browse.php @ line 24

In browse.html

        <%if $parent == 0%>
          <!-- random links in this category -->
          <%block_view_random_list_by_category category_id="$cat" max_item="10" template="block.view_random_list_by_category.html"%>
		<%/if%>

What am I missing?

[Harkster]

Ok, after spending hours without coming up with how to test for parent_id in the block html I decided to take a brute force approach.

Again, hours trying to find a function for this I finally (after many dead ends/errors) I did this.

In the block php I inserted:

    $children = $category_obj->GetChildren($cat);

// This to find parent - if parent not zero skip everything	
  $query = "select parent_id from idx_category where category_id = ".$cat;
  $res = $dbConn->Execute($query);
  $p = $res->Fields("parent_id");
if ($p == 0) {
// Execute if parent is zero  
    if (is_array($children)) {

Well, it works but I would like to know if anyone has a better/cleaner way.