fenway

Well, the CREATE TABLE stuff isn't secret, and doesn't help anyone else. And I don't need all of your data, just enough for a sample. Again, someone else might want to help -- or, better yet, learn from the example by playing around. Besides, I don't open attachments from online forums. You're call.

Wow, where to begin? There are at least half a dozen things that aren't optimized. For starters, when you're counting, you're not using GROUP BY, so I don't see how that could be working. Second, you can could all the different possibilities from each table (e.g. for acccounttype/status and votetype) in a single sub-query. If you actually want my help, I'll need SQL queries to create & populate these tables, along with the expected output, and current output.

Assuming you have root access to the box, just --skip-grants.

WHERE comes before GROUP BY. And use COUNT(*).

If you are well-versed in php, a simple if/else will do.

I've edited your post -- all we need to see is the query. You need something along the lines of "WHERE yourDateTimeField >= NOW() - INTERVAL 5 DAY". If you only want the dates, and not the times, you can use DATE() on the datetime field.

What do you mean "a lot"? I see 2.

You'll need to start a query with SELECT; and then alias those tables.

Afraid how? If you know for certain that each of the sub-selects will only return a single row, then just switch the word UNION to "JOIN".

You have to build your where clause dynamically.

One row means JOIN, not UNION.

Of course, assuming you're LEFT or INNER JOIN-ing, you only the need the column on the "right" table indexed.

You're copying value & username from table1 to table2.

And INSERT INTO... SELECT WHERE would work. But, again, why are you duplicating data?

Well, you can order by if the first character is alpha or digit.

You should never do that. First, you should always explicitly list your column names. Two, you should leave out the auto-increment column. Three, why are you copying data?

They should all say "ref" or "eq_ref" -- you need to an index for every column that you're using in ON conditions.

No, MYSQL's REGEXP -- you can determine if your values are all digits or not.

OK -- well, the only thing I see wrong with the EXPLAIN if that you're missing some key indexes for the JOINs.

I don't see any queries in that code.

Why do you need to insert blank?

INNER JOIN requires both sides of your ON clause to match.

You can use the boolean result of REGEXP.

So you're just trying to caculate each person's score. But is this query supposed to calculated EVERYONE's score, or just the one person's?

This topic has been moved to PHP Coding Help. http://www.phpfreaks.com/forums/index.php?topic=345756.0