Hi Guys,
I have a query that runs that selects all matching records according to a user_id. Each record is for a shortened url, something like tinyurl.com.
I have another table that collects data about each time a url is visited. (time, date, location).
Then there is a 3rd table which is for listing which member actually owns each record.
I know DNS is for domain name system, but it's the shorter than 'redirect' and works for me.
dns (table for all redirect entries)
dns_hits (table that counts hits on a particular redirect)
dns_owners (table that relates redirects to members)
What I'm trying to do is query select all records from table 'dns' that match a particular member id. I have a query for that below, and it works. However, I'm also wanting to compile a hit count for each redirect that matches in the query.
So as an example, the query below might grab 6 records for a particular member. This means they have 6 entries in our database for url shorteners (like having 6 tinyurl.com addresses). The query fetches the shortened url (tinyurl.com/12kdf) and the url it forwards to (mylongurl.com/2343/blalal), and it also grabs the unique ID
Record # - Shortened URL - Destination - unique ID
1 - tinyurl.com/sdfdfd - www.mytesturl.com - 456
2 - tinyurl.com/sdadsfdd - www.mytesturl123.com - 458
3 - tinyurl.com/DFDdfd - www.mytesturl456.com - 455
So the query grabs those records and each record contains a unique ID from another table. (as seen above). How can I join that table of hits, use the unique ID to query the table of hits, count the matches, and include that in the same query? Or will I have to do two queries and combine the data somehow?
<CFQUERY NAME="list_member_dns" DATASOURCE="dns">
SELECT
dns.dns_id,
dns.dns_domain,
dns.dns_subfolder,
dns.dns_redirect_url
FROM dns
INNER JOIN
dns_owners on dns.dns_id = dns_owners.dns_id
WHERE dns_owners.dns_member_id = <CFQUERYparam value="#myid#">
</CFQUERY>
I'd want to modify the query above to also join to the dns_hits table and do a count on the number of records that match the unique_id (dns.dns_id).
I have a query that runs that selects all matching records according to a user_id. Each record is for a shortened url, something like tinyurl.com.
I have another table that collects data about each time a url is visited. (time, date, location).
Then there is a 3rd table which is for listing which member actually owns each record.
I know DNS is for domain name system, but it's the shorter than 'redirect' and works for me.
dns (table for all redirect entries)
dns_hits (table that counts hits on a particular redirect)
dns_owners (table that relates redirects to members)
What I'm trying to do is query select all records from table 'dns' that match a particular member id. I have a query for that below, and it works. However, I'm also wanting to compile a hit count for each redirect that matches in the query.
So as an example, the query below might grab 6 records for a particular member. This means they have 6 entries in our database for url shorteners (like having 6 tinyurl.com addresses). The query fetches the shortened url (tinyurl.com/12kdf) and the url it forwards to (mylongurl.com/2343/blalal), and it also grabs the unique ID
Record # - Shortened URL - Destination - unique ID
1 - tinyurl.com/sdfdfd - www.mytesturl.com - 456
2 - tinyurl.com/sdadsfdd - www.mytesturl123.com - 458
3 - tinyurl.com/DFDdfd - www.mytesturl456.com - 455
So the query grabs those records and each record contains a unique ID from another table. (as seen above). How can I join that table of hits, use the unique ID to query the table of hits, count the matches, and include that in the same query? Or will I have to do two queries and combine the data somehow?
<CFQUERY NAME="list_member_dns" DATASOURCE="dns">
SELECT
dns.dns_id,
dns.dns_domain,
dns.dns_subfolder,
dns.dns_redirect_url
FROM dns
INNER JOIN
dns_owners on dns.dns_id = dns_owners.dns_id
WHERE dns_owners.dns_member_id = <CFQUERYparam value="#myid#">
</CFQUERY>
I'd want to modify the query above to also join to the dns_hits table and do a count on the number of records that match the unique_id (dns.dns_id).
Last edited:
