Combine multiple columns from array

Beginners - Learning PHP
#1

Hi all,

I have an array with all my column names and I want to add all those columns together so I get echo a total score. It works if I do this:

[php]
$data = mysql_query(“SELECT idnummer, productnaam, ($vraag[0]+$vraag[1]+$vraag[2]+$vraag[3]+$vraag[4]+$vraag[5]+$vraag[6]+$vraag[7]+$vraag[8]+$vraag[9]+$vraag[10]+$vraag[11]+$vraag[12]+$vraag[13]+$vraag[14]) AS total FROM table ORDER BY total DESC, prijs LIMIT 0, 5”) or trigger_error(“Fout in de query”);
[/php]

Only I want to do this dynamically, because the number of columns are different each time. Is it possible to do something like this:

[php]
$data = mysql_query(“SELECT idnummer, productnaam, ($vraag) AS total FROM table ORDER BY total DESC, prijs LIMIT 0, 5”) or trigger_error(“Fout in de query”);
[/php]

So I don’t have to do this 14 times?

Please let me know!

Thanks,
Mark

#2

I doubt that’ll work as you have it, as your total isn’t a column name. You’ll get an unknown column name error when you run it.

If all you’re trying to do is total the column, then you can do COUNT(vraag) as total in the query, then reference total as the key.

If you want to insert the total of all the numbers in the $vraag array, then just do array_sum($vraag);

#3

Hi Richei,

Thanks for your information! This is my script:

[php]
$data = mysql_query(“SELECT idnummer, productnaam, ($vraag[0]+$vraag[1]+$vraag[2]+$vraag[3]+$vraag[4]+$vraag[5]+$vraag[6]+$vraag[7]+$vraag[8]+$vraag[9]+$vraag[10]+$vraag[11]+$vraag[12]+$vraag[13]+$vraag[14]) AS total FROM table ORDER BY total DESC, prijs LIMIT 0, 5”) or trigger_error(“Fout in de query”);
$i = 1;
while($info = mysqli_fetch_array( $data ))
{
echo ‘

’;
echo '
Match: '.round($info[‘total’]/21*100);
echo ‘
’;
$i++;
}
[/php]

And this is an example pf my database

idnummer | productnaam | v1a2 | v2a1 | v3a2
1 | test 1 | 1 | 2 | 1
2 | test 2 | 2 | 2 | 1
3 | test 3 | 1 | 2 | 2
4 | test 4 | 1 | 2 | 1

So I want to count columns together (not all columns but based on the user selection so I can’t do it hardcoded in the database, it’s dynamic) so the total is

1 = 4
2 = 5
3 = 5
4 = 4

Is this possible with Count or Array_sum? Please let me know!

Kind regards,
Mark

#4

what I would do is a 2 step process. 1st, put the columns u want to add in an array then 2nd, use a foreach loop to run a query for each column. save the results in an array then use array sum to total the array.

look up mysql total multiple columns, theres a couple of good questions on stack overflow.

#5

Hi Richei,

I allready have an array that saves the results in a array:

[php]
$vraag = array();
foreach ($_POST[‘vraag’] as $index => $kolommen) {

array_push($vraag, $kolommen);
}
[/php]

So all I need is to sum the array $kolommen together. I can’t test it right now, but if I try

[php]
$data = mysql_query(“SELECT idnummer, productnaam, sum($kolommen) AS total FROM table ORDER BY total DESC, prijs LIMIT 0, 5”) or trigger_error(“Fout in de query”);
[/php]

It should work? Thanks so much!!

Kind regards,
Mark

#6

Hi,

I solved it with this:

[php]
(".implode(’+’, $vraag).")
[/php]

It looks like it works!!! Is this the right code? Please tell me! Or is this a bad solution?

Kind regards,
Mark

#7

Well, that is not a well formed query.

You could do it like this:
[php]
$data = mysql_query(“SELECT idnummer, productnaam, SUM($vraag[0]+$vraag[1]+$vraag[2]+$vraag[3]+$vraag[4]+$vraag[5]+$vraag[6]+$vraag[7]+$vraag[8]+$vraag[9]+$vraag[10]+$vraag[11]+$vraag[12]+$vraag[13]+$vraag[14]) AS total FROM table ORDER BY total DESC, prijs LIMIT 0, 5”) or trigger_error(“Fout in de query”);
[/php]
SUM is used for math inside of MySQL Queries…

Or you could use the column numbers for that table and total them in order with a formula.
But, that is trickier to handle as you need to know the column numbers and they can not change!

