Problem inserting rows in custom tables inside Modx database

14 Posts

keytarist Reply #1, 6 years, 9 months ago

Hello

I'm reading the tutorial of Bob Ray about creating custom database tables, and I added four custom tables to the Modx database. The tutorial includes running the CreateXpdoClasses snippet provided in the same blog, and modifying the xml schema file (in this case is store.mysql.schema.xml) created by the snippet to establish relationships.

I have an usual eCommerce group of tables:

client
order
orderproduct (intermediate table)
product

When the user buys an item, there is no problem when inserting rows in the order table. But, nothing gets inserted in the orderproduct table (intermediate table - many to many) and there are no errors displayed.

The line $oSave = $order->save(); works fine but the line $opSave = $orderProduct->save(); returns false and that is all I get.

I can insert insert rows fine manually, so the problem must be in the code I made.

Both database operations are included on a snippet I made (following the tutorial, with other code added to fit my website). I already checked for errors in the code and that the session and post variables are retrieved correctly. I can't find what I did wrong.

<?php
if(filter_has_var(INPUT_POST, 'pay') && filter_input(INPUT_POST, 'pay') === 'Complete Payment' && isset($_SESSION['shoppingCart']))
{
    $path = MODX_CORE_PATH.'components/store/';
    $result = $modx->addPackage('store',$path.'model/','wp_');
    if (!$result)
    {
        return 'Failed to add package';
    }
    
    $totalAmount = 0;
    foreach($_SESSION['shoppingCart'] as $cart => $product)
    {
        $totalAmount += $product['price'] * $product['quantity'];
    }
    $orderFields = array('idClient' => $_SESSION['loggedIn']['id'], 'totalAmount' => $totalAmount, 'paymentType' => 'debit card', 'date' => 'CURDATE();');
    $order = $modx->newObject('Order', $orderFields);
    $oSave = $order->save();
    $idOrder = $modx->lastInsertId();
    $orderProductList = array();
    foreach($_SESSION['shoppingCart'] as $cart => $product)
    {
        $orderProductList[] = array('idProduct' => $product['idProduct'], 'idOrder' => $idOrder, 'quantity' => $product['quantity']);
    }
    
    $opSave = true;
    foreach($orderProductList as $oP)
    {
        $orderProduct = $modx->newObject('OrderProduct', $oP);
        $opSave = $orderProduct->save();
        if(!$opSave)
        {
            break;
        }
    }
    
    if ($oSave && $opSave) 
    {
        unset($_SESSION['shoppingCart']);
        $cart = '';
        $message = 'Payment completed'.$listas;
        return $modx->getChunk('shoppingCartTpl', array('cart' => $cart, 'message' => $message));
    } 
    else 
    {
        unset($_SESSION['shoppingCart']);
        return 'The transaction failed';
    }
}

Here is the code for the xml schema file with the relationships I had to add manually. I think that I don't quite get how to do this part and the code may be wrong somewhere.

