query custom table

Quote from: wholein1 at Jun 18, 2017, 09:50 PM

Quote from: stefany at Jun 18, 2017, 09:44 PM

I think MODX ships with xPDO.

Remember to post the error you get when you are testing a snippet and it doesn't work.

How do you post the errors?

Copy & Paste them from the error log.

Quote from: stefany at Jun 18, 2017, 09:33 PM

The code I gave above was only to illustrate an example of using xPDO

$sql = 'SELECT * FROM `organizer_directory` ';
 
$c = new xPDOCriteria($modx,$sql); // connect to the MODX DB
$rows = array();
if ($c->stmt && $c->stmt->execute())
{
    while ($row = $c->stmt->fetch(PDO::FETCH_ASSOC))
    {
        $rows[] = $row;
    }
    
}
print_r($rows);

This one should help. It will probably spit out some errors since I do not know the internal organization of the DB, but you should be able to easily fix them.

This works.. spitting out the array info along with the DB information. Thank you.

Quote from: markh at Jun 19, 2017, 06:07 AM

The general process to start with xPDO is like this:

1. Create a schema file (XML) that describes your tables and its fields. See this section in the docs.
2. Use a build script to turn that schema file into model classes. These typically go into core/components/yourcomponent/model/yourcomponent/ and are the "compiled" version of your schema that xPDO will understand. Here's how that works, and a (random) full example is here.
3. Where you need to access your custom table(s), call $modx->addPackage('yourcomponent', 'path/to/core/components/yourcomponent/model/') - this registers your custom model files with xPDO. It's common to put this code in a service class that you load with getService instead. See here.
4. Use any of the $modx->newQuery/$modx->getObject/$modx->getCollection/$modx->getIterator/etc methods to interact with your tables. More info here.

Using the standard PDOStatements like you're doing is quicker (just feed in your query), but going through these steps give you access to everything xPDO has to offer.

thank you Mark. I do plan on work with the best practices of the XPDO, but just trying to get a visual on DB interactions.

Question: If you add just a table to the Modx DB do you have to create a schema? From what I am reading the Schema would be used for a whole new DB?

And for every table you have to build all this code to utilize and take advantage of the XPDO?

I think this is a direct answer to your question. It's about how to start with a custom table and make things xPDO-friendly: https://bobsguides.com/custom-db-tables.html.

There's a script there you can download to create the schema, class, and map, files from an existing custom DB table, as well as a discussion about how to use xPDO.

Quote from: BobRay at Jun 20, 2017, 09:32 PM

I think this is a direct answer to your question. It's about how to start with a custom table and make things xPDO-friendly: https://bobsguides.com/custom-db-tables.html.

There's a script there you can download to create the schema, class, and map, files from an existing custom DB table, as well as a discussion about how to use xPDO.

hello Bob thanks for the comments. I followed your tutorial, everything worked well.

I have translated all the code to work with a table I added that has data, but none of the fields are showing up.

<?php
$path = MODX_CORE_PATH . 'components/directory/';
$result = $modx->addPackage('directory',$path .
'model/','organizer_');

if (! $result) {
return 'failed to add package';
} else {
$output = '<p>added Package</p>';
}

$directory = $modx->getCollection('directory');

$output .= '<p>Total: '. count($directory) . '</p>';

foreach($directory as $directorys) {
$output .= '<p>First Name: ' . $directorys->get('firstName');
$output .= '
Last Name: ' . $directorys->get('lastLame');
$output .= '
Membership: ' . $directorys->get('membership') . '
</p>';
}
foreach($directory as $directorys) {
$fields = $quotes->toArray();
$output .= $modx->getChunk('directoryListTPL', $fields);
}

return $output;
Output page http://dev.leelogic.com/index.php?id=101

Not sure why it is not showing first name, last name and membership [ed. note: wholein1 last edited this post 9 years, 3 months ago.]

Is there anything in the MODX Error Log?

You can also try this:




Just a note: Any time you modify the schema, you have to re-run the script. MODX ignores the schema file itself and just uses the class and map files.

MODX doesn't like PHP tags embedded in the HTML, so they may be causing trouble. The content of the $output variable should appear where the snippet tag is placed (you do have a snippet tag on the page, right?).

Quote from: BobRay at Jun 22, 2017, 03:36 PM

Is there anything in the MODX Error Log?

You can also try this:

$fields=$quotes->toArray();
$modx->log(modX::LOG_LEVEL_ERROR, 'FIELDS: ' .  print_r($fields, true));

Just a note: Any time you modify the schema, you have to re-run the script. MODX ignores the schema file itself and just uses the class and map files.

MODX doesn't like PHP tags embedded in the HTML, so they may be causing trouble. The content of the $output variable should appear where the snippet tag is placed (you do have a snippet tag on the page, right?).

I got your example working and displaying. Trying to convert it to my table pulling only three values. Here is my updated snippet

<?php
$path = MODX_CORE_PATH . 'components/directories/';
$result = $modx->addPackage('directories',$path .
'model/','organizer_');

if (! $result) {
return 'failed to add package';
} else {
$output = '<p>added Package</p>';
}

$directories = $modx->getCollection('directory');

$output .= '<p>Total: '. count($directories) . '</p>';

foreach($directories as $directory) {
$output .= '<p>First Name: ' . $directory->get('firstName');
$output .= '
Last Name: ' . $directory->get('lastName');
$output .= '
membership: ' . $directory->get('membership') . '
</p>';
}

return $output;

//Snippet for resource: [[!CreateXpdoClassesDr? &myPackage=`directories` &myPrefix=`organizer_`]]

page output:
Show Directory

added Package

Total: 0

Error Log:
[2017-06-22 23:38:56] (ERROR @ /home/leelogic/public_html/dev/core/xpdo/xpdo.class.php : 761) directory::loadCollection() is not a valid static method.
[2017-06-22 23:38:56] (ERROR @ /home/leelogic/public_html/dev/core/cache/includes/elements/modsnippet/86.include.cache.php : 16) PHP warning: Invalid argument supplied for foreach()

Here is the page after creating xpdo
[2017-06-23 02:33:48] (WARN @ /home/leelogic/public_html/dev/core/xpdo/om/mysql/xpdogenerator.class.php : 199)

No indexes were found in table organizer_directory
[2017-06-23 02:33:48] (INFO @ /home/leelogic/public_html/dev/core/cache/includes/elements/modsnippet/85.include.cache.php : 207)

Schema file written to /home/leelogic/public_html/dev/core/components/directories/schema/directories.mysql.schema.xml
[2017-06-23 02:33:48] (INFO @ /home/leelogic/public_html/dev/core/cache/includes/elements/modsnippet/85.include.cache.php : 218)

Schema file parsed -- Files written to /home/leelogic/public_html/dev/core/components/directories/model/
[2017-06-23 02:33:48] (INFO @ /home/leelogic/public_html/dev/core/cache/includes/elements/modsnippet/85.include.cache.php : 224)

FINISHED [ed. note: wholein1 last edited this post 9 years, 3 months ago.]

Quote from: wholein1 at Jun 22, 2017, 06:41 PM

Quote from: BobRay at Jun 22, 2017, 03:36 PM

Is there anything in the MODX Error Log?

You can also try this:

$fields=$quotes->toArray();
$modx->log(modX::LOG_LEVEL_ERROR, 'FIELDS: ' .  print_r($fields, true));

Just a note: Any time you modify the schema, you have to re-run the script. MODX ignores the schema file itself and just uses the class and map files.

MODX doesn't like PHP tags embedded in the HTML, so they may be causing trouble. The content of the $output variable should appear where the snippet tag is placed (you do have a snippet tag on the page, right?).

I got your example working and displaying. Trying to convert it to my table pulling only three values. Here is my updated snippet

<!--?php
$path = MODX_CORE_PATH . 'components/directories/';
$result = $modx--->addPackage('directories',$path .
'model/','organizer_');

if (! $result) {
return 'failed to add package';
} else {
$output = '<p>added Package</p>';
}

$directories = $modx->getCollection('directory');

$output .= '<p>Total: '. count($directories) . '</p>';

foreach($directories as $directory) {
$output .= '<p>First Name: ' . $directory->get('firstName');
$output .= '
Last Name: ' . $directory->get('lastName');
$output .= '
membership: ' . $directory->get('membership') . '
</p>';
}

return $output;

//Snippet for resource: [[!CreateXpdoClassesDr? &myPackage=`directories` &myPrefix=`organizer_`]]

page output:
Show Directory

added Package

Total: 0

Error Log:
[2017-06-22 23:38:56] (ERROR @ /home/leelogic/public_html/dev/core/xpdo/xpdo.class.php : 761) directory::loadCollection() is not a valid static method.
[2017-06-22 23:38:56] (ERROR @ /home/leelogic/public_html/dev/core/cache/includes/elements/modsnippet/86.include.cache.php : 16) PHP warning: Invalid argument supplied for foreach()

Here is the page after creating xpdo
[2017-06-23 02:33:48] (WARN @ /home/leelogic/public_html/dev/core/xpdo/om/mysql/xpdogenerator.class.php : 199)

No indexes were found in table organizer_directory
[2017-06-23 02:33:48] (INFO @ /home/leelogic/public_html/dev/core/cache/includes/elements/modsnippet/85.include.cache.php : 207)

Schema file written to /home/leelogic/public_html/dev/core/components/directories/schema/directories.mysql.schema.xml
[2017-06-23 02:33:48] (INFO @ /home/leelogic/public_html/dev/core/cache/includes/elements/modsnippet/85.include.cache.php : 218)

Schema file parsed -- Files written to /home/leelogic/public_html/dev/core/components/directories/model/
[2017-06-23 02:33:48] (INFO @ /home/leelogic/public_html/dev/core/cache/includes/elements/modsnippet/85.include.cache.php : 224)

FINISHED

I built the adding directories and I get this error when trying to add a record. [2017-06-23 14:47:56] (ERROR @ /home/leelogic/public_html/dev/core/xpdo/xpdo.class.php : 643) Could not load class: directories from mysql.directories.

I basically imported the table from another database. Do you think this would be an issue?

Did you use the CreateXpdoClasses extra to create the class and map files based on your imported table?

If you wrote the schema yourself, make sure your directory object extends xPDOSimpleObject instead of xPDOObject. That way it will always have an ID field to use as an index.

Quote from: BobRay at Jun 23, 2017, 06:21 PM

Did you use the CreateXpdoClasses extra to create the class and map files based on your imported table?

If you wrote the schema yourself, make sure your directory object extends xPDOSimpleObject instead of xPDOObject. That way it will always have an ID field to use as an index.

I used your createxpdo file and modified the fiel]ds to represent my table. I did not write my own schema