I'm stuck with $modx->query()

793 Posts

ottogal Reply #1, 11 years, 8 months ago

I have a variable $list with a value like "(11,22,33)" and try the following:

$sql = "SELECT id FROM myTable WHERE id IN " . $list;
$results = $modx->query($sql);

I get the error message that $results is not an object.

What am I wrong about it? How to use a variable there?

This question has been answered by multiple community members. See the first response.

5,888 Posts

Bruno17 Reply #2, 11 years, 8 months ago

what are you trying todo with $results?

-------------------------------

you can buy me a beer, if you like MIGX

http://webcmsolutions.de/migx.html

Thanks!

793 Posts

ottogal Reply #3, 11 years, 8 months ago

For the moment I just want to find the syntax for the SQL query, given an array of ids and the criterium that the selected ids have to be "IN" this array - which is not a constant, but a variable. (That's the problem, all examples I find in the docs use constant arrays.)

I also tried the following, to no avail:

$myArr = array(11,22,33);
$sql = "SELECT id FROM myTable";javascript:void(0);
$all = $modx->query($sql);
$results = $all->where(array(
    "id:IN" => $myArr
));

Again the message: $results is not an object.

Later on I want to use this where clause for an UPDATE of a field in myTable. I want to use it in a post-hook of a FormIt form: The user selects some id values (which are saved in a $_SESSION variable); when he submits the form, the table rows with these ids will be updated. [ed. note: ottogal last edited this post 11 years, 8 months ago.]

discuss.answer

5,888 Posts

Bruno17 Reply #4, 11 years, 8 months ago

if you want to get an xpdo-object, which you want to update later and so on,
you should create a xpdo-schema, parse it, to get the xpdo-classes.
Than use it like that:

$myArr = array(11,22,33);
$classname = 'yourXpdoClass';
$c = $modx->newQuery($classname);
$c->where(array("id:IN" => $myArr));
if ($collection = $modx->getCollection($classname,$c)){
    foreach ($collection as $object){
        $object->set('yourfield',$yourvalue);
        $object->save();
    }
}

[ed. note: Bruno17 last edited this post 11 years, 8 months ago.]

-------------------------------

you can buy me a beer, if you like MIGX

http://webcmsolutions.de/migx.html

Thanks!

discuss.answer

24,544 Posts

BobRay Reply #5, 11 years, 8 months ago

If you don't want to go the xPDO route, you can test your query with trial and error on the PhpMyAdmin SQL tab.

You'll need to convert your array into the form that works in PhpMyAdmin. Then you can use the technique described here: http://bobsguides.com/blog.html/2014/11/19/using-my-sql-in-modx/

I think there may also be a way to extend Bruno's code with something like this. but I'm not at all sure of the details after line 4:

$myArr = array(11,22,33);
$classname = 'yourXpdoClass';
$c = $modx->newQuery($classname);
$c->where(array("id:IN" => $myArr));
$c->prepare();

if ($c) {
   while($row = mysql_fetch_assoc($c)) {
       echo print_r($row, true);
   }
}

Did I help you? Buy me a beer
Get my Book: MODX:The Official Guide
MODX info for everyone: http://bobsguides.com/modx.html
My MODX Extras
Bob's Guides is now hosted at A2 MODX Hosting

793 Posts

ottogal Reply #6, 11 years, 8 months ago

Thank you both for your helpful advice. Indeed I would like to avoid the whole arsenal of xPDO for this rather simple task, so your (Bob's) way of using mySQL in MODX seems interesting. I will dive into it tomorrow - it's late now in Germany...

5,888 Posts

Bruno17 Reply #7, 11 years, 8 months ago

Indeed I would like to avoid the whole arsenal of xPDO for this rather simple task

If you have MIGX installed and have a xpdo-schema for your table, you need some clicks from creating the components-folder to be ready to use your xpdo-class.

1. Create Package
2. Save schema
3. Create tables

Not more than 5 minutes.

It is it worth, even for very simple tasks.

-------------------------------

you can buy me a beer, if you like MIGX

http://webcmsolutions.de/migx.html

Thanks!

793 Posts

ottogal Reply #8, 11 years, 8 months ago

"myTable" is the custom table of my MIGXdb, the xPDO class is "myXpdoClass".
This code

$myArr = array(11,22,33);
$classname = 'myXpdoClass';
$c = $modx->newQuery($classname);
$c->where(array("id:IN" => $myArr));
$c->prepare();
print $c->toSQL();

prints the following:

SELECT `` FROM WHERE ``.`id` IN ('11','22','33')

The tablename myTable is missing. I guess that this is the reason that

if ($collection = $modx->getCollection('$classname',$c))

never results true.

discuss.answer

5,888 Posts

Bruno17 Reply #9, 11 years, 8 months ago

did you add the package somewhere, before trying to use its xpdo-classes?

$packageName = 'yourpackage';
$prefix = null;
$packagepath = $modx->getOption('core_path') . 'components/' . $packageName . '/';
$modelpath = $packagepath . 'model/';
if (is_dir($modelpath)) {
    $modx->addPackage($packageName, $modelpath, $prefix);
}

-------------------------------

you can buy me a beer, if you like MIGX

http://webcmsolutions.de/migx.html

Thanks!

24,544 Posts

BobRay Reply #10, 11 years, 8 months ago

Right, because the query has no way of knowing what the table name is. Bruno's method assumes that you have a schema and have made the table xPDO-friendly. Once you do that, MODX will know about the table.

Did I help you? Buy me a beer
Get my Book: MODX:The Official Guide
MODX info for everyone: http://bobsguides.com/modx.html
My MODX Extras
Bob's Guides is now hosted at A2 MODX Hosting

Answered I'm stuck with $modx->query()