#8

SORRY, typed that too fast!

Forgot to add that the array $vraag[99] must be the name of a col in the database table.
So, if inside your table, you use the "v1a2 , v2a1 , v3a2’ values, then the output of the array
needs to be those characters. I think that makes sense…

Hope that helps…

#9

Hi Ernie,

Thanks! This will work for sure, but I don’t want to enter each question seperate, I need it dynamicly, so i dont want, $vraag[0]+$vraag[1] etc. but I want to use 1 code to use the whole array, like $vraag[all].

Is this also possible without implode?

Thanks!
Mark

#10

After reading your posts AGAIN, sorry for that, you can do something like this:

SELECT SUM(COLUMN_NAME) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME=‘table’ AND COLUMN_NAME LIKE ‘vraaq%’

I had to research this a lot first. As you see, it uses the SCHEMA of the database to load each column
based on name. NOTE that this is just to get your total, the other items will have to be added in to finish
it off.

Hope that helps!

#11

Hi Ernie,

Thanks so much! I am a starter at php so I really have no idea how you figured this out :slight_smile: Let me try it tomorrow and I hope it works! I assume that this is a better method then the one I had with implode?

Thanks so far!! I love this forum!

kind regards,
Mark

#12

Well, glad to help! I knew you can use the actual “schema” of the database. This is basically the
structure of the entire database. It is seldom used by most programmers because it is usually never
needed except for queries like yours. Normally, you would design the table to just store an array instead.

To explain, if you had an array of arguments and called it $vraaq(), you would access it like $x=$vraaq[1],
$y=$vraaq[2], etc. Now, that is just a standard array. SO, you just store that in the database as one
field. Done deal… To retrieve it, you use a serialization method to pull it back out of the table. Once it is
retrieved, it is the full array of arguments again.

Using an array is easier as you can use a foreach function to display the values where needed and just
user the array[] otherwise.

Well, always a hundred ways to do something in programming… Let us know if that works for you.

#13

Hi Ernie,

Thanks so much for explaining! So if I understand it correctly, I can do this easier if I design my table different? I am fully flexible with my code and my database so if there is an easier method when I change some code or structure, I am really interested!

I tried it with information_schema but the result is empty, it’s probably me :):

[php]$data = mysql_query(“SELECT idnummer, productnaam, prijs, url, merk, afbeelding, SUM(COLUMN_NAME) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME=‘wasmachine’ AND COLUMN_NAME LIKE ‘v%’ AS total FROM wasmachine ORDER BY total DESC, prijs LIMIT 0, 5”) or trigger_error(“Fout in de query”); [/php]

What would you change so I do it the most logic way? If you need more information, please let me know!

Kind regards,
Mark

#14

Well, using the “schema” can be a pain. I never use it.

After some thought, I was thinking… Earlier you mentioned:

idnummer | productnaam | v1a2 | v2a1 | v3a2 1 | test 1 | 1 | 2 | 1 2 | test 2 | 2 | 2 | 1 3 | test 3 | 1 | 2 | 2 4 | test 4 | 1 | 2 | 1

So I want to count columns together (not all columns but based on the user selection so I can’t do it hardcoded in the database, it’s dynamic) so the total is

1 = 4
2 = 5
3 = 5
4 = 4


You have an ID, Product and arguments. All quite normal. But, you want totals from columns.

Normally, if you do a query, you are grabbing a group of data from your table. If you need a count from
the entire table, you would use the SUM function inside the query to create a, well, “Grand” total of one
column. If you need a total from columns across one row, you would either create that total when you
create the row of data and store it inside a field. (another column) OR, you grab all of your data from
the query and then as you parse thru the data, you create a count of the columns you want totaled.

So, as I see it, you have two choices.

  1. Add a “total” column to the table and create the total when the line is created. This would be done
    by using a simple “foreach” loop to parse each column and total it up in PHP before writing the data and
    saving the newly totaled amount.

  2. After running your query, use a simple “foreach” loop to total up the values and use it however.

I lean to the #1 version as then the total is always there and it is easier to display the total as it is
already there. This depends on the use of this data. If the data is created often and displayed seldom,
them #2 would be best. If the data is seldom created and viewed often, then, #1 is better.

When being logical about database tables, you mostly need to think outside the box and think about
how the data is going to be used. How often it is created, updated, displayed and even deleted are
all important things to think about.

Now, as far as your code goes, I can try to sort out getting it to work with the schema version if you
want to. It is possible. I suspect it is the way it is worded. It is a complicated query, I think…

