2 queries populating one dynamic table?

[Tinman]

hi, I'm back but I now know more than I did before.

I am trying to populate a html table via 2 queries from 2 different sql tables.
the 2 sql tables are credits and expenses. They will each have a different number of rows.
I can get a credit, or expense html table working fine independantly.
Trying to combine the two has got me at a loss.

Code:

////Assign variables from the drop down menu selection
$repyear=$_POST['report_year'];
if (isset($repyear) && $repyear !=="") {
$postyear = $repyear;
}
$repmonth=$_POST['report_month'];
if (isset($repmonth) && $repmonth !=="") {
$postmonth = $repmonth;
}

////Query database for results selected by user for specicified time period
$rep_c_query = ("select * from credits where UserID='$owner_rows' and year(date_of_credit)='$postyear' and month(date_of_credit)='$postmonth'");
$rep_c_result = mysql_query($rep_c_query) or die(mysql_error());

$rep_e_query = ("select * from expenses where UserID='$owner_rows' and year(date_purchasedt)='$postyear' and month(date_purchased)='$postmonth'");
$rep_e_result = mysql_query($rep_e_query) or die(mysql_error());


//the below code is what I use to build my "credit" html table and works fine.
//I would like 2 columns(one credit, one expense) but the fields could have a
//different amount of rows. if the credit field has 10 rows and the expense field has
//4 rows, it's ok that the extra 6 expense fields are empty as long as they are built.

//could I possibly do:
WHILE ($credit_rows = mysql_fetch_array($rep_c_result) or $expense_rows = mysql_fetch_array($rep_e_result)) {
//then add an extra <td>$expense_rows</td> in line with the credits?


WHILE ($credit_rows = mysql_fetch_array($rep_c_result)) {

echo "<tr>";
echo "<td height='30' align='left' valign='middle'>".$credit_rows[2]."</td>";
echo "<td height='30' align='left' valign='middle'>"."$".$credit_rows[3]."</td>";
echo "<td height='30' align='left' valign='middle'>".$credit_rows[4]."</td>";
echo "</tr>";

}


thanks, I do appreciate the help and direction.

Tin.

[jamie]

hi tinman,

try getting each of the results into two arrays with the same key (UserID), e.g.

while ($credit_rows = mysql_fetch_array($rep_c_result)) {
$arr_credits[$credit_rows['UserID']] = $credit_rows;
}

while ($expense_rows = mysql_fetch_array($rep_e_result)) {
$arr_expenses[$expense_rows['UserID']] = $expense_rows;
}

then loop through the $arr_credits with a foreach, using the key (UserID) to get the data for the same user's expenses

foreach ($arr_credits as $UserID => $data) {
// echo our data here, you can access the data from
// expenses using $arr_expenses[$UserID]
}

just start with echoing out data first, before you create the table, to make sure you can access all the info you need.

hth

[rocknbil]

This is an inherent behavior in mySQL, and you can do it in one query with a join. You need a common key in both tables, and you have it - UserID.

You need to use the full table prefix to avoid ambiguity between tables. Instead of UserID you use credits.UserID and expenses.UserID, etc.

You don't need concatenation, it can be all one statement

$query = "select * from credits,expenses where";
$query .= " credits.UserID=expenses.UserID"; // The join - NOTE BEGINNING SPACES
$query .= " and credits.UserID='$owner_rows'";
$query .= " and year(credits.date_of_credit)='$postyear'";
$query .= " and credits.month(date_of_credit)=$postmonth'";
$query .= " and year(expenses.date_purchasedt)='$postyear'";
$query .= " and month(expenses.date_purchased)='$postmonth'";
$query .= " order by credits.date_of_credit desc"; // latest first

$results = mysql_query("$query") or die(mysql_error());

Note you do not need to query user id in both tables, because you're joining on that field, and doing a where on credits.UserID='$owner_rows'. expenses.UserID='$owner_rows' is synonymous, try it, you can do either.

Now you may see a new problem, you have one row as

[results from table 1] [results from table 2]

This is where the indexed results from mysql_fetch_array come in very handy. All you need to do is determine the ending index of the first table, and skip user id in the second. So if both tables are 6 columns wide, remembering arrays start at zero,

$second = 6;

while ($row=mysql_fetch_array($results)) { 
 // Just see what happens when you do this next line, you'll like it. :-) 
 $bg = ($bg=='style="background:#fff"')?'style="background:#f5f5f5"':'style="background:#fff"'; 
 echo '<tr>'; 
 for ($i=0;$i<count($row);$i++) { 
 // skip user id in second col 
 if ($i==$second) { continue; } 
 echo "<td $bg>" . $row[$i] . '</td>'; 
 } 
 echo '</tr>'; 
} 

May contain errors, typed on the fly, but that's the logic . . .

[Tinman]

ok,
trouble with the array...

foreach ($arr_credits as $UserID=>$data) {
echo $data[3]."<br />";
}
only echo's the last record under that User, not all the previous records.

?

why won't it echo all the previous records?

thanks

[rocknbil]

This is because here,

$arr_credits[$credit_rows['UserID']] = $credit_rows;

You are storing an array ($credit_rows) in another array ($arr_credits). You have **something** like this:

Array(
123 =>
[value 1, value 2, value 3],
124 =>
[value 1, value 2, value 3],
125 =>
[value 1, value 2, value 3]
);

So if you use this approach, you'd want to loop through $data:

foreach ($arr_credits as $UserID => $data) {  
 echo "User $UserID "; 
 for ($j=0;$j<count($data);$j++) { 
 echo $data[$j] . "\n"; 
 } 
 echo "<br>\n"; 
} 

For that answer, I ask you try using the mySQL join method, it will give you much more power and control in searching and sorting. :-)

