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