I've got a problem, it might be an easy fix towards the problem, however i can't appear to decipher it. I'm a complete newbie with PHP and MySQL, so I am reading through everything everywhere, but lack of skill is extremely frustrating, as frequently occasions it requires age range to understand a little error. More often than not I am stabbing at nighttime, nevertheless its intriguing and I am learning a great deal. Sites such as this and those who request and respond to questions here a an excellent services are us newcomers, thankyou. Please consider the following tables and browse underneath the questions. Thanks ahead of time for the time, this website rocks !.

The php/mysql is within Joomla atmosphere, i'm attempting to modify a wordpress plugin, to ensure that is updates with values from different tables into some other tables, which were not initially intended, but all tables live in exactly 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 have to is to begin with obtain the order number based on the user which has put the order. The userid variable has been passed from elsewhere within the script. That part is working fine. Therefore the user 67 has placed a purchase. Fundamental essentials things i wish to achieve.

  1. Query 1: I wish to obtain the "orderid" value from "order_id" column of vm_orders table (table 1) i'll call the end result "vmorderid" and employ it in another query.

  2. Query 2: While using "vmorderid" from query 1 because the order_id value within the "order_id" column of vm_order_item table (table 2). I wish to obtain the order_item_sku value in the "order_item_sku" column of my_order_item table (table 2). I'll call the end result "vmsku" and employ it in another query.

  3. Query 3: While using "vmsku" from query 2 because the profiletype value within the "Profiletype" column of vm_customers table (table 3). I wish to UPDATE the need for the "profiletype" column, with "vmsku" value.

  4. Query 4: While using "vmsku" from query 2 because the value within the "value" column of community_fields_values (table 4). I wish to UPDATE the need for the "value" column in my_fields_values (table 4) "vmsku" value.

Okay, i think you'll are beside me to date, i've attempted a few queries, but it is no longer working. Here's what i've to date

Presuming the consumer it's being passed from the param area.

$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 additionally attempted it by doing this

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

}

Okay, which was very lengthy, thank you for reading through everything.

EDIT: I've now attempted as sugessted, to make use of JOIN to get the job done, but im beginning to consider i am not too vibrant or php/mysql dont much like me, haha anyway- here you go any gurus available please point me within the right direction. Cause to date no pleasure!:(

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

                    ");