Create a hyperlink in report query

9 posts by 3 authors in: Forums > CMS Builder
Last Post: November 14, 2022   (RSS)

By andreasml - November 11, 2022

Hi,
Can a hyperlink be created in a report to link each query record to the corresponding section record?
Regards,
Andreas Lazaris

By gkornbluth - November 11, 2022

Hi Andreas,

If you mean that you want a link in a front end list page type webpage report to allow you direct access the record in the back end?

If so you can copy the URL from your mult record editor (as if you were going to edit a record) and then substitute <?php echo $record['num'] ?> for the record number at the end.

Like this:

http://yoursite.com/cmsAdmin/admin.php?menu=your_menu&action=edit&num=<?php echo $record['num'] ?>

I you get security errors, you might be able to delete everything before the ? in the URL

and insert it into your foreach loop

Hope that helps...

If not, can you describe what you're looking for in a bit more detail?

Best,

Jerry Kornbluth

The first CMS Builder reference book is now available on-line!







Take advantage of a free 3 month trial subscription, only for CMSB users, at: http://www.thecmsbcookbook.com/trial.php

By andreasml - November 12, 2022

Hi Jerry, 

Thank you. My question regards the Report Builder plugin. I want to add a link for each record. For example, I have created a report with the following SQL query:

SELECT title AS "Surname", first_name AS "Name", age AS "Age", num AS "Number"
FROM cmsb_outpatients_clinic 
LIMIT 1

and the outcome is the following table

Surname	Name	Age	Number
Ευσταθίου	Παναγιώτα	73	1
Query took 0.07 seconds.

I would like to add a hyperlink in the SELECT option of the query, for example a link to the corresponding record something like "https://andreaslazaris.gr/logbook/admin.php?menu=outpatients_clinic&action=view&num=1"

Regards, 

Andreas

By gkornbluth - November 12, 2022

Sorry, I haven't used the report builder plugin.

I'm sure there's someone in the community that can help.

Jerry Kornbluth

The first CMS Builder reference book is now available on-line!







Take advantage of a free 3 month trial subscription, only for CMSB users, at: http://www.thecmsbcookbook.com/trial.php

By Djulia - November 13, 2022

Hi Andreas, 

You can use CONCAT in your query:

SELECT title AS "Surname", first_name AS "Name", age AS "Age", num AS "Number", CONCAT('<a href="index.php?num=', num, '">', first_name, '</a>') AS Link

Thanks,

Djulia

By andreasml - November 13, 2022

Hi Djulia

Thank you for your answer. Indeed, this seems to be a solution, but unfortunately, it does not create a clickable link. You can see the outcome in the following table:

#	Surname	    Name	    Age	Number	Link
1	Ευσταθίου	Παναγιώτα	73	1	<a href="index.php?num=1">Παναγιώτα</a>

The link is correct, but it is not clickable. Do you have any idea how it can be a clickable link?

Regards, 

Andreas

By andreasml - November 14, 2022

Hi Djulia,

Unbelievable!! It works!! Many, many thanks. 

However, I still have a problem. You can check the following SQL query:

SELECT 
   cmsb_outpatients_clinic.first_name, 
   cmsb_outpatients_clinic.phone, 
   cmsb_outpatients_clinic.num,
   CONCAT('<a href="admin.php?menu=outpatients_clinic&action=view&num=', num, '">', 'View', '</a>') AS Link

FROM 
   cmsb_outpatients_clinic INNER JOIN cmsb_visits ON cmsb_outpatients_clinic.num = cmsb_visits.foreignFieldNum

Unfortunately, when it runs, the following message appears:

Unfortunately, when it runs, the following message appears:

I assume the problem is because two sections are used in the query (cmsb_visits and cmsb_outpatients_clinic), and the field numexists in both. Do I need to identify which one of the two numfields I will use? As in the href the section cmsb_outpatients_clinic is used, the numfield should be from this section. I tried to use the following, 

    CONCAT('<a href="admin.php?menu=outpatients_clinic&action=view&num=', cmsb_outpatients_clinic.num=num, '">', 'View', '</a>') AS Link

but it did not work either. 

Do you have a suggestion?

Kind regards, 

Andreas

By andreasml - November 14, 2022

Hi Djulia, 

I think it was too simple.

Just changed num with its full path (cmsb_outpatient_clinin.num), and it worked!

   CONCAT('<a href="admin.php?menu=outpatients_clinic&action=view&num=', cmsb_outpatients_clinic.num, '">', 'View', '</a>') AS Link

Many thanks again, 

Regards, 

Andreas