I have a problem, maybe this is a simple solution to the problem, but I cannot figure out how to understand this. I am new to PHP and MySQL, so I read everything everywhere, but the lack of experience is very frustrating, as it often takes a long time to implement a small error. See the following tables and read the questions below.
PHP / mysql is in the Joomla environment, I'm trying to change the plugin, so this is updating values from different tables to a set of other tables that were not originally intended, but all the tables are 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
First of all, I need to get the order number according to the user who placed the order. The userid variable is passed from another place to the script. This part is working fine. Thus, user 67 placed the order. This is what I want to achieve.
Query 1: I want to get the "orderid" value from the "order_id" column of the vm_orders table (table 1); I will call the result of "vmorderid" and use it in another query.
Query 2: use the "vmorderid" from query 1 as the order_id value in the "order_id" column of the vm_order_item table (table 2). I want to get the value of order_item_sku from the column "order_item_sku" of the table my_order_item (table 2). I will name the result "vmsku" and use it in another query.
Query 3: Using "vmsku" from query 2 as the value of the profile property in the "Profiletype" column of the vm_users table (table 3). I want to UPDATE the value of the "profiletype" column with the value "vmsku".
Query 4: use “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".
Well, I hope you are with me so far, I tried a couple of requests, but it does not work. Here is what I still have:
Assuming the user is being passed from the 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; } }
I also tried this as follows:
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; } }
EDIT: I now tried, as suggested, to use JOIN to complete the task, until it pleases!
UPDATE
#__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 ");