Page 1 of 1

Data displayed in the table view with if statement

Posted: 2020-08-30 20:48
by hgarbe
Hello all,

I'm rather new to php. I wanted to modify the Appgini example replacing a rating with a star icon (https://bigprof.com/appgini/tips-and-tu ... eview-data). What I wanted to do is selecting the image with an if/elseif/else Statement.

My code is below. I guess it's not correct comparing it in this way: $ReqStatus = "New" but why? And what would be an appropirate way?
For testing I assigned the Value "New" to $ReqStatus it works. Any help appreciated.

Code: Select all

function itm_requirements_init(&$options, $memberInfo, &$args) {
		// get the original fields array
	$oldFields = $options->QueryFieldsTV;
	
	// loop through all fields
	foreach($oldFields as $field => $title){
		// find the field that we need to customize
		if($field == '`itm_requirements`.`requirement_status`'){
			// apply custom SQL formatting to the field
			$ReqStatus = '`itm_requirements`.`requirement_status`';
			// $ReqStatus = "New";
					
			// the row below seems not to be a correct comparison. But why?
			if ($ReqStatus == "New")  {
    			$modField = "CONCAT('<img src=\"hooks/','icon1','.png\" border=\"0\" />')";
			$newFields[$modField] = $title;
			
			} else {
    			$modField = $ReqStatus;
			$newFields[$modField] = $title;
			}
			

		}else{
			// for other fields, keep them unchanged
			$newFields[$field] = $title;
		}
	}
	
	// now apply the modified fields
	$options->QueryFieldsTV = $newFields;

		return TRUE;
	}

Re: Data displayed in the table view with if statement

Posted: 2020-09-01 10:47
by pbottcher
Hi,

you need to apply the if/else statement to the sql part (=$modField) as you are not reading the value of the field itself, but rather apply a custom sql code to the query for each row.

You may try

Code: Select all

		if($field == '`itm_requirements`.`requirement_status`'){
			// apply custom SQL formatting to the field
			$ReqStatus = '`itm_requirements`.`requirement_status`';
			$modField = "IF(".$ReqStatus."='New',CONCAT('<img src=\"hooks/','icon1','.png\" border=\"0\" />'), ".$ReqStatus.")";
			$newFields[$modField] = $title;
		}else{
			// for other fields, keep them unchanged
			$newFields[$field] = $title;
		}
Code is not tested, so you may need to check.

Re: Data displayed in the table view with if statement

Posted: 2020-09-01 23:01
by hgarbe
pböttcher wrote:
2020-09-01 10:47
Hi,

you need to apply the if/else statement to the sql part (=$modField) as you are not reading the value of the field itself, but rather apply a custom sql code to the query for each row.

You may try

Code: Select all

		if($field == '`itm_requirements`.`requirement_status`'){
			// apply custom SQL formatting to the field
			$ReqStatus = '`itm_requirements`.`requirement_status`';
			$modField = "IF(".$ReqStatus."='New',CONCAT('<img src=\"hooks/','icon1','.png\" border=\"0\" />'), ".$ReqStatus.")";
			$newFields[$modField] = $title;
		}else{
			// for other fields, keep them unchanged
			$newFields[$field] = $title;
		}
Code is not tested, so you may need to check.
Thanks it worked! You really deserve the Suphero title! Just one additional question: when I want to check for different values like $ReqStatus = New or = Closed how would that work. Tried:

$ReqStatus = '`itm_requirements`.`requirement_status`';
$modField = "IF(".$ReqStatus."='New',CONCAT('<img src=\"hooks/','icon1','.png\" border=\"0\" />'), ".$ReqStatus.")";
$modField = "IF(".$ReqStatus."='Closed',CONCAT('<img src=\"hooks/','iconw','.png\" border=\"0\" />'), ".$ReqStatus.")";
$newFields[$modField] = $title;

but not seems not to work. Googled if statements in Variable Statements but didn't got any working solution. Once again would appreciate your help.

Re: Data displayed in the table view with if statement

