query custom table

I am new to pdo and xpdo, so right now all I want to do is to get the snippet to populate the page snippet call. After I get it working I will convert it to XPDO. Below is what I am trying but the data is not showing up in the content section of the page. This code I have in a snippet called directory. any help would be apreciated

The revo 2.5.1

<?php
$rows= array();
$stmt= $modx->query("SELECT * FROM `organizer_directory` WHERE 1");
if ($stmt) {
// loop through the result set and inspect one row at a time
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
array_push($rows, $row);
print($rows);
}
}
?> [ed. note: wholein1 last edited this post 9 years, 4 months ago.]

Here is my snippet that should be helpful to you. It fetches data from a custom column, but you can edit it to do it from a custom table.



This one fetches URLs separated with a "|" sign and then explodes them. However you can implement any string manipulation that is appropriate.
Please note that you should filter any user input of the query. This one is just an example to point you to the right direction.

Quote from: stefany at Jun 17, 2017, 10:34 PM

Here is my snippet that should be helpful to you. It fetches data from a custom column, but you can edit it to do it from a custom table.

$sql = 'SELECT `images` FROM `modx_site_content` WHERE `id` = YOUR_FILTERED_ID';

$c = new xPDOCriteria($modx,$sql); // connect to the MODX DB
$html = '';
if ($c->stmt && $c->stmt->execute())
{
    $order_data = $c->stmt->fetch(PDO::FETCH_ASSOC);
 $images_string = explode('|', $order_data['images']); // Some string manipulations 

foreach($images_string as $image)
{
    if(!empty($image))
{
 $html .= "<li><img src="$image"></li>";
}
   
}
return $html;
}

This one fetches URLs separated with a "|" sign and then explodes them. However you can implement any string manipulation that is appropriate.
Please note that you should filter any user input of the query. This one is just an example to point you to the right direction.

I am getting a syntax error on line 4 where the variable html is placed. using this code do I have to let xpdo in the code know that this table exists, sorry not sure of terminology?

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



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.

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.

Still not working: I have it being called into a page with [[directory]]

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.

Also, I have not set up the xpdo for this database. I was just trying to access the table through the pdo just to gte the information from the table to display. Trying to learn more about using custom tables in the MOdx Revolution

I think MODX ships with xPDO.

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

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?

Are you sure you don't want to start with straight XPDO? This code, as is, should get all rows of your table:

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.