Mysql_query Confusion

[littlegeek]

so i have this query it works perfectly. in fact it works so good that it doubles everything!!!!

if i have one debit transaction and one credit transaction in each separate table it will list it twice on the displayed page so I'll have the same two transactions listed 4 times

 

i am stumped

 

$trans = mysql_query("Select Credits.* , Debits.* FROM Credits, Debits WHERE Credits.account_number AND Debits.account_number = '$accnt' ORDER BY Credits.date AND Debits.date ASC") or die(mysql_error());

while($rows = mysql_fetch_array($trans))
{ 
//Define Vars
$amount = $rows['amount'];
$date = $rows['date'];
$time = $rows['time'];
$ref = $rows['ref_number'];
$amount2 = $rows['Debits.amount'];
$date2 = $rows['Debits.date'];
$time2 = $rows['Debits.time'];
$ref2 = $rows['Debits.ref_number'];

// Display Accounts

echo "<tr>";
echo "<td><input name='transaction' type='radio' value='$ref_number'></input></td>";
echo "<td>$date</td>";
echo "<td>$time</td>";
echo "<td> </td>";
echo "<td> </td>";
echo "<td> </td>";
echo "<td>RI $amount</td>";
echo "<td>RI $amount2</td>";
echo "<td> </td>";
echo "</tr>";
}

 

 

 

 

any incite would be helpful

thanks

[gizmola]

When you do an inner join, you get the product of the number of rows that match on the join criteria.  So if you have 2 credits and 2 debits that all have the same account_number, you will get 2x2 = 4 rows. Add one debit, and you will find you get 6 rows, and so on.  If you just want a list of debits and credits for an account, since you have these in 2 different tables, do 2 queries, and UNION ALL the results.

[littlegeek]

omg thnk you took a min or two to get the syntax right but i got it now i just have to figure if i can define which table to display from

 

i.e.

while($rows = mysql_fetch_array($trans))

{

 

//Define Vars

 

$amount = $rows['Credits.amount'];    // From Credits table

$amount2 = $rows['Debits.amount'];  // From Debits table

 

echo "<tr>";

echo "<td>RI $amount</td>";

echo "<td>RI $amount2</td>";

echo "</tr>";

}

 

 

^ this doesn't work as is

 

thanks again for the union explanation

 

[gizmola]

With SQL you can make an alias for a column.  So the best solution is to make the results of the 2 queries exactly the same.  You can add an artificial column named "type" with a 'C' for Credit and 'D' for debit to identify the source.

 

select amount, 'C' as type, date FROM credit where...
UNION ALL ... etc.
select amount, 'D' as type, date FROM debit where...

[/code]

[littlegeek]

ok so i recreated my query and i now have the following

 

$trans = mysql_query("SELECT *, amount, 'C' as type, date FROM Credits WHERE account_number = '$accnt' UNION SELECT *, amount, 'D' as type, date FROM Debits WHERE  account_number = '$accnt'");

// List trans

while($rows = mysql_fetch_array($trans))
{ 

//Define Vars
$amount = $rows['C'];

 

close?

[gizmola]

List out all the columns you want.  In order to union the names and number of columns from each result must be exactly the same.  You keep using *.  That pulls all the columns from the table. If the names of all the columns in credit and debit are the same then you can use:  SELECT *, 'C' as type  ... and SELECT *, 'D" as type.  If you only really need a few of the columns, then you might want to just list those.  It's up to you. 

 

Otherwise, you are getting close to the answer.  Make sure you use UNION ALL so that no duplicate rows are removed.  I don't think you would have any duplicate rows, but you don't want to take a chance on that.  You can add on your ORDER BY after the 2nd query in the UNION in order to sort the entire result by date or whatever your need be.