How to reference array returned by MySql?

[deko]

This will take a bit of explanation, so please bear with me...

The code below dynamically builds hyperlinks using two queries - query A and
query B. I want to optimize the code so I can omit query B and replace query A
with this (let's call it query C):

$link_cats = $wpdb->get_results("S ELECT wp_linkcategori es.cat_id,
wp_linkcategori es.cat_name, wp_links.link_c ategory FROM wp_linkcategori es INNER
JOIN wp_links ON wp_linkcategori es.cat_id = wp_links.link_c ategory WHERE
wp_links.link_c ategory 1);

The problem with the current code is that it unnecessarily executes query B with
every iteration of a loop (over the items generated by query A).

The solution, I think, is to find a way to reference (in the loop) the elements
returned by query C (rather than executing query B to get the required items).

Here's current code:

<?php

$link_cats = $wpdb->get_results("S ELECT cat_id, cat_name FROM
$wpdb->linkcategori es WHERE cat_id 1"); //query A

foreach ($link_cats as $link_cat)
{
$catid = $link_cat->cat_id; ?>
<h2>&raquo;&nbs p;<?php echo $link_cat->cat_name; ?></h2>
<?php $result = mysql_query("SE LECT link_url, link_descriptio n, link_name
FROM wp_links WHERE link_category = ".$catid); //query B
while ($linkdata = mysql_fetch_arr ay($result))
{
if ($pp)
{
$link = "<a href='".$linkda ta['link_url'];
}
elseif ($rg)
{
$link = "<a href='http://www.example.com/example/example.php";
}
if ($linkdata['link_descripti on'] == '0')
{
$linkdesc = "";
}
else
{
$linkdesc = "#cat".$linkdat a['link_descripti on'];
}
echo "&bull;&nbsp;". $link.$linkdesc ."'>".$linkd ata['link_name']."</a><br
/>";
}
}
?>

Here's pseudo code:

<?php

$link_cats = $wpdb->get_results("S ELECT wp_linkcategori es.cat_id,
wp_linkcategori es.cat_name, wp_links.link_c ategory FROM wp_linkcategori es INNER
JOIN wp_links ON wp_linkcategori es.cat_id = wp_links.link_c ategory WHERE
wp_links.link_c ategory 1); //query C

foreach ($link_cats as $link_cat)
{
$catid = $link_cat->cat_id; ?>
<h2>&raquo;&nbs p;<?php echo $link_cat->cat_name; ?></h2>
<?php $array_C = ??? how to get array of elements (previously retrieved by
query B)???
foreach (???what to loop over???)
{
if ($pp)
{
$link = "<a href='".$array_ C['link_url'];
}
elseif ($rg)
{
$link = "<a href='http://www.example.com/example/example.php";
}
if ($array_C['link_descripti on'] == '0')
{
$linkdesc = "";
}
else
{
$linkdesc = "#cat".$arr ay_C['link_descripti on'];
}
echo "&bull;&nbsp;". $link.$linkdesc ."'>".$array _C['link_name']."</a><br
/>";
}
}
?>

My question is this: How do I create an array of elements (array_C) that
contains only the items I need - that is, only the items returned by query B?

How do I iterate over that array to build the links?

Thanks in advance.

[deko]

Re: How to reference array returned by MySql?

This is close.... but I need to limit the nested foreach somehow... suggestions?

$link_cats = $wpdb->get_results("S ELECT wp_linkcategori es.cat_id,
wp_linkcategori es.cat_name,
wp_links.link_c ategory, wp_links.link_u rl, wp_links.link_d escription,
wp_links.link_n ame
FROM wp_linkcategori es INNER JOIN wp_links ON wp_linkcategori es.cat_id =
wp_links.link_c ategory
WHERE wp_linkcategori es.cat_id != 1");

foreach ($link_cats as $link_cat)
{
$catid = $link_cat->cat_id;
?><h2>&raquo;&n bsp;<?php echo $link_cat->cat_name; ?></h2><?php
foreach ($link_cats as $link_cat)
{
if ($link_cat->cat_id == $catid)
{
if ($this)
{
$link = "<a href='".$link_c at->link_url;
}
elseif ($that)
{
$link = "<a href='http://www.example.com/example/example.php";
}
if ($link_cat->link_descripti on == '0')
{
$linkdesc = "";
}
else
{
$linkdesc = "#cat".$link_ca t->link_descripti on;
}
echo "&bull;&nbsp;". $link.$linkdesc ."'>".$link_ cat->link_name."</a><br />";
}
}
}

[deko]

Re: How to reference array returned by MySql?

actually, it's the first foreach that needs to be limited...

[deko]

Re: How to reference array returned by MySql?

This seems to work:


$linkdata = $wpdb->get_results("S ELECT link_category, link_url,
link_descriptio n, link_name
FROM wp02_links WHERE link_category != 1");

$linkcats = $wpdb->get_results("S ELECT DISTINCT cat_id, cat_name
FROM wp02_linkcatego ries WHERE cat_id != 1");

foreach ($linkcats as $link_cat)
{
?><h2>&raquo;&n bsp;<?php echo $link_cat->cat_name; ?></h2><?php
foreach ($linkdata as $link_datum)
{
if ($link_datum->link_categor y == $link_cat->cat_id)
{
if ($pp)
{
$link = "<a href='".$link_d atum->link_url;
}
elseif ($rg)
{
$link = "<a href='http://www.example.com/example/example.php";
}
if ($link_datum->link_descripti on == '0')
{
$linkdesc = "";
}
else
{
$linkdesc = "#cat".$link_da tum->link_descripti on;
}
echo "&bull;&nbsp;". $link.$linkdesc ."'>".$link_dat um->link_name."</a><br
/>";
}
}
}