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.
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
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.
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.
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.
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.
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;
}
}
I also tried it this way:
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 have now tried as suggested, to use JOIN to accomplish the task, so far no joy!
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
");
