Trying to merge a Db->GetRow dataset

Forgive me - I am tired and there is something I am not seeing.

The php:
The template:
the var_dump shows that the data is really there in $details:
array(11) { ["orders_status_history_id"]=> string(3) "132" ["orders_id"]=> string(8) "20050027" ["orders_status_id"]=> string(1) "1" ["date_added"]=> string(19) "2011-04-17 12:26:30" ["customer_notified"]=> string(1) "1" ["comments"]=> string(0) "" ["order_num"]=> string(8) "20050027" ["customers_id"]=> string(1) "1" ["customers_name"]=> string(15) "Brandon Sussman" ["delivery_name"]=> string(11) "bos Sussman" ["status"]=> string(7) "Pending" }
Errors look like:
TinyButStrong Error in field [torders.date_added...]: item 'date_added' is not an existing key in the array. This message can be cancelled using parameter 'noerr'.

My error might be a php error, might be a TBS error - help!

I will provide link to site if needed but not in the forum where I cannot remove it later.

this is silly but... try changing the name of the column in the recordset (ie, as `my_date`) just to see..

Forgot to be clear - I am getting an error like that one on every reference to a field in the template, not just date_added.

yah you're tired :)

$TBS->MergeBlock('torders', 'array', $Db->GetRow($sql2), '+');

should be

$TBS->MergeBlock('torders', 'array', $details, '+');
you already fetched it in the line above that sets $details so there may not be another row
;)

Yep - I thought of that too but I decided to pretend I hadn't so I tried it again:

Still produces:

array(11) { ["orders_status_history_id"]=> string(3) "132" ["orders_id"]=> string(8) "20050027" ["orders_status_id"]=> string(1) "1" ["date_added"]=> string(19) "2011-04-17 12:26:30" ["customer_notified"]=> string(1) "1" ["comments"]=> string(0) "" ["order_num"]=> string(8) "20050027" ["customers_id"]=> string(1) "1" ["customers_name"]=> string(15) "Brandon Sussman" ["delivery_name"]=> string(11) "bos Sussman" ["status"]=> string(7) "Pending" }

TinyButStrong Error in field [torders.date_added...]: item 'date_added' is not an existing key in the array. This message can be cancelled using parameter 'noerr'.

TinyButStrong Error in field [torders.comments...]: item 'comments' is not an existing key in the array. This message can be cancelled using parameter 'noerr'.

TinyButStrong Error in field [torders.delivery_name...]: item 'delivery_name' is not an existing key in the array. This message can be cancelled using parameter 'noerr'.

TinyButStrong Error in field [torders.order_num...]: item 'order_num' is not an existing key in the array. This message can be cancelled using parameter 'noerr'.

etc - there are more due to the fact that this is a loop....

The stinker here is that I am only doing this because the SQL to produce only the row set I need is very complex(exceeds my SQL skills), needing a self join - I cannot get to work so I am
doing one select and then iterating on the results to pick up the data I really need.

ok so maybe the corect sql is the way to go no?

what exactly about self joins do you not understand?

try:

$TBS->MergeBlock('torders', 'array', 'details', '+');

I am not sure about this case, but maybe something like
will work ? I remember a strange problem i had myself some time ago, and i resolved it "encapsulating" it into an array to the adecuate level..

