Help with loop inside loop and mysql queries

[Petre Agenbag]

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>';
}

[Petre Agenbag]

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>';
> }
>