I'm having trouble getting the information I need from my mysql db.

I need to write a query that gets me:

  1. A list of all combinations of answers from 8 columns and the frequency of them. Every row contains answers from 1 person from a survey and I want to check if there are some combinations of answers that are more common than others. The columns holds either "Y" or is empty.

  2. Next query is from a table where I want to sum a column only from them whos unique id only exists once in the table and are "new_custumors".

This is what i attempted without luck:

$query = mysql_query("SELECT sum(approved_amount) as approved FROM loanaccount_Backup1 WHERE type='new_customer' AND distinct(main_id) ");
    if ($query) {
    while ($row = mysql_fetch_assoc($query)) { 
            $approvedvolym .= $row['approved'];
    } else {
        print('MySQL query failed with error: ' . mysql_error());
    } ;

Take care

You must provide your data structure to give you a credit opinion.

One idea could be to make a select concatenating the answers ids of each user ordered by the question id (maybe using group_concat). Then making an outer select to it grouped by the concatenated column having a counter over them and of course ordered by it.

Thanks for taking a look!

The first query draws from a table that could look like this:
-1---- -----Y-----Y----- ----- ------ ---- -----Y--
-2---- ----- -----Y----- ----- ------ ----Y----- --
-3---- -----Y-----Y----- ----- ------ ---- -----Y--
-4---- ----- ----- ----- ----- ------ ----Y----- --

and expected result would look something like:
Top combinations :

1. Q1b and Q1b and Q1h (2 occurrencies)
2. Q1c and Q1g         (1 occurrencies)
3. Q1g                 (1 occurrencies)

The next query draws from a table that looks like this:

Here I would like to sum all new_customer's approved_amount IF they appear only once (as new_customer) in the table. So in the above case, only the last entry (main_id 11a21) would be summed.


Member Avatar
SELECT COUNT(Q1a) AS a, COUNT(Q1b) AS b, COUNT(Q1c) AS c, COUNT(Q1d) AS d, COUNT(Q1e) AS e, COUNT(Q1f) AS f, COUNT(Q1g) AS g, COUNT(Q1h) AS h FROM mytable 

for the sums

Not sure if I understand your last request. Maybe something like:

SELECT ... FROM mytable GROUP BY main_id HAVING COUNT(main_id) = 1

Yea or ORDER BY your counts, which diafol has named a, b, c, d, etc. :)

As for your first question , although you didn’t explained extensive your data structure , really seems that something is wrong there, you can tell that from that the answers to the questions are in columns and not in a map table.

From what I see from your structure I understand that the answers columns (Q1a etc) are VARCHAR and if contains ‘Y’ means true and ‘ ‘(blank) means false.

If that is so and with that structure that you have, the following query could count the same compositions of the answers.

SELECT COUNT(ID) AS counter , CONCAT(Q1a,Q1b,Q1c,Q1d,Q1e,Q1f,Q1g,Q1h) AS answers FROM test GROUP BY answers ORDER BY counter DESC

In fact what you are asking is a graph, but because of that you should really consider to redesign your data structure to a more normalized way.

Member Avatar

Agree with jkon - this is a strange table setup. My solution doesn't take into account multiple choices, just column counts. One idea to get combos:

Use bitwise operators:

Your checkbox form

<input type="check" name="q[]" value="1" id="q1a" />
<input type="check" name="q[]" value="2" id="q1b" />
<input type="check" name="q[]" value="4" id="q1c" />
<input type="check" name="q[]" value="8" id="q1d" />
<input type="check" name="q[]" value="16" id="q1e" />
<input type="check" name="q[]" value="32" id="q1f" />
<input type="check" name="q[]" value="64" id="q1g" />

Your form handling:

$q = (array) $_POST['q'];
$combo = array_sum($q);

For example if your user selected q1b and q1e - $combo would hold the value 18 (2+16).

Store this in your table as opposed to having all those columns:

user_id | answers
21   |  18

If you then need to check for the most popular combos:

SELECT answers,COUNT(answers) AS cnt FROM mytable GROUP BY answers ORDER BY cnt DESC

If you just need the most popular one, use LIMIT 1 at the end.

Then you retrieve the individual posts:

Assuming you have these stored in another table (this would be used to create your form checkboxes too):

check_id | check_form_id | check_value
1 | q1a | 1
2 | q1b | 2
3 | q1c | 4
4 | q1d | 8
5 | q1e | 16
6 | q1f | 32
7 | q1g | 64

You could put these into a nice array like array('q1a'=>1, 'q2b'=>2....) lets call it $myArray.

$matches = array();
foreach($myArray as $k=>$v) if($v & $answer)$matches[] = $k;
//you can use concatenation or an array to store the result from the loop

You could just use the check_id and not have a check_value in the table at all, and use something like:

$check_value = pow(2,($check_id - 1));