CODEWITHSUNDEEP
phpmysql
michaelmcgurk
michaelmcgurk

Reputation: 6509

Group MySQL results using PHP

I have a table in my database that stores data about 100 or so people. It stores when they started a job and also things like marital status & city of birth.

CREATE TABLE `entries` (
  `id` int(11) NOT NULL,
  `city` varchar(255) NOT NULL,
  `commencement_date` varchar(255) NOT NULL,
  `marital_status` varchar(255) NOT NULL,
  `employment_status` varchar(255) NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=latin1;

INSERT INTO `entries` (`id`, `city`, `commencement_date`, `marital_status`, `employment_status`) VALUES
(1, 'Glasgow', '01/04/2017', 'Married', 'Employed'),
(2, 'Glasgow', '04/04/2017', 'Married', 'Self-Employed'),
(3, 'Edinburgh', '01/04/2017', 'Single', 'Self-Employed'),
(4, 'Glasgow', '04/04/2017', 'Single', 'Employed'),
(5, 'North London', '01/04/2017', 'Divorced', 'Unemployed'),
(6, 'Glasgow', '04/04/2017', '', 'Self-Employed');

ALTER TABLE `entries`
  ADD PRIMARY KEY (`id`);

I use the following PHP to grab this data from the database:

$sql = "SELECT * FROM entries WHERE city = '$city' AND (commencement_date BETWEEN '$dateStart' AND '$dateEnd')";
$result = $conn->query($sql);

However, I am wondering, is it possible using PHP to total the number of Married, Single & Divorced and list these in a bullet list?

Preferably, my script should output:

Married: 2
Single: 2
Divorced: 1

I'm just wondering if there's a tidy way of doing this with PHP.

Upvotes: 1

Views: 1098

Answers (6)

Chamalka Kalansuriya
Chamalka Kalansuriya

Reputation: 311

You can try this query. I hope it will work.

$sql = "SELECT marital_status, count(*)
  FROM entries
  GROUP BY marital_status";
$result = mysql_query($sql) or die(mysql_error());

Upvotes: 2

ismael ansari
ismael ansari

Reputation: 486

Please use the below query to fetch exact what you want:

SELECT CONCAT(`marital_status`,":",COUNT(`id`)) FROM entries where marital_status!='' GROUP BY `marital_status` ORDER BY COUNT(`id`) DESC

Hope it will help you!

Upvotes: 1

Alex
Alex

Reputation: 1295

You can use SQL for it:

SELECT marital_status, count(*) FROM `entries` GROUP BY marital_status

Or PHP:

$count_married=$count_divorced=$count_single=0;
foreach($result as $next_result) {
   if($next_result['marital_status'] === 'Divorced') {
      $count_divorced++;
   }
   if($next_result['marital_status'] === 'Single') {
      $count_single++;
   }
   if($next_result['marital_status'] === 'Married') {
      $count_married++;
   }
}

echo "Married: $count_married<br />\n";
echo "Single: $count_single<br />\n";
echo "Divorced: $count_divorced<br />\n";

Upvotes: 1

Gopi Chand
Gopi Chand

Reputation: 174

select id,
sum(case when marital_status = 'Single' then 1 else 0 end) singlesum,
sum(case when marital_status = 'Marrid' then 1 else 0 end) marridsum
from entries
order by id

Upvotes: 1

Altaf Hussain
Altaf Hussain

Reputation: 191

function totalEntriesBasedOnMartialStatus($conn, $marital_status){
    $query = $conn->prepare("SELECT COUNT(id) as totalEntries FROM entries WHERE marital_status=:marital_status");
    $query->execute(array(':marital_status'=>$marital_status));
    $row = $query->fetch(PDO::FETCH_ASSOC);
    return $row['totalEntries'];
}

Please try this function i hope it will work you need to pass martial status for which you want to get total.

Upvotes: 1

Ahmed Ginani
Ahmed Ginani

Reputation: 6650

You can try below query to get total

$sql = "SELECT marital_status , count(id) as total FROM entries GROUP BY marital_status";

Upvotes: 1

Related Questions