create table in events using different variables

Scriptcase 8 Discussion
#1

[SIZE=15px]I’m trying to create a new table and display it in a field from events.

It works, but the retrieved data is from different tables, so i want to make it showing the the row only if has data >0.

the description of the type is in different table, so now will retrieve the description from this table:


[SIZE=15px]sc_lookup(get_types_desc, "SELECT description FROM table_desc WHERE type='MYTYPE'"); [/SIZE]

Now will check one table to see if this ‘type’ is available.

[SIZE=15px]

[/SIZE]
sc_lookup, get_type1_cnt("SELECT count(id) FROM my_table WHERE ACTIVE='Y' AND type=1");
[SIZE=15px]sc_lookup, get_type2_cnt("SELECT count(id) FROM my_table WHERE ACTIVE='Y' AND type=2");
sc_lookup, get_type3_cnt("SELECT count(id) FROM my_table WHERE ACTIVE='Y' AND type=3");
sc_lookup, get_type4_cnt("SELECT count(id) FROM my_table WHERE ACTIVE='Y' AND type=4");
sc_lookup, get_type5_cnt("SELECT count(id) FROM my_table WHERE ACTIVE='Y' AND type=5");

[/SIZE]

[SIZE=15px]If it is available (>0) then display it in the table along with description… same this for type=2, type=3…etc.[/SIZE]

Here is what could do assuming to display everything retrieved, even if the count in table ‘my_table’ is 0…



$tbl_header = '<tr>
<th width=220px align=left>Type description</th>
<th width=10px align=left>Active products of this type</th>
</tr>';

$tblx_data = '
<tr>
<td width=200px align=left><font color=#19a0a0>'.{[SIZE=15px]get_types_desc[/SIZE][0][0]}.'</td></font>
<td width=10px align=left><font color=#19a0a0>'.{[SIZE=15px]get_type1_cnt[/SIZE][0][0]}.'</td></font>
</tr><tr>
<td width=200px align=left><font color=#19a0a0>'.{[SIZE=15px]get_types_desc[/SIZE][1][0]}.'</td></font>
<td width=10px align=left><font color=#19a0a0>'.{[SIZE=15px]get_type2_cnt[/SIZE][0][0]}.'</td></font>
</tr><tr>
<td width=200px align=left><font color=#19a0a0>'.{[SIZE=15px]get_types_desc[/SIZE][2][0]}.'</td></font>
<td width=10px align=left><font color=#19a0a0>'.{[SIZE=15px]get_type3_cnt[/SIZE][0][0]}.'</td></font>
</tr><tr> 
<td width=200px align=left><font color=#19a0a0>'.{[SIZE=15px]get_types_desc[/SIZE][3][0]}.'</td></font>
<td width=10px align=left><font color=#19a0a0>'.{[SIZE=15px]get_type4_cnt[/SIZE][0][0]}.'</td></font>
</tr><tr>
<td width=200px align=left><font color=#19a0a0>'.{[SIZE=15px]get_types_desc[/SIZE][4][0]}.'</td></font>
<td width=10px align=left><font color=#19a0a0>'.{[SIZE=15px]get_type5_cnt[/SIZE][0][0]}.'</td></font>
</tr>
';


{table_field}="<table> $tblx_header $tblx_data </table>";

[SIZE=15px]again, this displays all the records, i want to display each row only if its count is >0…

I tried different failure attempts, nothing works so far, assuming need valid if statement within the html

any idea to shortcut this would be appreciated…[/SIZE]

[/SIZE]

#2

Hi Mike,
I’m trying to figure out what you want to do, but I’m not sure. You select a count but in your code it’s not used. But I’m sure that’s because you post snippets.

#3

Hi Albert,
I used the count to display in the table/second column as a result of the type count/quantity
actually later I found a way, although it is not ideal.
Here is what I wanted to reach to:

Type description | Quantity

Type A | 10
Type B | 21
Type C | 2
Type D | 33
Type E | 0
Type F | 0
Type G | 11

Now because these A, B, C…D… M. (limited), are listed in a table in a sequence and I’m counting from another table (a big table) thought if this way to show the description and to show the count directly, so I needed to make a raw for all types <td> shown up there, but still, this will show the 0’s, which I’m trying to avoid… because is a big list and I don’t display Type E = 0…

so I used something like


$tbl_header = '<tr>
<th width=220px align=left>Type description</th>
<th width=10px align=left>Active products of this type</th>
</tr>';

$table_data = "";

      $table_head = "<tr>
        <th width=220px align=left>Type description</th>
        <th width=10px align=left>Active products of this type</th>
      </tr>";
            if ({get_type1_cnt[0][0]} > 0) {
            $table_data = "<tr>
              <td width=2 20 px align=l eft>
                < font color=# 000000>". {get_types_desc[0][0]} . "</font>
              </td>
              <td width=10px align=left>
                <font color=#FF0000>". {get_type1_cnt[0][0]}. "</font>
              </td>
            </tr>";
           }
           
           if ({get_type2_cnt[0][0]} > 0) {
            $table_data .= "<tr>
              <td width=2 20 px align=l eft>
                < font color=# 000000>". {get_types_desc[1][0]} . "</font>
              </td>
              <td width=10px align=left>
                <font color=#FF0000>". {get_type2_cnt[0][0]}. "</font>
              </td>
            </tr>";
           }
           
       {mySCfield}="<table> $table_ head $table_data </table>";

this will cover if first variable ‘Type A’ gives results, but shows error if no result returns…

also this way will have very long code as you can see for each raw will have to repeat if statement… finally the table_data variable will need to be appended…

so I was thinking to use different ++ method but couldn’t (dont know how), ideally to show the rows only if rows only above 0, along with showing the type description for other table…

hope i could explain :smiley:

#4

Well a select count will always return a result, even if it’s 0. You could create an associative array like $MyArray[‘A’]=your count value. To compress code you could consider creating the sql dynamic so that you create an array from ‘A’…‘Z’ with values. Next you could do a foreach($MyArray as $key => $value) where A, B, C will be in the key and the number in the value. Then you can have a simple if $value > 0 to fill your html table.
Does that apply to your question?

#5

Yes, this is exactly what I wanted to say that I don’t know how to do (believe it or not) each time comes to arrays and forech feeling headache and use those long codes to avoid useless tries… it seems become a must now and need to go with it,

now, simply if we count how many types we have in the big table, lets forget the description of the type
something like:
a = 1
b = 12
c = 2
e = 0
f = 10

1- how i can do this simply array/forech… avoiding letter e as it is 0 (i know it is a result) but i want to avoid it
2- once this table is ready to show, need to replace a, b, c with full description from different table (having same a letter in both tables foreign key)

if you have time to explain a little would be helpful, in all cases I’m always thankful to you Albert :slight_smile:

#6

never mind, done it manually row by row, although wanted to learn that forech and arrayes thing :smiley: