user tables check for common entries

[a.mlw.walker]

So i have a database of users, where each of my users get a table. Each table has the same fields.

The situation is users vote on films and rate them, I want to average the ratings. So I want to search all tables for an entry and for the entries that match across tables, average a field.

My problem is how do you find matches across tables?

I suspect its a SELECT * FROM [all tables] where field = [something]

then run some sort of loop over a field for the averaging.

Can someone show me an sql/php example of howl this might be done.

Thanks

[PFMaBiSmAd]

The only way you can do what you are asking is if you fix your database design to store all the users in one table. The reason there is no query statement that can be applied to [all tables] is because that would be extremely inefficient, so the database simply does not support a query like that.

[a.mlw.walker]

So how does Amazon for example rate a movie or whatever based on users views?

At the moment I have no problem displaying the entries of each table:

$sql = "SHOW TABLES FROM $databaseuser";
$result = mysql_query($sql);

if (!$result) {
    echo "DB Error, could not list tables\n";
    echo 'MySQL Error: ' . mysql_error();
    exit;
}
$tables = array();
while ($row = mysql_fetch_row($result)) {
    //echo "Table: {$row[0]}<p>";
$tables[] = $row[0];
}
mysql_free_result($result);

Can I not run something else that could then match things between tables?

and then therefore *do something* based on a match?

[PFMaBiSmAd]

So how does Amazon for example rate a movie or whatever based on users views?

 

They are storing related (same meaning) data in one table. All user information is stored in a `user` table. All rating information is stored in a `rating` table. All item/product information is stored in a `product` table.

 

They are not storing that information with the user that rated the product. They are storing that information in a table specifically designed to hold rating/vote/view information. Only user information (user_id, user_name, email, hashed password, ...) is in the user table. A table holding information to calculate ratings for something would have columns for - who (the user_id who gave the rating), what (the product_id that the rating is for), when (the datetime of the rating), and the rating that was given.

[a.mlw.walker]

Wow! in terms of database space that sounds inefficient! Surely storing it in the table that stores the user data is more efficient!

The thing is my users are only giving ratings, and their "usefulness" is based on how "good" their ratings are. So Rather than the product being the important piece of info, its the user's ability to "rate" well.

I.e each table represents a user, and I want to be able to run through the ALL my users and cross reference the things that have been rated and come up with an average. What would you recommend.

 

To put it in other terms, forget what I've said so far, and think if you had a load of tables in a database, and you wanted to search all the tables for similar entries, and add up the value of a certain field within those tables of the matching entries, how would you do it?

[Pikachu2000]

I think you probably need to read up on database normalization, and why it's a good thing. Right now, it seems that you're envisioning a relational database as merely a spreadsheet.

[PFMaBiSmAd]

if you had a load of tables in a database

 

None of the top forum members here would have same meaning data spread out in a load of tables in a database.

[a.mlw.walker]

I see. so have one table and just add a field that is the userID or whatever. Then when search for entries by a user also include a match in that field for a current user. Yeah I see that:

http://en.wikipedia.org/wiki/File:Update_anomaly.png

[a.mlw.walker]

Ahhh thats soo much better. One table all entries and a unique identifier.

So now I just need to find all cases of a field having the same value - i.e a book title or whatever, and then average the rating for each one.

So I suppose the first thing to do is find out how many distinct 'books' there are, then for each of the cases, average their ratings box?

is that how you guys would do it?

[PFMaBiSmAd]

http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_avg