Jump to content

Ronel

Members
 View Profile  See their activity

  • Posts

    43

  • Joined

  • Last visited

Ronel's Achievements

Regular Member

Regular Member (3/5)

0

Reputation

1

Community Answers

  1. Ronel
    how do i put several WHERE conditions together it is not working or may be i am not aware of other logic please show some light if you can?
  2. Ronel
    SELECT * FROM History WHERE entrydate BETWEEN 'from_date' AND 'to_date'; SELECT `id`,`itemname`, `poqty`, `entrydate`,`date_encode` FROM history WHERE action = 'Outgoing Record Recorded' AND interface = 'Outgoing Stock';
  3. Ronel
    yes below image is what i got after running the query in my database. But how do put the itemname row on same line ? it has several entries with same itemnames?
  4. Ronel
    SELECT `itemname`,`poqty` FROM `history` WHERE action = 'Added qty' SELECT `received_qty` FROM `history` WHERE action = 'Stock Received' SELECT `outgoing_qty` FROM `history` WHERE action = 'Outgoing Record Recorded' How do i query these lines in one single line someone help please?? Below is my image for database history.
  5. Ronel
    okay i have went through the reference manual and solved it sir
  6. Ronel
    $outgoing_date = "CURRENT_DATE()"; $outgoing_date = date("Y-m-d", strtotime($_POST['outgoing_date'])); HOW ABOUT PASSING IT THROUGH THIS? THEN I NEED TO PASS IT THROUGH INPUT TYPE WHICH IS INSIDE TD TAG
  7. Ronel
    <td colspan="2"><input type="date" name="date_encode" style="width:100%" value="'.$date_encode.'" /></td>
  8. Ronel
    Sir the problem is all i wanted was update the datas being fetched from database and update it like remarks and stockqty from stock and stock_orders
  9. Ronel
    okay thanks for the solution
  10. Ronel
    sir i want to update my tables i have tried using these codes but not being able to update! i have two tables stock and stock_orders can you help me
  11. Ronel
    <?php $con = new mysqli("localhost","root","","loginsystem"); $errors = array(); if($_SERVER["REQUEST_METHOD"] == "POST" && isset($_POST['updatestock'])): /* echo "<pre>"; print_r($_POST); echo "</pre>"; */ $fields = array( "itemname" => "Item Name" ,"purchaseid" => "Purchase ID" ,"poqty" => "Purchase Order QTY" ); foreach($fields as $field => $label): if(empty($_POST[$field])): array_push($errors,"Please Enter ".$label); endif; endforeach; if(empty($errors)): $sqlstockid = "SELECT `id` FROM `stock_orders` WHERE stockid = ? LIMIT 1"; $querystockid = $con->prepare($sqlstockid); $querystockid->bind_param("i", $_POST['stockid']); $querystockid->execute(); $resultstockid = $querystockid->get_result(); $rowstockid = $resultstockid->fetch_assoc(); if(!empty($rowstockid)){ array_push($errors,"This Purchase ID already exists"); } endif; if(empty($errors)): $today = date('Y-m-d'); $sqlstockorder = "INSERT INTO `stock_orders`(`id`, `stockid`, `poqty`, `remarks`) VALUES (?,?,?,?)"; $querystockorder = $con->prepare($sqlstockorder); $querystockorder->bind_param("iiis", $_POST['id'], $_POST['stockid'], $_POST['poqty'], $remarks); $querystockorder->execute(); $sqlstock = "UPDATE `stock` SET `stockqty` = ABS(COALESCE(stockqty,0)+?) WHERE `id` = ?"; $querystock = $con->prepare($sqlstock); $querystock->bind_param("ii", $_POST['poqty'], $_POST['id']); $querystock->execute(); $num = $con -> affected_rows; $message = (!empty($num) ? "Stock Updated" : 'No Changes Made'); header("refresh: 3; URL=edit.php"); endif; endif; $sql = "SELECT `itemname` FROM `stock`"; $res = mysqli_query($con, $sql); ?> <!DOCTYPE html> <html> <head> <title></title> <script type="text/javascript" src="fetch.js"></script> <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.1.0/jquery.min.js"></script> <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.0.2/dist/css/bootstrap.min.css" rel="stylesheet"> <script src="https://cdn.jsdelivr.net/npm/bootstrap@5.0.2/dist/js/bootstrap.bundle.min.js"></script> </head> <body> <?php if(!empty($message)): echo '<div style="width:500px; text-align: center; margin:20px auto;">'."\r"; echo '<span style="color:green"><b>'.$message.'</b></span></br >'."\r"; echo '</div>'."\r"; endif; if(!empty($errors)): echo '<div style="width:500px; text-align: center; margin:20px auto;">'."\r"; foreach($errors as $error): echo '<span style="color:red">'.$error.'</span></br >'."\r"; endforeach; echo '</div>'."\r"; endif; if($_SERVER["REQUEST_METHOD"] == "POST" && isset($_POST['itemname'])){ echo '<form action="" method="post"> <table class="table table-bordered"> <thead class="table-primary" style="white-space:nowrap;"> <th>Stock ID</th> <th>Item Name</th> <th>Stock Quantity</th> <th>Remarks</th> <th>Update Stocks</th> </thead>'."\r"; $sql = "select `id`, `itemname` from stock where itemname = ? "; $query = $con->prepare($sql); $query->bind_param("s",$_POST['itemname']); $query->execute(); $result = $query->get_result(); while($rows = $result->fetch_assoc()){ //$purchaseid = (!empty($_POST['purchaseid']) ? $_POST['purchaseid'] : ''); //$poqty = (!empty($_POST['poqty']) ? $_POST['poqty'] : ''); echo '<tr> <td><input style="width:100%" type="text" name="id" value="'.$rows['id'].'" readonly /></td> <td><input style="width:100%" type="text" name="itemname" value="'.$rows['itemname'].'" readonly /></td> <td><input style="width:100%" type="text" name="stockqty" value="'.$stockqty.'" /></td> <td><input style="width:100%" type="text" name="remarks" value="'.$remarks.'" /></td> <td><input style="width:100%" type="submit" name="updatestock" value="Update" /></td> </tr>'."\r"; } echo '</table> </form>'."\r"; }else{ echo '<b class="d-inline p-2 bg-primary text-white">Update Stock Here</b><hr> <b>Select Item to update :</b> <select id="itemname" onchange="selectItem()"> <option value="Select Item here" selected>Select Item here</option>'."\r"; while ($rows = mysqli_fetch_array($res)){ echo '<option value="'.$rows['itemname'].'">'.$rows['itemname'].'</option>'."\r"; } echo '</select><br><br>'."\r"; echo '<div id="ans"></div>'; } ?> <script type="text/javascript"> function selectItem(){ var x = document.getElementById("itemname").value; $.ajax({ url:"edit.php", method: "POST", data:{ itemname : x }, success:function(DataView){ $("#ans").html(DataView); } }); } </script> </body> </html> below is my two different tables stock and stock_orders
  12. Ronel
    ok sir can you help me edit my items i am still stuck here!!! this is what ia was trying to do but cannot get my head around!! <?php $con = new mysqli("localhost","root","","loginsystem"); $errors = array(); if($_SERVER["REQUEST_METHOD"] == "POST" && isset($_POST['updatestock'])): /* echo "<pre>"; print_r($_POST); echo "</pre>"; */ $fields = array( "itemname" => "Item Name" ,"purchaseid" => "Purchase ID" ,"poqty" => "Purchase Order QTY" ); foreach($fields as $field => $label): if(empty($_POST[$field])): array_push($errors,"Please Enter ".$label); endif; endforeach; //if(empty($errors)): //$sqlpurchaseid = "SELECT `id` FROM `stock_orders` WHERE purchaseid = ? LIMIT 1"; //$querypurchaseid = $con->prepare($sqlpurchaseid); //$querypurchaseid->bind_param("i", $_POST['purchaseid']); //$querypurchaseid->execute(); //$resultpurchaseid = $querypurchaseid->get_result(); //$rowpurchaseid = $resultpurchaseid->fetch_assoc(); //if(!empty($rowpurchaseid)){ // array_push($errors,"This Purchase ID already exists"); //} //endif; if(empty($errors)): $today = date('Y-m-d'); $sqlstockorder = "INSERT INTO `stock_orders`(`stock_id`, `purchaseid`, `poqty`, `entrydate`) VALUES (?,?,?,?)"; $querystockorder = $con->prepare($sqlstockorder); $querystockorder->bind_param("iiis", $_POST['id'], $_POST['purchaseid'], $_POST['poqty'], $today); $querystockorder->execute(); $sqlstock = "UPDATE `stock` SET `stockqty` = ABS(COALESCE(stockqty,0)+?) WHERE `id` = ?"; $querystock = $con->prepare($sqlstock); $querystock->bind_param("ii", $_POST['poqty'], $_POST['id']); $querystock->execute(); $num = $con -> affected_rows; $message = (!empty($num) ? "Stock Updated" : 'No Changes Made'); header("refresh: 3; URL=edit.php"); endif; endif; $sql = "SELECT `itemname` FROM `stock`"; $res = mysqli_query($con, $sql); ?> <!DOCTYPE html> <html> <head> <title></title> <script type="text/javascript" src="fetch.js"></script> <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.1.0/jquery.min.js"></script> <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.0.2/dist/css/bootstrap.min.css" rel="stylesheet"> <script src="https://cdn.jsdelivr.net/npm/bootstrap@5.0.2/dist/js/bootstrap.bundle.min.js"></script> </head> <body> <?php if(!empty($message)): echo '<div style="width:500px; text-align: center; margin:20px auto;">'."\r"; echo '<span style="color:green"><b>'.$message.'</b></span></br >'."\r"; echo '</div>'."\r"; endif; if(!empty($errors)): echo '<div style="width:500px; text-align: center; margin:20px auto;">'."\r"; foreach($errors as $error): echo '<span style="color:red">'.$error.'</span></br >'."\r"; endforeach; echo '</div>'."\r"; endif; if($_SERVER["REQUEST_METHOD"] == "POST" && isset($_POST['itemname'])){ echo '<form action="" method="post"> <table class="table table-bordered"> <thead class="table-primary" style="white-space:nowrap;"> <th>Stock ID</th> <th>Item Name</th> <th>Purchase ID</th> <th>P.O. Quantity</th> <th>Update Stocks</th> </thead>'."\r"; $sql = "select `id`, `itemname` from stock where itemname = ? "; $query = $con->prepare($sql); $query->bind_param("s",$_POST['itemname']); $query->execute(); $result = $query->get_result(); while($rows = $result->fetch_assoc()){ $purchaseid = (!empty($_POST['purchaseid']) ? $_POST['purchaseid'] : ''); $poqty = (!empty($_POST['poqty']) ? $_POST['poqty'] : ''); echo '<tr> <td><input style="width:100%" type="text" name="id" value="'.$rows['id'].'" readonly /></td> <td><input style="width:100%" type="text" name="itemname" value="'.$rows['itemname'].'" readonly /></td> <td><input style="width:100%" type="text" name="purchaseid" value="'.$purchaseid.'" /></td> <td><input style="width:100%" type="text" name="poqty" value="'.$poqty.'" /></td> <td><input style="width:100%" type="submit" name="updatestock" value="Update" /></td> </tr>'."\r"; } echo '</table> </form>'."\r"; }else{ echo '<b class="d-inline p-2 bg-primary text-white">Update Stock Here</b><hr> <b>Select Item to update :</b> <select id="itemname" onchange="selectItem()"> <option value="Select Item here" selected>Select Item here</option>'."\r"; while ($rows = mysqli_fetch_array($res)){ echo '<option value="'.$rows['itemname'].'">'.$rows['itemname'].'</option>'."\r"; } echo '</select><br><br>'."\r"; echo '<div id="ans"></div>'; } ?> <script type="text/javascript"> function selectItem(){ var x = document.getElementById("itemname").value; $.ajax({ url:"edit.php", method: "POST", data:{ itemname : x }, success:function(DataView){ $("#ans").html(DataView); } }); } </script> </body> </html> below are my two tables
  13. Ronel
    thanks a lot
  14. Ronel
    okay thanks alot sir finally found this <td><?php echo $row['ponum']?></td> <td><?php echo $row['itemname']?></td> <td><?php echo $row['total_qty']?></td> <td><?php echo $row['status']?></td>
  15. Ronel
    Sir getting confused what i have done wrong $query = mysqli_query($conn, "select ponum, status, itemname, SUM(stockqty) as total_qty from purchaseorder inner join stock on purchaseorder.id=stock.purchaseid group by itemname"); <td><?php echo $row['ponum']?></td> <td><?php echo $row['itemname']?></td> <td><?php echo $row['0']?></td> <td><?php echo $row['status']?></td> Below is the result
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.