funbinod Posted April 30, 2014 Share Posted April 30, 2014 (edited) 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 April 30, 2014 by funbinod Link to comment Share on other sites More sharing options...
justsomeguy Posted April 30, 2014 Share Posted April 30, 2014 It sounds like you want a union rather than a join. Link to comment Share on other sites More sharing options...
davej Posted April 30, 2014 Share Posted April 30, 2014 Are you saying there are no cid values in common between the two tables? Did you try a full outer join? http://www.w3schools.com/sql/sql_join_full.asp Link to comment Share on other sites More sharing options...
funbinod Posted April 30, 2014 Author Share Posted April 30, 2014 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 More sharing options...
davej Posted April 30, 2014 Share Posted April 30, 2014 So you can have a sale without a receipt or a receipt without a sale and the cid values in each table are just surrogate auto-incrementing PK values? Link to comment Share on other sites More sharing options...
funbinod Posted April 30, 2014 Author Share Posted April 30, 2014 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 More sharing options...
funbinod Posted April 30, 2014 Author Share Posted April 30, 2014 and sorry @justsomeguy! i could'nt make it done even using UNION... can u please give some idea for using UNION regarding my problem!? Link to comment Share on other sites More sharing options...
davej Posted April 30, 2014 Share Posted April 30, 2014 Please list all the columns in the sales and receipt tables and identify the Primary Keys (PK). Link to comment Share on other sites More sharing options...
funbinod Posted April 30, 2014 Author Share Posted April 30, 2014 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 More sharing options...
davej Posted April 30, 2014 Share Posted April 30, 2014 As a temporary solution I would suggest creating a temporary table. http://www.w3schools.com/sql/sql_insert_into_select.asp Link to comment Share on other sites More sharing options...
funbinod Posted May 1, 2014 Author Share Posted May 1, 2014 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 More sharing options...
davej Posted May 1, 2014 Share Posted May 1, 2014 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 More sharing options...
funbinod Posted May 1, 2014 Author Share Posted May 1, 2014 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 More sharing options...
davej Posted May 1, 2014 Share Posted May 1, 2014 So in post#11 above why do you expect more than one row? Link to comment Share on other sites More sharing options...
funbinod Posted May 2, 2014 Author Share Posted May 2, 2014 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 More sharing options...
davej Posted May 2, 2014 Share Posted May 2, 2014 $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 More sharing options...
justsomeguy Posted May 2, 2014 Share Posted May 2, 2014 Using group by like that will remove duplicate rows with the same SVN. Link to comment Share on other sites More sharing options...
davej Posted May 2, 2014 Share Posted May 2, 2014 But he says svn is the primary key, so no duplicates. Link to comment Share on other sites More sharing options...
justsomeguy Posted May 2, 2014 Share Posted May 2, 2014 Hmm. since one 'svn' may have more than one row Link to comment Share on other sites More sharing options...
davej Posted May 2, 2014 Share Posted May 2, 2014 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 More sharing options...
funbinod Posted May 3, 2014 Author Share Posted May 3, 2014 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 More sharing options...
davej Posted May 3, 2014 Share Posted May 3, 2014 Every table must have a primary key. Perhaps you need to stop and read up on tables and database normalization before you create a giant mess. http://en.wikipedia.org/wiki/Database_normalization Link to comment Share on other sites More sharing options...
funbinod Posted May 3, 2014 Author Share Posted May 3, 2014 (edited) 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 May 3, 2014 by funbinod Link to comment Share on other sites More sharing options...
davej Posted May 3, 2014 Share Posted May 3, 2014 Only someone who understood your tables could tell you what to do, and you have not clearly explained your tables. Link to comment Share on other sites More sharing options...
funbinod Posted May 3, 2014 Author Share Posted May 3, 2014 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 More sharing options...
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now