Count and Display Different Table Registers PHP / MYSQL

Question

Count and Display Different Table Registers PHP / MYSQL

Navigation

#1 by (1 votes)

2

GOOD LATE. I'm creating a report to count and display: - the number of men and women (table: customer) who made booking (booking table) in a hotel in a certain period of time (past via form). My question is:

How to count and display using Count, or can you automatically count and divide the search by sex?

OUTPUT EXAMPLE

MEN: x
WOMEN: and
TOTAL: x + y

SQL to count women

SELECT COUNT(*) FROM booking 
INNER JOIN customer ON booking.customer_id = customer.id 
WHERE customer.gender='Female'

SQL to count men

SELECT COUNT(*) FROM booking 
INNER JOIN customer ON booking.customer_id = customer.id 
WHERE customer.gender='Male'

SQL to set the date range

SELECT * FROM booking 
INNER JOIN room ON booking.room_id = room.id 
WHERE (checkin < '$datainicialsql' AND checkout >= '$datainicialsql') OR 
((checkin >= '$datainicialsql' AND checkin <= '$datafinalsql') 
AND checkout <= '$datafinalsql') 
OR ((checkin >= '$datainicialsql' AND checkin <= '$datafinalsql') AND checkout > '$datafinalsql') 
OR (checkin < '$datainicialsql' AND checkout > '$datafinalsql')"

General.php

    // PEGA A DATA DIGITADA VIA FORM
$dateStart = $_POST['datainicial'];
$dateEnd = $_POST['datafinal'];

// VARIÁVEL AUXILIAR PARA DATA
$datainicial = $dateStart;
$datafinal = $dateEnd;

// CRIA E EXIBE A ARRAY COM A RANGE DE DATA ESCOLHIDA VIA FORM
$dateStart = implode('-', array_reverse(explode('/', substr($dateStart, 0, 10)))) . substr($dateStart, 10);
$dateStart = new DateTime($dateStart);

$dateEnd = implode('-', array_reverse(explode('/', substr($dateEnd, 0, 10)))) . substr($dateEnd, 10);
$dateEnd = new DateTime($dateEnd);

//IMPRIME INTERVALO
$dateRange = array();
while ($dateStart <= $dateEnd) {
    $dateRange[] = $dateStart->format('d-m-Y');
    $dateStart = $dateStart->modify('+1day');
}

$dias = count($dateRange);

//CONVERTE DATA FORMATO SQL
$datainicialsql = date("Y-m-d", strtotime($datainicial));
$datafinalsql = date("Y-m-d", strtotime($datafinal));

// SELECIONA OS BOOKINGS QUE TEM A DATA RELACIONADA AO INTERVALO
$result = mysql_query("SELECT COUNT(*) FROM booking LEFT JOIN customer ON booking.customer_id = customer.id WHERE (checkin < '$datainicialsql' AND checkout >= '$datainicialsql') OR 
((checkin >= '$datainicialsql' AND checkin <= '$datafinalsql') AND checkout <= '$datafinalsql') OR
((checkin >= '$datainicialsql' AND checkin <= '$datafinalsql') AND checkout > '$datafinalsql') OR
(checkin < '$datainicialsql' AND checkout > '$datafinalsql') AND (customer.gender='Male') ") or die(mysql_error());

mysql count

asked by anonymous 28.04.2015 / 17:27

1 answer

List coming with null value Allow automatic sending of emails with Outlook

score 1 · Accepted Answer

You can try this:

SELECT Gender, 
       COUNT(DISTINCT CASE WHEN GENDER = 'Male' THEN booking.customer_id END) [#Male],
       COUNT(DISTINCT CASE WHEN GENDER = 'Female' THEN booking.customer_id END) [#Female],
       COUNT(DISTINCT booking_customer_id) [#Total]
FROM booking 
INNER JOIN room  
   ON booking.room_id = room.id 
INNER JOIN customer 
   ON booking.customer_id = customer.id 
WHERE (checkin < '$datainicialsql' AND checkout >= '$datainicialsql') OR 
((checkin >= '$datainicialsql' AND checkin <= '$datafinalsql') 
AND checkout <= '$datafinalsql') 
OR ((checkin >= '$datainicialsql' AND checkin <= '$datafinalsql') AND checkout > '$datafinalsql') 
OR (checkin < '$datainicialsql' AND checkout > '$datafinalsql')"
GROUP BY Gender

You may have to adjust the joins because I do not know the structure of the tables but it should work.