[Resolved] Count identical stringvalue in a column

[Dr_Flink]

I have a tabel with a column called 'homescoorers'. This column keeps different string values like:
row 1: Sven Svensson
row 2: Erik Eriksson, Adam Adamsson
row 3: Sven Svensson, Lars Larsson
row 4: Sven Svensson, Sven Svensson
etc....

Now I want to count how many times 'Sven Svensson' appears in the column and store that value in a variable. Is there a way to do this?

[Installer]

One way would be to use substr_count(), something like this:

$result = mysql_query($your_query);
$sven_svensson_total = 0
while ($row = mysql_fetch_array($result)) {
    $sven_svensson_total += substr_count(strtolower($row['homescoorers']), 'sven svensson');
}

[Dr_Flink]

Hmmm, I get the following error:

Parse error: parse error, unexpected T_WHILE in /....

this is how I wrote your code:

<?php
$query = mysql_query("select * from amatch") or die(mysql_error());

$result = mysql_query($query); 
$tom_lillskog_total = 0
while ($row = mysql_fetch_array($result)) {
       $tom_lillskog_total += substr_count(strtolower($row['homescoorer']), 'tom lillskog');
}
?>

[Installer]

Just a typo; if you look at the code, you'll see I forgot the semicolon on the preceding line. Sigh.

[Dr_Flink]

Aaaaah, sorry. Didn't see it.

But now I get this error:

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /....

...sorry for me being such a pain in the a.. :queasy:

[Installer]

See if this works:

$query = "select * from amatch"); 

$result = mysql_query($query) or die(mysql_error());  

$tom_lillskog_total = 0;
while ($row = mysql_fetch_array($result)) { 
       $tom_lillskog_total += substr_count(strtolower($row['homescoorer']), 'tom lillskog'); 
}

You were executing "mysql_query()" twice, the second time with the result returned from the first one as the query string.

[Dr_Flink]

Yes, It's working perfect now.

Thank u for all your help!!!

[Installer]

Glad to help. Try this, to get more complete results in one pass:

$query = "select * from amatch"); 

$result = mysql_query($query) or die(mysql_error());  

while ($row = mysql_fetch_array($result)) { 
    $scoorer_arr = explode(',', $row['homescoorer']);
    foreach ($scoorer_arr as $name) {
        $name = trim($name);
        if (!isset($totals[$name])) {
            $totals[$name] = 1;
        } else {
            $totals[$name]++;
        }
    }
}
foreach ($totals as $key => $val) {
    echo $key . ' => ' . $val . '<br />';
}

Edited to correct another typo. Check for others. 🙂

[Dr_Flink]

lol, I was sitting thinking of how to do something like that. This code u wrote me looks a liiittle bit to complicated for my level of knowledge...

But I tested it and got these errors:

Warning: Invalid argument supplied for foreach() in /...on line 93

Warning: Invalid argument supplied for foreach() in /...on line 104

I tried to figure out whats wrong, but I'm just to much of a rookie :bemused:

[Installer]

Did you notice that I edited the code? I forgot to delete a couple lines at first.

If so, is your column formatted the same way, i.e. with a string in each row's "homescoorer" comprising names separated by commas?

And if that's so, it should work. But I don't have the db structured to test it on, so there might be some little thing I'm missing.

[Dr_Flink]

YES! I tride out the edited version and it works absolutely PERFECT! 😃

Hope u understand how much I appreciate all the help you've given me.
Thank u soooo much!

[Installer]

As for the code being too complicated for a rookie, I can understand that. Maybe these comments, together (and especially) with the manual, will help. Getting familiar with PHP's string and array functions will give you a good foundation for learning PHP.

$query = "select * from amatch"; 
$result = mysql_query($query) or die(mysql_error());  

while ($row = mysql_fetch_array($result)) { 
    // Explode homescoorer into an array of names.
    $scoorer_arr = explode(',', $row['homescoorer']);
    // Loop through the names.
    foreach ($scoorer_arr as $name) {
        // Remove any white space from around name.
        $name = trim($name);
        // If this is the first time the name is found,
        // then that key in the totals array is yet to be set.
        // Initialize the totals array item with
        // the player's name as the index, and start
        // the count at one.
        if (!isset($totals[$name])) {
            $totals[$name] = 1;
        } else {
            // If the name has been found before,
            // increment the array item.
            $totals[$name]++;
        }
    }
}
// Loop through the totals array for display.
foreach ($totals as $key => $val) {
    echo $key . ' => ' . $val . '<br />';
}

Edit again: I just saw your last post. 😃

[Dr_Flink]

I don't know what to say ....but thank u once again!!!