CODEWITHSUNDEEP
phpmysql
Mr.Web
Mr.Web

Reputation: 7154

MYSQL/PHP: Select identical numbers in different rows

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?

Upvotes: 0

Views: 62

Answers (1)

M Khalid Junaid
M Khalid Junaid

Reputation: 64476

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

Upvotes: 1

Related Questions