在 PHP 环境中的另一个查询中使用一个 MySQL 查询的结果

2022-01-06 00:00:00 php mysql joomla

我遇到了一个问题，这可能是对问题的一个简单修复，但我似乎无法弄清楚.我是 PHP 和 MySQL 的新手，所以我到处阅读所有内容，但缺乏经验非常令人沮丧，因为通常需要很长时间才能意识到一个小错误.请查看下表并阅读下面的问题.

I have a problem, it may be a simple fix to the issue, but I can't seem to figure it out. I am new to PHP and MySQL, so I'm reading everything everywhere, but lack of experience is very frustrating, as often times it takes ages to realize a small error. Please look at the following tables and read below the questions.

PHP/mysql 在 Joomla 环境中，我正在尝试修改一个插件，以便将不同表中的值更新到一组其他表中，这不是最初打算的，但所有表都驻留在同一个数据库中.

The PHP/mysql is in Joomla environment, I am trying to modify a plugin, so that is updates with values from different tables into a set of other tables, that were not originally intended, but all tables reside in the same database.

   Table 1           vm_orders
---------------------------------------------
order_id    user_id     
---------------------------------------------
20          1           
55          6           
65          2           
30          4           
50          67          




   Table 2   vm_order_item
---------------------------------------------
order_item_id   order_id  order_item_sku    
---------------------------------------------
20                  20          1
55                  55          35
65                  65          60
30                  30          22
50                  50          3



Table 3 xipt_ users
---------------------------------------------------
userid  Profiletype template
----------------------------------------------------
1       1       default
6       3       default
2       1       default
4       8       default
67      7       default


Table 4      community_fields_values
---------------------------------------------
id      user_id     field_id    value
---------------------------------------------
1           1           55      Female
2           6           35      Cat
3           2           2       2
4           4           18      Texas
5           67          12      bike

我需要首先根据下订单的用户获取订单号.userid 变量是从脚本中的其他地方传递过来的.那部分工作正常.因此用户67已经下订单.这些就是我想要实现的目标.

What I need to is first of all get the order number according to the user that has place the order. The userid variable is being passed from elsewhere in the script. That part is working fine. So the user 67 has placed an order. These are the things I want to achieve.

  1. 查询 1:我想从 vm_orders 表(表 1)的order_id"列中获取orderid"值；我将调用结果vmorderid"并在另一个查询中使用它.

  1. Query 1: I want to get the "orderid" value from "order_id" column of vm_orders table (table 1); i will call the result "vmorderid" and use it in another query.

查询 2:使用查询 1 中的vmorderid"作为 vm_order_item 表(表 2)的order_id"列中的 order_id 值.我想从 my_order_item 表(表 2)的order_item_sku"列中获取 order_item_sku 值.我将调用结果为vmsku"并在另一个查询中使用它.

Query 2: Using the "vmorderid" from query 1 as the order_id value in the "order_id" column of vm_order_item table (table 2). I want to get the order_item_sku value from the "order_item_sku" column of my_order_item table (table 2). I will call the result "vmsku" and use it in another query.

查询 3:使用查询 2 中的vmsku"作为 vm_users 表(表 3)的Profiletype"列中的 profiletype 值.我想用vmsku"值更新profiletype"列的值.

Query 3: Using the "vmsku" from query 2 as the profiletype value in the "Profiletype" column of vm_users table (table 3). I want to UPDATE the value of the "profiletype" column, with "vmsku" value.

查询 4:使用查询 2 中的vmsku"作为 community_fields_values(表 4)的值"列中的值.我想更新 my_fields_values(表 4)vmsku"值中value"列的值.

Query 4: Using the "vmsku" from query 2 as the value in the "value" column of community_fields_values (table 4). I want to UPDATE the value of the "value" column in my_fields_values (table 4) "vmsku" value.

好的，到目前为止，我希望你和我在一起，我已经尝试了几个查询，但它不起作用.这是我目前所拥有的:

Okay, I hope you are with me so far, I have tried a couple of queries, but it's not working. Here is what I have so far:

假设用户是从参数字段传递的.

Assuming the user it is being passed from a param field.

$userid = $this->params->get('userid', 'defaultValue'); 




function _vm_custom_order($vmorderId) 
{

    $vmorderId = $database->loadResult();
    $database = JFactory::getDBO();

    // query the db to see if the user is already a member of group

     $vmorderId ="

                SELECT MAX
                    `order_id` 
                FROM 
                    #__vm_orders';
                WHERE 
                    `user_id` = '{$userid}'
                    ";

     $database->setQuery( $vmorderId );
    $data = $database->loadResult();

    return $data;
}



function _vm_sku($vmsku) 
{

    $vmsku = $database->loadResult();
    $database = JFactory::getDBO();

    // query the db to see if the user is already a member of group
    $vmsku = "
                SELECT 
                    `product_sku` 
                FROM 
                    #__vm_order_item';
                WHERE 
                    `order_id` = '{$vmorderId}'

                    ";

      $database->setQuery( $vmsku );
    $data = $database->loadResult();

    return $data;

}



function _add( $userid, $groupid, $vmsku)
{
    $success = false;
    $database = JFactory::getDBO();

    if (!$allow_multiplegroups = $this->params->get( 'allow_multiplegroups', '1' )) {
        // query the db to see if the user is already a member of ANY group
        $database->setQuery("
            SELECT 
                `profiletype` 
            FROM 
                #__xipt_users
            WHERE 
                `userid` = '{$userid}' 
        ");
        $member = $database->loadResult();

        // if so, do not execute
        if (intval($member) > 0) {
            return $success;
        }
    }

    $already = plgAmbrasubsAddToXipt::_already( $userid, $groupid );

    if (($already != $userid)) 
    {
    $database->setQuery("

                SELECT MAX
                    `order_id` 
                FROM 
                    #__vm_orders
                WHERE 
                    `user_id` = '{$userid}'
                        ");

                    $vmorderId = $database->loadResult();

        if ($database->query()) {
            $success = true; 
        }
    }       


    if (($already != $userid)) 
    {

$database->setQuery("

                SELECT 
                    `product_sku` 
                FROM 
                    #__vm_order_item
                WHERE 
                    `order_id` = '{$vmorderId}'
                        ");                     

                    $vmsku = $database->loadResult();

        if ($database->query()) {
            $success = true; 
        }
    }           


    // if they aren't already a member of the group, add them to the group
    if (($already != $userid)) 
    {
        $database->setQuery("
            UPDATE 
                #__xipt_users
            SET
                `profiletype` = '{$vmsku}'

            WHERE
                `userid` = '{$userid}'

            LIMIT 1

        ");

        if ($database->query()) {
            $success = true; 
        }
    } 

    return $success;
}

}

我也这样试过:

function _add( $userid, $groupid, $vmsku)
{
    $success = false;
    $database = JFactory::getDBO();

    if (!$allow_multiplegroups = $this->params->get( 'allow_multiplegroups', '1' )) {
        // query the db to see if the user is already a member of ANY group
        $database->setQuery("
            SELECT 
                `profiletype` 
            FROM 
                #__xipt_users
            WHERE 
                `userid` = '{$userid}' 
        ");
        $member = $database->loadResult();

        // if so, do not execute
        if (intval($member) > 0) {
            return $success;
        }
    }

    $already = plgAmbrasubsAddToXipt::_already( $userid, $groupid );

    if (($already != $userid)) 
    {
    $database->setQuery("

                SELECT MAX
                    `order_id` 
                FROM 
                    #__vm_orders
                WHERE 
                    `user_id` = '{$userid}'
                        ");

                    $vmorderId = $database->loadResult();

        if ($database->query()) {
            $success = true; 
        }
    }       


    if (($already != $userid)) 
    {

$database->setQuery("

                SELECT 
                    `product_sku` 
                FROM 
                    #__vm_order_item
                WHERE 
                    `order_id` = '{$vmorderId}'
                        ");                     

                    $vmsku = $database->loadResult();

        if ($database->query()) {
            $success = true; 
        }
    }           



    // if they aren't already a member of the group, add them to the group
    if (($already != $userid)) 
    {
        $database->setQuery("
            UPDATE 
                #__xipt_users
            SET
                `profiletype` = '{$vmsku}'

            WHERE
                `userid` = '{$userid}'


            LIMIT 1

        ");

        if ($database->query()) {
            $success = true; 
        }
    } 

    return $success;
}

}

我现在已经按照建议尝试使用 JOIN 来完成任务，到目前为止没有任何乐趣！

I have now tried as suggested, to use JOIN to accomplish the task, so far no joy!

更新

                        #__xipt_users

                    SET
                        `profiletype.#__xipt_users` = `product_sku.#__vmsku`

                    WHERE

                        `userid` = '{$userid}'


                    AND                         

                    ( 
                     SELECT `order_id.#__vm_orders`
                           FROM #__vm_orders, #__vm_order_item
                            LEFT JOIN #__vm_orders 
                            ON #__vm_orders.`order_id` = #__vm_order_item.`order_id` 
                            ORDER BY `order_id.#__vm_order` DESC LIMIT 1
                            WHERE
                            `user_id.#__vm_orders` = '{$userid}'

                    ) AS #__vmorder_id


                     SELECT ` product_sku.#__vm_order_item`
                           FROM #__vm_order_item, #__vmorder_id
                            LEFT JOIN #__vm_order_item 
                            ON `#__vm_order_item.order_id` = `#__vmorder_id.order_id` 
                            WHERE
                            `order_id.#__vm_order_item` = `order_id.#__vmorder_id`
                            )

                      AS #__vmsku

                    LIMIT 1

                    ");

推荐答案

Join Statements
我建议您开始学习如何在 MySQL 中创建 Join 语句.

Join Statements
I would suggest you start learning how to create Join Statements in MySQL.

看看这个网站:
http://www.keithjbrown.co.uk/vworks/mysql/mysql_p5.php

这样您就可以将多个查询合并为一个.这将使这项工作变得容易得多！

That way you are able to combine multiple queries into one. It will make this job a lot easier!

一张纸
它还可以帮助您在一张纸上绘制您的数据库，以便更好地了解您想要做什么.例如，您可以在要链接的表格字段之间画线.

Piece of paper
Also it will help you to draw your database on a piece of paper to get a better overview of what you want to do. For example you can draw lines between the table fields you want to link.

相关文章