Join: combining "Hits" extra table with custom table array

- 131 Posts
lancipoos Reply #1, 9 years, 5 months ago
We're using a custom table in Modx to generate an array which we use in a custom manager page. The code below works fine. What We are trying and failing to do is join an extra field into the array from the modx_hits table, which the "Hits" page count extra uses.

In our first custom table, the "JobID" column uses the same value as the modx_hits table "hit_key" column. Where they match, we need to add the modx_hits "hit_count" field value.
```
$results = $modx->query("SELECT * FROM modx_applications_for_jobs WHERE 1");
	if (!is_object($results)) {
		$modx->log(MODX::LOG_LEVEL_ERROR, '[[UserApplications]] Database error 1');
	}else {
		while ($r = $results->fetch(PDO::FETCH_ASSOC)) {
			$arrData = $arrData. "['".$r[recruiter] . "', '".$r[job] . "', '" . $r[jobID] . "', '" . $r[applications] ."'],";
		
		}	
		$arrData = substr($arrData, 0, -1);
		
	}
```
So what we'd like to do is add the modx_hits.hit_count value to the $arrdata array where modx_applications_for_jobs.jobID and modx_hits.hit_key match. Any guidance would be greatly appreciated. Revo 2.2.14 BTW.

131 Posts

lancipoos Reply #2, 9 years, 5 months ago

I seem to be answering my own posts once I've figured it out. In the end, we merged "Hits" custom table with the modx_site_content to output a table with no. of page views plus the resource title. It uses inner join as below. This gives us an array which we can then use to output to a table. I guess you could set placeholders etc instead. When a resource is deleted, it disappears our array.

$hitData;
$results = $modx->query("SELECT modx_site_content.*, modx_hits.* FROM modx_site_content INNER JOIN modx_hits
        ON modx_site_content.id=modx_hits.hit_key WHERE modx_site_content.deleted = 0");
     
         
  if (!is_object($results)) {
   $modx->log(MODX::LOG_LEVEL_ERROR, '[[UserApplications]] Database error 3');
  }else {
   while ($r = $results->fetch(PDO::FETCH_ASSOC)) {
    $hitData = $hitData. "['".$r[pagetitle] . "', '".$r[hit_count] ."'],";
            }
            $hitData = substr($hitData, 0, -1);
        }