Jump to content

confusing JOIN


funbinod

Recommended Posts

i'm trying to JOIN 2 tables and get their data on a single html table to display customer LEDGER REPORT. but a lot of tries made me hopeless. please create some hope on me.

what i want to do is like ----

 

| date | sales | receipt | balance |

|--------------- |-- -------- |------------- |-------------|

| 2014/03/01 | $5000 | (blank) | $5000 |

| 2014/03/03 | (blank) | $3000 | $2000 |

| 2014/03/25 | (blank) | $1400 | $500 |

--------------------------------------------------------

| TOTAL | $5000 | $3000 | $500 |

--------------------------------------------------------

 

and it orders by DATE. i wish it take all the 'date's from both tables and order data by 'date'. and then in each html row display sales amount and receipt amount alternately according to date.

edit:

explaining more clearly - if the date is from 'sales' field then display sales on sales column(on html table) and leave the receipt column blank and same for receipt.

 

both "sales" and "receipt" tables have 'cid', 'date', amount'

 

i tried all JOIN types. but perhaps i know less, i couldn't make it done.

Edited by funbinod
Link to comment
Share on other sites

yes @davej! there is common 'cid' between them. and i tried FULL OUTER JOIN also....

 

& @justsome guy! i'vent tried union. but i dont think it is the case of union. anyway let me try first

Link to comment
Share on other sites

sorry! i couldn't understand what u meant by PK values. but i can have sales & receipts separately but can not put in a single "HTML" table as i've figured above.

 

cid values are acutally from "customer" table and used to record 'sales' & 'receipt' on separate tables. it auto increments in customer table not in sales and receipts. in them auto increment fields are 'svn' and 'sn' respectively.

 

i can put all(any) values from separate mysql table to separate html table. i can also combine them. but i there is "blank" fields on html table for if the transaction is sales it is displayed in sales column and receipt column is left blank and if the transaction is receipt, the sales column is left blank.

Link to comment
Share on other sites

uhh! sorry! i couldn't understand PK before.

 

these are the columns on sales ---

svn(PK), sn, date, cid(customer id), sid(stock id), qty, rate, less, amt

 

and these are on receipt ---

sn(PK), cid, date, ref, amt

Link to comment
Share on other sites

thank u davej! i'm trying to collect information from both sales & receipt tables to insert it into 'tempTran' table.

 

but i encountered problem looping sum(amt) GROUPed BY 'svn' column..

 

i tried this--

 

$query5 = "SELECT sum(amt) FROM sales WHERE svn=$svn";$result5 = mysqli_query($connect, $query5) or die("Unable to select database: " . mysqli_error($connect));// fetch datawhile ($row5 = mysqli_fetch_array($result5)){$rows[] = $row5;}foreach ($rows as $row5){echo "Rs." . $row5['sum(amt)'] . "<br />";}

it is only giving value from one row, not looping

 

please help me....
Link to comment
Share on other sites

I don't really understand what you are doing. My suggestion was to create a temporary table that would combine the sales and receipt tables.

Link to comment
Share on other sites

i understood ur suggestion. and for that i wish the GROUPed SUM of total 'amt' (Amount) from one particular 'svn' (Sales Voucher Number) to be copied to 'tempTran' (a temporary table). otherwise, i think, the table would be more complex......

Link to comment
Share on other sites

i need it to display a billwise ledger report with receipts ordered by date. and after clicking on the sales voucher number only the bill's detail would be opened. and thank u. i succeeded to make it with ur sugestion to keep both receipts and sales in a single table. but one problem happened. it showed only one receipt row but counted all the rows.

 

i did this ---

$sql1 = "SELECT *, sum(amt) FROM sales where cid='$cid' GROUP BY svn ORDER BY date";$result1 = mysqli_query($connect, $sql1) or die(mysqli_error($connect));while($row1 = mysqli_fetch_array($result1))	{		echo "<tr bgcolor='#009999' align='center'>";		echo "<td>" . $row1['date'] . "</td>";		echo "<td>";		    if ($row1['amt'] != 0) {		    echo "<a href='invreport.php?svn=$row1[svn]' class='slink'>SVN-" . $row1['svn'] . "</a>";		    } else { echo "  "; }		echo "</td>";		echo "<td>";		    if ($row1['ramt'] != 0) {		    echo "<a href='receiptreport.php?svn=$row1[svn]' class='slink'>" . $row1['rref'] . "</a>";		    } else { echo " "; } 		echo "</td>";		    if ($row1['amt'] > 0) {		    echo "<td align='right'>Rs." . number_format(($row1['sum(amt)']), 2, '.', ',') . "</td>";		    } else { echo "<td align='center'>-</td>"; }		    if ($row1['ramt'] > 0) {		    echo "<td align='right'>Rs." . number_format(($row1['ramt']), 2, '.', ',') . "</td>";		    } else { echo "<td align='center'>-</td>"; }		echo "<td align='right'>Rs. "	. number_format(($balance), 2, '.', ',') . "</td>";echo "</tr>";	}