p.s. maybe using the plugin "tbssql" (look into http://www.tinybutstrong.com/tools.php) can help simplify Your job..

Yes the SQL solution is the preferred way, though I should be able to do the code that is above!

The problem is complex - I have read up on it.

I have order rows in an order table, each with multiple status rows in a status table that include a date stamp. The two tables join on a typical common field, order_id. There is a
duplicate (unnormalized) field for status in the order table as well. I need to select the latest status row for each order, when one of the columns in the status table is in a value set.
There can be status rows with duplicate values.  I must have 1 and only 1 row per order, the latest (by date of status table row) status.  Sounds like a
database class problem from somewhere other than heaven.

Here id the closest I got:

Clearly, selecting order_id from orders where order_status in (5 ,7, 8 ) and iterating on that, using the sql that started this thread,
gets it for me and frankly, though the self join is more elegant,  performance is not a big issue in this case and I need something
that works and that I can look when sleepy if there is a problem!

how many status values could there be for each order?

@kle_py

That killed the errors but now the call to the template is always outputting the row passed in the first iteration.

It is bizarre - I have a test set up - go to http://dev.signilar.com/shipper/index1.php to see it.  You will see a dump
of the $details array, followed by the template output for the call, formatted as you suggested ( array(yaddayadda ).

It is like the first set of data is stuck.

@lauren

Unpredictable - an order can have multiple status because there might be a problem that moves it from shipit to problem to shipit to problem to shipit to shipped

And I only need the very last by date.  sort of a max(multiples per joined set) kind of thing.

I heard there are ways of using temp tables (that don't scale) to do this but I have nightmares about that sort of thing.

I hate tricky programming (legal but obscure) in production systems.

that may be because your call using "array($details)" is producing an array with one entry, and that entry is your $details array
also i think your data is repeating because of the query you are using

the reason i asked about the number os status's is if it isn't oo many you could use correlated sub-queries to pull them out easier than complex joins ... but i would have to know more to help properly

@lauren:  look at the URI i gave - I think it shows that $details is changing on each iteration.

That is why I started with a "maybe I am missing something" question :)

try it like i have modified it to be here

@lauren:

Not that I know how to do them correctly, but correlated subquery would probably look less bizarre in code as
it would resemble my iteration on the order number, using it to query the status table for max(date).

I know it might sound like a lot of work, but keeping the code very straightforward is the only way to escape permanent
24x7 responsibility for the code :)

Sigh - Every project has one of these...........

correlated sub-queries are *really* easy but can only return one column to be merged into the result set ... that's why i asked how many status values there could be

select order_id,name,(select status from tblOrderStatus where order_id=tblOrders.order_id and status_date > now()) as ostatus
from tblOrders
order by order_id

see how the inner query doesn't have the tblOrders table mentioned in it> that makes the sql parser go look for the outer query to mention it
when it finds it there it makes them auto-sync up on the order_id value so the rows "match" with each other

;)

I altered the template as suggested, included a text string to be sure I really altered it :)

Identical result - you can see it yourself at the same URI.

and removed your "block=begin" and "block=end" stuff?

uuh - I need 4 cols from the table to be subqueried :(

So I will still be stuck doing a second select but I will have exactly the correct record, I think.

Still means I need to make my iterator work.

if you want to email me direct feel free
;)

Re:  "and removed your "block=begin" and "block=end" stuff?"

Yes!  my template consists of exactly and only the lines you suggested :)

Replace your
with

The structure of the array is not the same in the two wases.
If you use MergeBlock with a simple array, then the available columns are only "key" and "val".

I tried that and got errors, leading me to code as follows:
total contents of the template being loaded:
if you go to http://dev.signilar.com/shipper/index1.php you can see the generated page - the $details being passed seems to change but the generated code reflects the first time it was called!

@brandon - i think you *really* need to make sure the query is working right as a single query using either self joins or sub-queries
it will make your life so much easier imo

Brandon:
this is not the same code, now your MergeBlock('torders') is inside a loop. You cannot merge several times the same block.

> I tried that and got errors,
What is the error you've got?

By the way: the last argument '+' in MergeBlock() has no effect.

@Skrol29:  I had several things messed up.  You are partly to blame because TBS works so well that it is so easy that when I start doing screwy things, I am not on my guard to look for the problems that I cause :)

I am literally in tears, thanking you for it. 

THE REST OF YOU:  Everybody was right, I finally debugged my self-join code, which I hate because it is not obvious except to SQL programmers.  Tricky code should be illegal.

The problem was that a field I was using as a comparator (a date/time stamp) must have unique values and it was duplicated in pairs of rows due to a very cranky way that the
application works,using multiple rows to do what should be done in only one row.  Once I changed to the pkey which is (typical) an autoincrement number, the code sailed through.

Case closed, done through, works, I hope nobody but me has to look at that select statement ever again :)