<?xml version="1.0" encoding="UTF-8"?>
<model package="store" baseClass="xPDOObject" platform="mysql" defaultEngine="MyISAM" version="1.1">
	<object class="Client" table="client" extends="xPDOSimpleObject">
        <field key="name" dbtype="varchar" precision="30" phptype="string" null="false" />
        <field key="password" dbtype="char" precision="8" phptype="string" null="false" />
        <!-- PPPPPPPPPPPPPPPPPPPPPPPP -->
        <composite alias="Orders" class="Order" local="id" foreign="clientKey" cardinality="many" owner="local" />
    </object>
    <object class="Order" table="order" extends="xPDOSimpleObject">
        <field key="idClient" dbtype="int" precision="11" phptype="integer" null="false" index="index" />
        <field key="totalAmount" dbtype="int" precision="11" phptype="integer" null="false" />
        <field key="paymentType" dbtype="varchar" precision="10" phptype="string" null="false" />
        <field key="date" dbtype="datetime" phptype="datetime" null="false" />

        <index alias="fk_order" name="fk_order" primary="false" unique="false" type="BTREE" >
            <column key="idClient" length="" collation="A" null="false" />
        </index>
        <!-- FFFFFFFFFFFFFFFFFFFFFFFF -->
        <field key="clientKey" dbtype="int" precision="100" phptype="integer" null="false" default="" />
        <aggregate alias="ClientAlias" class="Client" local="clientKey" foreign="id" cardinality="one" owner="foreign" />
        <!-- PPPPPPPPPPPPPPPPPPPPPPPP -->
        <composite alias="Ordersproducts" class="Orderproduct" local="id" foreign="orderKey" cardinality="many" owner="local" />
    </object>
    <object class="Orderproduct" table="orderproduct" extends="xPDOObject">
        <field key="idProduct" dbtype="int" precision="11" phptype="integer" null="false" index="pk" />
        <field key="idOrder" dbtype="int" precision="11" phptype="integer" null="false" index="pk" />
        <field key="quantity" dbtype="int" precision="11" phptype="integer" null="false" />

        <index alias="PRIMARY" name="PRIMARY" primary="true" unique="true" type="BTREE" >
            <column key="idProduct" length="" collation="A" null="false" />
            <column key="idOrder" length="" collation="A" null="false" />
        </index>
        
        <index alias="fk_1_orderProduct" name="fk_1_orderProduct" primary="false" unique="false" type="BTREE" >
            <column key="idProduct" length="" collation="A" null="false" />
        </index>
        <index alias="fk_2_orderProduct" name="fk_2_orderProduct" primary="false" unique="false" type="BTREE" >
            <column key="idOrder" length="" collation="A" null="false" />
        </index>
        <!-- FFFFFFFFFFFFFFFFFFFFFFFF -->
        <field key="orderKey" dbtype="int" precision="100" phptype="integer" null="false" default="" />
        <aggregate alias="OrderAlias" class="Order" local="orderKey" foreign="id" cardinality="one" owner="foreign" />
        <!-- FFFFFFFFFFFFFFFFFFFFFFFF -->
        <field key="productKey" dbtype="int" precision="100" phptype="integer" null="false" default="" />
        <aggregate alias="ProductAlias" class="Product" local="productKey" foreign="id" cardinality="one" owner="foreign" />
    </object>
    <object class="Product" table="product" extends="xPDOSimpleObject">
        <field key="name" dbtype="varchar" precision="30" phptype="string" null="false" />
        <field key="description" dbtype="varchar" precision="100" phptype="string" null="true" />
        <field key="price" dbtype="int" precision="11" phptype="integer" null="false" />
        <field key="stock" dbtype="int" precision="11" phptype="integer" null="false" />
        <!-- PPPPPPPPPPPPPPPPPPPPPPPP -->
        <composite alias="Ordersproducts" class="OrderProduct" local="id" foreign="productKey" cardinality="many" owner="local" />
    </object>
</model>

Localhost details:
Modx Revolution 2.5.7 with ACE and FormIt

Please, help me find how to insert the rows in the orderproduct table.

Thanks in advance!!

This question has been answered by BobRay. See the first response.

[ed. note: keytarist last edited this post 6 years, 9 months ago.]

discuss.answer

24,544 Posts

BobRay Reply #2, 6 years, 9 months ago

There are a number of cases where setting all the fields in the second argument of the newObject() call doesn't work. It's also wise, at least during development, to test the return value from newObject().

Try setting the fields individually:

$orderProduct = $modx->newObject('OrderProduct');

if ($orderProduct) {
    $orderProduct->set('idOrder', $idOrder);
    // etc.
    $opSave = $orderProduct->save();
} else {
    $modx->log(modX::LOG_LEVEL_ERROR, 'Failed to create OrderProduct');
}

Another way to go would be to use $order->addMany(), to add the related object(s) to the $order object before saving it. That way the intersect object will be saved automatically when you save the $order, though TBH, the method you're using is often easier to implement and just as fast.

Did I help you? Buy me a beer
Get my Book: MODX:The Official Guide
MODX info for everyone: http://bobsguides.com/modx.html
My MODX Extras
Bob's Guides is now hosted at A2 MODX Hosting

14 Posts

keytarist Reply #3, 6 years, 9 months ago

Thanks a lot, Bob!!
After setting the fields individually, I got the rows inserted properly in the orderProduct table.

The error related to passing an array as the second argument to the newObject function, could be solved for the next Modx release.

Thanks for your time and generosity helping developers with your blog tutorials.

24,544 Posts

BobRay Reply #4, 6 years, 9 months ago

I'm glad I could help.

Did I help you? Buy me a beer
Get my Book: MODX:The Official Guide
MODX info for everyone: http://bobsguides.com/modx.html
My MODX Extras
Bob's Guides is now hosted at A2 MODX Hosting

Answered Problem inserting rows in custom tables inside Modx database