Topic: I need help linking information in different data tables...

[Jim R]

This is primarily for WordPress, but I can't get anyone to help me, here (on another board) or elsewhere.  When I click on a Tag on my blog, the user is taken to a Tag Archive page where any Post written tagged with that basketball player's name (most of my tags) shows up. 

That Tag carries with it a value, let's 68, for example, is the term_id, and it carries the label of post_tag.  I'm also using a image plugin which lets me Tag images, and if it uses the same name, it gives it the same term_id but has the label of ngg_tag.  So basically, when you go to the Tag Archive page, you have all the posts which carry that tag, as well as 8 random images (per the query below) that also carry that tag.

Below is a MySQL query that links the images in ngg_pictures data table to my term_ids in another data table:


$mf_query = <<<EOF
    SELECT t.*, tt.*, n.*
    FROM {$wpdb->term_relationships} as tr, {$wpdb->terms} AS t, {$wpdb->term_taxonomy} AS tt, {$wpdb->ngg_pictures} AS n
    WHERE tt.`taxonomy` = 'ngg_tag'

AND tt.`term_id` = t.`term_id`

AND tt.`term_taxonomy_id` = tr.`term_taxonomy_id` 
  

AND tr.`object_id` = n.`pid`

    ORDER BY rand()

LIMIT 8
EOF;

$mf_terms = $wpdb->get_results( $mf_query );

var_dump($mf_terms);

echo '
    <div id="gameCenter">';


    
    foreach ( $mf_terms as $image  ) {

echo '<a href="' . $image->imageURL . '" title="' . $image->pid . '"' .  $image->thumbcode . '>';

echo '<img title="' . $image->alttext . '" alt="' . $image->alttext . '" src="' . $image->thumbnailURL . '"' . $image->size . '/>';

}

echo '</div>';



I put the var_dump in there because it wasn't working.  All the var_dump produces is array(0) { } , which is to say it's not extracting anything.


Here is a sample page:
http://hoosierhoopsreport.com/tag/alexander-hutson/

[Jim R]

Let me provide a little direct insight into my database:

I have a data table with images in it.  ngg_pictures AS n.
Each image has a 'pid', known as n.`pid`.

Each 'pid' corroborates with an object_id in term_relationship AS tr.
....which carries with it a term_taxonomy_id.
Each tr. term_taxonomy_id has an equal in term_taxonomy AS tt.
....tt.term_taxonomy_id also has a term_id within tt.

tt.term_id has two terms, post_tag (WP tag) and ngg_tag (image tag).

I'm trying to pull images that meet all of the criteria with WHERE AND statements.



But from I can tell, it would just organize it internally.  Nothing tells it which ones to pull.  When clicking on a Tag Archive link, I do carry the tag_id, which is a standard WP query.  The 'tag_id' is essentially the term_id.  For another function on the Tag Archive page, I use this:

$wp_tagID = get_query_var('tag_id');

That allows me to link that page to a custom data table where I have a player's information, such as school and in some cases contact information.  I need to associate that with my image query too.   

[fenway]

Sorry, I don't follow.

[Jim R]

Hmmm...

Posts (post_tag) and images (ngg_tag) have the same term_id with post_tag and ngg_tag being their taxonomy. 

Tag Archive pages in WordPress usually print all Posts with the same term_id with the Taxonomy of post_tag.  I'd like to also display images (Taxonomy => ngg_tag). 

To get from term_id to the image file name moves across three different data tables.