[Tinman]

Thanks Rocknbil,
I have been working several days on this.

I ended up using your original suggestion (the table join with all the query statements)....
This does achieve reading the 2 sql tables and formats them in a html table.

With one problem..
currently my sql expense table is 10 rows
my sql credits table has 2 rows.
I am getting repeating rows in the output..

credits | expenses
paycheque | dog food
paycheque | gas
lotto winnings | dog food

looking at the while statemnet..

while ($row=mysql_fetch_array($results))
echo '<tr>';
for ($i=0;$i<count($row);$i++) {
if ($i==$second) { continue; }
echo '<td>' . $row[$i] . '</td>';
}
echo '</tr>';
}
it seems to make sense.
I have no idea why it's repeating rows then moving on to the next row.
In one table, during a loop the pointer just moves on to the next row.
Not sure what's going on here.

Thanks Rocknbil,

tin

[Readie]

for ($i=0;$i<count($row);$i++) {

I'm too tired to try thinking about your problem Tinman, sorry, but I will point out that the above is inefficient coding. The count() function is being called for every iteration of the for() loop. Declare it as a variable instead, like so:

$count = count($row);
for ($i = 0; $i < $count; $i++) { 

Should make your script run a bit faster.

[Tinman]

Thanks for the tip Readie.

It is faster.

cheers
tin

[rocknbil]

The count() function is being called for every iteration of the for() loop

Busted. :-)

But it still calls count() if it's inside the loop . . . you could do

$count = ($count > 0)?$count:count($row);

Which should only call count the first time. Or alternatively, if you know the length of the array, just declare it:

$cols = 5;

for ($i=0;$i<$cols;$i++) {
if ($i==$second) { continue; }

But if you don't know the length of the expected array, you need something dynamic.

I'm not following on the repeating rows, those rows look unique? You could use the distinct keyword . . .

select distinct(fieldname),fieildname2,fieldname3....

instead of *.

[Tinman]

Thanks Rocknbil,

cols = 14; //I have 7 fields in each sql table (credits and expenses)

for ($i=0;$i<$cols;$i++) {
if ($i==$second) { continue; }

still get repeated rows. Each of the rows have unique id's, so when I echo the rows I can tell they are repeating.

credits.....amount....uniqueID...expenses....amount...uniqueID
paycheque...$20.......1..........dog food......$50........1..//first record of each table and is correct
paycheque...$20.......1..........gas...........$200.......2..//credits repeats expenses is correct
paycheque...$20.......1..........gum...........$100.......7..//credits repeats expenses is correct
winnings....$10.......2..........dog food......$50........1..//credits is correct expenses repeats
winnings....$10.......2..........gas...........$200.......2..//credits repeats expenses repeats
winnings....$10.......2..........gum...........$100.......7..//credits repeats expenses repeats
inheritance.$30......11..........dog food......$50........1..// credits is correct expenses repeats
inheritance.$30......11..........gas...........$200.......2..// credits repeats expenses repeats
inheritance.$30......11..........gum...........$100.......7..// credits repeats expenses repeats
..
etc etc until end of loop

Now if you notice, 3 seems to be the number of times it repeats.
I suspect (since currently the expense sql table has 3 rows for this user) that if I add a fourth record in the sql expense table for this user, things will start repeating 4 times.



the "unique ID" field is actually an auto increment for both sql tables.
so in expense table, gum is the actual 7th row in the table. This is because rows 3,4,5,6 are used by a different users and do not show up as a result of the query conditions


The above example is the actual way it is repeating.
it repeats paycheque 3 times before showing "winnings" as the next row
the sql credits table currently has 6 rows, while expenses has 3 rows. (for this user)
some fields are empty in certain rows (not shown in my illustration) but that does not seem to pose a problem when echoing out the rows.
It simply echos an empty <td></td> and moves on, to the next <td> in the row.
So it seems to work well except for the repeating rows.

I have no idea why it's repeating.

thanks again
tin

[rocknbil]

Ahh . . . yeah I see . . . well this many not be possible to get these in rows like this without duplicate values in one or the other, I'll explain why. This one set the stage so to speak, so I followed . . .

I am trying to populate a html table via 2 queries from 2 different sql tables.

What you have here are two different tables, and I mean in terms of the way they'd display, not the database tables themselves. I don't think you'll ever get a joined row that belongs on the same row without duplicates.

True they have a common field to join on, you can see that, but the data from the two tables does not intersect, it doesn't belong in the same row unless the data is related in some other way. For example, for each paycheck there is an expense related to that paycheck.

Here is **why** it's happening. Let's take a simplified example.

credits
UserID|credit|amount
1234|paycheck|50

UserID|expenses|anount
1234|gas|200
1234|gum|100
1234|dog food|50

So this will indeed generate three rows, because, well, there are three matching records in the join:

select * from table where tablea.UserID=tableb.UserID

1234|paycheck|50|1234|gas|200
1234|paycheck|50|1234|gum|100
1234|paycheck|50|1234|dog food|50

The problem will inverse itself if you have say, 3 records in the first table an one in the second.

What you can do, if this is more or less doing what you want except for the duplicates, is only print the values if they are not the same as the previous.

$col_1 = ($col_1!=$row[0])?$row[0]:'&nbsp;';
$col_2 = ($col_2!=$row[1])?$row[1]:'&nbsp;';
$col_3 = ($col_3!=$row[2])?$row[2]:'&nbsp;';
echo "<td>$col_1</td><td>$col_2</td><td>$col_3</td>";

Kind of a home grown way to do it, but in cases like this you might have to resort to that, in order to get varying numbers of records for the same day in one set of rows.

[Tinman]

Thanks yet again Rocknbil,
I will try that and see if it works.

cheers
tin