CODEWITHSUNDEEP
phpmysqlsorting
Wes
Wes

Reputation: 764

PHP - Combining Multiple Items Into One

I am attempting to create an end-user page where I present different servers that are available to check-out. A server at any given time can have either an "Available" status or a "Reserved" status. I'm using a MySQL backend. This is how I am doing my query:

SELECT *, COUNT(CASE WHEN Status = 'Available' THEN 1 ELSE NULL END) AS Amount
FROM products GROUP BY id

This is the 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 is equal to 0, and Available is equal to 1. I only want the end-user to be able to checkout a server in Available status.

To the question: What I want to do in the page is present the list of servers on the page in this way, where Available is equal to the amount:

BL460 - Amount: 2
DL360 - Amount: 1

How can I achieve this format in PHP?

Upvotes: 3

Views: 68

Answers (2)

TimoStaudinger
TimoStaudinger

Reputation: 42480

Well, that would probably be easier if you do it 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 it in PHP, the easiest solution would probably be to loop through your SQL result and count the number of available servers per Server_Type in an associative array where the Server_Type is your array key:

$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: in order to print the values as described in the question, you could use the following code snippet:

foreach($amounts as $name=>$amount) {
    echo $name + " - Amount: " + $amount + "<br>";
}

Upvotes: 0

Jay Blanchard
Jay Blanchard

Reputation: 34426

Another option is a crosstab query - 

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 would look like this - 

Status    |    BL460    |    DL360    |
Available |        2    |        1    |
Reserved  |        0    |        0    |

Here is an EXAMPLE

Even better would be to flip things 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 would result in a table that looks like this (based on data in the fiddle) - 

server_type    |    Available    |    Reserved
BL460          |            3    |           1
DL360          |            1    |           2

Here is that EXAMPLE

Here I could continue to add servers to the table without having to worry about adding them to the query as you would have to do in the first query. If you add an additional status you would have to change the query.

Note in both cases there is no need for an Amount column as the status is the item counted. By placing the load on the database server it makes it much easier to output the HTML as you are just going row bu row as you normally would.

Upvotes: 1

Related Questions