pulling data from multiple categories at the same time

Dev Articles Community Forums Sponsor:

August 14th, 2003, 12:27 AM

Decoy

Registered User

Join Date: Aug 2002

Posts: 28

Time spent in forums: < 1 sec
Reputation Power: 0

pulling data from multiple categories at the same time

Hi,

I've put together two functions that pull a list of categories from a db which are hyperlinked to all of the links that correspond to that particular category. And two almost identical functions that display the links. What I would like to do is to have the category names echoed out with all of it's respective links automatically under it as opposed to clicking the category to see them. They are called like so...

PHP Code:


		
			
 $cat_array = get_categories();

display_categories($cat_array);



function get_categories()

{

 $query = "select catid, cat_name

            from link_cat"; 

  $result = @mysql_query($query);

  if(!$result)

    return false;

  $num_cats = @mysql_num_rows($result);

  if($num_cats ==0)

     return false;  

  $result = db_result_to_array($result);

  return $result; 

}

function display_categories($cat_array)

{

  if(!is_array($cat_array))

  {

     echo '<br>No categories currently available<br>';

     return;

  }

  foreach($cat_array as $row)

  {

    //$url = 'subpages/show_cat.php?catid=' .($row['catid']). ' target=content';

    $url = 'test_show_cat.php?catid=' .($row['catid']);    

    $title = $row["cat_name"]; 

    //do_html_url($url, $title);

    linkBox($url, $title, $link);    

  }

}

Here is the code to display the links

PHP Code:


		
			
 $link_array = getLinks($_REQUEST['catid']);

displayLinks($link_array);



function getLinks($catid)

{ 

  if(!$catid || $catid=="")

    return false;

  $query = "select * from links where catid='$catid'";

  $result = @mysql_query($query);

  if(!$result)

    return false;

  $num_links = @mysql_num_rows($result);

  if($num_links == 0)

     return false;

  $result = db_result_to_array($result);

  return $result;

}

function displayLinks($links)

{

  if(!is_array($links))

  {

    echo '<br>No links currently available in this category<br>';

  }

  else

  {

    foreach($links as $row)

    {

      $url = 'show_book.php?linkid=' .($row["linkid"]). ' target=content';

      $title = $row["name"];

      do_html_url($url, $title);

    }

  }

}

The problem is having to pass the 'catid' via the hyperlink. I can't think of a way to have them automatically displayed when the page is loaded. Anyone have any suggestions?

Thanks

August 14th, 2003, 08:08 AM

dhouston

Contributing User

Dev Articles Beginner (1000 - 1499 posts)

Join Date: May 2003

Location: Tennessee

Posts: 1,355

Time spent in forums: < 1 sec
Reputation Power: 7

I've done something similar with the following logic (pseudo-code):

Code:


SELECT c.cat_id, c.cat_name, l.link, l.name FROM link_cat as c, links as l WHERE l.cat_id=c.cat_id ORDER BY c.cat_name ASC, l.name ASC

$cat="";

while($row=mysql_fetch_row()){
    if($row["cat_name"]!=$cat){
        $cat=$row["cat_name"];
        print $cat . "<br>";
    }
    print $row["link"]; //formatted to your liking
}

Basically, you're joining the cat and the link tables and ordering first by category. Then as you loop through the results, you check a variable to see if it equals the current category name. If not, a new category has been reached and you should print it. Else just print the link. I think that'll accomplish what you were wanting, with much less code. Hope I've understood you correctly.

August 14th, 2003, 01:13 PM

Decoy

Registered User

Join Date: Aug 2002

Posts: 28

Time spent in forums: < 1 sec
Reputation Power: 0

It seems so painfully obvious now. I tweaked it a bit to fit my needs and it's perfect.

Thank you

Rob

August 15th, 2003, 01:26 AM

Decoy

Registered User

Join Date: Aug 2002

Posts: 28

Time spent in forums: < 1 sec
Reputation Power: 0

I am using this code and it is working okay until a point. Odd things are happening. I currently have three categories and each one has a number of links associated with them. I have added a form to add categories, as well as the links that corresepond to them. This works fine. They show up like so...

CATEGORY_1
link
link

CATEGORY_2
link
link
link

CATEGORY_3
link
link
link
link
link

The weird thing is when I add more than 2 links to the first category, or 3 links to the second category, the category name and the link I add are added to the end of the list. Like so...

CATEGORY_1
link
link

CATEGORY_2
link
link
link

CATEGORY_3
link
link
link
link
link

CATEGORY_1 <-- category 1 already exists!
link <-- new link I just added

If I were to add a fourth link under CATEGORY_2 it would repeat itself at the bottom as well. The last category doesn't seem to be affected.

The two tables involved are set up like so...
LINK_CAT
catid
cat_name

LINKS
linkid
catid
name
link_url

Code below (provided by dhouston)...

PHP Code:


		
			
 $query = "SELECT link_cat.catid, link_cat.cat_name, links.name, links.link_url FROM link_cat, links

WHERE links.catid = link_cat.catid"; 

$result = @mysql_query($query);



$cat='';

while($row = mysql_fetch_array($result))

{

  if($row["cat_name"] != $cat)

  {

    $cat = $row["cat_name"];

    echo '<p><b><u>' . strtoupper($cat) . '</u></b><br>';

  }

  $link = $row["name"];

  $address = $row["link_url"]; 

  do_html_url($address, $link);

}

Thanks for any help.

August 15th, 2003, 07:49 AM

dhouston

Contributing User

Join Date: May 2003

Location: Tennessee

Posts: 1,355

Time spent in forums: < 1 sec
Reputation Power: 7

It's the lack of an ORDER BY clause in your query. You have to order by the category in order for this to work; else mysql spits out results in database order, with the most recent entries appearing last. The category names show up again because by the time you get to the end of the list, $cat no longer equals CATEGORY_1 (it equals CATEGORY_3) and so it reprints as if it's reached a new category. Check out the ORDER BY clause in my original posting and see if that solves the problem for you.

August 15th, 2003, 02:11 PM

Decoy

Registered User

Join Date: Aug 2002

Posts: 28

Time spent in forums: < 1 sec
Reputation Power: 0

Hey dhouston,

Got it. Thanks for your time. Appreciate it.

Rob

Thread Tools	Search this Thread
Email this Page	Search this Thread: Advanced Search
Display Modes	Rate This Thread
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode	Rate This Thread: