Get data from table using snippet

Development
1

Hello, i am trying to create a simple snippet who will return specific data from database table using xpdo. Following the official modx tutorial: (Using Custom Database Tables - Tutorials | MODX Documentation) i got stacked in one basic step.
In this tutorial is describing that we need to follow the steps:

  1. creating a schema xml for the table we will need to query
  2. Building the Schema
  3. Using our New Model (creating and using the snippet.)

I got stacked into the second step: Building the Schema. My question is how to build it? it is a php coded file but what tool i need to use in order to run it under modx?
I tried to run using console tool that i installed in modx but it return some errors regarding the directory structure etc… Of course icorected the directories to be under ~/core/components/console/ and i succeed to build and use my snippet but i believe that this is not the correct way.
Also i started by creating the directory of my packageName inside core/components/ is this the correct way?

2

Which MODX version? Steps are a bit different between 2.x and 3.x

No matter, which version. MIGX should help to build and parse your schema, create your tables and get your data from your tables with migxLoopCollection.

3

modx version 2.8.4, i wanted to learn the native way not the migx.
Lets say that the tables already exist and i just want to create and build my own schema and then my own snippet.

4

did you allready write your schema?

5

It looks like the documentation you linked above, assumes that you have the standard folder structure for a MODX extra. (For the standard folder structure take a look at this article or look at any Github repository of a MODX extra.)

You usually build these extras locally and outside of the folder with the MODX installation.
To create the schema, you can just call _build/build.schema.php in your browser.

You would then create a transport package for the extra and install that on your server.

Usually to create a quick custom table directly on the server, it’s much easier to use an extra like MIGX or ExtraBuilder.

The code you linked requires a lot of setup to basically just run $generator->parseSchema(). Honestly I don’t think there is much to learn by doing it all manually.

The xPDOGenerator has a function writeSchema that you can use to generate a schema from existing database tables.

6

yes i wrote the schema so i am not sure about the next steps. As far as i understood i should create in core/components/myAppNAme and then inside that the model directory and schema directory. Then to put my schema xml and somehow to build it.

7

Thank you for your advices, so as far as i understood the best tactic is to create a directory _build out of modx folder and then to create manually the deafaulte folder structure, to put the schema xml and build config, to build it and then copy paste the directories and files inside modx. in core/components.

8

This is only the best tactic, if you want to create a MODX extra. (The advantage of a separate folder outside of MODX is, that you can easily manage this folder with Git.)

If this custom table is just a one-off thing, then the proceeding you described is way too complicated.

  • Use MIGX and you can parse the schema and create the DB-tables with 2 clicks.

  • If you really want to progam it yourself for some reason, then maybe create a snippet (with hard-coded folder paths) that you run once. Use the function parseSchema to parse the schema and createObjectContainer to create the database tables.
    (The example code in the documentation loads MODX externally, which you don’t have to do in a snippet.)

It’s just a convention that the schema and the class files are located in such a folder structure. But you don’t have to follow this convention. Just make sure that when you use $modx->addPackage() in your code, you provide the correct path.

9

Thank you for your advice. Actually what i want to do is to create a snippet that i will use inside my html like that: [[!MySnippet? &someOptions]] and receive data from allready existing table ex. ms_products table. I do not need to create new table.

10

can you share your xml schema here?
The next step, that you need is to parse that schema.

11

If the table already exists, you don’t necessarily need a schema to access the data.
(You only need a schema if you want to have a PHP class, that corresponds to your database table.)

You can always use normal PDO to query a table. For example like this:

<?php
$stmt = $modx->prepare("SELECT * FROM `ms_products`");
$stmt->execute();
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
$output = '';
foreach ($rows as $row) {
    $output .= '<pre>' . print_r($row, 1) . '</pre>';
    // $output .= $modx->getChunk('myRowChunk', $row); // To output a chunk for every row
}
return $output;
12

Here is some example code for parsing a schema in MODX 2.x:

Let’s assume we choose a packagename “somepackage”.

  1. Create a schema under core/components/somepackage/model/schema/somepackage.mysql.schema.xml with the content:
<?xml version="1.0" encoding="UTF-8"?>
<model package="somepackage" baseClass="xPDOObject" platform="mysql" defaultEngine="InnoDB" version="1.1">
    <object class="somepackageTest" table="somepackage_test" extends="xPDOSimpleObject">
        <field key="some_field" dbtype="varchar" phptype="string" precision="100" null="false" default="" />
    </object>
</model>
  1. To parse the schema, create a snippet with code like this:
<?php
$package_name = "somepackage";
$core_path = $modx->getOption('core_path', null, MODX_CORE_PATH ); // Path to 'core' folder
$model_folder = $core_path . 'components/'  . $package_name . '/model/'; // Path where the generated class files should be saved to.
$schema_path = $core_path . 'components/'  . $package_name . '/model/schema/' . $package_name . '.mysql.schema.xml'; // Path to your schema file

$manager = $modx->getManager();
$generator = $manager->getGenerator();
$success = $generator->parseSchema($schema_path, $model_folder);
if ($success){
    return 'schema successfully parsed.';
} else {
    return 'error parsing the schema.';
}

Run the snippet once. This should create the class files in core/components/somepackage/model/somepackage/.

  1. To create the database table from the class files, create a snippet with code like this:
<?php
$package_name = "somepackage";
$core_path = $modx->getOption('core_path', null, MODX_CORE_PATH );
$modx->addPackage($package_name, $core_path . 'components/' . $package_name . '/model/');

$manager = $modx->getManager();
$success = $manager->createObjectContainer('somepackageTest');
if ($success){
    return 'table successfully created.';
} else {
    return 'error creating the table.';
}

Run the snippet once. This should create the database table modx_somepackage_test.

  1. To use your class, create snippets with code like this:

To create a new entry:

<?php
$package_name = "somepackage";
$core_path = $modx->getOption('core_path', null, MODX_CORE_PATH );
$modx->addPackage($package_name, $core_path . 'components/' . $package_name . '/model/');

$new_object = $modx->newObject('somepackageTest');
$new_object->set('some_field', 'some value');
if ($new_object->save()) {
    return 'new row successfully created.';
} else {
    return 'error creating new row.';
}

To output the table content:

<?php
$package_name = "somepackage";
$core_path = $modx->getOption('core_path', null, MODX_CORE_PATH );
$modx->addPackage($package_name, $core_path . 'components/' . $package_name . '/model/');

$rows = $modx->getCollection('somepackageTest');
$output = '';
foreach ($rows as $row) {
    $output .= '<pre>' . print_r($row->toArray(), 1) . '</pre>';
}
return $output;
13

yes this is doing the job, thank you for your advice and your time

14

Perfect now is clear how it works Thanks alot

15

This topic was automatically closed 2 days after discussion ended and a solution was marked. New replies are no longer allowed. You can open a new topic by clicking the link icon below the original post or solution and selecting “+ New Topic”.