0

I am trying to retrieve data from my database. I need three pieces of data: kaupunki_id, Opisto and pisteet_1. The problem is that they all are in different tables. My code does retrieve the right data but it's not super automatic. At the moment the variable $t decides which data to pick.

My table structure:

Table name: pisteet

enter image description here

Table name: oppilaitokset

enter image description here

Table name: kaupungit

enter image description here

What i need: For each opisto(row) i need to get the matching pisteet_1(row) and count the average of the points. and then echo it in a <td> with the matching kaupunki_id(row) class.

In the table oppilaitokset there can be multiple rows that have the same kaupinki_id(row) ID.

My code that is working but is only showing one result and its not not so automatic:

$keskiynnays = 0;
$kerto=0;
foreach ($db->query("SELECT kaupunki_id FROM pisteet") as $kaupunki_id) {
    foreach ($kaupunki_id as $kaikkinimet => $t) {
        $t= 1;
        foreach ($db->query("SELECT opisto FROM oppilaitokset WHERE kaupunki_id = '$t'") as $kaupungit) {
            foreach ($kaupungit as $kaupunki => $k) {
            }
        }
        foreach ($db->query("SELECT pisteet_1 FROM pisteet WHERE opisto_id = '$t'") as $keskiarvo1) {
            foreach ($keskiarvo1 as $loopkeskiarvo1 => $kes1) {
            }
            $kerto++;
            $keskiynnays +=$kes1;
            $keskiarvolaskettu1 = $keskiynnays / $kerto;
        }
    }       
}
echo "<td class=\"" . $t . "\">" . $k  . ": " . $keskiarvolaskettu1 . "</td>";
Improve this question
2
  • 5
    I think you may want to learn about SQL JOIN statements... Also, non-english variable/table names don't provide very readable code for us :-) Commented Aug 18, 2014 at 12:53
  • @Carpetsmoker Ye i know i'm sorry for that I had already so many and could not really change them anymore. :/ Commented Aug 18, 2014 at 12:58

4 Answers 4

Reset to default
5

You are setting variable $t = 1 and that is why you are getting always the same row.

Generally speaking, your code is terrible and you're running lots of queries. Try googling about SQL JOINS to see how you can achieve the same results way faster.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

i have read and watched some tutorials of sql joins. but i only understand the basics and have no idea how to implement it here.
4

As mentioned I would try a SQL join - something like this:

$query = "SELECT opisto As O, AVG(pisteet_1) As P, ka.kaupunki_id As K FROM ".
"oppilaitokset op ".
"LEFT JOIN pisteet pi ".
"ON op.id = pi.opisto_id ".
"LEFT JOIN kaupungit ka ".
"ON op.kaupunki_id = ka.kaupunki_id ".
"GROUP BY opisto, ka.kaupunki_id";

foreach ($db->query($query) as $row) {
    print $row['O'] . "<br>";
    print $row['P'] . "<br>";
    print $row['K'] . "<br>";
}
Improve this answer

5 Comments

Some The code does not execute. I am getting nothing. I looked through the code but did not find anything that could fix this.
I think I might have written that for SQLite. Try with my new edit.
Now it works partially but the points are not calculated or printed on the page.
it's also not printing the kaupunki id. It's only printing the opisto.
You are not really forthcoming enough with information to identify the problems you are having at this point. As others have repeatedly suggested, I think your next step here is to learn something about writing SQL queries. It looks like you're running something like PhpMyAdmin there to view your tables, you should use it to practice writing and understanding queries, get the data you want from a single query and then use PHP to put it on the page.
1

So, you got pisteet Table, related to the others, by using foreign keys. Okay. Nice thing you uploaded these pictures.

If the database was not related, you should had to work like the way you do. It would be a terrible thing for a bunch of reasons. But that's why SQL was invented for. A Relational Database System MUST be used at its full potential. You make your life hard anyway though.

You said:

"@Carpetsmoker Ye i know i'm sorry for that I had already so many and could not really change them anymore. :/ – "

Yes you can "do the job" using PHP and queries with no Join, BUT you must think of it. Why did @Carpetsmoker told you that?

" I had already so many and could not really change them anymore" makes me feel like you don't understand what you have put yourself into, but most important, what you insist keeping yourself into.

Let's say you fixed the problem on this piece of code. Next thing is reproducing code wrongly done everywhere on your project. How will you do that? Copy - paste and edit to fit your needs maybe?

It is 100% best practice to take some time, learn JOIN commands and what they do, then gradually remake queries using Joins. At LEAST PHP will do the job it was meant to do and MySQL will do the quering.

As for the "there are too many" part, well, I trust you already know the concept of modular structured programming.

In case you do not, or you forgot, here is an example. Imagine you wanna do something, in 10 places on your code. What do you do?

1] You write the code and then copy paste it, slightly editing it to fit your needs.

2] You make a function that contains the code, then call the function on these ten parts. The editing could be implemented as a few arguments passed.

Approach 1] IS WRONG.

Approach 2] is a better way to do it. Maybe not the best, but a better for sure. Can you imagine why?

In the second approach, if you make a mistake, you change a function ONCE and it is changed for all ten places automaticaly. In the first approach, copy paste is your nemesis. You are already fed up with it. Your own words say so: "you could not change them ANYMORE". Trust me on my first steps on programming I did worse mistakes than you. And I was not fortunate to have Internet back then. :(

So, a bad practice, can be easier to do in the begining (since you don't have to study a few concepts) but that doesn't make it easy in the long run. You unfortunately learned that the hard way here.

Check out the following links for a quick understanding: Click here and Here

(a few first google search results)

You can thank us all later.

As for functions, start from here

Combining these concepts, you will make your life easier for yourself.

Finally, I assume you do not know these concepts. I have no intention to offend you or make you feel bad. What I see is just a piece of code.

Cheers.

Edit: Typos.

Improve this answer

Comments

0

This retrieves all the values and prints them correctly. Modify code of @Kez (see above)

echo "<table class=\"zebra1\">";
    echo "<tr>";
    $query = "SELECT opisto As O, AVG(pisteet_1) As P, ka.kaupunki_id As K FROM ".
    "oppilaitokset op ".
    "LEFT JOIN pisteet pi ".
    "ON op.opisto_id = pi.opisto_id ".
    "LEFT JOIN kaupungit ka ".
    "ON op.kaupunki_id = ka.kaupunki_id ".
    "GROUP BY opisto, ka.kaupunki_id";

    foreach ($db->query($query) as $row) {
        echo "<td class=\"" . $row['K'] . "\">" . $row['O'] . ": ";
        echo $row['P'] . "</td>";
    }
    echo "</tr>";
    echo '</table>';
Improve this answer

1 Comment

Ah, I see I got the IDs mixed up in my query. Glad you got it sorted - I have edited my answer above.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.