Jump to content

SELECT DISTINCT not working to what i want

cainam29

By cainam29
in MySQL Help

 Share
Go to solution Solved by Barand,

Recommended Posts

cainam29 Member

cainam29

Posted
Posted

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:

 

EaDct.png

 

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>";
Link to comment
Share on other sites
Barand Prolific Member

Barand

Posted
Posted (edited)

pseudocode

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

Ditto for other columns

Edited by Barand
Link to comment
Share on other sites
cainam29 Member

cainam29

Posted
  • Author
Posted (edited)

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 by cainam29
Link to comment
Share on other sites
Barand Prolific Member

Barand

Posted
Posted (edited)

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.

post-3105-0-63685900-1367666141_thumb.png

Edited by Barand
Link to comment
Share on other sites
cainam29 Member

cainam29

Posted
  • Author
Posted (edited)

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?

 

izzMX.png

Edited by cainam29
Link to comment
Share on other sites
Barand Prolific Member

Barand

Posted
Posted 
$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

Link to comment
Share on other sites
cainam29 Member

cainam29

Posted
  • Author
Posted

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?

Link to comment
Share on other sites
cainam29 Member

cainam29

Posted
  • Author
Posted

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');
Link to comment
Share on other sites
Barand Prolific Member

Barand

Posted
Posted

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

post-3105-0-18042400-1367759794_thumb.png

Link to comment
Share on other sites
cainam29 Member

cainam29

Posted
  • Author
Posted

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?

Link to comment
Share on other sites
cainam29 Member

cainam29

Posted
  • Author
Posted

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
Link to comment
Share on other sites
cainam29 Member

cainam29

Posted
  • Author
Posted

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 

 

C7DXJ.png

Link to comment
Share on other sites
  • Solution
Barand Prolific Member

Barand

Posted
  • Solution
Posted

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

Link to comment
Share on other sites
cainam29 Member

cainam29

Posted
  • Author
Posted

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...:)

Link to comment
Share on other sites
This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
 Share
Go to topic listing
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.