Database Query

165 Posts

raaviraaj Reply #1, 19 years, 8 months ago

I have done a database query as under...

$counter = 1;
$sc= $modx->getFullTableName('site_content');
$sql="SELECT sc.id,sc.pagetitle,sc.longtitle FROM {$sc} sc WHERE sc.published=1 AND (sc.parent=5 OR sc.parent=12 OR sc.parent=62 OR sc.parent=175) ORDER BY sc.pub_date DESC";
$rs = $modx->db->query($sql);
$total = $modx->db->getRecordCount($rs);

while ($row = $modx->db->getRow($rs))
{
...
$counter++;
}

The above while statement loops till total.

Can I specifically get a particular row using $modx->db->getRow() e.g. 10th row without looping through the entire recordset.

I am sorry if this question is lame, i’m still learning laugh

-Raavi

☆ A M B ☆
24,524 Posts

sottwell Reply #2, 19 years, 8 months ago

That should be done in the query, unless you will also be wanting to use the other query results elsewhere.

As it is, you could use a for() loop,

 
for($i=0;$i<10;$i+=8) { // start at 0, skip from 0-8 and pick up 9 (counting from 0), stop the loop.
   ... 
}

or something like that, I don’t think that’s quite right, but should get the idea across.

A while loop would do much the same thing; I just tend to think in terms of for loops.

Studying MODX in the desert - http://sottwell.com
Tips and Tricks from the MODX Forums and Slack Channels - http://modxcookbook.com
Join the Slack Community - http://modx.org

151 Posts

Avander_be Reply #3, 19 years, 8 months ago

Found this in the PHP manual:

[tt]<?php
$link = mysql_connect(’localhost’, ’mysql_user’, ’mysql_password’);
if (!$link) {
die(’Could not connect: ’ . mysql_error());
}
$result = mysql_query(’SELECT name FROM work.employee’);
if (!$result) {
die(’Could not query:’ . mysql_error());
}
echo mysql_result($result, 2); // outputs third employee’s name

mysql_close($link);
?>[/tt]

There should be an equivalent in the Modx API.

A thing of beauty is a joy forever ( John Keats)

165 Posts

raaviraaj Reply #4, 19 years, 8 months ago

Thanks guys for all the suggestions.

Quote from: Avander_be at Feb 13, 2007, 02:51 AM

echo mysql_result($result, 2); // outputs third employee’s name

There should be an equivalent in the Modx API.

Dear experts, is there a MODx equivalent as above or is the while loop my best tool.
Raavi

☆ A M B ☆
24,524 Posts

sottwell Reply #5, 19 years, 8 months ago

No, but it won’t hurt to use that php function on the result set from a modx API select.

Studying MODX in the desert - http://sottwell.com
Tips and Tricks from the MODX Forums and Slack Channels - http://modxcookbook.com
Join the Slack Community - http://modx.org

165 Posts

raaviraaj Reply #6, 19 years, 8 months ago

Thanks sotwell. You are really sweet. You’ve guided me a number of times. I appreciate all the effort.

-Raavi