Need Help Pairing Database Values

Question

I'm working on a Joomla Based CMS website and I'm currently having some trouble figuring out how to pair my database values. I posted this question yesterday but deleted it because I thought I had this issue resolved, and the question was poorly structured. Hopefully I'll be able to better articulate what I'm trying to accomplish. I'm willing to post this to a dev site for review if necessary (assuming that's allowed here).

With that being said, I am querying four tables. The four tables are items, attachments, tags and then another table that pairs the tag ID's with the relevant item ID's.

You can see these queries below:

        // Get all Items associated with this category page

    // ID of current  category

    $current_cat = $this->category->id;

    // Product IDs array

    $product_ids_array = array();

    // Product Names Array

    $item_names_array = array();

    // Product Descriptions Array

    $item_descriptions_array = array();

    $db = JFactory::getDbo();

    $query = $db->getQuery( true );

    $query->select($db->quoteName(array( 'title', 'introtext', 'id' )));

    $query->from( $db->quoteName( '#__k2_items' ) );

    $query->where( $db->quoteName( 'catid' )." = " .$current_cat );

    $db->setQuery( $query );

    $row = $db->loadObjectList();

    // Store Titles, Descriptions and IDs in arrays

    foreach ($row as $value)
    {
        $item_names_array[] = $value->title;

        $item_descriptions_array[] = $value->introtext;

        $product_ids_array[] = $value->id;

    };

// Now we're going to get the IDs of the tags associated with the items

    // Create comma seperated list of product ids

    $product_ids = implode(',', $product_ids_array);

    // Tag IDs Array

    $tag_IDs_array = array();

    $tag_itemIDs_array = array();

    $db = JFactory::getDbo();

    $query = $db->getQuery( true );

    $query->select($db->quoteName(array( 'tagID', 'itemID' )));

    $query->from( $db->quoteName( '#__k2_tags_xref' ) );

    $query->where($db->quoteName('itemID') . ' IN (' . $product_ids . ' )'  );

    $db->setQuery( $query );

    $row = $db->loadObjectList();

    // Store tag IDs and item IDs

    foreach ($row as $value)
    {
        $tag_IDs_array[] = $value->tagID;

        $tag_itemIDs_array[] = $value->itemID;

    };

// Now we're going to get the names of the tags

    // Create comma seperated list of tag ids

    $tag_IDs = implode(',', $tag_IDs_array );

    // Tag Names Array

    $tag_names_array = array();

    $db = JFactory::getDbo();

    $query = $db->getQuery( true );

    $query->select($db->quoteName(array( 'name' )));

    $query->from( $db->quoteName( '#__k2_tags' ) );

    $query->where($db->quoteName('id') . ' IN (' . $tag_IDs . ' )'  );

    $db->setQuery( $query );

    $row = $db->loadObjectList();

    // Store tag names

    foreach ($row as $value)
    {

        $tag_names_array[] = $value->name;

    };

    // Now we're going to get the attachments

    // Attachments Arrays

    $attachment_itemID_array = array();

    $attachment_id_array = array();

    $attachment_filename_array = array();

    $attachment_title_array = array();

    $attachment_title_attr_array = array();

    $db = JFactory::getDbo();

    $query = $db->getQuery( true );

    $query->select($db->quoteName(array( 'id', 'itemID', 'filename', 'title', 'titleAttribute' )));

    $query->from( $db->quoteName( '#__k2_attachments' ) );

    $query->where($db->quoteName('itemID') . ' IN (' . $product_ids . ' )'  );

    $db->setQuery( $query );

    $row = $db->loadObjectList();

    // Store tag names

    foreach ($row as $value)
    {

        $attachment_itemID_array[] = $value->itemID;

        $attachment_id_array[] = $value->id;

        $attachment_filename_array[] = $value->filename;

        $attachment_title_array[] = $value->title;

        $attachment_title_attr_array[] = $value->titleAttribute;

    };

        $db = JFactory::getDbo();

    $query = $db->getQuery( true );

    $query->select($db->quoteName(array( 'title', 'introtext', 'id' )));

    $query->from( $db->quoteName( '#__k2_items' ) );

    $query->where($db->quoteName('alias') . ' LIKE '. $db->quote('acid-%') . ' AND ' . $db->quoteName('catid'). ' = ' . $current_cat . ' AND published = 1'  );

    $db->setQuery( $query );

    $row = $db->loadObjectList();

    foreach ($row as $value) {

        $a = 0;

        $a++;

        echo $value->title . '<br/>';

        echo $value->introtext . '<br/>';

        echo $value->id . '<br/>';

    }

