PHP - combining multiple elements into one
I am trying to create an end user page where I represent the different servers available for registration. The server can have the status "Available" or "Reserved" at any time. I am using MySQL backend. This is how I am doing my request:
SELECT *, COUNT(CASE WHEN Status = 'Available' THEN 1 ELSE NULL END) AS Amount
FROM products GROUP BY id
As a result, I get:
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,Available,1 7,DL360,Reserved,0 8,DL360,Reserved,0
Where Reserved
equals 0 and Available
equals 1. I want the end user to be able to check the server is in state Available
.
To the question: what I want to do on the page is a list of servers on the page in such a way, where Available
equals the number:
BL460 - Amount: 2 DL360 - Amount: 1
How can I get this format in PHP?
source to share
Ok, it would be easier if you do this directly in your SQL query:
SELECT Server_Type, COUNT(*) AS Count FROM products WHERE Status = 'Available' GROUP BY Server_Type
This should give you exactly the table you want.
If you want to do this in PHP, the simplest solution is probably to look at your SQL result and count the number of available servers on Server_Type
an associative array, where Server_Type
is your array:
$amounts = array();
foreach($sql_result as $entry) {
if($entry['Amount'] == 1) {
if(isset($amounts[$entry['Server_Type']])) {
$amounts[$entry['Server_Type']]++;
} else {
$amounts[$entry['Server_Type']] = 1;
}
}
}
echo $amounts;
Edit: to print the values as described in the question, you can use the following piece of code:
foreach($amounts as $name=>$amount) {
echo $name + " - Amount: " + $amount + "<br>";
}
source to share
Another option is to query a crosstab -
SELECT `Status`,
SUM(IF(`Server_Type` = 'BL460' AND `Status` = 'Available', `Amount`, 0)) AS `BL460`,
SUM(IF(`Server_Type` = 'DL360' AND `Status` = 'Available', `Amount`, 0)) AS `DL360`
FROM `products`
GROUP BY `Status`
Your table will look like this:
Status | BL460 | DL360 |
Available | 2 | 1 |
Reserved | 0 | 0 |
Here is an EXAMPLE
It would be even better to turn everything around -
SELECT `server_type`,
SUM(IF(`status` = 'Available', 1, 0)) AS `Available`,
SUM(IF(`status` = 'Reserved', 1, 0)) AS `Reserved`
FROM `servers`
GROUP BY `server_type`;
Which will result in a table that looks like this (based on the data in the fiddle) -
server_type | Available | Reserved
BL460 | 3 | 1
DL360 | 1 | 2
Here's what EXAMPLE
Here I could keep adding servers to the table without worrying about adding them to the query as you would have done in the first query. If you add an additional status, you will have to change the request.
Note. In both cases, there is no need for a column Amount
as the status is considered counted. By placing the load on the database server, it is much easier to output the HTML code as you just start the line by line as usual.
source to share