CODEWITHSUNDEEP
phpmysqlloops
user3788673
user3788673

Reputation: 7

PHP + MYSQL - Looping Through Table Data Based

I currently have a MySQL database that includes a table containing data for a certain set of words. The structure is like this (with much more data of course):

**ID**        **word**        **number**
----------------------------------------
  1             Test              10
  2             Test              14
  3             Test              20
  4             Apple             7
  5             Apple             8
  6             Apple             11
  7             Bus               3
  8             Bus               3
  9             Bus               5

Where ID is the Unique Key.

What I am trying to do is grab all the data from this table and loop through it to get the "Number" data for each "word" set in order to run a few sums.

So, I want to get all the data, then loop through it and do some if statements for each word set, e.g: Get all the numbers relating to the word Test, then all the numbers relating to the word Apple etc etc. However, being that the word is not the unique key I am not sure how to split the data up once I've pulled it from the database.

So far I've got the following:

public function getData() {

        $query = $this->db->prepare("SELECT * FROM table");
        $query->execute();

    try{

        $query->execute();
        $result = $query->fetchAll();

    }catch(PDOException $e){
        die($e->getMessage());
    }

}

So obviously, I've pulled all the data from the database as needed and can print_r the array of data it returns without any issue. Now I want to loop through the data and complete a particular function for each word set. E.g: run an if statement that checks if the 3 numbers for the word "Test" are the same, then run that same check to see if the 3 numbers for "Apple" are the same, and then again for "Bus" etc etc.

As an example, at the end of the loop/check I want to be able to echo a statement that says: "The word Bus contains 2 matching numbers"

Upvotes: 0

Views: 135

Answers (1)

Barmar
Barmar

Reputation: 782717

Here's how you do it in the SQL query:

SELECT word, COUNT(DISTINCT number) = 1 AS numbers_all_the_same
FROM table
GROUP BY word

With your data this will return:

word    numbers_all_the_same
Test    0
Apple   0
Bus     0

because none of them have all the same numbers in their group. If any of the words had all the same number, they would have 1 in the second column.

Here's how you can create an array of all the data, grouped by word:

$all_words = array();
while ($row = $query->fetch(PDO::FETCH_ASSOC)) {
    $word = $row['word'];
    $number = $row['number'];
    if (!isset($all_words[$word])) { // We haven't seen this word before
        $all_words[$word] = array($number); // Create an array with the number
    } else {                         // We have seen this word before
        $all_words[$word][] = $number; // Add the number to the array
    }
}

Now you can perform any kind of analysis you want on the numbers associated with each word. If you want to know if all the numbers are the same, you can do:

$uniq = array_unique($all_words["Apple"]);
if (count($uniq) == 1) ...

Upvotes: 1

Related Questions