calculate total

[Alicia]

Hi,

 

If I have a list as below fetched from mysql db

123

321

112

234

213

234

 

I want to count how many times this 1,2 and 3 numbers are displayed together regardless the positioning of the number.. is there any function I can use to accomplish this. In the case above, it should able to show 3 counts as the result (123, 321 and 213)

 

Please advise and thanks in advance.

 

 

[Psycho]

If you are always going to be comparing three digit numbers, then I would suggest you do this in the query itself by determining the possible combinations and counting the results that match.

 

$match = "123";

//create combinations for where clause
$combos = array();
$combos[] = "`field` = $match[0].$match[1].$match[2]";
$combos[] = "`field` = $match[0].$match[2].$match[1]";
$combos[] = "`field` = $match[1].$match[0].$match[2]";
$combos[] = "`field` = $match[1].$match[2].$match[0]";
$combos[] = "`field` = $match[2].$match[0].$match[1]";
$combos[] = "`field` = $match[2].$match[1].$match[0]";

//Create query to count mathching values
$query = "SELECT COUNT(*)
          FROM table_name
          WHERE " . implode(' OR ', $combos);

 

The resulting query would look something like this

SELECT COUNT(*)
FROM table_name
WHERE `field` = 123
   OR `field` = 132
   OR `field` = 213
   OR `field` = 231
   OR `field` = 312
   OR `field` = 321

[silkfire]

Wrong mate. Perform this query then use mysql_num_rows() to determine the unique count:

 

SELECT number FROM `test` WHERE number LIKE '%1%' AND number LIKE '%2%' AND number LIKE '%3%' GROUP BY number

number is the column with the numbers. Change test to the name of your table.

[Muddy_Funster]

Wrong mate. Perform this query then use mysql_num_rows() to determine the unique count:

 

SELECT number FROM `test` WHERE number LIKE '%1%' AND number LIKE '%2%' AND number LIKE '%3%' GROUP BY number

number is the column with the numbers. Change test to the name of your table.

 

and how does that come close to producing the desired result set breakdown?

[silkfire]

He wanted to know how many times the numbers appeared I gave him the solution. If you don't want unique count skip the GROUP BY clause.

 

Test the query yourself with those 6 numbers, 1 per row in a test table.

[Muddy_Funster]

In the case above, it should able to show 3 counts as the result (123, 321 and 213)

Confused me on first reading, sounded like the OP was asking for a breakdown of each of the 3 counts ie 1x123, 1x321 & 1x213

My mistake, I apologise.

Still, I would have used a COUNT() in the SELECT rather than take the mysql_num_rows() from the result.

SELECT count(set1) as total FROM sample WHERE set1 LIKE '%1%' AND set1 LIKE '%2%' AND set1 LIKE '%3%'

[SOliver]

Contrary to what seems to be popular opinion avoid doing this in sql, especially with wildcards. I'm not saying don't use some sql filtering, for example:

SELECT id FROM table_name WHERE id BETWEEN 123 and 321;

but generally speaking you want to take the load off of teh sql server; a good way to visulise why this is important is to image that you have a spreadsheet of the data in front of you and you are carrying out the select statement manually.

 

Once you've got the results a small for loop with a conditional will sort the rest

 

$res = mysql_query($query);

