Multiple category MOD

[esm]

a true multiple category MOD (to replace the current limit of 2 addtional categories), could either be real easy or impossible. At least impossible the way I did it.

I created a multiple category MOD for the free version of INDEXU 5 years ago next month (May 2003). By the end of the year, I had created a multiple category MOD for v3.2 and included it in my v3.2E enhanced version which many folks used.

Over 4 years ago I created a simple clone of INDEXU 5 called links_coder. For the most part, it does what I need: banners, multiple categories and search engine friendly urls in addtion to the standard, new, hot, editor picks, etc.

I say all that to say that the multiple categories MOD actually turned out to be pretty easy. The hard part was the SQL statement.

Here is the actual code from my browse.php page.

$query="SELECT
link_id, category_id, url, title, description, date, hits, url_name, thumb, pick, new
FROM idx_link 
LEFT JOIN idx_mcats  
ON link_id = mlink_id
WHERE category_id=$cat_id  or mcat_id=$cat_id
ORDER BY $order_by";

I did say easy or impossible. Here is the same SQL statement from v5.4.

select idx_link.*, if (isnull(premium) || expire < now() || paid != 1, 0, 
premium) premium from idx_link left join idx_paid_listing on (idx_link.link_id =
idx_paid_listing.link_id) left join idx_pagerank on
(idx_link.link_id = idx_pagerank.link_id) where (category_id = '1' or cat1 = '1'
or cat2 = '1') and suspended = 0 and idx_link.link_id <> '5' order by 
premium desc, bid desc

This is an hardcoded version (notice the idx_link.link_id <> '5' toward the end)

I can do simple SQL statements and some not-so-simple statements. But the statement above already has a left join and adding another may be impossible or easy for someone who is a SQL expert.

So here is the deal. If you know someone who can incorporate the gist of my statement into the INDEXU statement, I'm willing to see if I can make a true multiple category MOD for INDEXU DELUXE v6.


.

[inspireme]

So here is the deal. If you know someone who can incorporate the gist of my statement into the INDEXU statement, I'm willing to see if I can make a true multiple category MOD for INDEXU DELUXE v6.


.

woudlnt this work?

select idx_link.*, if (isnull(premium) || expire < now() || paid != 1, 0, 
premium) premium from idx_link 
left join idx_paid_listing on (idx_link.link_id =
idx_paid_listing.link_id) left join idx_pagerank on
(idx_link.link_id = idx_pagerank.link_id) where (category_id = '1' or cat1 = '1'
or cat2 = '1') and suspended = 0 and idx_link.link_id <> '5'
LEFT JOIN idx_mcats  ON link_id = mlink_id WHERE category_id=$cat_id  or mcat_id=$cat_id
order by premium desc, bid desc

note the table names though, you will probably have to do this :
LEFT JOIN idx_mcats ON idx_link.link_id = mlink_id WHERE idx_mcats.category_id=$cat_id or idx_mcats.mcat_id=$cat_id

But im not sure which table contains which field though

[esm]

i'll give it a shot. after I get some sleep...


.

[esm]

woudlnt this work?

select idx_link.*, if (isnull(premium) || expire < now() || paid != 1, 0, 
premium) premium from idx_link 
left join idx_paid_listing on (idx_link.link_id =
idx_paid_listing.link_id) left join idx_pagerank on
(idx_link.link_id = idx_pagerank.link_id) where (category_id = '1' or cat1 = '1'
or cat2 = '1') and suspended = 0 and idx_link.link_id <> '5'
LEFT JOIN idx_mcats  ON link_id = mlink_id WHERE category_id=$cat_id 
or mcat_id=$cat_id order by premium desc, bid desc

well, the above did not work. Like I said, it was a very complex SQL statement to begin with.

After I removed all the stuff that was there and replaced it with mine, my multiple category MOD works just fine.

If you want to take a look it, send me a PM.

here is the SQL statement that works:

    $links_obj->query = "
      select idx_link.*
      from idx_link

    LEFT JOIN idx_mcats ON link_id = mlink_id

      where (category_id = '$cat' or mcat_id='$cat')
      and suspended = 0"; 

Folks are going to be sick at how easy this really is when they see it. (well, except for the SQL statement issue).

1. create the a two column SQL table (link_id and category_id)
2. integrate the mcats SQL code (see below) into the browse.php file
3. generate the category dropdown in the add.php file
4. add the multiple select html to the add_form.html file

Hey, there may be more but this is probably about 98% of it.

Now if dody will just take this and run with it....!!!



.

[inspireme]

select idx_link.*, if (isnull(premium) || expire < now() || paid != 1, 0, 
premium) premium from idx_link 

left join idx_paid_listing on (idx_link.link_id =
idx_paid_listing.link_id) 

left join idx_pagerank on (idx_link.link_id = idx_pagerank.link_id) 
where (category_id = '1' or cat1 = '1' or cat2 = '1') and suspended = 0 and idx_link.link_id <> '5'

LEFT JOIN idx_mcats  ON (idx_link.link_id = idx_mcats.mlink_id WHERE idx_link.category_id=$cat_id  or idx_link.mcat_id=$cat_id)

