I'm having a bit of trouble figuring out how to associate a resource ID to a pagetitle in a snippet.
I'm using a snippet to retrieve the ID from a unique table I've created in my database and I want to associate it to the resource's corresponding pagetitle.
As of right now, what I am retrieving from the database is simply a number, but I want to grab the pagetitle associated with that number.
I have this:
<?php
$result = $modx->db->query( 'SELECT * FROM `referral_leaderboard` WHERE referral_id > 1 ORDER BY count ASC' );
while( $row = $modx->db->getRow( $result ) ) {
echo $row['referral_id'];
}
?>
I tried PHx, but then realized that doesn't work that way. So, instead of echoing the id, how can I associate that to the corresponding pagetitle and display that instead?
-
☆ A M B ☆
- 24,524 Posts
You'll need to do a join to the site_content table to fetch the pagetitle at the same time the data from the referral_leaderboard table. That's a bit esoteric for me; I'm sure there are other more sql-savvy members of the community who can help you with the specific query.
may be something like that can do the trick:
<?php
$sitecontent = $modx->getFullTablename('site_content');
$query = 'SELECT rl.*, sc.pagetitle FROM `referral_leaderboard` rl ';
$query .= 'LEFT JOIN ' . $sitecontent . ' sc ON sc.id = rl.referral_id';
$query .= 'WHERE referral_id > 1 ';
$query .= 'ORDER BY count ASC';
$result = $modx->db->query($query);
while ($row = $modx->db->getRow($result)) {
echo $row['pagetitle'] . '(' . $row['referral_id'] . ')';
}
?>
you should get the pagetitle of the resource which is joined to your table-row by the referral_id.
Or isn't it the field where you have stored the resource-id?
Is there a resource with id 18 ?
what is the structure and the content of your referral_leaderboard-table?
