I got table like below
mysql> select * from dts;
+----+------+------+--------+------+------+------+------+------+
| Id | key1 | key2 | serial | pr1 | pr2 | pr3 | pr4 | pr5 |
+----+------+------+--------+------+------+------+------+------+
| 1 | 1 | 1 | 1 | 0 | 0 | 1 | 0 | 2 |
| 2 | 1 | 1 | 2 | 0 | 0 | 0 | 0 | 0 |
| 3 | 1 | 1 | 3 | 0 | 0 | 0 | 1 | 0 |
| 4 | 1 | 1 | 4 | 1 | 0 | 1 | 1 | 3 |
| 5 | 1 | 2 | 5 | 0 | 0 | 0 | 2 | 5 |
| 6 | 1 | 2 | 6 | 0 | 0 | 0 | 0 | 1 |
| 7 | 1 | 2 | 7 | 0 | 1 | 0 | 0 | 0 |
| 8 | 2 | 2 | 1 | 1 | 1 | 1 | 1 | 2 |
| 9 | 2 | 2 | 2 | 0 | 0 | 0 | 0 | 0 |
| 10 | 3 | 2 | 3 | 0 | 0 | 0 | 0 | 0 |
| 11 | 3 | 3 | 1 | 1 | 1 | 0 | 0 | 1 |
| 12 | 3 | 3 | 5 | 0 | 0 | 1 | 1 | 0 |
+----+------+------+--------+------+------+------+------+------+
12 rows in set (0.00 sec)
I would like to pass key1,key2,db,table and list of fields comma separated to procedure, and would like to search for nonzero values from those fields for the records returned by the select statement, if suppose If I get all fields which are nonzero just break loop and return string back .
What I tried is below using php
function show_available($key1, $key2, $db, $table, $conn, $fields=null)
{
/* Select all fields in argument from db table where key... */
$query = "select ".$fields." FROM $db.$table where key1=$key1 and key2=$key2";
/* Query */
$result = $conn->query($query , MYSQLI_USE_RESULT);
/* Output array */
$out = array();
while($row=$result->fetch_assoc())
{
/* Loop through fields */
foreach($row as $key => $val)
{
/* If val is greater than 0*/
if($val > 0 ){
/*Ok we got field which has value greater than 0*/
$out[$key]=1;
}
}
/* If all fields are found ok in so many records where key1=x and key2=x, break loop */
if(count($out) == count($field_arr))break;
}
/* Return which all fields has value greater than 0 */
return implode(',', array_keys($out));
}
In the same function, I would like to convert into procedure to speedup my task and want to have output like below, how it is possible, please help someone
If I pass someprocedure(1,1,db,table,'pr1,pr2,pr3,pr4,pr5') I would like to get output pr1,pr3,pr4,pr5 because when key1=1 and key2=1
+----+------+------+--------+------+------+------+------+------+
| Id | key1 | key2 | serial | pr1 | pr2 | pr3 | pr4 | pr5 |
+----+------+------+--------+------+------+------+------+------+
| 1 | 1 | 1 | 1 | 0 | 0 | 1 | 0 | 2 | - Found pr3,pr5
| 2 | 1 | 1 | 2 | 0 | 0 | 0 | 0 | 0 |
| 3 | 1 | 1 | 3 | 0 | 0 | 0 | 1 | 0 | - Found pr4
| 4 | 1 | 1 | 4 | 1 | 0 | 1 | 1 | 3 | - Found pr1
Similarly for key1=2 and key2=2
| 8 | 2 | 2 | 1 | 1 | 1 | 1 | 1 | 2 | - Found pr1-pr5, break loop and return string
| 9 | 2 | 2 | 2 | 0 | 0 | 0 | 0 | 0 |
Expected output
# For procedure call expected o/p
key1 key2 fields_non_zero
1 1 pr1,pr3,pr4,pr5
1 2 pr2,pr4,pr5
2 2 pr1,pr2,pr3,pr4,pr5
3 2
3 3 pr1,pr2,pr3,pr4,pr5
table dump
DROP TABLE IF EXISTS `dts`;
CREATE TABLE `dts` (
`Id` int(11) NOT NULL AUTO_INCREMENT,
`key1` int(11) DEFAULT '-99',
`key2` int(11) DEFAULT '-99',
`serial` int(11) DEFAULT '-99',
`pr1` int(11) DEFAULT '-99',
`pr2` int(11) DEFAULT '-99',
`pr3` int(11) DEFAULT '-99',
`pr4` int(11) DEFAULT '-99',
`pr5` int(11) DEFAULT '-99',
PRIMARY KEY (`Id`),
KEY `main` (`key1`,`key2`,`serial`)
) ENGINE=MyISAM AUTO_INCREMENT=13 DEFAULT CHARSET=latin1;
LOCK TABLES `dts` WRITE;
INSERT INTO `dts` VALUES (1,1,1,1,0,0,1,0,2),(2,1,1,2,0,0,0,0,0),(3,1,1,3,0,0,0,1,0),(4,1,1,4,1,0,1,1,3),(5,1,2,5,0,0,0,2,5),(6,1,2,6,0,0,0,0,1),(7,1,2,7,0,1,0,0,0),(8,2,2,1,1,1,1,1,2),(9,2,2,2,0,0,0,0,0),(10,3,2,3,0,0,0,0,0),(11,3,3,1,1,1,0,0,1),(12,3,3,5,0,0,1,1,0);
UNLOCK TABLES;
What you need is aggregation with max (or sum) with concat_ws:
select
key1, key2,
concat_ws(
',',
case when max(pr1) <> 0 then 'pr1' end,
case when max(pr2) <> 0 then 'pr2' end,
case when max(pr3) <> 0 then 'pr3' end,
case when max(pr4) <> 0 then 'pr4' end,
case when max(pr5) <> 0 then 'pr5' end
) as val
from dts
group by key1, key2;
Produces:
key1 key2 val
1 1 pr1,pr3,pr4,pr5
1 2 pr2,pr4,pr5
2 2 pr1,pr2,pr3,pr4,pr5
3 2
3 3 pr1,pr2,pr3,pr4,pr5