2 table Avg Rating

[dino2dy]

I wonder if you could help me with a problem, I have a Rating system, which rates movies (not really but its easier to explain using this example and its exactly the same). So i have a form for inserting a movie, and in it I have a drop down list choosing 1-5 as a rating of the movie. Now I also have another table Actors, and a form for inserting Actors. The tables are connected via ActorID which is a foreign key in table Movies. Now I want to somehow automatically insert into table Actors an Average Rating (which is a column I have in the table, but its not a field in the form and can't be inserted by a user on the webpage) for the Actor, which would be an average of the ratings his movies have got. So when inserting a movie if you choose that actor it automatically inserts his ID into the table Movies and the rating for that particular movie, and somehow I would like to automatically calculate for every actor his rating. The code I have so far goes a bit like this (I'm on a computer with no Dreamweaver so am copying this from notepad so pardon the syntax mistakes)

 

<?php
$query=SELECT KomitentID, AVG (tenderi.RatingTendera)
FROM komitenti, tenderi
WHERE komitenti.KomitentID=tenderi.KomitentID
while ($prosjek=mysql_fetch_array($query)){

$sql=INSERT INTO komitenti.ProsjecniRatingKomitenta
VALUES $prosjek;
?>

 

It's quite bad I'm sure, and I have no idea where to even insert it. Cos I have 2 files I use for inserting, one as a form, and the other is a php file that the form is transferred to (action=...) and thats for inserting a movie, and the same for inserting an actor, so 4 files really.

 

If somebody can shed some light on the issue, I would wish him good women and good ale for the rest of his days.

[Psycho]

Absolutely no reason to create a column in the actors table to store an average of the movie ratings he is associated with. That is the whole reason for a relational database. You can get the information in real time when you query the database. Trying to constantly update the average field is just a waste of time trying to do something manually that can be done automatically and will only lead to error in the long run.

 

Here is a mock example of what the query would look like to grab the info for an actor along with the average of the movie ratings for the movies he is associated with.

SELECT a.*, AVG(m.rating) as avg_rating
FROM actors a
LEFT JOIN movies .
    ON a.actor_id = m.actor_id
WHERE a.actor_id = '$actorID'
GROUP BY a.actor_id

 

EDIT: Also you should not be storing the actor ID into the movies table. This is a many-to-many scenario - a movie may be associated with many actors and an actor may be associated with many movies. You should be using an intermediary table to associate actors and movies. It just needs two columns: movie_id and actor_id.

[dino2dy]

First, thanks for the reply.

 

I'm not sure I really understand you though. You say I don't need the avg rating column in the actors table but surely without it how am I supposed to store the avg rating (it has to go into a column) and by storing it automatically (which was the idea not me typing it in every time) they can then see it along with all the other columns.

 

  $query = "SELECT NazivKom, KomitentID, Adresa, Telefon, E_mail, PostanskiBroj, MatBrKom, PorBrKom FROM komitenti ORDER BY NazivKom ASC";

 

This is my Query for showing the actors. I would implement your query here into this query?

But even so isn't it important to store the actor rating for each actor? I mean that way people know which actors to avoid and which to see. Your way, if I got it right, would show them here the actor rating, but wouldn't store it in the database, then again can't right now think of a reason why it should.

 

As for the many to many, the database was given to me by a company as a failed project and the company, not having time to introduce me to their business just told me to work using that database. The database they gave me sucks on an epic scale (and I'm new to this but even I could see it) but I don't wanna anger the Gods by changing it too much (I mean they have columns there they are never ever going to need, 15 bloody tables for something that should have been done in 6,7).

 

Anyhoo if you could kinda explain to me better how to implement your query with my select I'd really appreciate it. 

[Psycho]

Ok, my understanding is that the actor rating would consist of the average of the movie ratings for which the actor is associated with. Is that correct? If so, then you should not try to "store" that value because you can always get that value dynamically with the database query.

 

The whole point of a relational database is to be able to dynamically extract data across tables. The example I gave was just an example based upon a "mock" database with two tables movies and actors. I can't provide any specifics in your situation because I have no clue as to your table structure. I'm actually kind of curious how you associate movies and actors if you don't have a third table for the associations.

 

Also, not to be disrespectful, but do you understand what JOINS are and how they are used?

[dino2dy]

No offense taken, not so long ago I would have said no but yeah they are used to join different tables in a database in other words create a bigger table using a column which exists in both as a sort of reference. And as for the many to many thing, I dunno, haven't had any problems yet, I just made the actorID in the movie table a foreign key, but as I said the database was given to me I didn't create it. As for the avg you got it, and I got you, I don't need to store the values I'll just show em, but I'm afraid I won't be able to show em in a table (so it looks pretty), cos now I'm showing all the key values in a table as columns, but I don't know maybe you can even if you do it dynamically. Here's how i show the columns

  while ($row = mysql_fetch_array( $result ))
   {
    // build table to display results
print( "<tr style= border-color:#00FF00;>" );
print( "<td style= border-color:#00FF00>");
print ($row['NazivKom']);
print( "<td style= border-color:#00FF00>");
print ($row['Adresa']);
print( "<td style= border-color:#00FF00>");
print ($row['Telefon']);
print( "<td style= border-color:#00FF00>");
print ($row['E_mail']);
print( "<td style= border-color:#00FF00>");
print ($row['MatBrKom']);
print( "<td style= border-color:#00FF00>");
print ($row['PorBrKom']);
print( "<td style= border-color:#00FF00>");
print ($row['PostanskiBroj']);
print("</td><td style= border-color:#00FF00;> <a href=\"komitenti.php");
print("\"> Dodaj</a></td>");
print("</td><td style= border-color:#00FF00;> <a href=\"EditKomitent2.php?KomitentID=");
print($row[1]);
print("\"> Promijeni</a></td> <td style= border-color:#00FF00;><a href=\"?dio=politika&brisi=OK&id=");
print($row[1]);
print("\">Izbrisi</a></td>");
print( "</tr>" );
} // end for

 

 

Hope this makes it kinda clearer, and thanks for your help.

[Psycho]

To be honest, I'm not sure what to make of your code above. Or that it even matters. All you need to do is use a JOIN, AVG and GROUP BY in the query for the actors and you will,in effect, be dynamically adding an "avg" column. Just give the AVG value a name and display it as you do any other field.

 

And as for the many to many thing, I dunno, haven't had any problems yet, I just made the actorID in the movie table a foreign key

But, do you only have one field in the movie table to associate the actor? How do you associate more than one actor to a movie?

[dino2dy]

Well in the table actors I have different actors and in the table movies I have different movies and on the web page I created a drop down list which shows the Actor Name but inserts the actor ID into the movie table and so in the movie table every movie really has one actor (I just got what was so confusing, a movie in this case can only have one actor cos it's not really a movie, it's a client and the other thing is project and only one client per project, and then for another project there can be another client but not for the same project). Ok that explanation sucked let me try the real thing. The company i'm doing this for is a big company that makes everything from electrical power plants to buildings. And it wants a sort of CMS to take care of its Tenders (I think thats how u say it, might be Public Procurements or somethin). And each Tender is given by a Komitent, or a Bosnian word for a company or client that is offering you the Tender to do (not only you but several other companies and the one that comes up with the best offer gets the contract), it is a singular tender to a singular Komitent (client) (of course a tender can have "lotovi" but thats a different thing and theres a tenderlot table for that). Anyhoo I hope this explains it a bit better.

 

 

As for your solution, the code there is php code that takes the query

   $query = "SELECT NazivKom, KomitentID, Adresa, Telefon, E_mail, PostanskiBroj, MatBrKom, PorBrKom FROM komitenti ORDER BY NazivKom ASC"; 

and then turns it into an array and prints out the results in neat little rows, as well as printing out a table (green, yay!!!) for those rows. So how would I implement your code on this select query and would I then be able to use the php in the previous message to get the rows (would there be an avg thing specified in the array when fetching it, I mean would the array get the dynamically created column average as any other column (NazivKom, Adresa...) or would I have to do something differently In order to be able to show it on the web page???

 

Once again thanks a lot for answering, usually I don't get many answers on my posts.

 

[Psycho]

I'm making some gross assumptions on table and field names, but it would look something like this:

 

SELECT k.NazivKom, k.KomitentID, k.Adresa, k.Telefon, k.E_mail,
       k.PostanskiBroj, k.MatBrKom, k.PorBrKom, AVG(t.someField) as avgValue

FROM komitenti k

  LEFT JOIN tender t ON k.KomitentID = t.KomitentID

GROUP BY k.KomitentID

ORDER BY NazivKom ASC

 

You would get the same results as before with an additional field called "avgValue" (or whatever you wanted to call it).

[dino2dy]

Thanks a lot mate it worked. I really don't know all the uses of the join queries i mean I know what they do but I always thought their main use was to just create a cooler table where people can see everything from different tables but thats kinda stupid when I think about, but the whole creating a whole new field as an average of two tables using join thats some mighty cool shit, don't even have to store it or anything.

Well it's pretty much done (the application) so if you ever need anything (not via mysql or php cos you obviously rank a couple of levels higher than me on that, unless you need a ; checker or something)

By the way any chance I could get something to contact you so I could ask you questions in the future (it's a thin line between engaging and obnoxious isn't it)???

[Psycho]

My pleasure.

 

And, yes, JOINS (as well as other database functions) are very cool s**t. That was why I asked if you "understood" JOINS, but I guess your answer was appropriate based on the fact that you had a general understanding but did not understand their true power. What I showed only scratches the surface of how to use a database - and I don't even consider myself very knowledgeable.

 

If you have questions in the future, just as in this forum. It's better to get help from many people than just one as one person might not always have the best answer. You can always PM me on the forum, but I will probably tell you to just start a new post.

[dino2dy]

Thanks a bunch once again