Running custom database queries in ModX Revo

Question

I am trying to query the database within a snippet within ModX Revo:

<?php

$leadersql = "SELECT * FROM `modx_menus`";

$result = mysql_query($leadersql);

while ($row = mysql_fetch_array($result)) {
  echo "hello";
};

?>

On Evo this worked fine but returns nothing in Revo.

Do I need to set it up differently?

score 10 · Accepted Answer · answered Sep 18 '12 at 12:20

Christian's code does work you may need to define the $rows array first:

$leadersql = "SELECT * FROM `modx_menus`";

$query = $modx->query($leadersql);

$rows = array();

if ($query) {
    // loop through the result set and inspect one row at a time
    while ($row = $query->fetch(PDO::FETCH_ASSOC)) {

        array_push($rows, $row);

    }
}

echo '<br /><br /><pre>';
        print_r($rows);
echo '</pre>';

if not: - you do have menu items defined? - you are calling your snippet uncached?

christianhanvey · Answer 2 · 2012-09-18T05:27:57.353

5

Ideally, you would use the xPDO method for building database queries. It automatically escapes supplied arguments, creates queries that translate across database types (currently mysql & mssql), and has many other benefits. However, it's also trickier to set up as you need to create classes and maps for your custom table. Bob's guides has good info, as does Lazylegs

But of course you can implement your particular query, without using XPDO:

$leadersql = "SELECT * FROM `modx_menus`";
$query = $modx->query($leadersql);
if ($query) {
    // loop through the result set and inspect one row at a time
    while ($row = $query->fetch(PDO::FETCH_ASSOC)) {
        array_push($rows, $row);
    }
}

And there's also the Rowboat add-on, for iterating across database table rows - if your requirements are straightforward.

edited Sep 18 '12 at 05:27

answered Sep 18 '12 at 05:16

christianhanvey

141
6

thanks but no luck there! Would it be easier to create my own database with my own tables and connect to it normally? – MeltingDog Sep 18 '12 at 05:27
I just added a line about Rowboat - have you looked at that? – christianhanvey Sep 18 '12 at 05:29
Yes - Rowboat just outputs the query results doesnt it? I cant put it into PHP variables can I? – MeltingDog Sep 18 '12 at 05:30

score 3 · Answer 3 · answered Sep 20 '12 at 08:33

3

Why not use the built in xPDO methods:

$menus = $modx->getCollection('modMenu');
if ($menus) {
    foreach ($menus as $menu) {
        echo $menu->get('text');
    }
}

answered Sep 20 '12 at 08:33

okyanet

3,106
1
22
16

score -2 · Answer 4 · answered Oct 27 '15 at 15:28

-2

You can try this code:

<?php
include './core/config/config.inc.php';

$con=mysqli_connect($database_server,$database_user,$database_password,$dbase);
// Check connection
mysqli_set_charset($con, "utf8");
if (mysqli_connect_errno()) {
    //echo "Failed to connect to MySQL: " . mysqli_connect_error();
    return "DB error";
}

$result = mysqli_query($con,"SELECT id, pagetitle, uri FROM `modx_site_content` WHERE parent = ".$parentId); 
while($row = mysqli_fetch_array($result))
{
  //do something with $row['pagetitle'];
}

where $parentId is example parameter from Snippet call.

answered Oct 27 '15 at 15:28

TomoMiha

1,218
1
14
12

Don't use this concact of parameters from GET or POST, to prevent SQL injection. Use prepare instead of this. Check http://php.net/manual/ro/mysqli.prepare.php – Laurentiu Oct 13 '16 at 10:33
Thank you for commenting, but purpose of my post was to show how to connect to database from Modx Revo, in a working version. It is up to the author to decide weather sql injection protection is nessessary or not. For example, you can use modx for internal websites too, where there is no risk from sql injections. – TomoMiha Feb 10 '17 at 09:56

Running custom database queries in ModX Revo

4 Answers4