codeSeven codeSeven - 5 months ago 16
PHP Question

multi row validation

I'm making barcode system, my validation depends only in one row which supposed to validate depends on the request qty of my item. it works in one item but when i add another item. it will just validate on a wrong way. for ex: request qty is only 10. when the item reaches 10. i can't add another item because it only depends in one row.

barcode image

I have 3 tables to inner join.

| stockrequesttb |
----------------------
|in_code |requestqty |
| | |


| receivetb |
---------------------------------
|itemcode| status |refnumber |
| | | |


| allinvty3 |
----------------------
|in_code | item |
| | |


Here's my code:

<form method ="POST">

<input type="text" name="itemcode">

</form>

<?

if(isset($_POST['itemcode']))
{
$sql="SELECT allinvty3.*, receivetb.* , stockrequesttb.*, count(receivetb.itemcode) as icount from receivetb
INNER JOIN stockrequesttb on receivetb.itemcode =stockrequesttb.in_code
INNER JOIN allinvty3 on receivetb.itemcode = allinvty3.in_code
where receivetb.refnumber='$temp' group by receivetb.itemcode";

$result = $conn->query($sql);


while($row = $result->fetch_assoc())
{
$icount = $row['icount'];
$qty = $row['requestqty'];
$total1 =$row['itemcode'];

}

if($itemcode!== $total1)
{
echo"<script type=\"text/javascript\">alert('No item found'); </script>";
}
?>


This is my problem. i made an sql count which is icount to make sure items are equal in request. what is wrong here it will just depend on one row and i can't add another item if request qty is already equal.

<?

if($icount == $row['requestqty'])

{

echo"<script type=\"text/javascript\">alert('Over'); </script>";
}

else {

/*My INSERT CODE works here*/
}
}
?>

Answer

Try this code

SELECT
    receivetb.itemcode,
    allinvty3.item,
    receivetb.refnumber,
    stockrequesttb.requestqty,
    COUNT(receivetb.itemcode) AS icount
FROM receivetb
    INNER JOIN stockrequesttb ON receivetb.itemcode = stockrequesttb.in_code
    INNER JOIN allinvty3 ON receivetb.itemcode = allinvty3.in_code
WHERE receivetb.refnumber = '$temp'
GROUP BY
    receivetb.itemcode, allinvty3.item,
    receivetb.refnumber,
    stockrequesttb.requestqty

And change this line:

if($icount == $row['requestqty'])

To be:

if($icount >= $qty)

Also you have to change this if($itemcode!== $total1) to be if(!$total1)