PHP + MYSQL - Looping Through Table Data Based

Question

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"

If you want to do it in PHP, it's not clear why MySQL is even relevant. The code is the same regardless of where the data came from. — Barmar
– Barmar, Commented Jul 1, 2014 at 0:17
One way is to make an associative array whose keys are the words and values are an array of all the numbers for that word. Then find the array for "Apple" and check if all its members are the same. — Barmar
– Barmar, Commented Jul 1, 2014 at 0:18
I can't do it in the query because I will extend the function to then run a few other checks even if the numbers are different. — user3788673
– user3788673, Commented Jul 1, 2014 at 0:28

Barmar · Accepted Answer · 2014-07-01 00:41:39Z

1

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) ...

edited Jul 1, 2014 at 0:41

answered Jul 1, 2014 at 0:22

Barmar

789k57 gold badges554 silver badges668 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

user3788673 Over a year ago

Thanks Barmar, how would one go about doing this in PHP after the data has been pulled rather than in the query.

Barmar Over a year ago

I described the algorithm in a comment above. Think you could put in some effort and try to implement it yourself? You'll never become a decent programmer if you depend on others to do your thinking for you.

user3788673 Over a year ago

I know how to loop through the data, It's the matter of deciphering the different between the different words, example: while($row = $query->fetch()) { $data['rows'] = ****; } But I'm lost how then running a statement for each word.

user3788673 Over a year ago

Thank you for this Barmar. Now I understand where I was going wrong with the while and foreach statements I was attempting. It appears I wasn't first creating a variable with the particular row set which is what was preventing me from deciphering between them.

Collectives™ on Stack Overflow

PHP + MYSQL - Looping Through Table Data Based

1 Answer 1

4 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Related