funbinod

why these lines are not working?

$sql3 = "SELECT *,sum(amt),sum(qty) FROM purchase WHERE sid=$sid AND pvn!=0 ORDER BY pvn DESC LIMIT 2";$result3 = mysqli_query($connect, $sql3) or die(mysqli_error($connect));$row3 = mysqli_fetch_array($result3);echo $row3['sum(qty)'] . "<br />";$sql4 = "SELECT *,sum(amt),sum(qty) FROM purchase WHERE sid=$sid AND pvn!=0 ORDER BY pvn DESC LIMIT 3";$result4 = mysqli_query($connect, $sql4) or die(mysqli_error($connect));$row4 = mysqli_fetch_array($result4);echo $row4['sum(qty)'] . "<br />";

i'm trying to write some line for stock valuation with FIFO method. in the internet somewhere i found with STACK &/or QUEUE method it might be done. but i couldn't understand how it might be done. can anyone please suggest me if STACK &/or QUEUE are the real solution. if no suggest me something better and if yes help me understand these functions..

tahnks in advance.....

something like that.

for example =>

on page3 , i want to use this---

if ( redirected from page1 ) { retrive some string from page1; }

if ( redirected from page2 ) { retrive some string from page2; }

i printed everything else in php except BALANCE. i'm ok to use SQL cursor. please guide

can information of a page be grabbed clicking on which, current page is opened?

like=>

if i clicked 'page2.php' link on 'page1.php', then i wish to get 'page1.php' information on 'page2.php'......

 

So try...

$sql1 = "SELECT * FROM sales where cid='$cid' ORDER BY date,svn,rn";

but how does that get the "BALANCE" information. my query is ready for anything else. i wish some string to get BALANCE column

hehe!

let me explain u with this --->

this is mysql table for customer_id '1' =>

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

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.

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.....

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...

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......

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 />";}

why the last row of the auto increment value is taken as '9' after reaching '10' ? how can i select the last row value from auto increment field after '9'?

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

and sorry @justsomeguy! i could'nt make it done even using UNION... can u please give some idea for using UNION regarding my problem!?

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.

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

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/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.

how can we change a value in another input field while changing the selection on another field.

like

if any name is selected in "name" field then his/ her stat recorded in database be appeared automatically on another input field or elsewhere.

is it possible?

i tried something on jQuery but could not reach anywhere...

its fine. i solved it myself. but there arouse a question.. can anyone please define it?

i used

$query = "SELECT * FROM tempsales";$result = mysqli_query($connect, $query) or die(mysqli_error($connect));

	$rowcid = mysqli_fetch_array($result);	$query1 = "SELECT * FROM customer WHERE cid = '$rowcid[cid]'";	$result1 = mysqli_query($connect, $query1) or die(mysqli_error($connect));	$rowcname = mysqli_fetch_array($result1);

$cnamequery = "SELECT * FROM tempsales";$cnameresult = mysqli_query($connect, $cnamequery) or die(mysqli_error($connect));$rowcid = mysqli_fetch_array($cnameresult);$query1 = "SELECT * FROM customer WHERE cid = '$rowcid[cid]'";$result1 = mysqli_query($connect, $query1) or die(mysqli_error($connect));$rowcname = mysqli_fetch_array($result1);

can't i use same query to different places and querys?

"escapes" means it doesn't display first row but shows all other rows....

i dunno what happened to these fetching lines. it escapes first row and returns rest of the rows...

$query = "SELECT * FROM tempsales";$result = mysqli_query($connect, $query) or die(mysqli_error($connect));echo "<table border='1' cellpadding='4px'> <tr bgcolor='#009933' align='center'> <td>S.N.: </td> <td>SID: </td> <td>Quantity: </td> <td>Rate: </td> <td>Discount.: </td> <td>Totel Amount: </td> <td>Edit:</td> </tr>";while($row = mysqli_fetch_array($result)) { echo "<tr bgcolor='#009966'>"; echo "<td>" . $row['sn'] . "</td>"; echo "<td>" . $row['sid'] . "</td>"; echo "<td>" . $row['qty'] . "</td>"; echo "<td>" . $row['rate'] . "</td>"; echo "<td>" . $row['less'] . "</td>"; echo "<td>" . $row['amt'] . "</td>"; echo "<td><a href="cedit.php?cid=$row[cid]&m=edit" class=slink>Edit</a></td>"; echo "</tr>";} echo "</table>";

thank u friend!

it did a great help. really glad.

hi all!

is there any idea to reset a mysql table's auto_increment field to restart from "1" after emptying it?

thank u very very much!

i was confused on if it should be applied on classUser.php file. so i failed.

now i tried it on login-process.php file and it is done.....

thank u again....