Categories > TinyButStrong general >

TbsSql and subblock ?

The forum is closed. Please use Stack Overflow for submitting new questions. Use tags: tinybutstrong , opentbs
By: TomH
Date: 2013-03-18
Time: 18:31

TbsSql and subblock ?

Hello,

Trying to use subblock to list the representatives for each manufacturer

The code (below) produces a result for the first tbssql query - giving me a value for 'mfg_id'
For the second query  - tbssql console does not report an mysql error
But there's no result - It appears the p1 is not being passed to the second query

My trouble shooting so far...
The first query gives a result and has  mfg_id = '4300'

Getting the representatives from the "reps" table requires using an intermediary table "mfgs_reps" with fields rep_id, mfg_id  to link to the "reps" table to get teh reps data

Then if manually I run the second query " SELECT reps.name,  mfgs_reps.rep_id, mfgs_reps.mfg_id  FROM reps,mfgs_reps WHERE mfgs_reps.mfg_id='4300' AND mfgs_reps.rep_id=reps.rep_id  " directly in mysql I do get a valid result

I get
name                    rep_id     mfg_id
Susan Stevens                548       4300


Here's my php and template

// main query
from the form submitted $mfr_name = "acer";
$sql = "SELECT * FROM mfgs WHERE name RLIKE '\[\[:<:\]\]$mfr_name' ORDER BY name";
$result = $Db->GetRows($sql);
$TBS->MergeBlock('mfg',$result);

$sql = "SELECT reps.name, reps.rep_id, mfgs_reps.rep_id, mfgs_reps.mfg_id  FROM reps,mfgs_reps WHERE mfgs_reps.mfg_id='%p1%' AND mfgs_reps.rep_id=reps.rep_id ";
$reps = $Db->GetRows($sql);
$TBS->MergeBlock('sub',$reps);


and template
    <!-- main row -->
    <tr bgcolor='#eeffff'>
    <td align=right valign=top >[mfg.#]</td>
    <td valign=top align=right>[mfg.mfg_id;block=tr;] </td>
    <td valign=top style="padding-left:20px;"> [mfg.company;noerr] </td>    
    <td valign=top style="padding-left:20px;"> [mfg.name;noerr] </td>
    <td valign=top> [mfg.email;ifempty='NO EMAIL';noerr] </td>
    </tr>   
   
    <!-- sub row -->
    <tr bgcolor='#ffeeee'>
    <td align=right valign=top >[sub.#]</td>
    <td valign=top align=right> [sub.rep_id;block=tr;p1=[mfg.mfg_id]] </td>
    <td valign=top style="padding-left:20px;"> [sub.company;noerr] </td>    
    <td valign=top style="padding-left:20px;"> [sub.name;noerr]</td>
    <td valign=top></td>
    </tr>


Anyone see what I'm missing?

Thanks for TBS and TbsSQL every day!
TomH
By: Skrol29
Date: 2013-03-19
Time: 14:28

Re: TbsSql and subblock ?

Hi TomH,

You sub-block is not embedded in the main-block. So When merging the sub-block there is only one, not several.
You should replace
[mfg.mfg_id;block=tr;]
with
[mfg.mfg_id;block=tr+tr;]
By: TomH
Date: 2013-03-20
Time: 15:32

Re: TbsSql and subblock ?

Hi Skrol, thanks for replying

I was unclear previous - I was getting NO subblock result row(s) at all - not even the one row expected without the tr+tr fix

I added the tr+tr  to the 'main' row above - but no subquery executed as before

The main query executes perfectly (see below)

And the TbsSQL Console shows that the p1 is not being passed to the subquery

Sorry the cut and paste is not pretty - but at least you can see the sql for the subquery


[SQL]: SELECT * FROM mfgs WHERE name RLIKE '\[\[:<:\]\]aaa' ORDER BY name

#    mfg_id      name                  address1        address2                     city           state       zip
0    6574           AAA 111 Office Systems    Suite 601    123 Main St    Salem    MA    01970
1    6616           AAA 222 Office Systems                                        Cambridge    MA    02139
2    6636           AAA 3/9/13 NO zip  w    Suite 601    151 Federal Street    Pacoima    CA    91333
3    6634        AAA AAA ZZZ test paid search details                      Salem    MA    01970

[SQL]: SELECT reps.name, reps.rep_id, mfgs_reps.rep_id, mfgs_reps.mfg_id FROM mfgs_reps,reps WHERE (mfg_id='%p1%') AND mfgs_reps.rep_id=reps.rep_id

No data


Any ideas where I can look?
By: TomH
Date: 2013-03-20
Time: 18:53

Re: TbsSql and subblock ?

Hello Skrol29

Maybe I found the issue -

The subquery will only work (for me) by doing the following
This works
$TBS->MergeBlock('sub','tbssql', "SELECT reps.rep_id, reps.name, reps.phone, reps.email, reps.rep_id, mfgs_reps.rep_id, mfgs_reps.mfg_id  FROM reps,mfgs_reps WHERE (mfgs_reps.mfg_id='%p1%') AND mfgs_reps.rep_id=reps.rep_id" );


I was doing standard TbsSQL like below -- but this does NOT work - which surprised me
$sql = "SELECT reps.rep_id, reps.name, reps.phone, reps.email, reps.rep_id, mfgs_reps.rep_id, mfgs_reps.mfg_id  FROM reps,mfgs_reps WHERE  mfgs_reps.rep_id=reps.rep_id AND (mfgs_reps.mfg_id='%p1%')";   
$reps = $Db->GetRows($sql);
$TBS->MergeBlock('sub',$reps);

I went back and tested and the main query can be done using $Db->GetRows() methed  (the sub-query will work)

Of course I can only tell you what happens - i cannot tell why this does not work as expected

Thanks for TBS every day,
TomH
By: Skrol29
Date: 2013-03-20
Time: 23:45

Re: TbsSql and subblock ?

Hi TomH,

Of course, I've missed that.
Sub-blocks with dynamic queries works only if the source is a SQL string, not a PHP array.
This is logical: TBS can apply the criteria only in a the SQL statement, not on a result.
By: TomH
Date: 2013-03-21
Time: 09:43

Re: TbsSql and subblock ?

Hello Skrol,

Thanks for the explicit explanation - I had to think about what you were saying, and it makes sense when you say it that way - even to me ;)

Now I only have to remember. It amazes me how dependent I have become on TbsSQL - the debug console is such a powerful tool for rapid development - thanks again for all the work you put into that.

Cheers, and thanks for TBS every day,
TomH