SELECT DISTINCT not working to what i want

cainam29 · May 3, 2013

need to generate the correct format of this report, please advise if what i want is feasible or not. what i want is that at the image below i only want to show a distinct value under Severity, Category 2 and Category 3 columns, meaning there should just be one entry for Severity 3 and Severity 5 under Severity column and same goes for the Category 2 and Category 3 column entries

here is the image:

below is my SQL statement which generated the image above:

$dates = $_POST['dates'];

$sql="SELECT DISTINCT trouble_type_priority, category_1, category_2, status
FROM tbl_main
WHERE resolved_date = '$dates'
ORDER BY trouble_type_priority,category_1,category_2";

here is the other part of the code:

echo "<table width='150' border=0 align='center'>
<tr>
    <th colspan='2'>Remaining Tickets:</th>
</tr>
<tr>
    <th width='72'>Wireless:</th>
    <th><input type='text' name='WirelessRemaining' id='WirelessRemaining' size='7' /></th>
</tr>
<tr>
    <th>Wireline:</th>
    <th><input type='text' name='WirelineRemaining' id='WirelineRemaining' size='7' /></th>
</tr>";

echo "<table width='auto' cellpadding='1px' cellspacing='0px' border=1 align='center'>
<tr>
    <th colspan='3' align='center'>Ticket Bucket</th>
    <th colspan='3' align='center'>Status</th>
</tr>
<tr>
    <th width='auto' align='center'>Severity</th>
    <th width='auto' align='center'>Category 2</th>
    <th width='auto' align='center'>Category 3</th>
    <th width='auto' align='center'>Resolved</th>
    <th width='auto' align='center'>Re-assigned</th>
    <th width='auto' align='center'>Closed</th>
    <th width='auto' align='center'>Grand Total</th>
</tr>";

while($info = mysql_fetch_array($myData)) 
{
echo "<form action='report.php' method='post'>";
echo"<tr>"; 
echo  "<td align='center'>" . $info['trouble_type_priority'] . "<input type=hidden name=trouble_type_priority value=" . $info['trouble_type_priority'] . " size='1' maxlength='1' /> </td>"; 
echo  "<td align='center'>" . $info['category_1'] . "<input type=hidden name=category_1 value=" . $info['category_1'] . "' /> </td>";  
echo  "<td align='center'>" . $info['category_2'] . "<input type=hidden name=category_2 value=" . $info['category_2'] . "' /> </td>";
echo  "<td align='center'>" . $info['status'] . "<input type=hidden name=status value=" . $info['status'] . "' /> </td>";
echo "</tr>"; 
echo "</form>";
}
}
echo "</table>"; 

echo "<table width='auto' cellpadding='1px' cellspacing='0px' border=1 align='center'>
<tr>
    <th colspan='3' align='center'>Total</th>
    <th> </th>
    <th> </th>
    <th> </th>
</tr>";
echo "</table>";

Barand · May 3, 2013

Store the previous values and only print when they change

cainam29 · May 4, 2013

any idea how to code that?

Barand · May 4, 2013

pseudocode

prevcategory1 = ''
while fetch next row {
    if (category1 != prevcategory1) {
        print category1
        prevcategory1 = category1
    }
    else
        print blank
}

Ditto for other columns

Edited May 4, 2013 by Barand

cainam29 · May 4, 2013

hi there, sorry im new to this php/sql coding, can u help me where to place this code, and by the way i followed ur code above and i used the id's below if that was right,

prevtrouble_type_priority = ''
while fetch next row {
    if (trouble_type_priority != prevtrouble_type_priority) {
        print trouble_type_priority
        prevtrouble_type_priority = trouble_type_priority
    }
    else
        print blank
}


prevcategory_1 = ''
while fetch next row {
    if (category_1 != prevcategory_1) {
        print category_1
        prevcategory_1 = category_1
    }
    else
        print blank
}


prevcategory_2 = ''
while fetch next row {
    if (category_2 != prevcategory_2) {
        print category_2
        prevcategory_2 = category_2
    }
    else
        print blank
}

Edited May 4, 2013 by cainam29

Barand · May 4, 2013

you do realize that it is pseudocode ?

cainam29 · May 4, 2013

would u be able to help me put in the actual code then...

Barand · May 4, 2013

Here's an example of how to do it

<?php
include("/db_inc.php");
$db = new mysqli(HOST,USERNAME,PASSWORD,'test');

