Grr
Needed to reset the $XXX and $YYY vars BEFORE the for loop...
Anyway, should you have nothing better to do, plz look through and see
if there is a "better" way to do this with possibly less queries made on
the db...
Ta
On Wed, 2003-07-16 at 09:25, Petre Agenbag wrote:
> Hi List.
>
> I cannot see my error:
>
> I have relation tables setup.
>
> main
> identity_name main_type etc etc date_in
> 1 testtype1 x y 2003-06-02
> 2 test2 type2 xx yy 2003-03-11
> 3 test3 type1 xxx yyy 2003-02-02
>
>
> type1
> id_type1 id field1 field2 field3
> 1 1 1st rec (1) 2003-07-07 -00-00
> 2 3 1st rec (3) 2003-07-10 2003-07-12
> 3 1 2nd rec (1) 2003-07-13 -00-00
>
> type2
> id_type2 id field1 field2
> 1 2 1st rec (2) 2003-01-23
> 2 2 2nd rec (2) 2003-07-07
>
> etc...
>
>
> So, what I'm trying to do is this:
>
> On a search page, the user selects a date range to view the records in
> the main table.
>
> What I want to display now on the result page (code below), is something
> like this:
>
> Date Captured NameLast Action Date of Action
> 2003-02-02test3 field3 2003-07-12
> 2003-03-11test2 field2 2003-07-07
> 2003-06-02testfield2 2003-07-13
>
>
> In the above examples, if there are dates as record entries, then the
> field_type is "date". The field_name is the "action" name, like "info
> sent", "contact made" etc, so if there is a non-zero date in the field,
> it means that specific "action" happened that day.
>
>
> So in the code below I tried to
> 1) query the table for all the entries in the data range ( for my
> example, the date range was wide enough to include ALL)
> 2) Make sure that I have the "last" row for the specific entity by
> looking for the max id in the related table matching the main table's
> id.
> 3) Once I know that id, I query the "sub" table for all the fields in
> that row.
>
> Then I tried to run through all the fields in the result set and get the
> "highest" date from that row, else fall back to the original capture
> date_in in the main table (meaning that no "actions" have yet been taken
> (when I enter something in the main table, it automatically enters a new
> row in the $main_type table with default "zero" dates)
>
>
> My problem is somewhere in the code, but I cannot see where (maybe my
> logic sucks with the whole thing?)
>
> It returns the correct stuff for the first entry, but then takes the
> same for the rest (as if it's not going back to the beginning of the
> loop, or not resetting the values)
>
> I use a class that does the connect and querying to the db. I'm 100%
> sure the class is correct, I'm not 100% sure if I USE it correct
> though...
>
>
> Any help appreciated.
>
>
>
>
>
>
> include ("main_class.php");
> $db = new my_db_class;
> $db ->connect("localhost","user","password","db");
> if ($_GET[st] == "date") {
> $sql = "select * from main where (date_in > '$_POST[date_1]' and date_in
> < '$_POST[date_2]') order by entity_name";
> }
> //echo $sql.'';
> $db ->query($sql);
> if ($sql) {
> echo 'Date InNameLast
> ActionDate of last action';
> while ($myrow = mysql_fetch_assoc($db->result)) {
> extract($myrow);
> $sql_search = "select MAX(id_$main_type) as mid from $main_type where
> id = '$id'";
> //echo $sql_search.'';
> $db1 = new my_db_class;
> $db1->query($sql_search);
> $myrow_search = mysql_fetch_assoc($db1->result);
> $pointer = $myrow_search[mid];
> $sql_search_2 = "select * from $main_type where id_$main_type =
> '$pointer'";
> //echo $sql_search_2.'';
> $db2 = new my_db_class;
> $db2->query($sql_search_2);
> $myrow_search_2 = mysql_fetch_assoc($db2->result);
> $fields = mysql_num_fields($db2->result);
> $test_date = "-00-00";
> for ($i=0; $i < $fields; $i++) {
> $type = mysql_field_type($db2->result,$i);
> if ($type == "date") {
> $action_name = mysql_field_name($db2->result,$i);
> $val = $myrow_search_2[$action_name];
> if (($val >= $test_date) && ($val != "-00-00")) {
> $test_date = $val;
> $XXX = $action_name;
> $YYY = $test_date;
> }
> }
> }
> if (!$XXX) {
> $XXX = "Original Capture";
> }
> if (!$YYY) {
> $YYY = $date_in;
> }
> echo ''.$date_in.' href="main_view.php?id='.$id.'">'.$entity_name.''.$XXX.''.$YYY.'';
> if ((mysql_num_rows($db->result)) == "" || (mysql_num_rows($db->result)
> == "0")) {
> echo
