需要帮助配对数据库值 [英] Need Help Pairing Database Values

问题描述

我正在基于Joomla的CMS网站上工作，目前在确定如何配对数据库值时遇到一些麻烦.我昨天发布了这个问题，但是删除了它，因为我认为我已经解决了这个问题，并且该问题的结构还很糟糕.希望我能够更好地阐明我要完成的工作.如有必要，我愿意将其发布到开发站点进行审查(假设此处允许).

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).

话虽如此，我要查询四个表.这四个表分别是项目，附件，标签，然后是将标签ID与相关项目ID配对的另一个表.

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/>';

    }

如您所见，最后的代码块是对数据库的最终查询，在该数据库中，我使用通配符语句执行SQL Select.我希望它是动态的.

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.

使其具有动态性的想法是，商品具有与其名称前缀匹配的标签.例如，我有一个名为酸性腐蚀抑制剂"的标签，而我的产品的别名为"acid-corrosion-inhibitors-aci-136".别名是我可以从数据库中检索到的东西.

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.

无论如何，问题不在于检索数据.问题是一旦我有了数据，如何才能将数据智能地配对在一起?从每个实例的查询中可以看出，我正在提取itemID，因此我有一些要比较的地方，但是从那时起，我很迷路.

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.

谢谢.

更新#1

我想知道in_array()是否是一个不错的起点.至少我可以检查一下itemID是否存在.与此类似:

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.

更新#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.

更新#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 ) ) 

因此，我可以使用它来检查当前项目ID是否与数组中的itemID相匹配，但是在使用类似$ array ['itemID']的代码时出现索引未定义错误；很明显，我以错误的方式来处理这个问题.希望找到一些答案，我发现了一些但对于在某些情况下甚至完全不同的场景有意义的但并非全部的

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.

好，看起来像:

$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.

更新#4

想出了如何使用json_decode简化数组

Figured how to simply the array somewhat using json_decode

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

会导致

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'];

更新#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/>';

    }

推荐答案

好的，我知道了.我不确定这是否是最优雅的解决方案.如果有人想通过改进来吸引我，我会全力以赴.希望这将对将来遇到类似问题的人有所帮助.

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 -->

这篇关于需要帮助配对数据库值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！