it returned all the SALES records but only ONE receipt record since (according to my db) there are 10 records and the num_rows query also counts 10 nows. since one 'svn' may have more than one row, it needs to be summed and 'rref' has only one row, it need not to be (i thought).

 

and one thing more... i failed to derive balance amount after each row on html table...

Link to comment
Share on other sites

$sql1 = "SELECT *, sum(amt) FROM sales where cid='$cid' GROUP BY svn ORDER BY date";

 

This still doesn't make any sense. If svn is a UNIQUE PK (and not a reoccurring value) then how can it make any sense to GROUP BY svn?

Link to comment
Share on other sites

since one 'svn' may have more than one row,

 

 

these are the columns on sales ---

svn(PK), sn, date, cid(customer id), sid(stock id), qty, rate, less, amt

 

and these are on receipt ---

sn(PK), cid, date, ref, amt

 

Do you understand what a primary key is?

Link to comment
Share on other sites

thank u guys for taking time for me. and sorry for my miss-interpretation. i make php made auto value for 'svn' on 'sales' table so i thought it would be PK. so i think there is no PK value in 'sales' table so it has many rows for 'svn'. sorry for this.

 

and regarding the above question i solved it adding GROUP BY 'rvn' (receipt voucher number) to it

$sql1 = "SELECT *, sum(amt) FROM purchase WHERE vid='$vid' GROUP BY pvn, payvn ORDER BY date";

thank u for ur company.

-------------------------------

and please now help me solve 2 other problems for the same query

 

one is -- for 'php made auto value' i retrived last row value of 'svn' from the database and added by '+1' and stored it to db.

// grab svn    $query2 = "SELECT svn FROM sales ORDER BY svn DESC LIMIT 1";    $result2 = mysqli_query($connect, $query2) or die(mysqli_error($connect));    $svn = mysqli_fetch_array($result2);    $newsvn = ($svn['svn'] + 1); 

but it also has error. after '10' it assumes '9' to be last row.....

and another is

 

Link to comment
Share on other sites

thank u davej for ur suggestion. i started reading database normalization from the link u provided. but sorry, i've not stopped at all. coz i love LEARNING BY DOING. i hate ONLY theory classes. in Nepal, my country, most of the colleges are just for theories so i stopped my university attendance. i'm learning everything practical. learning-trying, learning-trying, and so on.. and it doesn't mean i don't study. i'm doing according to ur recommendation.

 

and good news. i found why 9 was assumed to be the last row even after 10, 11 and so on. its because i had set the 'svn' column as VARCHAR. now i changed it to INT and it worked. thank u for ur company.

 

but i'm still not getting how to calculate balance after each row. it is like we do in excel => "=D3+B4-C4" (add 4th row of column-B to 3rd row of column-D and subtract 4th row of column-C, and keep the loop to last row).. can u please guide me for this.

Edited by funbinod
Link to comment
Share on other sites

hehe!

let me explain u with this --->

 

this is mysql table for customer_id '1' =>

+-----+-----+------------+---------+------+--------|
| svn | cid | date | samt | rvn | ramt |
|-----+------+------------+---------+------+--------|
| 1 | 1 | 2014/3/1 | 5000 | | |
| | 1 | 2014/3/4 | | 2 | 2000 |
| 4 | 1 | 2014/3/7 | 4000 | | |
| | 1 | 2014/3/9 | | 5 | 2000 |
+-----+-------+---------+----------+------+--------|
and in html i wish this to be dispalyed as =>
ledger for => John
+-----+------------+------------+------------+----------+
| svn | rvn | date | sales | ramt | balance |
|-----+--------+------------+--------+-------+-----------+
| 1 | | 2014/3/1 | 5000 | | 5000 |
| | 2 | 2014/3/4 | | 2000 | 3000 |
| 4 | | 2014/3/7 | 4000 | | 7000 |
| | 5 | 2014/3/9 | | 2000 | 5000 |
+----+--------+------------+--------+-------+-----------|
| Total | 9000 | 4000 | 5000 |
------------------------------------------------------------

 

i did all but failed to derive "balance" column.....

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...