MODx querying custom db table

Question

See the code below. I'm trying to get some data from a database is modx. The data is there, meaning, when i query the database in phpmyadmin, i get results. I can't figure out why it doesnt work in modx.

$sql =  'SELECT * FROM orders ORDER BY created DESC LIMIT 1';

$stmt = $modx->prepare($sql);
$stmt->execute(); 

// Put data in array
$order_data = $stmt->fetch(PDO::FETCH_ASSOC);
if ($order_data == '') {
    return 'Resultset empty for user '. $user_id.'.<br />'.$sql;
} else {
    return 'Data found!';
}

Is this from a custom 3rd party component? Have you made an object for this field in the database? — OptimusCrime, Jun 27 '12 at 13:33
Yes and yes, but I also need it to work for custom database tables that have no object in modx. I got this from this webpage: http://forums.modx.com/thread/35321/querying-a-non-modx-table-in-revolution — Meddie, Jun 27 '12 at 13:35
Tried switching `PDO::FETCH_ASSOC` with `PDO_FETCH_ASSOC`? Do you get any errors? — OptimusCrime, Jun 27 '12 at 13:41
I did try switching, but no errors, just the same empty result. — Meddie, Jun 27 '12 at 13:43
this is evolution? does your fetch return an array, shouldn't you be getting a string conversion error in $order_data == '' & modx is just hiding it? try counting your array. — Sean Kimball, Jun 27 '12 at 13:50
If you simply `var_dump($order_data)`, does that also return empty? Like @SeanKimball wrote; I think it returns a array. And I think this is revo, because he made objects of the tables (according to the 2nd comment). — OptimusCrime, Jun 27 '12 at 14:02
this is revo yes..sorry for not making that more clear. And var_dump($order_data) returns false. — Meddie, Jun 27 '12 at 14:15

score 0 · Answer 1 · answered Jun 27 '12 at 23:34

0

Perhaps try using xPDO:

$sql = 'SELECT * FROM orders ORDER BY created DESC LIMIT 1';

$c = new xPDOCriteria($modx,$sql);
if ($c->stmt && $c->stmt->execute()) {
    $order_data = $c->stmt->fetch(PDO::FETCH_ASSOC);
    var_dump($order_data);
}

answered Jun 27 '12 at 23:34

okyanet

3,106
1
22
16

score 0 · Answer 2 · answered Sep 21 '13 at 17:44

You need to loop through your output, the result will be an array of arrays.

The below example will return the data and present it according to a chunk format

<?php
$sql = "SELECT * FROM `table_name`";
$query = $modx->query($sql);
$rows = array();
if ($query) {
    // loop through the result set and inspect one row at a time
    while ($row = $query->fetch(PDO::FETCH_ASSOC)) {
        array_push($rows, $row);
        $output .= $modx->getChunk($tpl,$row);
    }
}
$output = "<table border=\"1\" cellpadding=\"5\" >
<th>ID</th><th>row1_Head</th><th>row2_Head</th><th>row3_Head</th><th>row4_Head</th><th>row5_Head</th>
$output</table>";
return $output;

This is an example of the chunk:

<tr>
<td>[[+id]]</td> <td>[[+row1]]</td> <td>[[+row2]]</td> <td>[[+row3]]</td> <td>[[+row4]]</td> <td>[[+row5]]</td> 
</tr>

Now in a resource call your snippet like

[[!Snippet_name? &tpl=`chunk_name`]]

Or perhaps you can follow there own guide here: http://rtfm.modx.com/revolution/2.x/case-studies-and-tutorials/using-custom-database-tables-in-your-3rd-party-components — Mina Gerges, Sep 21 '13 at 17:53

score 0 · Answer 3 · answered Dec 27 '13 at 10:58

$sql =  'SELECT * FROM orders ORDER BY created DESC LIMIT 1';

Assume your schema is like

<?xml version="1.0" encoding="UTF-8"?>
<model package="your_package_name" baseClass="xPDOObject" platform="mysql" defaultEngine="MyISAM" version="1.1">
    <object class="Orders" table="orders" extends="xPDOSimpleObject">
        <field key="order_no" dbtype="varchar" precision="255" phptype="string" null="false" />
        <!-- and everything else below  -->

    </object>

    <!-- and everything else below  -->
</model>

What you need to do is as simple as this:

$modx->addPackage('your_package_name'); // Add your table schema to MODX object

$orders = $modx->getCollection('Orders'); // class's name, not table's name
$output = '';
if ($orders){
    $outputArray = array();
    foreach ($orders as $order) {
        $orderArray = $order->toArray();
        $outputArray[] = $modx->getChunk('your_row_chunk_name', $orderArray);
    }
    $wrapper = array(
        'orders' => @implode("\n", $outputArray);
    );
    $output = $modx->getChunk('your_wrapper_chunk_name', $wrapper);
}

return $output;

MODx querying custom db table

3 Answers3