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 > id entity_name main_type etc etc date_in > 1 test type1 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 0000-00-00 > 2 3 1st rec (3) 2003-07-10 2003-07-12 > 3 1 2nd rec (1) 2003-07-13 0000-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 Name Last Action Date of Action > 2003-02-02 test3 field3 2003-07-12 > 2003-03-11 test2 field2 2003-07-07 > 2003-06-02 test field2 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.'<br>'; > $db ->query($sql); > if ($sql) { > echo '<table><tr bgcolor="#99FF99"><td>Date In</td><td>Name</td><td>Last > Action</td><td>Date of last action</td></tr>'; > 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.'<br>'; > $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.'<br>'; > $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 = "0000-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 != "0000-00-00")) { > $test_date = $val; > $XXX = $action_name; > $YYY = $test_date; > } > } > } > if (!$XXX) { > $XXX = "Original Capture"; > } > if (!$YYY) { > $YYY = $date_in; > } > echo '<tr><td>'.$date_in.'</td><td><a > href="main_view.php?id='.$id.'">'.$entity_name.'</a></td><td>'.$XXX.'</td><td>'.$YYY.'</td></tr>'; > if ((mysql_num_rows($db->result)) == "" || (mysql_num_rows($db->result) > == "0")) { > echo 'No Results Found!<br>'; > } > > } > echo '</table>'; > } > -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php