tail

Ok thanks anyways!

Is there a way to optimize this query so I don't have to use so many subqueries? SELECT COUNT( hits ) AS `unique` , SUM( hits ) AS `total` , ( SELECT SUM( `hits` ) FROM `stats` WHERE `date` > UNIX_TIMESTAMP( ) -86400 ) AS `today` , ( SELECT SUM( `hits` ) FROM `stats` WHERE `date` > UNIX_TIMESTAMP( ) -604800 ) AS `week` , ( SELECT SUM( `hits` ) FROM `stats` WHERE `date` > UNIX_TIMESTAMP( ) -2419200 ) AS `month` , ( SELECT COUNT( * ) FROM `stats` WHERE `online` > UNIX_TIMESTAMP( ) - '600' ) AS `online` FROM `stats` Thanks in advance!

I got to this point: SELECT info.id , info.name FROM info LEFT OUTER JOIN rating ON rating.game_id = info.id GROUP BY info.id HAVING COUNT(rating.rating_value) >= 10 ORDER BY AVG(rating.rating_value) DESC However only games with > 10 votes showed in the result. How can I make it so all of the games show up, but order priority goes to games with > 10 votes and an avg rating higher than 3?

Got it! SELECT `id` , `name` FROM `info` LEFT JOIN `rating` ON rating.game_id = info.id GROUP BY info.id ORDER BY ( AVG( rating.rating_value ) ) DESC Thanks for the help! Is there a way I can make it so results with < 10 votes doesn't come up first?

Sort result by a different tables values I'm working on a script that shows games compiled in a MySQL database. I'm trying to sort them by highest rated. The ratings are stored in a different table. The tables are set up as follows: table `info` | id | user_id | name | desc | category | plays | date_added | table `rating` | game_id | user_id | rating_value | I think this is possible using a JOIN. I don't know much about JOIN's but it seems that it could be done. The way I'm trying to do it right now is add up the rating values for the specific ID, then divide that by the number of ratings for the specific ID, then sort the result by the highest number. I'm using this query to no avail: SELECT `id` , `name` FROM `info` JOIN `rating` AS `rating` ORDER BY ( COUNT( rating.game_id ) / SUM( rating.rating_value ) ) DESC Any help is much appreciated!

Bump!

As long as you validate information passed through URL query's it does not pose as a threat.

You may want to look into password salting if you're concerned with security. Here is a link: http://www.richardlord.net/blog/php-password-security

Set a session for the users id upon logging in, then whenever you need to pull data for the logged in user, use the session variable.

I'm working on a script that shows game compiled in a MySQL database. I'm trying to sort them 3 different ways: newest, most popular, and highest rated. The first two are easy because they are in the same table as the game information. However, the ratings are stored in a different table. I think this is possible to do using a join, but I don't know how to do that. The tables are set up as follows: table `info` | id | user_id | name | desc | category | plays | date_added | table `rating` | game_id | user_id | rating_value | Is it possible to add up the rating values, then divide that by the number of ratings for the specific id, then sort the result by the highest rated game?

Did someone kill it? Warning: mysql_connect() [function.mysql-connect]: Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (13) in /mounted-storage/home48c/sub007/sc33591-LWQU/picks_web/new_kp/core/includes/db-config.php on line 11 Site Returned Error: Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (13)

Very nice job cleaning it up. It looks great now!

Haha wow, the query is wrong. I'm never going to try coding anything at 7:00am again!

If you didn't notice, I used the mysql_fetch_array() function, which puts the results into variable $r, as seen in my code. The only reason my code wouldn't work is because I put two $ by accident.

$r=mysql_fetch_array(mysql_query("SELECT 'Username','XP' FROM 'users' WHERE 'ID'='".$id."' ORDER BY RAND() LIMIT 1")); echo 'Username: '.$$r['username'].' XP: '.$$r['xp'];