order by premium desc, bid desc

what error does the above come up with?

[esm]

well, the above did not work BUT....!!!

The following does work...!!!

query = "

select
idx_link.*,
if (isnull(premium) || expire < now() || paid != 1, 0, premium) premium
from idx_link
left join idx_paid_listing on (idx_link.link_id = idx_paid_listing.link_id)

left join idx_pagerank on (idx_link.link_id = idx_pagerank.link_id)
LEFT JOIN idx_mcats ON (idx_link.link_id = mlink_id)

where (category_id = '1' or mcat_id=1) and suspended = 0 and idx_link.link_id <> '5'

";

well, with two exceptions. First, if the link is a sponsored link, it shows up only in the category that it sponsors. It does not show up as multiple category link. For example, see idx_link.link_id <> '5' on the last line above. link_id 5 is set as a sponsor link.

Second, if the multiple category link is the only listing on a subsequent page, you will get an error message.

But more testing is needed.


.

[esm]

If any one wants to test the multiple category MOD, the first thing you need to do is to create the following table.

CREATE TABLE idx_mcats (
  mlink_id mediumint(9) unsigned DEFAULT '0'  ,
  mcat_id mediumint(9) unsigned   
);

Populate the idx_mcats table. Enter the link_id and the additional category that you would like the link to appear in. (Later on, doing this thru the add.php and add_form.html files will be an easy process.)

Next, edit the browse.php file. Look for the following code about line 85:

     $links_obj = new clsLink;
    $links_obj->query = "
      select idx_link.*,
             if (isnull(premium) || expire < now() || paid != 1, 0, premium) premium
      from idx_link
           left join idx_paid_listing on (idx_link.link_id = idx_paid_listing.link_id)
           left join idx_pagerank on (idx_link.link_id = idx_pagerank.link_id)
      where (category_id = '$cat' or cat1 = '$cat' or cat2 = '$cat')
      and suspended = 0";
    } 

remove the above code and replace it with the following:

    $links_obj = new clsLink;
    $links_obj->query = "
select 
idx_link.*, 
if (isnull(premium) || expire < now() || paid != 1, 0, premium) premium 
from idx_link 
left join idx_paid_listing on (idx_link.link_id = idx_paid_listing.link_id) 

left join idx_pagerank on (idx_link.link_id = idx_pagerank.link_id)
LEFT JOIN idx_mcats ON (idx_link.link_id = mlink_id)

where (category_id = '$cat' or mcat_id='$cat')  and suspended = 0

"; 

.

[esm]

well, the only isssue I am seeing with this MOD (but I haven't looked for others ) is that it is not allowing Sponsored links to show up in the additional categories.

But I'm pretty sure that I can find a way to at least include them, either at the beginning or end of the listing. But it is solvable.

But I would be intereted in any other comments...


.

[Mitchell]

Later on, doing this thru the add.php and add_form.html files will be an easy process

Would you please kindly tel us which part of add.php do you modify? I love to test it out.

Cheers
Mitchell

[FSGDAG]

I'm having a "Miss Cleo" moment I can see ESM winning the Mod of the Month contest!!!

[Mitchell]

I can see ESM winning the Mod of the Month contest!!!

Perhaps Mod of the year contest, Dody was saying that it would be for the version 7.

[esm]

Would you please kindly tel us which part of add.php do you modify? I love to test it out.

for the moment, I'm just testing the browse.php file. I have done anything with the add.php or add_form.html files but in the add.php file you'll need to add a dropdown variable for the categories with a multiple select option. I'm not sure if the current INDEXU code allows that. If not, maybe cloning it. Then add it to the displaytemplate line in the ShowFormAddUrl function. In the add_form.html file, add the placeholder variable, e.g. <%$mult_cats%>.

Now for the hard part. you are going to have to add the mcats data from the form to the mcats table. Probably the section that deals with the add results (about line 272). Since I don't use INDEXU, I'm going to have to study the logic and ask some questions. I know that links can be automatically added, added to the temp file for later approval and there is the issue of paid links (that have either been paid or are pending payment).

Or, it may be that all of this is dealt with the add function in the link.class.php file in the lib folder or the validate_link.php file in the admin folder.

time will tell.



.

[esm]

I'm having a "Miss Cleo" moment I can see ESM winning the Mod of the Month contest!!!

well, there are some behind the scenes coding yet to do. I'm off to Tennessee and then Florida next week so it will be at least next month before I've finished this one.


.

[esm]

well, the only isssue I am seeing with this MOD is that it is not allowing Sponsored links to show up in the additional categories.

YEAHHHH! SOLVED. That was a little toughie to solve. The MOD works great now. I have almost finished modifying a new DisplayCategoryListBox to allow for multiple selections on the add_form.html and the modify_form.html. and I probably need to do the same for the admin side.

Get out of here cat1 and cat1...!!! Multiple Categories are in the house....!!!!


.

[Mitchell]

Hi ESM,

Any chance of releasing the codes?

Cheers
Mitchell