在PHP环境中的另一个查询中使用一个MySQL查询的结果
我有一个问题，它可能是该问题的简单解决方法，但我似乎无法弄清楚.我是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:我想从vm_orders表(表1)的"order_id"列中获取"orderid"值；我将结果称为"vmorderid"，并在另一个查询中使用它.
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"值中值"列的值.
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:
假设是用户，它是从param字段传递的.
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
");
加入声明
我建议您开始学习如何在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_mysql_p5.php
Have a look at this website:
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.