[SOLVED] Tag Cloud - two-tables problem

[EternalSorrow]

I've been working on a tag cloud code, but I've ran into a problem with the setup of my tables versus the code itself.

 

I have two tables the tag must query:

The catjoin table holds category_id and entry_id.  It is used to show the COUNT for instances of category use per entry_id.

The category table holds category_id and the actual category_name.

 

I've found that when joining the two tables, the category_name from category, which is the second table, isn't queried; only an empty space appears where the $category_name should be.  I've tried using variations in the SELECT area and WHILE area, but either it doesn't work or I receive an error message concerning the ARRAYs.

 

Here's the code I use:

<?php
mysql_connect(localhost,user,pw);
@mysql_select_db(db) or die( "Unable to select database");

$query = "SELECT category_id AS tag, COUNT(entry_id) AS quantity
FROM catjoin 
JOIN category USING (category_id)
GROUP BY category_id
ORDER BY category_id ASC";

$result = mysql_query($query);

while ($row = mysql_fetch_array($result)) {
    $tags[$row['tag']] = $row['quantity'];
}

$max_size = 250; // max font size in %
$min_size = 100; // min font size in %

$max_qty = max(array_values($tags));
$min_qty = min(array_values($tags));

$spread = $max_qty - $min_qty;
if (0 == $spread) { // we don't want to divide by zero
    $spread = 1;
}

$step = ($max_size - $min_size)/($spread);

foreach ($tags as $key => $value) {

    $size = $min_size + (($value - $min_qty) * $step);

    echo '<a href="categories.php?category_name='.$category_name.'" style="font-size: '.$size.'%"';
    echo ' title="'.$value.' article(s) tagged with '.$key.'"';
    echo '>'.$category_name.'</a> ';

}
?>

 

I'd be most grateful if anyone could spot the (probably simple) error.  If the action cannot be performed with two tables, would it be recommended if I simply added the category_name field to the catjoin table?  Thanks in advance.

[luminous]

It looks to me as if you haven't defined your $catagory_name variable anywhere so the contents of it is NULL and therefor echoing out nothing. Try replacing all instances of $catagory_name with $tags.

[EternalSorrow]

  Quote

It looks to me as if you haven't defined your $catagory_name variable anywhere so the contents of it is NULL and therefor echoing out nothing. Try replacing all instances of $catagory_name with $tags.

 

I wish it were as simple as that.  Replacing the instances changes the category_name field into the word Array, so that all categories are now called Array.

 

However, I've managed to get a category_name to appear by adding category_name to the SELECT statement and adding it as a row variable in the WHILE.  Unfortunately there appears to be no relationship between the category_name and its ID, so the first category is being repeated for all categories.  Any ideas how to set up a relationship with the category_name and its corresponding ID?

 

Here's the changed code:

<?php
mysql_connect(localhost,user,pw);
@mysql_select_db(db) or die( "Unable to select database");

$query = "SELECT category_id AS tag, COUNT(entry_id) AS quantity, category_name
FROM catjoin 
JOIN category USING (category_id)
GROUP BY category_id
ORDER BY category_id ASC";

$result = mysql_query($query) or die(mysql_error());

while ($row = mysql_fetch_array($result)) {
    $tags[$row['tag']] = $row['quantity'];
    $category_name = $row['category_name'];
}

$max_size = 250; // max font size in %
$min_size = 100; // min font size in %

$max_qty = max(array_values($tags));
$min_qty = min(array_values($tags));

$spread = $max_qty - $min_qty;
if (0 == $spread) { // we don't want to divide by zero
    $spread = 1;
}

$step = ($max_size - $min_size)/($spread);

foreach ($tags as $key => $value) {

    $size = $min_size + (($value - $min_qty) * $step);

    echo '<a href="categories.php?category_name='.$category_name.'" style="font-size: '.$size.'%"';
    echo ' title="'.$value.' article(s) tagged with '.$category_name.'"';
    echo '>'.$category_name.'</a> ';

}
?>

[EternalSorrow]

Another update to the code as I play with options.  I've managed to relate the category_name to the category_id, but when the category names are viewed only the first letter of each category_name appears in the list.  I'm guessing somehow the name is being limited by the ID, but I don't know how to avoid or solve this problem.

 

Here's the modified code:

<?php
mysql_connect(localhost,user,pw);
@mysql_select_db(db) or die( "Unable to select database");

$query = "SELECT category_id AS tag, COUNT(entry_id) AS quantity, category_name
FROM catjoin
JOIN category USING (category_id)
GROUP BY category_id
ORDER BY category_name ASC";

$result = mysql_query($query) or die(mysql_error());

while ($row = mysql_fetch_array($result)) {
    $tags[$row['tag']] = $row['quantity'];
    $category_name[$row['tag']] = $row['category_name'];
}

$max_size = 250; // max font size in %
$min_size = 100; // min font size in %

$max_qty = max(array_values($tags));
$min_qty = min(array_values($tags));

$spread = $max_qty - $min_qty;
if (0 == $spread) { // we don't want to divide by zero
    $spread = 1;
}

$step = ($max_size - $min_size)/($spread);

foreach ($tags as $key => $value) {

    $size = $min_size + (($value - $min_qty) * $step);

    echo '<a href="categories.php?category_name='.$category_name[$key].'" style="font-size: '.$size.'%"';
    echo ' title="'.$value.' article(s) tagged with '.$category_name[$key].'"';
    echo '>'.$category_name[$key].'</a> ';

}
?>