bubblegum.anarchy

Have a look at reply # 7 from the following thread - something similar may be applicable. http://www.phpfreaks.com/forums/index.php/topic,141742.msg605260.html#new

The following appears to be quicker on a small record base: SELECT collections.id , collections.subject , collections.numfiles , collections.groups , collections.size , collections.from , collections.date , collections.nfo , ( SELECT sum(mainsubjects.num) FROM mainsubjects INNER JOIN collectionpartids ON mainsubjects.id = collectionpartids.fid AND collectionpartids.cid = collections.id ) AS mainsubjects_num , ( SELECT sum(mainsubjects.max) FROM mainsubjects INNER JOIN collectionpartids ON mainsubjects.id = collectionpartids.fid AND collectionpartids.cid = collections.id ) AS mainsubjects_max FROM collections WHERE MATCH (collections.subject) AGAINST ('dvdr' IN BOOLEAN MODE) GROUP BY collections.id ORDER BY collections.date desc

Do you have an index on wv_user.login and UserActivity.UserLogin ?

The value is available in MySQL Administrator in: View => Startup Variables <File Menu> Advanced Networking <Tab> Timeout Settings <Frameset>

Adding the following to the end of a query returns that record: LIMIT 42, 1 In the above query, 42 represents the starting position and 1 represent the amount of records to return.

By joining the singles table to a result set of just dog and time using the dog and time values.

Since a single user references a single team the user table should hold the reference key, in this case a team_id user.id user.team_id user.name team.id team.name The following would then provide a list of all the users and their respective teams: SELECT user.name, AS user_name, ifnull(team.name, 'No Team') AS team_name FROM user LEFT JOIN team ON user.team_id = team.id ORDER BY team.name, user.name Use a single table for tournies and matches as well... search google for more information on relational database design.

Add the following to the end of query: ORDER BY concat(project_order, time_to_sec(project_time)); assuming that project_time is actually stored as a time data type EDIT: ...and to add an actual rank, use a php variable or the following concept: mysql_query("SET @rank = 0"); mysql_query("SELECT @rank := @rank + 1 AS rank, students.id, ... FROM students ....");

Pay attention in class!

Please provide a small sample data set of each table (in code quotes) What is there a difference between using: WHERE match (`collections`.`subject`) against ('dvdr' in boolean mode) and: WHERE collections.subject LIKE '%dvdr%' ? ...I have very little knowledge regarding MATCH...AGAINST

I mean, look at the results of the following query: SELECT s.dog, s.event, MIN(s.time) AS time FROM singles s GROUP BY s.dog s.event is not necessarily going to be what you are expecting, only s.dog is going to match s.time, s.event on the other hand will just be the first record of the entire group of s.dog and not necessarily match s.dog and s.event.

Number three is nearly brilliant, Wildbug - expect when the missing id is 1.

This is a mysql forum, but anyway... I would do something similar to the following: <?php $result = mysql_query($query = "SELECT * FROM departments") or trigger_error(mysql_error()."<PRE>".$query."</PRE>", E_USER_ERROR); if (mysql_num_rows($result)) { ?> <SELECT id="department_id" name="department_id"> <?php while ($record = mysql_fetch_assoc($result)):?> <OPTION value="<?php=$record['department_id'];?>"<?php if ($record['department_id'] == $_GET['department_id']):?> selected<?php endif;?>><?php=$record['department_name'];?></OPTION><?php endwhile;?> </SELECT> <?php } else ; // handle null records ?> <?php only used for colour coding.

<?php $result = mysql_query($query = "SELECT gangid FROM gang WHERE gangname = 0") or trigger_error(mysql_error()."<PRE>".$query."</PRE>", E_USER_ERROR); list($gangid) = mysql_fetch_row($result); print "The gangid: ".$gangid; ?>

Add an index on project_time to shave ~500 milliseconds with 10000 student records and 40000 project records.

no no, I needed something dynamic... and have gone with just using php like SELECT * FROM prefix_$table_name since my limited research suggests that dynamic table names are not possible unles using a procedure. Thanks.

SELECT * FROM calendar WHERE Date >= CURRENT_DATE;

sure... 150 should be enough.

Does `table` in `SELECT * FROM table` have to be a constant.. or can the table be a dynamically generated value based on another tables column value. SELECT concat('prefix_', tbl_name) FROM where_table_names_are_stored WHERE id = $id I woud like to select from the above return value as a table name rather than a result set in one query. Any ideas?

At first glance I thought your query was fairly optimal but the following is better and quicker: SELECT students.id , students.student_name , count(projects.id) AS project_count , latest_project.project_name , latest_project.project_time FROM students LEFT JOIN projects ON students.id = projects.student_id LEFT JOIN ( SELECT projects.* FROM projects INNER JOIN ( SELECT student_id, max(project_time) AS latest_time FROM projects GROUP BY projects.student_id ) AS latest_project ON projects.student_id = latest_project.student_id AND projects.project_time = latest_project.latest_time GROUP BY projects.student_id ) AS latest_project ON projects.student_id = latest_project.student_id GROUP BY students.id

SELECT * FROM table WHERE column1 = '$var1' AND column2 = '$var2' or SELECT * FROM table WHERE column1 = '$var1' && column2 = '$var2'

I do not know what the `adverse affects` may be - but you can drop the index and then add the index later if the site slows of breaks, though the site should not break for lack of an index as far as I know.

Do not store the password as htmlentities.

Yeah sure... I usually code with a function to insert prepared variables: $departments .= '<select name="user_department" size="5" id="user_department">'.to_html($lst_departments).'</option>';

Great Wildbug... the following works also: mysql_query("SET @id = 0"); mysql_query("SELECT @id := @id + 1 FROM table"); But I am unsure why this does not: mysql_query("SELECT @id := ifnull(@id, 0) + 1 FROM table");