php select statement

[adishardis]

Hi,

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
Adam

[jkon]

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.

[adishardis]

Thanks for taking a look!

The first query draws from a table that could look like this:
id---Q1a---Q1b---Q1c---Q1d---Q1e---Q1f---Q1g---Q1h
-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:
main_id-----type----approved_amount---date...
-32n35--new_customer----30000------2011-01-09------
-32n35--old_customer----10000------2012-04-19------
-32n35--new_customer----10000------2013-02-11------
-11a21--new_customer----60000------2012-01-16------

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.

Cheers!
/Adam

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

[minitauros]

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

[jkon]

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.

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