how do i properly display tv with @select binding & use docid in sql statement

- 17 Posts
jrad Reply #1, 16 years, 8 months ago
two part question:

i have a custom table and that keeps bid information. i’d like to have a tv of [*bidCount*] on my auction items (these are seperate documents in the doc tree) that binds to a sql query getting count of bids for each item.

1. first question: i’ve created a tv of "bidCount" for a template called "auction item" with a default value of:
```
"@SELECT COUNT( id ) AS cnt FROM  `bids` WHERE doc_id ={{id}} GROUP BY doc_id 
```
//{{id}} should evaluate to the current document id

i realize that the query is stored in the database so wondering if there is a placeholder for [*id*] that will evaluate to the current docs id as each "auction items" page is viewed.

2. when i tested it for a specific id i show on my page "array". i’m needing it evaluate to the actual count of bids as a number b/c i’d like to use this as a sort field in later ditto calls. is this possible?

thanks in advance for any suggestions. this forum is always very helpful

2,310 Posts

bunk58 Reply #2, 16 years, 8 months ago

When I’ve done something similar I’ve had to use @EVAL to then run a DB query.
Try something like this (Untested) :

@EVAL $rs = $modx->db->query('SELECT COUNT( id ) FROM `bids` WHERE doc_id = '. $_REQUEST['id'].' GROUP BY doc_id ');  
$res_t = mysql_query ($rs);
$row_t = mysql_fetch_array ($res_t, MYSQL_NUM);
$output = $row_t[0].;
return $output;