Posted: 2020-09-02 08:27
by pbottcher
Hi,

glad it works.

In that case use the case statement:

Code: Select all

$modField = "CASE ".$ReqStatus."
    WHEN 'New' THEN CONCAT('<img src=\"hooks/','icon1','.png\" border=\"0\" />')
    WHEN 'Closed' THEN CONCAT('<img src=\"hooks/','icon2','.png\" border=\"0\" />')
    ELSE ".$ReqStatus."
END CASE"

Re: Data displayed in the table view with if statement

Posted: 2020-09-02 09:44
by hgarbe
pböttcher wrote:
2020-09-02 08:27
Hi,

glad it works.

In that case use the case statement:

Code: Select all

$modField = "CASE ".$ReqStatus."
    WHEN 'New' THEN CONCAT('<img src=\"hooks/','icon1','.png\" border=\"0\" />')
    WHEN 'Closed' THEN CONCAT('<img src=\"hooks/','icon2','.png\" border=\"0\" />')
    ELSE ".$ReqStatus."
END CASE"
Causes an SQL Syntax Error. Error Text says in Line 2. First I thought was caused by the missing ";" after END CASE but no its not.

Re: Data displayed in the table view with if statement

Posted: 2020-09-02 11:17
by pbottcher
Hi,

can you post the complete error message?

Re: Data displayed in the table view with if statement

Posted: 2020-09-02 11:24
by hgarbe
This is the complete Error Message. Just copy paste of your suggested code.

You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'WHEN 'New' THEN CONCAT('<img src="hooks/','icon1','.png" border="0" />') ' at line 2
SQL-Anfrage:
SELECT `itm_requirements`.`id` as `id`, IF( CHAR_LENGTH(`itm_project_list1`.`project_id`) || CHAR_LENGTH(`itm_project_list1`.`project_name`), CONCAT_WS('', `itm_project_list1`.`project_id`, ' ', `itm_project_list1`.`project_name`), '') /* Projekt */ as `associated_project`, CASE (`itm_requirements`.`requirement_status`
WHEN 'New' THEN CONCAT('<img src="hooks/','icon1','.png" border="0" />')
WHEN 'Closed' THEN CONCAT('<img src="hooks/','icon2','.png" border="0" />')
ELSE `itm_requirements`.`requirement_status`)
END CASE as `requirement_status`, `itm_requirements`.`requirement_name` as `requirement_name`, `itm_requirements`.`requirement_description` as `requirement_description`, `itm_requirements`.`mantis_reference` as `mantis_reference`, `itm_requirements`.`mantis_reference_link` as `mantis_reference_link`, IF( CHAR_LENGTH(`itm_cycle_planning1`.`cycle_name`), CONCAT_WS('', `itm_cycle_planning1`.`cycle_name`), '') /* Geplanter Cycle */ as `associated_cycle`, IF( CHAR_LENGTH(if(`itm_cycle_planning1`.`cycle_start`,date_format(`itm_cycle_planning1`.`cycle_start`,'%d.%m.%Y %H:%i'),'')), CONCAT_WS('', if(`itm_cycle_planning1`.`cycle_start`,date_format(`itm_cycle_planning1`.`cycle_start`,'%d.%m.%Y %H:%i'),'')), '') /* Cycle Start */ as `associated_cycle_start`, IF( CHAR_LENGTH(if(`itm_cycle_planning1`.`cycle_end`,date_format(`itm_cycle_planning1`.`cycle_end`,'%d.%m.%Y %H:%i'),'')), CONCAT_WS('', if(`itm_cycle_planning1`.`cycle_end`,date_format(`itm_cycle_planning1`.`cycle_end`,'%d.%m.%Y %H:%i'),'')), '') /* Cycle Ende */ as `associated_cycle_end`, `itm_requirements`.`creation_info` as `creation_info`, `itm_requirements`.`edit_info` as `edit_info`, `itm_requirements`.`id` as 'itm_requirements.id' from `itm_requirements` LEFT JOIN `itm_project_list` as itm_project_list1 ON `itm_project_list1`.`id`=`itm_requirements`.`associated_project` LEFT JOIN `itm_cycle_planning` as itm_cycle_planning1 ON `itm_cycle_planning1`.`id`=`itm_requirements`.`associated_cycle` order by 1 desc limit 0,2000

