Posted by deko on 08/10/06 21:36

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("SELECT wp_linkcategories.cat_id,
wp_linkcategories.cat_name, wp_links.link_category FROM wp_linkcategories INNER
JOIN wp_links ON wp_linkcategories.cat_id = wp_links.link_category WHERE
wp_links.link_category > 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("SELECT cat_id, cat_name FROM
$wpdb->linkcategories WHERE cat_id > 1"); //query A

foreach ($link_cats as $link_cat)
{
$catid = $link_cat->cat_id; ?>
<h2>&raquo;&nbsp;<?php echo $link_cat->cat_name; ?></h2>
<?php $result = mysql_query("SELECT link_url, link_description, link_name
FROM wp_links WHERE link_category = ".$catid); //query B
while ($linkdata = mysql_fetch_array($result))
{
if ($pp)
{
$link = "<a href='".$linkdata['link_url'];
}
elseif ($rg)
{
$link = "<a href='http://www.example.com/example/example.php";
}
if ($linkdata['link_description'] == '0')
{
$linkdesc = "";
}
else
{
$linkdesc = "#cat".$linkdata['link_description'];
}
echo "&bull;&nbsp;".$link.$linkdesc."'>".$linkdata['link_name']."</a><br
/>";
}
}
?>

Here's pseudo code:

<?php

$link_cats = $wpdb->get_results("SELECT wp_linkcategories.cat_id,
wp_linkcategories.cat_name, wp_links.link_category FROM wp_linkcategories INNER
JOIN wp_links ON wp_linkcategories.cat_id = wp_links.link_category WHERE
wp_links.link_category > 1); //query C

foreach ($link_cats as $link_cat)
{
$catid = $link_cat->cat_id; ?>
<h2>&raquo;&nbsp;<?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_description'] == '0')
{
$linkdesc = "";
}
else
{
$linkdesc = "#cat".$array_C['link_description'];
}
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.

• Next in forum: Re: how do i avoid typing the keyword "$this->" every time inside a class
• Prev in forum: Help to automatically traverse a login session
• Thread view: How to reference array returned by MySql?

[Reply to this message]