CODEWITHSUNDEEP
phpmysqlarrays
ForgivenIT
ForgivenIT

Reputation: 169

Php/MYSQl to output first and last result of query foreach value of an array?

I am looking for php example on how to output the first and last values of a query for each value of an array..I might be over looking the obvious..but here is what i have.

$userId = $_SESSION['userId'];
$sourceID = $_POST['sourceID'];
ProcessDate= $_POST['processDate'];

$result = $conn->query("SELECT location_id FROM inventory
where source_date = '$sourceID' and source_id = '$sourceID' and created_by= '$userId'");
while($row = $result->fetch_assoc()) {
            $location_id[$x] = $row['location_id'];

    $x++;
}

i would like to take the each value of $location_id and run the queries

$resultF = $conn->query("Select sku from inventory where  location_id="$location_id" Order by sku ASC ");
while($row = $resultF->fetch_assoc()) {
$firstsku= $row['sku']; }           
$resultL = $conn->query("Select sku from inventory where location_id="$id" Order by sku DSC ");
while($row = $resultL->fetch_assoc()) {
    $lastsku= $row['sku'];}

the ouput would be $firstsku,$lastsku, location_id for each location_id

Upvotes: 1

Views: 56

Answers (1)

random_user_name
random_user_name

Reputation: 26170

Because you are using ORDER BY sku, I am inferring that it truly holds a good "sortable" value, and therefore this can be done in one simplified query leveraging GROUP BY combined with MAX and MIN functions:

SELECT location_id, MAX(sku) AS lastsku, MIN(sku) AS firstsku 
    FROM inventory
    WHERE source_date = '$sourceID'
        AND source_id = '$sourceID'
        AND created_by= '$userId' 
    GROUP BY location_id

Upvotes: 2

Related Questions