/********* create test data ******************************

$sql = "CREATE TABLE cattest (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    cat1 VARCHAR(20),
    cat2 VARCHAR(20),
    cat3 VARCHAR(20),
    name VARCHAR(20)
    )";
$db->query($sql);

$sql = "INSERT INTO cattest (cat1, cat2, cat3, name) VALUES
    ('aaa', 'bbb',  'cccc','Peter'),
    ('aaa', 'dddd', 'cccc','Paul'),
    ('aaa', 'dddd', 'eee', 'Mary'),
    ('bbbb','bbb',  'eee', 'Dave'),
    ('bbbb','bbb',  'cccc','Jane'),
    ('bbbb','hhhhh','cccc','John'),
    ('bbbb','hhhhh','cccc','Allan'),
    ('ggg', 'bbb',  'cccc','Henry'),
    ('hhh', 'bbb',  'cccc','Amelia'),
    ('hhh', 'bbb',  'cccc','Janet'),
    ('hhh', 'bbb',  'cccc','Liz')";
$db->query($sql);
***********************************************************/
$sql = "SELECT id, cat1, cat2, cat3, name 
    FROM cattest
    ORDER BY cat1, cat2, cat3";
$result = $db->query($sql);

$output = "<tr><th>Cat 1</th><th>Cat 2</th><th>Cat 3</th><th>Name</th></tr>\n";
$prev1 = $prev2 = $prev3 = '';
while (list($id,$cat1,$cat2,$cat3,$name) = $result->fetch_row()) {
    if ($cat1 != $prev1) {
        $prCat1 = $cat1;
        $prCat2 = $cat2;
        $prCat3 = $cat3;
        $prev1 = $cat1; 
        $prev2 = $cat2;
        $prev3 = $cat3;
    }
    elseif ($cat2 != $prev2) {
        $prCat1 = '&nbsp';
        $prCat2 = $cat2;
        $prCat3 = $cat3;
        $prev2 = $cat2;
        $prev3 = $cat3;
    }
    elseif ($cat3 != $prev3) {
        $prCat1 = '&nbsp';
        $prCat2 = ' ';
        $prCat3 = $cat3;
        $prev3 = $cat3;
    }
    else $prCat1 = $prCat2 = $prCat3 = ' ';
    $output .= "<tr><td>$prCat1</td><td>$prCat2</td><td>$prCat3</td><td>$name</td></tr>\n";
}
?>
<html>
<head>
<meta name="generator" content="PhpED Version 8.1 (Build 8115)">
<title>Example</title>
<meta name="author" content="Barand">
<meta name="creation-date" content="05/04/2013">
</head>
<body>
    <table border="1" cellpadding="4">
        <?php echo $output?>
    </table>
</body>
</html>

Note if Cat1 has changed then Cat2 and Cat3 are also deemed to have changed. Similarly if Cat2 changed then Cat3 is deemed to have changed also.

Edited May 4, 2013 by Barand

cainam29 · May 4, 2013

with some modification that i made to the code you provided...i was able to generate the report that i wanted...thank you very much for ur help...i really appreciate your help...my report is half done now...

now i just need to worry how to show the count under status and its total...d u have an idea if what i have in the image below can be placed to the code u provided?

Edited May 4, 2013 by cainam29

Barand · May 4, 2013

$totalResolved = $totalReassigned = $totalClosed = 0;
while (fetch next record) {
    // other record processing

    $totalResolved += $resolved;
    $totalReassigned += $reassigned;
    $totalClosed += $closed;
}
echo "$totalResolved  $totalReassigned   $totalClosed"

That should give the general idea

cainam29 · May 4, 2013

thanks for that wonderful idea...but how can i include the COUNT functionality to your code?

Barand · May 4, 2013

What COUNT functionality - there was none in your posted query

$sql="SELECT DISTINCT trouble_type_priority, category_1, category_2, status
FROM tbl_main
WHERE resolved_date = '$dates'
ORDER BY trouble_type_priority,category_1,category_2";

cainam29 · May 5, 2013

indeed i did not include it as i was just getting the error, now for me to accomplish the other half of this report generator, i need to include a COUNT functionality that would show the count of Resolved, Re-assigned and Closed ticket under their columns including their Totals, right?

can you please tell me how to include the COUNT functionality into the code you provided or should i be using it or not to accomplish what i want under Status column?

Barand · May 5, 2013

Can you post your table structure and sample data or even a dump of the table?

cainam29 · May 5, 2013

here it is:

--
-- Table structure for table `tbl_main`
--


