Represent Unique Items in Same Category

Question

I am trying to create a page in which I present an inventory from a MySQL backend. Here is a rough example of the table I have.

id,Server_Type,Status
 1,BL460,Available
 2,BL460,Available
 3,BL460,Reserved
 4,BL460,Reserved
 5,BL460,Reserved
 6,DL360,Reserved
 7,DL360,Reserved
 8,DL360,Reserved
 9,DL580,Available
10,BL460,Available`

To display these items on the page, I need to group each item by "Server_Type." However, simply grouping the item by "Server_Type" in my SQL query is not enough as the unique ID of each item needs to be preserved and represented.

I want to present the choices to the end-user like this on the web page (using above table as our data):

BL460 - Amount: 3 DL360 - Amount: 0 DL580 - Amount: 1

NOTE: I need items with a 0 "Available" amount to display on the page as well.

To the end-user, it does not matter what the unique id of the server is - if he/she chooses 2 BL460s, then during checkout any 2 BL460s in an "Available" status need to be switched over to "Reserved" status - does not matter which ones (I was thinking of just having it do by ASC order).

The issue I keep having is how to present the page in PHP format. I need a way to be able to present each value as an option in the page (even the id, though that will remain hidden on the page since it is not important to the end-use). I tried doing this as an array, but I don't know enough about PHP to use array data and properly present it in the page.

Does anyone know how this could be accomplished?

EDIT: So let me add some more info - I've done this query: SELECT *, COUNT(CASE WHEN Status = 'Available' THEN 1 ELSE NULL END) AS Amount FROM products GROUP BY id

Which presents a table like this:

id,Server_Type,Status,Amount 1,BL460,Available,1 2,BL460,Available,1 3,BL460,Reserved,0 4,BL460,Reserved,0 5,BL460,Reserved,0 6,DL360,Reserved,0 7,DL360,Reserved,0 8,DL360,Reserved,0 9,DL580,Available,1 10,BL460,Available,1

And can write an array like this: $amounts = array(); foreach($results as $entry) {if($entry['Amount'] == 1) {if(isset($amounts[$entry['Server_Type']])) {$amounts[$entry['Server_Type']]++;} else {$amounts[$entry['Server_Type']] = 1;}}};

Which produces a result like this: array(2) { ["BL460"]=> int(3) ["DL580"]=> int(1) }

But that still doesn't give me Server_Types with a 0 amount (though I supposed I could just define another array to give the desired result), nor do I know enough about PHP to present that data in html in the format described above. I suppose my biggest issue is putting an array like that into html format. Does anyone know - or...does anyone know of a better solution?

Answer 1

Build your form in php from a simple query:

"SELECT id, Server_Type, Status FROM table ORDER BY Server_Type, id"

Then, oversimplified without bothering with HTML layout and details:

$parsedItems = [];
foreach ($resultset as $item) {
    if (!array_key_exists($item['Server_Type'], $parsedItems)) {
        // initialize
        $parsedItems[$item['Server_Type']] = [
            "ids" => [],
            "amount" => 0
        ];
    }
    if ($item['Status'] === "Available") {
        $parsedItems[$item['Server_Type']]['ids'][] = $item['id'];
        $parsedItems[$item['Server_Type']]['amount']++;
    }
}
foreach ($parsedItems as $item => $values) {
    if ($values['amount'] > 0) {
        echo "<button onclick=\"reserve('".implode(",", $values['ids'])."')\">Reserve {$item}</button>";
    } else {
        echo "<button>{$item} out of stock!</button>";
    }
}

You must also define a form and a reserve() JS function:

<form name="myForm" id="myForm" action="reserve.php" method="POST">
    <input type="hidden" name="reserve_ids" id="reserve_ids" value="">
</form>
<script type="text/javascript">
    function reserve(ids) {
        document.getElementById('reserve_ids').value = ids;
        document.getElementById('myForm').submit();
    }
</script>

Finally, your reserve.php could be something like that:

$reserve_ids = explode(",", $_POST['reserve_ids']);
// pick the first id, whatever...
$id = mysqli_real_escape_string($dbh, $reserve_ids[0]);

And update your table:

"UPDATE table SET Status='Reserved' WHERE id={$id}"

Please, keep in mind it's a simple example to illustrate a possible solution, prepared statements should be used, input should be filtered, use CSRF protection, etc...

You should also think about concurrency, 2 users can reserve same id with that approach, so you should check on reserve.php that selected id is still available and wrap the whole thing in a MySQL transaction or something similar...

Hope it helps

Answer 2

you can use this sql to get count of available server:

SELECT Server_Type, COUNT(*) as count FROM table Where `Status` = 'Available' GROUP BY Server_Type

but this isn't show 0 available servers, if you want handle it in php:

first, store result of above sql command into $available variable, and then select all server types from table with this:

SELECT Server_Type FROM table GROUP BY Server_Type

and store this results into $server_types variable, then loop in php:

foreach ($server_types as $type){
    $converted_array = array_column($available, 'count', 'Server_Type');
    echo (array_key_exists($type, $converted_array)) ? $converted_array[$type] : 0;
}

note: $converted_array just convert [0] => array('BL460'=>3) into ['BL460'] => 3.