funbinod

hi all!

please guide me how can i setup table prefix for different users while registering.

like. i want to provide same table structure for all users and i think it would be better to use different table prefixes. please suggest me how can i select table prefix from registration data and write it to the mysql connection script automatically. and how can i setup mysql connection better with prefixes.....

thank u in advance...

ok! now i got the solution. i used

end($total)

to find the last element of the array. and now my complete problem regarding stock(inventory) valuation is now solved. now i can use LIFO method either to calculate stock value along with FIFO.

thank u for ur precious time and support to help me solve this.

moreover i'll seek ur precious feedback and suggestions after i complete this project.

thank u again and again and again....

perhaps u didn't understand my question.

i didn't mean to put all $cqty in an array. i meant to put all $totamt for all $sid.

in spite of this, i think we are on the way to the solution. i changed

$cqtyarr[ $sid ] = $cqty

to

$totamtarr[ $sid ] = $totamt

now the code looks like this ---

$totamtarr[ $sid ] = $totamt;$runSum = 0;foreach($totamtarr as $clamt) {$runSum += $clamt;$total[] = $runSum;}print_r($total);

 

Array ( [0] => 0 ) Array ( [0] => 0 [1] => 0 [2] => 0 ) Array ( [0] => 0 [1] => 0 [2] => 0 [3] => 0 [4] => 0 [5] => 0 )

..............................................................................

.........................................................................

Array ( [0] => 0 [1] => 0 [2] => 0 [3] => 0 [4] => 0 [5] => 0 [6] => 0 [7] => 0 [8] => 0 [9] => 73000 [10] => 0 [11] => 0 [12] => 0 [13] => 73000 [14] => 77750 [15] => 0 [16] => 0 [17] => 0 [18] => 73000 [19] => 77750 [20] => 81750 [21] => 0 [22] => 0 [23] => 0 [24] => 73000 [25] => 77750 [26] => 81750 [27] => 92350 [28] => 0 [29] => 0 [30] => 0 [31] => 73000 [32] => 77750 [33] => 81750 [34] => 92350 [35] => 106350 [36] => 0 [37] => 0 [38] => 0 [39] => 73000 [40] => 77750 [41] => 81750 [42] => 92350 [43] => 106350 [44] => 107050 [45] => 0 [46] => 0 [47] => 0 [48] => 73000 [49] => 77750 [50] => 81750 [51] => 92350 [52] => 106350 [53] => 107050 [54] => 112400 [55] => 0 [56] => 0 [57] => 0 [58] => 73000 [59] => 77750 [60] => 81750 [61] => 92350 [62] => 106350 [63] => 107050 ...............................................................[226] => 135250 [227] => 135250 [228] => 135900 [229] => 153500 [230] => 203900 )

can't i just get the last element(or result) from the array. (in the above quote, i want to get 203900 only, not that huge array..)?

and by running sum

did u mean the process like this---?

$total = array();$runSum = 0;foreach($totamt as $clamt) {	$runSum += $clamt;	$total[] = $runningSum;}print_r($total);

but how can i put the all values of $totamt to an array? like

$totamt = (0, 0, 73000, 4750, 4000, 10600)

or there is mistake or u suggest something else...???

i dont know exactly but i think i had to loop that each time it finds $sid so using require_once made limit only to $sid=1 and using require made it open for all $sid that were found with while loop...

ups sorry! it was my mistake.

i used require_once('classStockReport.php'), thats why it was selecting $sid only once.

i changed it to require('classStockReport.php') and it worked for all $sid.

thank u for ur company.

now will u please guide me how can i sum all $totamt of all $sid ?????

OK, that looks good if it works. The other stuff we have been working earlier on would just go inside this while loop.

sorry i couldn't understand

ups! sorry! but does he edit his html each time he has new book? is so this is not a problem, i think. if he does so, he can write his latest book at the top, or not?

it would be better if u submit the code what u've tried.

u should use 3 columns (as u wish) with column fixed width and using php (if u r using) u should sort the rows by date(if ur table has it) descending (for the latest book to display first) and print that to the left column..

because u've not submitted ur try, it can't be explained clearly...

am i right @davej ???

I don't understand the question. Is the image above showing the problem or the desired solution?

i think the image above is the desired solution but he wants latest books at the top.

like this but i want latest books in top

yes! so i need now is to get $totamt for all $sid and SUM them