for($i=0;$i<mysql_num_rows($res);$i++){
    switch (mysql_result($res,$i,"id"){
        case  123:
              $count123++ ;(or $count++ if indiscriminate of value)
            break;
        .....
    }
}

 

[Muddy_Funster]

Contrary to what seems to be popular opinion avoid doing this in sql, especially with wildcards. I'm not saying don't use some sql filtering, for example:

SELECT id FROM table_name WHERE id BETWEEN 123 and 321;

but generally speaking you want to take the load off of teh sql server; a good way to visulise why this is important is to image that you have a spreadsheet of the data in front of you and you are carrying out the select statement manually.

 

Once you've got the results a small for loop with a conditional will sort the rest

 

$res = mysql_query($query);

for($i=0;$i<mysql_num_rows($res);$i++){
    switch (mysql_result($res,$i,"id"){
        case  123:
              $count123++ ;(or $count++ if indiscriminate of value)
            break;
        .....
    }
}

 

Except that your actual query would have nothing to do with producing the dataset that is being sought.  And please, visualising a database as a spreadsheet is more damaging than using multiple wildcards in a WHERE clause.  Given the simplicity of the query in question the server load is going to be completly insignificant.  I am interesed as to how you think that selecting and returning a bigger dataset than is required, then looping through this oversized dataset with with a switch case in php is more efficient than having the initial query produce only the results that are required. 

 

Oh, and welcome to the forum by the way

[Psycho]

Wrong mate. Perform this query then use mysql_num_rows() to determine the unique count:

 

SELECT number FROM `test` WHERE number LIKE '%1%' AND number LIKE '%2%' AND number LIKE '%3%' GROUP BY number

number is the column with the numbers. Change test to the name of your table.

 

You are being presumptuous silkfire. While your solution would work with data exactly like that shown it would not work with data that has different numbers of characters. For example, if the numbers being searched are "1", "2" and "3" and there is a number in the database such as "1123" your code would match that and mine would not. Which is right? Neither of us knows. The OP didn't give very explicit requirements, so I over-compensated in my solution to account for multiple scenarios. And, you don't need a GROUP BY to get the count if you are only using COUNT against one field as Muddy_Funster showed.

[SOliver]

Contrary to what seems to be popular opinion avoid doing this in sql, especially with wildcards. I'm not saying don't use some sql filtering, for example:

SELECT id FROM table_name WHERE id BETWEEN 123 and 321;

but generally speaking you want to take the load off of teh sql server; a good way to visulise why this is important is to image that you have a spreadsheet of the data in front of you and you are carrying out the select statement manually.

 

Once you've got the results a small for loop with a conditional will sort the rest

 

$res = mysql_query($query);

for($i=0;$i<mysql_num_rows($res);$i++){
    switch (mysql_result($res,$i,"id"){
        case  123:
              $count123++ ;(or $count++ if indiscriminate of value)
            break;
        .....
    }
}

 

Except that your actual query would have nothing to do with producing the dataset that is being sought.  And please, visualising a database as a spreadsheet is more damaging than using multiple wildcards in a WHERE clause.  Given the simplicity of the query in question the server load is going to be completly insignificant.  I am interesed as to how you think that selecting and returning a bigger dataset than is required, then looping through this oversized dataset with with a switch case in php is more efficient than having the initial query produce only the results that are required. 

 

Oh, and welcome to the forum by the way

The Sql statement would limit the return from the db to id values which are within the lower and upper limits of the numerical comparison (123 = lowest possible value, 321 = highest possible value) which would then be handled by php.

 

Surely visualising how a query will process is paramount to creating efficient queries? I will admit that if it isn't carried out with the right considerations the value of the exercise would decrease in a more or leass exponential manner but as with any skill it is the responsibility of the practitioner  to become proficient in the use of the tools they use, not the tool maker's, to ensure they are implemented properly.

 

As for why return a large result set.

Wildcard: Other than it generally bein' discouraged a wildcard query would require that the integer is first parsed into a string followed at least 9 char comparisons  to be considered invalid, a minimum of 12 upto 19 (based on 3 digit id) to be considered valid.

Between/php: Minimum of 1 integer comparison to be considered a failure, upto 2 comparisons.  2 comparisons to be returned from the db, a 6 comparisons to fail, a minimum of 1 upto 6 comparisons to pass - totalling fail:1 - 8, pass 3 - 8. Ignoring the parse to string  (possibly 3 in each?)  the table would need to be filled with data designed to trigger a best case for the wildcard method  and worst case for the between/php method in order for the wildcard method to produce results in favour of it's us..

 

Also thanks, I look forward to discussing php with other enthusiasts.

 

 

[silkfire]

Whenever I create an example I assume only what I can find in the OP's instructions. I think his set was based on numbers with a character length of 3. If you use COUNT with my query you get a result set where all the retrieved numbers have been replaced by '1'. Don't know why maybe I need to perform the COUNT on my query as a subquery.

[Psycho]

If you use COUNT with my query you get a result set where all the retrieved numbers have been replaced by '1'. Don't know why maybe I need to perform the COUNT on my query as a subquery.

 

No, you just need to do a COUNT() on a single field (do not include other fields in the SELECT statement) and remove the GROUP BY. Using your original query:

 

SELECT COUNT(number) FROM `test` WHERE number LIKE '%1%' AND number LIKE '%2%' AND number LIKE '%3%'

 

That will return a single result of the number of records matching the WHERE clause.

[silkfire]

Thanks MJ but even with the GROUP BY, why wouldn't COUNT return 3?

[Alicia]

Hi guys,

 

I tried this one but it shows error :

SELECT `1st`, `2nd`, `3rd`, `s1`, `s2`, `s3`, `s4`, `s5`, COUNT(*)
FROM
MyStory
WHERE
`1st`, `2nd`, `3rd`, `s1`, `s2`, `s3`, `s4`, `s5` IN (123, 132, 213, 231, 312, 321)

 

I have multiple numbers stored in different fields under the same row that I need to search.  Please advise.