Kin Kin - 7 months ago 14
SQL Question

How can i count loop and set value in a field

I have the following code to transpose 1 row into Multiple row or record to Create BOM Structure for an Assembly Item Some have 3 Component/Item the other have 2,1 or more.

When I run this script It takes the last INSERT Query to create a record if Assembly has only one component . On the attached Image on the first record highlighted in yellow - the bomEntry Field Should show 1 instead of 3 because it has only one Component.

If Assembly have two BOMs, Let say Assembly A has Components A1 and A2. It should be displayed as

Item PartiD bomEntry
A A1 1
A A2 2


Currently when insert my data in a table looks like this.
enter image description here

mysqli_query($con,"INSERT INTO table1 (ItemID,partid,qty,rev,bomEntry) VALUES ($itemid, $bom1,'1','A','1')");
mysqli_query($con,"INSERT INTO table1 (ItemID,partid,qty,rev,bomEntry) VALUES ($itemid, $bom2,'1','A','2')");
mysqli_query($con,"INSERT INTO table1 (ItemID,partid,qty,rev,bomEntry) VALUES ($itemid, $bom3,'1','A','3')");


How can i count Number of Component and Set
bomEntry

Answer

Use a variable that you increment between each INSERT query. Also, use a prepared query instead of substituting variables.

Before inserting each component, check whether the component actually exists in the record.

$stmt = myqsli_prepare($con, "INSERT INTO table1 (ItemID,partid,qty,rev,bomEntry) VALUES (?, ?,'1','A',?)");
mysqli_stmt_bind_param($stmt, "iii", $itemid, $bom, $bomEntry) or die(mysqli_error($con));

$recordd = $tv->search(30008475847254898, 's=2'); 
foreach($recordd as $data2) { 
    $itemid = $data2['fields']['CALC STOCK NO']; 

    $bomEntry = 1;
    if ($data2['fields']['BOM WHEEL PN']) {
        $bom = $data2['fields']['BOM WHEEL PN']; 
        mysqli_stmt_execute($stmt) or die(mysqli_stmt_error($stmt));
        $bomEntry++;
    }
    if ($data2['fields']['BOM TIRE PN']) {
        $bom = $data2['fields']['BOM TIRE PN'];
        mysqli_stmt_execute($stmt) or die(mysqli_stmt_error($stmt));
        $bomEntry++;
    }
    if ($data2['fields']['BOM VALVE PN']) {
        $bom = $data2['fields']['BOM VALVE PN'];
        mysqli_stmt_execute($stmt) or die(mysqli_stmt_error($stmt));
        $bomEntry++;
    }

    echo "<br>";
}