CREATE TABLE IF NOT EXISTS `tbl_main` (
  `ars_no` varchar(25) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL,
  `phone_number` varchar(45) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL,
  `category_1` varchar(150) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL,
  `category_2` varchar(250) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL,
  `status` varchar(20) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL,
  `create_date` varchar(25) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL,
  `resolved_date` date NOT NULL,
  `trouble_type_priority` varchar(2) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL,
  `ban_type` varchar(2) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL,
  `employee_id_name` varchar(60) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL,
  `time_stamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`ars_no`,`category_1`,`category_2`,`status`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


--
-- Dumping data for table `tbl_main`
--


INSERT INTO `tbl_main` (`ars_no`, `phone_number`, `category_1`, `category_2`, `status`, `create_date`, `resolved_date`, `trouble_type_priority`, `ban_type`, `employee_id_name`, `time_stamp`) VALUES
('123', '123', 'ESS_Remedy', '''''''', 'Suspended', '123', '2013-05-01', '5', '1', 'AAA', '2013-05-01 10:54:04'),
('12345', '12345', 'Wireless_Remedy', '12345', 'Re-assigned', '12345', '2013-05-01', '5', '4', 'AAA', '2013-05-02 13:53:46'),
('1994967', '7802937578', 'Wireless_Remedy', 'SMP Validation Error | Couldnt find # in cross reference tables', 'Closed', '03/02/2013 17:02', '2013-05-01', '3', '1', 'AAA', '2013-05-01 08:22:00'),
('2', '2', 'SMP_Backend', 'Pending Request', 'Resolved', '2', '2013-05-01', '5', 'I', 'AAA', '2013-05-01 06:19:34'),
('2', '2', 'SMP_Backend', 'Task Error | WNP', 'Resolved', '2', '2013-05-01', '5', 'I', 'AAA', '2013-05-01 10:52:05'),
('2', '2', 'SMP_Backend', 'Validation Error | Aging', 'Resolved', '2', '2013-05-01', '5', 'I', 'AAA', '2013-05-01 06:20:01'),
('2', '2', 'SMP_Backend', 'Validation Error | RCM', 'Resolved', '2', '2013-05-01', '5', 'I', 'AAA', '2013-05-01 06:21:03'),
('2001255', '6043170773', 'Wireless_Remedy', 'SMP Validation Error | Aging request has reached max age', 'Resolved', '04/02/2013 19:16', '2013-05-01', '4', '1', 'AAA', '2013-05-01 08:17:59'),
('2001341', '4038579605', 'Wireless_Remedy', 'SMP Validation Error | Validation Error Not Listed', 'Resolved', '04/02/2013 19:19', '2013-05-01', '5', '0', 'AAA', '2013-05-01 08:13:07'),
('3', '3', 'SMP_Backend', 'Pending Request', 'Resolved', '3', '2013-05-01', '5', 'I', 'AAA', '2013-05-01 06:19:34'),
('3', '3', 'SMP_Backend', 'Task Error | WNP', 'Resolved', '3', '2013-05-01', '5', 'I', 'AAA', '2013-05-01 10:52:05'),
('3', '3', 'SMP_Backend', 'Validation Error | Aging', 'Resolved', '3', '2013-05-01', '5', 'I', 'AAA', '2013-05-01 06:20:01'),
('test', 'test', 'Wireless_Remedy', 'test', 'Resolved', 'test', '2013-05-01', '1', '2', 'AAA', '2013-05-01 08:10:32');

Barand · May 5, 2013

query

SELECT trouble_type_priority as `Severity`
    , category_1
    , category_2
    , SUM(IF(status='Resolved', 1, NULL)) as `Resolved`
    , SUM(IF(status='Re-assigned', 1, NULL)) as `Re-assigned`
    , SUM(IF(status IN ('Closed','Suspended'), 1, NULL)) as `Closed`
    , COUNT(status) as `Total`
FROM tbl_main
GROUP BY Severity, category_1, category_2;

SELECT DISTINCT status FROM tbl_main

results attached

cainam29 · May 5, 2013

thank you very much for the code but just have a couple of question: would your new code will replace the previous code that you've provided me which works perfectly when it comes to echoing the distinct value for Severity, Category 2 and Category 3. or can this be integrated to the previous code that you provided?

Barand · May 5, 2013

All it replaces is the query. You still need the processing to blank the dupe values.

cainam29 · May 5, 2013

hi there...i know im near to finishing this php now and im getting excited already...but when i replace the query to your new code, its giving me an error: Parse error: syntax error, unexpected 'DISTINCT' (T_STRING) in C:\xampp\htdocs\Dennis\report_sample.php on line 16

and line 16 refers to this:

SELECT DISTINCT status FROM tbl_main

here's how it looks like when i edited your code:

$sql ="SELECT trouble_type_priority as `Severity`
    , category_1
    , category_2
    , SUM(IF(status='Resolved', 1, NULL)) as `Resolved`
    , SUM(IF(status='Re-assigned', 1, NULL)) as `Re-assigned`
    , SUM(IF(status IN ('Closed','Suspended'), 1, NULL)) as `Closed`
    , COUNT(status) as `Total`
	FROM tbl_main
	WHERE resolved_date = '$dates'
	GROUP BY Severity, category_1, category_2";
	SELECT DISTINCT status FROM tbl_main

Barand · May 5, 2013

Sorry. That last line is a separate query that I used to see what status values you had.

Remove it.

cainam29 · May 5, 2013

oh okay...thanks, i removed it already but to no avail, its not showing the count under Status column, here is my entire code:

<?php
require 'include/DB_Open.php';

$dates = $_POST['dates'];

$sql ="SELECT trouble_type_priority as `Severity`
    , category_1
    , category_2
    , SUM(IF(status='Resolved', 1, NULL)) as `Resolved`
    , SUM(IF(status='Re-assigned', 1, NULL)) as `Re-assigned`
    , SUM(IF(status IN ('Closed','Suspended'), 1, NULL)) as `Closed`
    , COUNT(status) as `Total`
	FROM tbl_main
	WHERE resolved_date = '$dates'
	GROUP BY Severity, category_1, category_2";
	
$myData = mysql_query($sql)or die(mysql_error());

$output = 
"<tr>
    <th colspan='3' align='center'>Ticket Bucket</th>
    <th colspan='3' align='center'>Status</th>
</tr>
<tr>
    <th width='auto' align='center'>Severity</th>
    <th width='auto' align='center'>Category 2</th>
    <th width='auto' align='center'>Category 3</th>
    <th width='auto' align='center'>Resolved</th>
    <th width='auto' align='center'>Re-assigned</th>
	<th width='auto' align='center'>Closed</th>
    <th width='auto' align='center'>Grand Total</th>
</tr>\n";
$prev1 = $prev2 = $prev3 = '';
while (list($trouble_type_priority,$category_1,$category_2) = mysql_fetch_array($myData)) {

    if ($trouble_type_priority != $prev1) {
        $prCat1 = $trouble_type_priority;
        $prCat2 = $category_1;
        $prCat3 = $category_2;
        $prev1 = $trouble_type_priority; 
        $prev2 = $category_1;
        $prev3 = $category_2;
    }
    elseif ($category_1 != $prev2) {
        $prCat1 = '&nbsp';
        $prCat2 = $category_1;
        $prCat3 = $category_2;
        $prev2 = $category_1;
        $prev3 = $category_2;
    }
    elseif ($category_2 != $prev3) {
        $prCat1 = '&nbsp';
        $prCat2 = ' ';
        $prCat3 = $category_2;
        $prev3 = $category_2;
    }
    else $prCat1 = $prCat2 = $prCat3 = ' ';
    $output .= "<tr><td>$prCat1</td><td>$prCat2</td><td>$prCat3</td></tr>\n";
}
?>
<html>
<head>
<meta name="generator" content="PhpED Version 8.1 (Build 8115)">
<title>Example</title>
<meta name="author" content="Barand">
<meta name="creation-date" content="05/04/2013">
</head>
<body>
	<table border="0" cellpadding="1" >
	<tr>
	<th>Team Report</th>
	</tr>
	</table>
	<table border="0" cellpadding="1" >
	<tr>
    <th colspan='2'>Remaining Tickets:</th>
	</tr>
	<tr>
    <th width='72'>Wireless:</th>
	<th><input type='text' name='WirelessRemaining' id='WirelessRemaining' size='5' align='middle' /></th>
	</tr>
	<tr>
    <th>Wireline:</th>
    <th><input type='text' name='WirelineRemaining' id='WirelineRemaining' size='5' align='middle' /></th>
	</tr>
	</table>
    <table border="1" cellpadding="2">
        <?php echo $output?>
    </table>
	</table>
    <table border="1" cellpadding="1">
    <tr>
    <td width="34" style="display:none"> </td>
    <td width="68" style="display:none"> </td>
    <td width="144" style="display:none"> </td>
    <td width="56" style="display:none"> </td>
    <td width="34" style="display:none"> </td>
    <td width="93" style="display:none"> </td>
    <td width="107" style="display:none"> </td>
    </tr>
	<tr>
    <td colspan="6" align="center">Total</td>
    <td align="left"> </td>
	</tr>
    </table>
</body>
</html>

and here is the newly generated report

Barand · May 5, 2013

To display them you have to

A ) pick them up from the query results

while (list($trouble_type_priority,$category_1,$category_2, $resolved, $reassigned, $closed, $total) = mysql_fetch_row($myData))

B ) output them

$output .= "<tr><td>$prCat1</td><td>$prCat2</td><td>$prCat3</td>
        <td>$resolved</td><td>$reassigned</td><td>$closed</td><td>$total</td></tr>\n";

I attach the revised PHP file.

cainam.php

fenway · May 5, 2013

Barand's in a good mood this week -- this usually isn't the place if you want someone to write a script for you.

cainam29 · May 5, 2013

that was awesome Barand...working perfectly now...i couldn't have done it without your help...you see im new to this php/sql coding and could not imagined myself writing those type of codes...again thank you very much to all of your help...

im sorry fenway...yeah i know this is not the place...but ive tried to write a code which just doesn't work...and yeah im so lucky that Barand is in a good mood...

Sign In

SELECT DISTINCT not working to what i want

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information