Data displayed in the table view with if statement

If you're a new user of AppGini, feel free to ask general usage questions, or look for answers here.
Post Reply
hgarbe
Veteran Member
Posts: 57
Joined: 2020-01-21 17:35

Data displayed in the table view with if statement

Post by hgarbe » 2020-08-30 20:48

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;
	}

pbottcher
AppGini Super Hero
AppGini Super Hero
Posts: 1635
Joined: 2018-04-01 10:12

Re: Data displayed in the table view with if statement

Post by pbottcher » 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.
Any help offered comes with the best of intentions. Use it at your own risk. In any case, please make a backup of your existing environment before applying any changes.

hgarbe
Veteran Member
Posts: 57
Joined: 2020-01-21 17:35

Re: Data displayed in the table view with if statement

Post by hgarbe » 2020-09-01 23:01

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.

pbottcher
AppGini Super Hero
AppGini Super Hero
Posts: 1635
Joined: 2018-04-01 10:12

Re: Data displayed in the table view with if statement

Post by pbottcher » 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"
Any help offered comes with the best of intentions. Use it at your own risk. In any case, please make a backup of your existing environment before applying any changes.

hgarbe
Veteran Member
Posts: 57
Joined: 2020-01-21 17:35

Re: Data displayed in the table view with if statement

Post by hgarbe » 2020-09-02 09:44

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.
Attachments
sql_error.png
sql_error.png (19.25 KiB) Viewed 4415 times

pbottcher
AppGini Super Hero
AppGini Super Hero
Posts: 1635
Joined: 2018-04-01 10:12

Re: Data displayed in the table view with if statement

Post by pbottcher » 2020-09-02 11:17

Hi,

can you post the complete error message?
Any help offered comes with the best of intentions. Use it at your own risk. In any case, please make a backup of your existing environment before applying any changes.

hgarbe
Veteran Member
Posts: 57
Joined: 2020-01-21 17:35

Re: Data displayed in the table view with if statement

Post by hgarbe » 2020-09-02 11:24

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

pbottcher
AppGini Super Hero
AppGini Super Hero
Posts: 1635
Joined: 2018-04-01 10:12

Re: Data displayed in the table view with if statement

Post by pbottcher » 2020-09-02 11:42

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.
Any help offered comes with the best of intentions. Use it at your own risk. In any case, please make a backup of your existing environment before applying any changes.

hgarbe
Veteran Member
Posts: 57
Joined: 2020-01-21 17:35

Re: Data displayed in the table view with if statement

Post by hgarbe » 2020-09-02 11:57

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

User avatar
jsetzer
AppGini Super Hero
AppGini Super Hero
Posts: 1807
Joined: 2018-07-06 06:03
Location: Kiel, Germany
Contact:

Re: Data displayed in the table view with if statement

Post by jsetzer » 2020-09-02 12:24

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.  ;)
Kind regards,
<js />

My AppGini Blog:
https://appgini.bizzworxx.de/blog

You can help us helping you:
Please always put code fragments inside [code]...[/code] blocks for better readability

AppGini 24.10 Revision 1579 + all AppGini Helper tools

User avatar
jsetzer
AppGini Super Hero
AppGini Super Hero
Posts: 1807
Joined: 2018-07-06 06:03
Location: Kiel, Germany
Contact:

Re: Data displayed in the table view with if statement

Post by jsetzer » 2020-09-02 12:30

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.
Kind regards,
<js />

My AppGini Blog:
https://appgini.bizzworxx.de/blog

You can help us helping you:
Please always put code fragments inside [code]...[/code] blocks for better readability

AppGini 24.10 Revision 1579 + all AppGini Helper tools

User avatar
jsetzer
AppGini Super Hero
AppGini Super Hero
Posts: 1807
Joined: 2018-07-06 06:03
Location: Kiel, Germany
Contact:

Re: Data displayed in the table view with if statement

Post by jsetzer » 2020-09-02 12:31

Last: The error is related to wrong quotes in line 5:
`requirem'
...has to be...
'requirem'
Regards,
Jan
Kind regards,
<js />

My AppGini Blog:
https://appgini.bizzworxx.de/blog

You can help us helping you:
Please always put code fragments inside [code]...[/code] blocks for better readability

AppGini 24.10 Revision 1579 + all AppGini Helper tools

hgarbe
Veteran Member
Posts: 57
Joined: 2020-01-21 17:35

Re: Data displayed in the table view with if statement

Post by hgarbe » 2020-09-02 13:00

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?
Attachments
highlight quotation.jpg
highlight quotation.jpg (52.35 KiB) Viewed 4396 times

User avatar
jsetzer
AppGini Super Hero
AppGini Super Hero
Posts: 1807
Joined: 2018-07-06 06:03
Location: Kiel, Germany
Contact:

Re: Data displayed in the table view with if statement

Post by jsetzer » 2020-09-02 13:16

You are right, I'm sorry for my last, wrong assumption.
Kind regards,
<js />

My AppGini Blog:
https://appgini.bizzworxx.de/blog

You can help us helping you:
Please always put code fragments inside [code]...[/code] blocks for better readability

AppGini 24.10 Revision 1579 + all AppGini Helper tools

pbottcher
AppGini Super Hero
AppGini Super Hero
Posts: 1635
Joined: 2018-04-01 10:12

Re: Data displayed in the table view with if statement

Post by pbottcher » 2020-09-02 13:25

Please replace

END CASE

by

END
Any help offered comes with the best of intentions. Use it at your own risk. In any case, please make a backup of your existing environment before applying any changes.

hgarbe
Veteran Member
Posts: 57
Joined: 2020-01-21 17:35

Re: Data displayed in the table view with if statement

Post by hgarbe » 2020-09-02 13:32

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!

User avatar
jsetzer
AppGini Super Hero
AppGini Super Hero
Posts: 1807
Joined: 2018-07-06 06:03
Location: Kiel, Germany
Contact:

Re: Data displayed in the table view with if statement

Post by jsetzer » 2020-09-02 13:34

;)
chrome_l9WmJHiMpc.png
chrome_l9WmJHiMpc.png (2.42 KiB) Viewed 4381 times
Kind regards,
<js />

My AppGini Blog:
https://appgini.bizzworxx.de/blog

You can help us helping you:
Please always put code fragments inside [code]...[/code] blocks for better readability

AppGini 24.10 Revision 1579 + all AppGini Helper tools

Post Reply