Re: Data displayed in the table view with if statement

Posted: 2020-09-02 11:42
by pbottcher
Hi,

the case statement is surrounded by brackets.

CASE (`itm_requirements`.`requirement_status`
WHEN 'New' THEN CONCAT('<img src="hooks/','icon1','.png" border="0" />')
WHEN 'Closed' THEN CONCAT('<img src="hooks/','icon2','.png" border="0" />')
ELSE `itm_requirements`.`requirement_status`)

it should be

CASE `itm_requirements`.`requirement_status`
WHEN 'New' THEN CONCAT('<img src="hooks/','icon1','.png" border="0" />')
WHEN 'Closed' THEN CONCAT('<img src="hooks/','icon2','.png" border="0" />')
ELSE `itm_requirements`.`requirement_status`

But I cannot see where this comes from. Can you double check that.

Re: Data displayed in the table view with if statement

Posted: 2020-09-02 11:57
by hgarbe
Sorry this was my fault. That was a remain of one of my many own attempts to find the syntax error before posting here again. Syntax error remains but now is pointed to be at line 5.

This is the init Function:

Code: Select all

function itm_requirements_init(&$options, $memberInfo, &$args) {
		// get the original fields array
	$oldFields = $options->QueryFieldsTV;
	
	// loop through all fields
	foreach($oldFields as $field => $title){
		// find the field that we need to customize
		if($field == '`itm_requirements`.`requirement_status`'){
			// apply custom SQL formatting to the field
			$ReqStatus = '`itm_requirements`.`requirement_status`';
			$modField = "CASE ".$ReqStatus."
    			WHEN 'New' THEN CONCAT('<img src=\"hooks/','icon1','.png\" border=\"0\" />')
   			 WHEN 'Closed' THEN CONCAT('<img src=\"hooks/','icon2','.png\" border=\"0\" />')
    			ELSE ".$ReqStatus."
			END CASE";
			
			$newFields[$modField] = $title;
		}else{
			// for other fields, keep them unchanged
			$newFields[$field] = $title;
		}
	}
	
	// now apply the modified fields
	$options->QueryFieldsTV = $newFields;
		return TRUE;
	}
This is the syntax error message after removing the brackets:

You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'CASE as `requirement_status`, `itm_requirements`.`requirement_name` as `requirem' at line 5
SQL-Anfrage:
SELECT `itm_requirements`.`id` as `id`, IF( CHAR_LENGTH(`itm_project_list1`.`project_id`) || CHAR_LENGTH(`itm_project_list1`.`project_name`), CONCAT_WS('', `itm_project_list1`.`project_id`, ' ', `itm_project_list1`.`project_name`), '') /* Projekt */ as `associated_project`, CASE `itm_requirements`.`requirement_status`
WHEN 'New' THEN CONCAT('<img src="hooks/','icon1','.png" border="0" />')
WHEN 'Closed' THEN CONCAT('<img src="hooks/','icon2','.png" border="0" />')
ELSE `itm_requirements`.`requirement_status`
END CASE as `requirement_status`, `itm_requirements`.`requirement_name` as `requirement_name`, `itm_requirements`.`requirement_description` as `requirement_description`, `itm_requirements`.`mantis_reference` as `mantis_reference`, `itm_requirements`.`mantis_reference_link` as `mantis_reference_link`, IF( CHAR_LENGTH(`itm_cycle_planning1`.`cycle_name`), CONCAT_WS('', `itm_cycle_planning1`.`cycle_name`), '') /* Geplanter Cycle */ as `associated_cycle`, IF( CHAR_LENGTH(if(`itm_cycle_planning1`.`cycle_start`,date_format(`itm_cycle_planning1`.`cycle_start`,'%d.%m.%Y %H:%i'),'')), CONCAT_WS('', if(`itm_cycle_planning1`.`cycle_start`,date_format(`itm_cycle_planning1`.`cycle_start`,'%d.%m.%Y %H:%i'),'')), '') /* Cycle Start */ as `associated_cycle_start`, IF( CHAR_LENGTH(if(`itm_cycle_planning1`.`cycle_end`,date_format(`itm_cycle_planning1`.`cycle_end`,'%d.%m.%Y %H:%i'),'')), CONCAT_WS('', if(`itm_cycle_planning1`.`cycle_end`,date_format(`itm_cycle_planning1`.`cycle_end`,'%d.%m.%Y %H:%i'),'')), '') /* Cycle Ende */ as `associated_cycle_end`, `itm_requirements`.`creation_info` as `creation_info`, `itm_requirements`.`edit_info` as `edit_info`, `itm_requirements`.`id` as 'itm_requirements.id' from `itm_requirements` LEFT JOIN `itm_project_list` as itm_project_list1 ON `itm_project_list1`.`id`=`itm_requirements`.`associated_project` LEFT JOIN `itm_cycle_planning` as itm_cycle_planning1 ON `itm_cycle_planning1`.`id`=`itm_requirements`.`associated_cycle` order by 1 desc limit 0,2000

