Create a hyperlink in report query

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

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

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

Jerry Kornbluth

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

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

Hi Andreas,

In reportBuilder.php line 217 replace :

<td><?php echo htmlencode($fieldValue); ?></td>
to 
<td><?php echo strip_tags($fieldValue, '<a>'); ?></td>

But, don't forget to make a backup of the file.

Thanks,
Djulia

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

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