As you can see the final block of code is the final query to the database in which I do a SQL Select with a wildcard statement. I want that to be dynamic.

The idea behind making it dynamic is that the items have tags that match the their name prefix. An example is I have a tag called 'Acid Corrosion Inhibitors' and I have a product who has an alias of 'acid-corrosion-inhibitors-aci-136'. The alias being something I can retrieve from the the DB.

Anyway, the issue is not retrieving the data. The issue is once I have the data how and the hell can I intelligently pair the data together? As you can see from my queries in each instance I am pulling the itemID so I have something to compare but from that point on and I'm pretty lost.

I'm essentially trying to match items with their associated tag (each item will only have one corresponding tag) as well as their associated attachment.

I hope this is clear, if it is not I will check back to further clarify and answer any questions. Any help would be much appreciated.

Thanks.

Update #1

I'm wondering if in_array() is a good place to start. At least I can check to see if the itemID exist. Something along the lines of this:

       if( in_array( $value->id, $attachment_itemID_array ) ) {

            echo 'match';

        }

I'm not sure if that is a bullet proof way to make sure the right attachment is linked to the right item, etc. etc. I would imagine it's not but hopefully it's a start.

Update #2

Starting to think I'd be better off (in some instances) just having an associative array when returning values for a query. I'm thinking it'll make it easier to make sure things are matched up correctly.

Update #3

Hope someone finds this useful. I'm attempting to access this monster:

Array ( [0] => stdClass Object ( [id] => 2 [itemID] => 5 [filename] => Flotek-logo_1.png [title] => Flotek-logo_1.png [titleAttribute] => Flotek-logo_1.png ) [1] => stdClass Object ( [id] => 3 [itemID] => 6 [filename] => Logo_Court_Guru_Gray.png [title] => Logo_Court_Guru_Gray.png [titleAttribute] => Logo_Court_Guru_Gray.png ) [2] => stdClass Object ( [id] => 5 [itemID] => 4 [filename] => Icon_Search.png [title] => Icon_Search.png [titleAttribute] => Icon_Search.png ) ) 

So I can use it to check that the current item ID matches the itemID in the array but I'm getting an index undefined error when using something along the lines of $array['itemID']; it's clear I'm approaching this in the wrong way. Hoping to find some answers, I've found several but not any that make much sense to or in some cases completely different scenarios.

Ok, looks like:

$attachmentRow[1]->itemID

Is the answer. Now I'm wondering if there is a better way to access it seeing that I'll have no way of knowing index of the Object. I'd rather just comb over every object in the associative array, at least.

Update #4

Figured how to simply the array somewhat using json_decode

$attachmentResult = json_decode(json_encode($attachmentRow),true);

Which results in

Array ( [0] => Array ( [id] => 2 [itemID] => 5 [filename] => Flotek-logo_1.png [title] => Flotek-logo_1.png [titleAttribute] => Flotek-logo_1.png ) [1] => Array ( [id] => 3 [itemID] => 6 [filename] => Logo_Court_Guru_Gray.png [title] => Logo_Court_Guru_Gray.png [titleAttribute] => Logo_Court_Guru_Gray.png ) [2] => Array ( [id] => 5 [itemID] => 4 [filename] => Icon_Search.png [title] => Icon_Search.png [titleAttribute] => Icon_Search.png ) ) 

Still getting an indexed undefined error when using something a long the lines of

 echo $attachmentResult['itemID'];

Still trying to wrap my head around properly accessing an associative array.

Never mind. This is the correct way.

echo $attachmentResult[0]['itemID'];

UPDATE #5

All right, I think I just about got it. I found a very helpful solution that can be found here: How to search by key=>value in a multidimensional array in PHP - very handy for searching multidimensional arrays

Updated Query for looping through the items/products

        $db = JFactory::getDbo();

    $query = $db->getQuery( true );

    $query->select($db->quoteName(array( 'title', 'introtext', 'id' )));

    $query->from( $db->quoteName( '#__k2_items' ) );

    $query->where($db->quoteName('alias') . ' LIKE '. $db->quote('acid-%') . ' AND ' . $db->quoteName('catid'). ' = ' . $current_cat . ' AND published = 1'  );

    $db->setQuery( $query );

    $row = $db->loadObjectList();

    foreach ($row as $value) {

        $itemID = $value->id;

        $search_result = (search($attachmentResult, 'itemID', $itemID));

        if($search_result) {

            $db = JFactory::getDbo();

            $query = $db->getQuery( true );

            $query->select($db->quoteName(array( 'filename' )));

            $query->from( $db->quoteName( '#__k2_attachments' ) );

            $query->where( $db->quoteName( 'itemID' )." = " .$itemID );

            $db->setQuery( $query );

            $attachmentRow = $db->loadObjectList();

             foreach ($attachmentRow as $attachmentValue) {

                 echo $attachmentValue->filename;

             }

        }

        echo $value->introtext . '<br/>';

    }

Answer 1

Ok, I got it working. I'm not sure if it's the most elegant solution. If someone wants to chime in with improvements I'm all ears. Hopefully this will help someone who has a similar problem in the future.

My final item/product loop query:

    // Get all Items associated with this category page

    // ID of current  category

    $current_cat = $this->category->id;

    // Product IDs array

    $product_ids_array = array();

    $db = JFactory::getDbo();

    $query = $db->getQuery( true );

    $query->select($db->quoteName(array('id' )));

    $query->from( $db->quoteName( '#__k2_items' ) );

    $query->where( $db->quoteName( 'catid' )." = " .$current_cat . ' AND published = 1' );

    $db->setQuery( $query );

    $row = $db->loadObjectList();

    // Store Titles, Descriptions and IDs in arrays

    foreach ($row as $value)
    {
        $product_ids_array[] = $value->id;

    };

    // Now we're going to get the IDs of the tags associated with the items

    // Create comma seperated list of product ids

    $product_ids = implode(',', $product_ids_array);

    // Tag IDs Array

    $tag_IDs_array = array();

    $db = JFactory::getDbo();

    $query = $db->getQuery( true );

    $query->select($db->quoteName(array( 'tagID', 'itemID' )));

    $query->from( $db->quoteName( '#__k2_tags_xref' ) );

    $query->where($db->quoteName('itemID') . ' IN (' . $product_ids . ' )'  );

    $db->setQuery( $query );

    $row = $db->loadObjectList();

    $tagsRow = $db->loadObjectList();

    $tagsResult = array();

    $tagsResult = json_decode(json_encode($tagsRow),true);

    // Store tag IDs and item IDs

    foreach ($row as $value)
    {
        $tag_IDs_array[] = $value->tagID;     
    };

    // Now we're going to get the names of the tags

    // Create comma seperated list of tag ids

    $tag_IDs = implode(',', $tag_IDs_array );

    // Tag Names Array

    $tag_names_array = array();

    $db = JFactory::getDbo();

    $query = $db->getQuery( true );

    $query->select($db->quoteName(array( 'name' )));

    $query->from( $db->quoteName( '#__k2_tags' ) );

    $query->where($db->quoteName('id') . ' IN (' . $tag_IDs . ' )'  );

    $db->setQuery( $query );

    $row = $db->loadObjectList();

    // Store tag names

    foreach ($row as $value)
    {

        $tag_names_array[] = $value->name;

    };

    // Now we're going to get the attachments

    // Attachments Arrays

    $db = JFactory::getDbo();

    $query = $db->getQuery( true );

    $query->select($db->quoteName(array( 'id', 'itemID', 'filename', 'title', 'titleAttribute' )));

    $query->from( $db->quoteName( '#__k2_attachments' ) );

    $query->where($db->quoteName('itemID') . ' IN (' . $product_ids . ' )'  );

    $db->setQuery( $query );

    $attachmentRow = $db->loadObjectList();

    $attachmentResult = array();

    $attachmentResult = json_decode(json_encode($attachmentRow),true); 

    // Function to search multidimensional arrays
    function search($array, $key, $value)
    {
        $results = array();

        if (is_array($array)) {
            if (isset($array[$key]) && $array[$key] == $value) {
                $results[] = $array;
            }

            foreach ($array as $subarray) {
                $results = array_merge($results, search($subarray, $key, $value));
            }
        }

        return $results;
    }


    // Now we're going to create our product loop


    // Get Tag Names

    foreach( $tag_names_array as $display_tag_name ) {

        // Unformatted Tag Name - this is the one that will be displayed on the front end

        $unformatted_display_tag_name = $display_tag_name;

        // Convert Tag Name White Spaces to Dashes

        $display_tag_name = preg_replace("/[\s_]/", "-", $display_tag_name);

        // Lower Case Tag Name

        $display_tag_name = strtolower($display_tag_name);

        switch ( $display_tag_name == $display_tag_name ) {

            case $display_tag_name: 

            $db = JFactory::getDbo();

            $query = $db->getQuery( true );

            $query->select($db->quoteName(array( 'title', 'introtext', 'id' )));

            $query->from( $db->quoteName( '#__k2_items' ) );

            $query->where($db->quoteName('alias') . ' LIKE '. $db->quote($display_tag_name.'-%') . ' AND ' . $db->quoteName('catid'). ' = ' . $current_cat . ' AND published = 1'  );

            $db->setQuery( $query );

            $row = $db->loadObjectList();

            // Start Row

            echo '<div class="row">';

            // Start 12 Column

            echo '<div class="col-lg-12">';

            // Start Row

            echo '<div class="row">';

            // Start Item Container

            echo '<section class="item-container">';

            // Display Tag Name 

            echo '<div class="col-lg-12"><section class="tag-name"><a href="#">' . $unformatted_display_tag_name . '</a></section></div>';


            foreach ($row as $value) {

                // Start Column 6

                echo '<div class="col-lg-6 is-hidden">';

                // Store ID of item

                $itemID = $value->id;

                // Search attachmentResult array

                $attachment_search_result = (search($attachmentResult, 'itemID', $itemID));

                // Check to see if there are any associated attachments - display attachment is present

                if($attachment_search_result) {

                    $db = JFactory::getDbo();

                    $query = $db->getQuery( true );

                    $query->select($db->quoteName(array( 'filename' )));

                    $query->from( $db->quoteName( '#__k2_attachments' ) );

                    $query->where( $db->quoteName( 'itemID' )." = " .$itemID );

                    $db->setQuery( $query );

                    $attachmentRow = $db->loadObjectList();

                     foreach ($attachmentRow as $attachmentValue) {

                         echo $attachmentValue->filename . '<br/>';

                     }

                }

                // Display Item Title

                echo '<h5>' .$value->title. '</h5>';

                // Display Item Text

                echo '<p>' .$value->introtext. '</p>';

                // End Column 6

                echo '</div>';

            }

            // Close Item Container

            echo '</section>';

            // Close Row

            echo '</div>';

            // Close 12 Column

            echo '</div>';

            // Close Row

            echo '</div>';

        }

    }

?>
<!-- /Display Category Items -->