MYSQL/PHP: Select identical numbers in different rows

Question

I have a PHP application where I load numbers from a MYSQL table composed this way:

DATE | BA1 | BA2 | BA3 | BA4 | BA5 08/01/2014 1 8 66 34 89 07/01/2014 45 32 1 48 67 06/01/2014 17 8 90 35 31 05/01/2014 19 12 75 31 64 04/01/2014 32 43 18 21 67 ...

And so forth.

At this point I have to find the repeating numbers in a max of 12 rows (12 rows one after the other).

In the example above I should be getting the numbers 1, 8, 32 and 67 (because this numbers appear in line 1, 2 and 5. If the numbers do not appear in the following 12 rows then the operation has to finish.

I'm using PHP and I'm not sure if the best way to achieve this is thru PHP once I loaded the MYSQL or if I can do it straight in MYSQL.

How do I go about this?

I have to load all of the numbers in arrays (one for each line) and then do a function, or should I just load some of the results in PHP?

Also 31 should be included in result set because it is repeated in 3rd and 4th row — M Khalid Junaid
– M Khalid Junaid, Commented May 3, 2015 at 21:04

M Khalid Junaid · Accepted Answer · 2015-05-03 20:58:38Z

In php you can create 2 arrays one for numbers and one for identical numbers lets say your query will be

select * from table order by date limit 12

After getting results in php you can loop through the query results and find identical numbers as

$numbers=array();
$identicals=array();
foreach($results as $result){

    if (in_array($result['BA1'], $numbers)) {
    $identicals[]=$result['BA1'];
    }else{
    $numbers[]=$result['BA1'];
    }
/* same if else for BA2,BA3,BA4 and BA5 */
}

print_r($identicals);

Using Mysql only you can use union all

SELECT num FROM(
(SELECT BA1 num FROM table1 ORDER BY `DATE` LIMIT 12)
UNION ALL
(SELECT BA2 num FROM table1 ORDER BY `DATE` LIMIT 12)
UNION ALL
(SELECT BA3 num FROM table1 ORDER BY `DATE` LIMIT 12)
UNION ALL
(SELECT BA4 num FROM table1 ORDER BY `DATE` LIMIT 12)
UNION ALL
(SELECT BA5 num FROM table1 ORDER BY `DATE` LIMIT 12)
) a
GROUP BY num
HAVING COUNT(num) > 1
ORDER BY num

DEMO

Collectives™ on Stack Overflow

MYSQL/PHP: Select identical numbers in different rows

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related