PHP Count Similiar Values in multiple arrays

Question

I have values in database like these :

Row 1 : ["2","3"]
Row 2 : ["1","3"]
Row 3 : ["2","3","4"]

In frontend i selected all rows, now i want to show count of similar values. For eg : Desired o/p from above code : Count for 1 = 1 , 2 = 2 , 3 = 3 , 4 = 1

When i json_decode above values and using print_r i got like these :

Array ( [0] => 2 [1] => 3 ) 
Array ( [0] => 1 [1] => 3 )
Array ( [0] => 2 [1] => 3 [2] => 4 )

Note : List of rows can be increased, how can i find similar values.

I tried array_intersect as shown here , but didn't work.

Eg: image here Please Note Data in image, is different from above data

Code to get above data :

    $conn = new mysqli("localhost", "root", "", "ams");
    $query="SELECT * FROM attendance WHERE subject = '$subj'";
    $result = $conn->query($query);


<table class="table table-responsive">
            <tr>
                <th>Sr. No</th>
                <th>Col 1 </th>
                <th>Col 2</th>
            </tr>
            <form method="post">
                <?php
                $i=1;
                    if (mysqli_num_rows($result) > 0) {
                        while ($row=mysqli_fetch_assoc($result)) {

                            $data = $row['att'];
                            $data = json_decode($data);

                            echo "<tr>";
                            echo "<td>" . $i . "</td>";
                            echo "<td>" . $row['date1'] . "</td>";
                            echo "<td>" . print_r($data) . "</td>";
                            echo "</tr>";
                            $i++;
                        }
                    }
                ?>
            </form>
            </table>

You shouldn't be getting that as a response. What driver are you using to connect to Mysql? Can you please show some code in this question. It is very vague. — Teddy Codes, Mar 04 '17 at 05:12
Can I give you an answer in PDO? I am not exactly sure how to leverage `mysqli_`? — Teddy Codes, Mar 04 '17 at 05:17
Do you mind providing me the column name in SQL that you are attempting to get? — Teddy Codes, Mar 04 '17 at 05:26

Teddy Codes · Answer 1 · 2017-03-04T05:48:11.067

So, I made this class for you. It will create the connection when you initalize it.

class RowData {

    private $connection;
    private $returnContent = array();
    private $stmt = null;

    function __construct() {
        $connection = new PDO("mysql:host=" . MYSQL_HOST . ";dbname=" . MYSQL_DB, MYSQL_USERNAME, MYSQL_PASSWORD);
        $connection->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
        $connection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        $this->connection = $connection;
    }

    public function get($subj) {
        $this->getContentFromDB($subj);
        $this->parseContent();
        return $this->returnContent;
    }

    private function getContentFromDB($subj) {
        $stmt = $this->connection->prepare("SELECT * FROM attendance WHERE subject = '{$subj}'");
        $stmt->execute();
        $this->stmt = $stmt;
    }

    private function parseContent() {
        $content = $stmt->fetchAll(PDO::FETCH_OBJ);
        if(count($content) < 1) {
            throw new Exception('Unable to find any attendies');
        }
        foreach($content as $values) {
            $row = $this->getJsonArray($values->att);
            $this->findValues($row);
        }
    }

    private function getJsonArray($content) {
        return json_decode($content);
    }

    private function findValues(array $row) {
        foreach($row as $key => $value) {
            if(isset($this->returnContent[$value])) {
                $this->returnContent[$value] = $this->returnContent[$value] + 1;
            } else {
                $this->returnContent[$value] = 1;
            }
        }
        return;
    }
}

So, let me explain it. The constructor will be the function that is initialized when you write $x = new RowData();. It creates the connection the the MySQL database. All you have to do is change the MYSQL_HOST, MYSQL_DB, MYSQL_USERNAME, MYSQL_PASSWORD values to the appropriate ones. The only function that is available for you to use publicly would be the get() function. The get() function calls 2 separate functions and accepts one value as a parameter. The one value is what you called $subj. One of the functions in the get() function just gets the content from the MySql Table using the query you provided. The second function parseContent() gets an obj from PDO and loops through it. lastly, there is the findValues() function that accepts $row as a parameter. This function will see if the number is already in the data set. If it is, then it is basically a counter. Otherwise, it makes a new key and sets the value to 1.

The values returned from the get() function would be something like this:

array(2=>2, 1=>1, 3=>3, 4=>1)

To use this class, you would write something like this:

$rowData = new RowData();
try {
    $content = $rowData->get();
} catch (Exception $e) {
    // No results were found
}

Hope this helps! If you need help implementing this, let me know and i'll be more than happy to help you out!

PHP Count Similiar Values in multiple arrays

1 Answers1