if u look at my previous reply (#41) u'll understand it, i hope.

there are different tables to record purchase and sales and debit note and credit note. will making transactions i simultaneously insert opening stock, purchase and credit note to their respective tables and also to another table named 'fifo'. $cqty (closing or current quantity) is calculated from 'stock'(that has opening stock), 'purchase', 'sales', 'cnote' and 'dnote' tables like this--

$sql = mysqli_query($connect, "SELECT null date, item, oamt, oqty, sum(samt)samt, sum(sqty)sqty, sum(pamt)pamt, sum(pqty)pqty, sum(cnamt)cnamt, sum(cnqty)cnqty, sum(damt)damt, sum(dqty)dqty, tr_type, sid, catFROM (SELECT null date, item, oamt, oqty, null samt, null sqty, null pamt, null pqty, null cnamt, null cnqty, null damt, null dqty, null tr_type, st.sid, cat from stock st	JOIN category cat on cat.catid=st.catid GROUP BY st.sidunion allSELECT date, item, null oamt, null oqty, sum(amt)samt, sum(qty)sqty, NULL AS pamt, NULL AS pqty, null cnamt, null cnqty, null damt, null dqty, tr_type, s.sid, cat FROM sales s	JOIN stock st on st.sid=s.sid	JOIN category cat on cat.catid=st.catid GROUP BY s.sidunion allSELECT date, item, null oamt, null oqty, null samt, null sqty, sum(amt)pamt, sum(qty)pqty, null cnamt, null cnqty, null damt, null dqty, tr_type, p.sid, cat FROM purchase p	JOIN stock st on st.sid=p.sid	JOIN category cat on cat.catid=st.catid GROUP BY p.sidunion allSELECT date, item, null oamt, null oqty, null samt, null sqty, null pamt, null pqty, sum(amt)cnamt, sum(qty)cnqty, null damt, null dqty, tr_type, c.sid, cat FROM cnote c	JOIN stock st on st.sid=c.sid	JOIN category cat on cat.catid=st.catid GROUP BY c.sidunion allSELECT date, item, null oamt, null oqty, null samt, null sqty, null pamt, null pqty, null cnamt, null cnqty, sum(amt)damt, sum(qty)dqty, tr_type, d.sid, cat FROM dnote d	JOIN stock st on st.sid=d.sid	JOIN category cat on cat.catid=st.catid GROUP BY d.sid ORDER BY item) AS test	GROUP BY sid") or die("Error:" . mysqli_error($connect));while ($row = mysqli_fetch_array($sql)) {	$inqty = $row['pqty']+$row['cnqty'];	$outqty = $row['sqty']+$row['dqty'];	$cqty = $row['oqty']+$inqty-$outqty;

and after having $cqty for single $sid i calculated $totamt (closing amount for last inward items (purchase & debit note)) i use the method u suggested from 'fifo' table.

if u need here is the schema for the tables..

http://sqlfiddle.com/#!2/da4187

yes! it did the same. and gave the perfect result. thank u

now i got one more confusion.

i've many $sid there in the table. how can i get $totamt for all....?

i can get $cqty for all items ($sid) with the following. but getting $camt( or $ totamt) for all of them is quite difficult

i tried something rubbish on line 65,66 but that is selecting only from $sid=1...

<?phprequire_once('functions.php');$user = new User;if (!$user->isLoggedIn) {	die(header("location: login.php"));}echo "<h2>ALL Items</h2><hr noshade='noshade' size='2px' color='#000000' /><br />";//echo "Total Closing Stock: Rs." .  number_format(($camtsum), 2, '.', ',') . "<br />";$sql = mysqli_query($connect, "SELECT null date, item, oamt, oqty, sum(samt)samt, sum(sqty)sqty, sum(pamt)pamt, sum(pqty)pqty, sum(cnamt)cnamt, sum(cnqty)cnqty, sum(damt)damt, sum(dqty)dqty, tr_type, sid, catFROM (SELECT null date, item, oamt, oqty, null samt, null sqty, null pamt, null pqty, null cnamt, null cnqty, null damt, null dqty, null tr_type, st.sid, cat from stock st	JOIN category cat on cat.catid=st.catid GROUP BY st.sidunion allSELECT date, item, null oamt, null oqty, sum(amt)samt, sum(qty)sqty, NULL AS pamt, NULL AS pqty, null cnamt, null cnqty, null damt, null dqty, tr_type, s.sid, cat FROM sales s	JOIN stock st on st.sid=s.sid	JOIN category cat on cat.catid=st.catid GROUP BY s.sidunion allSELECT date, item, null oamt, null oqty, null samt, null sqty, sum(amt)pamt, sum(qty)pqty, null cnamt, null cnqty, null damt, null dqty, tr_type, p.sid, cat FROM purchase p	JOIN stock st on st.sid=p.sid	JOIN category cat on cat.catid=st.catid GROUP BY p.sidunion allSELECT date, item, null oamt, null oqty, null samt, null sqty, null pamt, null pqty, sum(amt)cnamt, sum(qty)cnqty, null damt, null dqty, tr_type, c.sid, cat FROM cnote c	JOIN stock st on st.sid=c.sid	JOIN category cat on cat.catid=st.catid GROUP BY c.sidunion allSELECT date, item, null oamt, null oqty, null samt, null sqty, null pamt, null pqty, null cnamt, null cnqty, sum(amt)damt, sum(qty)dqty, tr_type, d.sid, cat FROM dnote d	JOIN stock st on st.sid=d.sid	JOIN category cat on cat.catid=st.catid GROUP BY d.sid ORDER BY item) AS test	GROUP BY sid") or die("Error:" . mysqli_error($connect));echo "<table border='1' cellpadding='6' style='font-size:14px' cellspacing='0' width='95%' align='center'>	<tr bgcolor='#009933' align='center' style='font-weight:bold;'>		<th>Item</th>		<th>Category</th>		<th>Opening Quantity</th>		<th>Item Inward</th>		<th>Item Outward</th>		<th>Current Stock</th>		<th>Current Value</th>	</tr>";while ($row = mysqli_fetch_array($sql)) {	$inqty = $row['pqty']+$row['cnqty'];	$outqty = $row['sqty']+$row['dqty'];	$cqty = $row['oqty']+$inqty-$outqty;	echo "	<tr bgcolor='#FFCC99' align='center'>		<th><a href='stockreport.php?sid=$row[sid]' class='index'>".$row['item']."</a></th>		<th>".$row['cat']."</th>		<th>";		if ($row['oqty'] == NULL) {			echo '0';		} else {			echo $row['oqty'];		}	echo "</th>		<th>".$inqty."</th>		<th>".$outqty."</th>		<th>".$cqty."</th>";	$sid = $row['sid'];	require_once('classStockReport.php'); // this is the code that u suggested....	echo "<th>".$totamt."</th>	</tr>";}echo "</table>";mysqli_close($connect);?>

and one thing more--

i removed all conditions relating 'remain' (columns, where clause) and i got the same result. i think i dont need this extra remain column. here is the code i changed--

<?phprequire_once('functions.php');if (!$connect){    $error = mysqli_connect_error();    $errno = mysqli_connect_errno();    echo "$errno: $errorn";    exit();}$sid = 1;  // this is the stock identifier$cqty = 5; // this is the qty sold$totqty = 0;$totamt = 0;$sql = "select qty, amt from fifo where sid=? order by date desc";$stmt = mysqli_prepare($connect, $sql);if (mysqli_stmt_bind_param($stmt, 'i', $sid)){	mysqli_stmt_execute($stmt) or die("Error: " . mysqli_error($connect));	$result = mysqli_stmt_get_result($stmt);	while ($row = mysqli_fetch_array($result, MYSQLI_ASSOC)) {		if ( !is_null($row['qty']) ){			if ($row['qty']>0 && $row['qty']+$totqty > $cqty){				$diff = $cqty - $totqty;				$totqty = $cqty;				$totamt += $diff*$row['amt']/$row['qty'];			// now you must record the remain count = $row->remain-$diff			} else {				$totqty += $row['qty'];				$totamt += $row['qty']*$row['amt']/$row['qty'];			// now you must record the remain=0			}		} else if ($row['qty']+$totqty > $cqty){			$diff = $cqty-$totqty;			$totqty = $cqty;			$totamt += $diff*$row['amt']/$row['qty'];		// now you must record the remain count = $row->qty-$diff		} else {			$totqty += $row['qty'];			$totamt += $row['amt'];		// now you must record the remain=0		}	    echo "qty={$row['qty']} amt={$row['amt']} cqty={$cqty} totqty={$totqty} totamt={$totamt} <br/>";	}	if ($cqty != $totqty) {		echo "error: recorded totqty={$totqty} purchased is insufficient to match cqty={$cqty}";	} else { echo $totamt; }} else {	echo 'error: could not bind';}mysqli_stmt_close($stmt);mysqli_close($connect);?>

it returned the same

 

 

qty=1 amt=45000 cqty=5 totqty=1 totamt=45000qty=5 amt=230000 cqty=5 totqty=5 totamt=229000qty=3 amt=114000 cqty=5 totqty=5 totamt=229000229000

and after this i'll need another guidance about summing all the $totamt for all the $sid from one category

wait wait!!

changing

	while ($row = mysqli_fetch_array($result, MYSQLI_ASSOC) && $totqty<$cqty) {

to

        while ($row = mysqli_fetch_array($result, MYSQLI_ASSOC)) {

yes its the same--

<link rel="stylesheet" type="text/css" href="style.css" /><?phprequire_once('functions.php');if (!$connect){    $error = mysqli_connect_error();    $errno = mysqli_connect_errno();    echo "$errno: $errorn";    exit();}$sid = 1;  // this is the stock identifier$cqty = 7; // this is the qty sold$totqty = 0;$totamt = 0;$sql = "select qty, amt, remain from fifo where sid=? and remain!=0 order by date desc";$stmt = mysqli_prepare($connect, $sql);if (mysqli_stmt_bind_param($stmt, 'i', $sid)){	mysqli_stmt_execute($stmt) or die("Error: " . mysqli_error($connect));	$result = mysqli_stmt_get_result($stmt);	while ($row = mysqli_fetch_array($result, MYSQLI_ASSOC) && $totqty<$cqty) {		if ( !is_null($row['remain']) ){			if ($row['remain']>0 && $row['remain']+$totqty > $cqty){				$diff = $cqty - $totqty;				$totqty = $cqty;				$totamt += $diff*$row['amt']/$row['qty'];			// now you must record the remain count = $row->remain-$diff			} else {				$totqty += $row['remain'];				$totamt += $row['remain']*$row['amt']/$row['qty'];			// now you must record the remain=0			}		} else if ($row['qty']+$totqty > $cqty){			$diff = $cqty-$totqty;			$totqty = $cqty;			$totamt += $diff*$row['amt']/$row['qty'];		// now you must record the remain count = $row->qty-$diff		} else {			$totqty += $row['qty'];			$totamt += $row['amt'];		// now you must record the remain=0		}	    echo "qty={$row['qty']} amt={$row['amt']} remain={$row['remain']} cqty={$cqty} totqty={$totqty} totamt={$totamt} <br/>";	}	if ($cqty != $totqty) {		echo "error: recorded totqty={$totqty} purchased is insufficient to match cqty={$cqty}";	} else { echo $totamt; }} else {	echo 'error: could not bind';}mysqli_stmt_close($stmt);mysqli_close($connect);?>

remain>0 & remain!=0 are, i think, round same conditions. even changing that didn't work...

then i thing on my side also there is all fine.

its still showing

error: recorded qty purchased is insufficient

ok! i'm getting it somewhat. still a bit confusion..

can u explain what is this line conditioning here....

$row['remain']+$totqty > $cqty

& here

elseif ($row['qty']+$totqty > $cqty){

& here

if ($cqty != $totqty) {

actually i'm not understanding the use of $totqty

thats fine. ok! now its showing just the error msg that u set-

error: recorded qty purchased is insufficient 

now i think there is not error in the script and now i should understand the logic u used here. please help me understand the overall process - how is this set to work?

i think one of the methods is using jQuery (if u r fond of. and actually i'm not ). u can append new element with the required fields and loop through the 'X' amount.

Fatal error: Call to undefined function mysqli_stmt_fetch_object() in E:xampphtdocsactest6.php on line 25

moreover: its saying

Fatal error: Only variables can be passed by reference in E:xampphtdocsactest6.php on line 22

for the line

if (mysqli_stmt_bind_param($stmt, 'ii', $sid, 0)){

(sorry i know less about bind param)

thanks for the reply.

but i'm completely beyond understanding what u suggested. sorry! will u please explain the complete process? coz only solving the problem is not my aim. i want to understand it..

i understood that. it can be done in the same table or on a separate table. but what i'm having trouble is doing this with php. i'm done already with just making logic.. but how can i do this with php...?