fenway

Many-to-many would mean that the same row in table1 can be related to multiple rows in table2, and vice versa. And the reason it's so "difficult" is because you've presented vague generalities without giving the real-world example. And you've left out fields too --- like the rating aspect -- so its hard to know what's in the tables. You may think it's not relevant, but that doesn't make it any eaiser for the rest of us to figure out what you already know. Based on what you've just recently posted, I understand the tables as follows: "table1" = rating - ( item_id, user_id, rating_value ) "table2" = item - ( item_id, user_id, ready ) I assume the user_id in the item table refers to the person who created the item, whereas the user_id in the rating table refers to the person actually rating the item. So this is a one-to-many relationship -- each item can have multiple ratings, but not vice-versa. If this is all true, then you need to be joining on item_id, NOT user_id. select count(r.item_id) from ratings as r inner join items as i using ( item_id ) where i.ready = 1 There should be no "multiplying" as you indicated above, since there is only one item record for each ratings record.

Who set up cPanel -- ask them.

I was confused by the query above... so there's a many-to-many relationship here?

Make sure you are using the appropriate character set/ colleation.

Sure... let's take it from the top... what are you trying to display, and why do you have the group by?

Of course you do... I didn't say anything about distinct.

Neither can we, since we don't see (a) the actual query sent to the server or (b) the actual error message returned from the server (from mysql_error()).

You can't use GROUP BY and get back multiple columns.

Hard to see without table prefixes... but you'll need to add a condition of OR absence.person_id IS NULL".

I don't see why you need to use table1 at all.

Hopefully this presentation will stay online at scribd... it's simply fantastic, probably the best I've come across in recent memory. At 220 slides, it's quite lengthy -- but the lessons learned are invaluable, so be sure to read all the way to the end. A MUST READ!!!! EDIT: This year's version of the presentation -- some really great stuff in here, particuarly about hierarchies.

At the 2008 MySQL Conference and Expo, The Pythian Group gave away EXPLAIN cheatsheets (PDF).

This resource covers a very broad range of topics... it's worth a look, though, especially if you're stumbling in the early stages of configuration.

Basically, this blog contains a regularly-updated run-down of MySQL functions, with a short description and a few hints on how to use them; RSS feed here.

Please read the excellent FAQ thread on this issue by our very own wildteen88.

To ensure that your post gets answered as soon as possible, please make sure you've included the following: your MySQL server version -- absolutely required! the raw MySQL statement in question [in a CODE block, and without any PHP variables] any errors that MySQL returns to the client [from mysql_error()] the table structure & column indexes of the relevant tables [via SHOW CREATE TABLE is preferred] the EXPLAIN output for your query, if applicable a clear and concise description of what you want this statement to achieve a description of what it's currently doing that's not to your liking a brief listing of the types of things you've attempted so far If you don't provide any or all of the above, don't be surprised if your post never gets the attention it deserves. --fenway

You'll find a very good list of do's and don'ts written by Matt Kruse here -- highly recommended.

MySQL's tech resource article on database normal forms is an excellent read. NEW!!! - A large (160MB, 4M record) sample database with test suite for MySQL -- fantastic for running "real" queries. In addition, MySQL provides a number of sample databases for testing purposes; personally, I find this one to be the most useful for playing around with SQL statements and such. The Zip Code Database Project exists to provide US Zip Codes in their entirety; latitude and longitude coordinates included! The downloads are in CSV and MySQL table dump formats. Also, there's an pretty good online "data generator", and SQL is one of the options... although personally, I prefer the integers table approach (courtesy of Baron Schwartz): set @num_gamers := 10000, @num_countries := 5, @num_games := 10; drop table if exists gamer; drop table if exists game; drop table if exists country; drop table if exists score; drop table if exists semaphore; create table gamer( gamer int not null, country int not null, name varchar(20) not null, primary key(gamer) ); create table game( game int not null, name varchar(20) not null, primary key(game) ); create table score( gamer int not null, game int not null, score int not null, primary key(gamer, game), index(game, score), index(score) ); create table country( country int not null, name varchar(20) not null, primary key(country) ); -- I use the integers table to generate large result sets. drop table if exists integers; create table integers(i int not null primary key); insert into integers(i) values(0),(1),(2),(3),(4),(5),(6),(7),(,(9); insert into country(country, name) select t.i * 10 + u.i, concat('country', t.i * 10 + u.i) from integers as u cross join integers as t where t.i * 10 + u.i < @num_countries; insert into game(game, name) select t.i * 10 + u.i, concat('game', t.i * 10 + u.i) from integers as u cross join integers as t where t.i * 10 + u.i < @num_games; insert into gamer(gamer, name, country) select th.i * 1000 + h.i * 100 + t.i * 10 + u.i, concat('gamer', th.i * 1000 + h.i * 100 + t.i * 10 + u.i), floor(rand() * @num_countries) from integers as u cross join integers as t cross join integers as h cross join integers as th; insert into score(gamer, game, score) select gamer.gamer, game.game, floor(rand() * @num_gamers * 10) from gamer cross join game;

This page contains some in-depth, extremely well-written articles on query optimization in very common situations. A collection of fantastic resources on the subject of MySQL Performance: A Few Common Performance Mistakes - NEW! Join-fu: The Art of SQL Tuning - NEW! Basic Performance Tuning of SQL Queries and Schema/Index Strategies (FAQ) MySQL Performance Tuning - Best Practices MySQL Index Tuning and Coding Techniques for Optimal Performance MySQL Server Variables MySQL Server Variables - SQL layer or Storage Engine specific Optimizing Rank Data in MySQL Group-Wise Maximum -- doesn't really belong here, but it's an important concept anyway

So, for all of us who are used to "ORDER BY RAND() LIMIT 1" -- which doesn't scale well at all -- here's a "better" way. With a JOIN (concept from Jan Kneschke): SELECT * FROM tablename AS t1 INNER JOIN ( SELECT ROUND(RAND() * (SELECT MAX(id) FROM tablename)) AS id ) AS t2 ON t1.id >= t2.id ORDER BY t1.id ASC LIMIT 1; Or with subqueries: SELECT * FROM tablename WHERE id >= FLOOR( RAND( ) * ( SELECT MAX( id ) FROM tablename ) ) ORDER BY id ASC LIMIT 1 In principle, this can be extended to N rows... just remember that it's possible than <N rows are retrieved, depending on the distribution of id values -- so if it really matters that you get N back, you may want to ask for some more, just in case. Either way, you'll still have to check the size of the result set. Hope that helps.

A fantastic reference card-style sheet can be found here; available as a PDF or a PNG. Visibone now has one too!

http://www.sqlzoo.net/ An oldie but a goodie... site is always a little too slow, but the fact that they make you write queries you can test is a definite plus for anyone who isn't versed in SQL statement syntax.

What does the error log say?

PHPMyAdmin is just a cumbersome tool for any but very basic things... if you want to edit 7 records at the same time, that would require about 100 clicks in PHPMyAdmin. It's just a very kludgy interface.

Well, PHPMyAdmin is totally useless for just about everything... Navicat is fantastic, though often far too powerful, and not very free. The MySQL Query Browser is fairly good as well (the old Control Center has many, many bugs). Also, I use another tool, whose name I can't recall, for DB<->DB copying / backup.