Re: Data displayed in the table view with if statement

Posted: 2020-09-02 12:24
by jsetzer
What about dumping out $modField variable using var_dump($modField); or print_r($modField); and then exit; to see the final SQL statement of that one field.

I'm pretty sure it will be much, much easier to find the mistake and safe you time if you see the final SQL statement of that one field.

Code: Select all

// ...

    // build your SQL here
    $modField = "CASE WHEN ... THEN ... ELSE ... END";

    // just for debugging purposes ------v
    echo "<pre>";
    print_r($modField);
    echo "</pre>";
    exit;
    
// ....
[code]

From my experience it is much easier for narrowing down. You can even use your SQL tool and check this out. Maybe the problem is somewhere else.

Just my 2 cents.  ;)

Re: Data displayed in the table view with if statement

Posted: 2020-09-02 12:30
by jsetzer
Additionally:
Those two <img ... /> images are static. There is no variable part.

So, from my point of view there is no need for using CONCAT in SQL here. It makes it more unreadable and there is an extra chance of breaking your SQL statement. Which usually is not good during debugging.

Just another 2 cents.

Re: Data displayed in the table view with if statement

Posted: 2020-09-02 12:31
by jsetzer
Last: The error is related to wrong quotes in line 5:
`requirem'
...has to be...
'requirem'
Regards,
Jan

Re: Data displayed in the table view with if statement

Posted: 2020-09-02 13:00
by hgarbe
Thank you for your valueable feedback. As I step by step work my way into php and sql this is really appreciated.
But not sure if it's caused by wrong quotes. First this part is code generated by appgini and this here is the referenced part of line 5

Code: Select all

CASE as `requirement_status`, `itm_requirements`.`requirement_name` as `requirement_name`, `itm_requirements`.`requirement_description` as `requirement_description`, 
is this "wrong" quotation not just the way appgini highlights the sql error?

Re: Data displayed in the table view with if statement

Posted: 2020-09-02 13:16
by jsetzer
You are right, I'm sorry for my last, wrong assumption.

Re: Data displayed in the table view with if statement

Posted: 2020-09-02 13:25
by pbottcher
Please replace

END CASE

by

END

Re: Data displayed in the table view with if statement

Posted: 2020-09-02 13:32
by hgarbe
pböttcher you are amazing!
That was the wrong syntax. Thank you so much for your help and the effort you put into it!!!
If this forum would have a like button i would really hit it hard and multiple times!

Re: Data displayed in the table view with if statement

Posted: 2020-09-02 13:34
by jsetzer
;)
chrome_l9WmJHiMpc.png
chrome_l9WmJHiMpc.png (2.42 KiB) Viewed 8671 times