Simple "Hello World" type SQL query

Hi Everyone,
I'm working on some modifications to my web page. I didn't create it but I'm stuck with supporting it.

So I'm trying to learn all the bits and pieces that go together to make MODX work.

My ultimate goal to solve my current problem is to look up a file name in a table then remove that file from the file system.
Towards that goal I have created a Snippet that I can run from the CronManager. (Based on an existing example)
This Snippet can execute a PHP file. ( Also based on an existing example)


I have a fairly complex SQL query that I want to run against a table in the database but I can't figure out how to do it.
I have some examples where the existing code query's the database but it is so convoluted I can't figure out what it is doing.

I'd be happy to start with a "Select * from tablename" and get that working. Output can go to the cron manager log.
After that I should be able to continue teaching my self.

I did do a search through the rtfm doco but couldn't find any examples.
Any pointers in the right direction would be appreciated.

My Modx version 2.2.6 (I know I'm working on upgrading it)

Thanks
David

I can see this example within my existing web page code but the sql only seems half complete (where is the "FROM") and I can't really understand the syntax.

What you're seeing is the xPDO data base layer. There are (should be) no actual "SELECT * FROM table" queries within the MODX core, and it's not recommended in extras either.

The $query_obj you're seeing is an xPDOQuery object. You can read more about that here: http://rtfm.modx.com/xpdo/2.x/class-reference/xpdoquery

The xPDOQuery replaces manually writing SQL, making sure it uses prepared PDO statements and that it's mostly database-agnostic.. the same code can normally run on MySQL and SqlServer for example. At the end of that block of code, there is probably a $modx->getObject, $modx->getCollection or $modx->getIterator statement passing the $query_obj as second parameter. Those methods build the actual SQL statement and fire it off to the database, returning an array of xPDOObject instances. In your example code, that instance happens to be a modResource.

It's also possible to use $modx->query('SELECT * FROM table'), which will return a PDOStatement object. I wouldn't recommend that as it bypasses the security measures and a bunch of other xPDO features.

Hi Mark,
That is exactly what I needed to know. You explained it clearly as well.
It also explains why when ever I look at a PHP example I always see them passing passwords around. I figured that MODX was picking up the password from somewhere but couldn't see how.

I'll make my simple Hello World example and then try to upscale it to my more complex query.

Thanks!
David

As you have surmised, MODX opens a connection to the database automatically on every page request. So any code, be it snippet, plugin, or TV @EVAL binding, has access to that database connection. The same with the session; you do not need to ever start a session when working in MODX-based code.

These default connections and sessions are why it's always best to use a MODX resource as the vehicle for handling AJAX processors or any other "external" code that isn't used to generate a page in itself. You can use a snippet in a resource with a (blank) template for returning AJAX request, or for generating dynamic code for cron to use - anywhere where you will need a simple returned value.

All of the .php files that you can see in the MODX core, including the cached snippet and plugin files in core/cache/ as well as .php files for add-ons in core/components/, are included into the index.php file using include, include_once, require and require_once directives.

Likewise, the Manager does the same thing with the /manager/index.php file. That is the actual file called when any requests are made to the Manager.

You might find this useful: http://bobsguides.com/revolution-objects.html.

Is the table you want to query a MODX table, or a custom table of your own?

As far as "Hello World" goes this did the trick.
I tried to understand the XPDOQuery stuff but it is just to obscure for me.
I kept getting confused.
Next I'll try and convert this to use my more complex query.

Thanks again for your help guys.

David

I don't believe it. I started with the above script working then slowly added stuff to make my more complex SQL Query. It didn't work so I started removing things again to get back to the exact script above and it still doesn't work.

Part of my problem is I can't debug things apart from outputting variables to the cron log.
I'm executing my code as a cron job as this is how it will be executed once complete.
If I make any syntax errors nothing at all happens, no errors that I can find.

If I comment out the foreach loop above my code executes so the problem is with this line.


Any idea's what I'm doing wrong or how I can capture any actual error messages?

Thanks
David

Since you have $modx instantiated, you can do this at any point to write to the MODX error log:



You might find this useful: http://bobsguides.com/blog.html/2014/11/19/using-my-sql-in-modx/

not sure, if pollardd has really instantiated modx.
maybe, it is not, is the reason, why his code stops execution at this line



seems to be the only line, where a modx-method is called.