So, just some more thoughts. Let us know where you would head next…

#15

Hi Ernie,

First of all, I want to thank you for the great support and your time! This is much appreciated!!! Now back to the question. Let me tell you how I use the data.

The data is updated about once a year. Users see the data a lot, because it’s used for a quiz. I’m building a website where users can do multiple tests. All products have a different score per question/answer which we build from Excel to the database.

After they filled in the test, I want to show the top 5 of products that match best. That’s why I need the total score so I can show how many % it match and order the products to show the top 5.

So I think option 1 is the best! But do I understand correctly that I need to create an empty column in my database?

My form now looks like this:

<form action="result.php" method="post">


<ul class="vraag1">
<li><label for="v1a1"><input type="radio" id="v1a1" name="vraag[0]" value="v1a1" /><span>Alleenstaand</span></label></li>
<li><label for="v1a2"><input type="radio" id="v1a2" name="vraag[0]" value="v1a2" /><span>Samenwonend</span></label></li>
<li><label for="v1a3"><input type="radio" id="v1a3" name="vraag[0]" value="v1a3" /><span>Gezin met kinderen</span></label></li>
</ul>

<ul class="vraaggroep">
<li><label for="v2a1"><input type="radio" id="v2a1" name="vraag[1]" value="v2a1" /><span>Voorlader</span></label></li>
<li><label for="v2a2"><input type="radio" id="v2a2" name="vraag[1]" value="v2a2" /><span>Bovenlader</span></label></li>
</ul>

My php result page looks like:

[php]
// Controle of een formulier gepost is
if($_SERVER[‘REQUEST_METHOD’] == ‘POST’) {

$vraag = array();
foreach ($_POST[‘vraag’] as $index => $kolommen) {
array_push($vraag, $kolommen);
}
[/php]

So for example I have my table like this:

idnummer | productnaam | v1a1 | v1a2 | v2a1|v2a2 1 | test 1 | 1 | 2 | 1 | 3 2 | test 2 | 2 | 2 | 1 | 1 3 | test 3 | 1 | 2 | 2 | 3 4 | test 4 | 1 | 2 | 1 | 2

It’s possible that the user answer “1” on question 1 (v1a1) and “2” on question 2 (v2a2). Then the total would be:
1 = 4
2 = 3
3 = 4
4 = 3

Do you have an example foreach loop that I can use after I created a empty total column in the database? I have multiple tests/tables like this so I want to do it good from the start!

Thanks!! Really!!

Kind regards,
Mark

#16

Mark, no problem on helping you! That is why we are on this site. Glad to help!

Now, I guess I do not understand your overall process here. Sorry, still don’t!
Or, maybe I do… So, V1A1 means test-1/Answer-1 ??? So, V2A2 means test-2/Answer-2 ???
If true, then your coding makes more sense.

I would think that the way this table was created is oddly formatted. Normally if you have repeating
values of questions/answers, you would put them into a table coded by the value themselves. So,
the table would be more like: Test-ID, Product-ID, V-nummer, A-nummer, value. With that type of
layout, you have many more rows of data in the table, depending on how many V/A combo’s like
V1A1 to V24A10, whatever. And, to total any part of the data, you would just need a well-formed
query to put the total out when displaying the line from the row of data.

Anyway, that might be a hard thing to do as far as recreating the entire project to use a different table
layout. So, it would probably be best to just continue with what you have and fix it to work. So, looking
at the data you currently have, to total up fields just before you write them to a row of data in a table,
you just need to total the values as we have talked about before.

So, in the PHP code you show, you have a look that pulls all the posted values into an array.
You should be able to create a total from that with ease. This is untested, but, something similar should
work for you:
Old version:
[php]
$vraag = array();
foreach ($_POST[‘vraag’] as $index => $kolommen) {
array_push($vraag, $kolommen);
}
[/php]
Slightly altered version:
[php]
$vraag = array();
$vtotal = 0;
foreach ($_POST[‘vraag’] as $index => $kolommen) {
$vtotal .= $kolommen;
array_push($vraag, $kolommen);
}
[/php]
Not sure, but, I think what this should do is to just total all the values of the posted “vraaq” items.
After, you would need to use the $vtotal variable to store or display as needed. Is this what you
need? Hope it is…

#17

Also, I did NOT put the silly icon faces in… LOL The site changed my three question marks into a face!

? ? ? ( put these next to each other… You get… ) ???

Sponsor our Newsletter | Privacy Policy | Terms of Service