Balagi

I'm sorry I should have specified that I don't want to have to order the results by each students rank (though it will be an option for the guests). So, lets assume that the guest wants to order the results by student_name, I still want one of the fields to show the student's rank, for example: student_name | overall_rank | gender_rank Adam | 3 | 3 Alex | 2 | 1 Alice | 5 | 2 Bob | 4 | 2 Tiffany | 1 | 1 edit: project_time is a timestamp BTW.

While I'm still working with these tables I have another set of information I want to grab, but I'm not sure if this is even possible or how to do it if it is. I've researched MySQL.com and can't find anything that helps with what I want to do. If you have an idea I'd greatly appreciate to hear it. Basically I want to return a "rank #" for each student based on the projects they have turned in. Say the projects table has the following fields: id, student_id, project_name, project_time, project_order I want to grab the rank based on the order ASC and then the time ASC. So for example, if this was my projects table, the ranks would be as follows: 1 | 1 | cheese | 2222 | 5 = rank 4 2 | 1 | water | 4444 | 4 = rank 2 3 | 2 | cheese | 1111 | 5 = rank 3 4 | 2 | water | 3333 | 4 = rank 1 5 | 3 | pizza | 0111 | 6 = rank 5 Here, the ORDER BY of the query would be (project_order, project_time), but I will be using the query posted by bubblegum.anarchy earlier, so I don't care about the ranks of the projects, I care about the ranks of students. Using the above example User 2 would be ranked #1, User 2 would be ranked #2, and User 3 as #3. Using this query, is it possible to get each students rank using another JOIN? SELECT students.id , students.student_name , count(projects.id) AS project_count , latest_project.project_name , latest_project.project_time , something.something AS student_rank 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 LEFT JOIN something GROUP BY students.id I haven't even made an attempt at figuring this out as I have no idea where to start. If you have an idea what I'm talking about (I have the feeling my explanation was rather vague), please toss some advice my way. P.S. I am shying away from adding a "rank" field to the students table because that will eventually require updating thousands of records for every insert, update, and delete done to the projects table.

Thanks very much, bubblegum.anarchy. Your idea worked perfectly, although it took me like an hour to fully comprehend. I would have never figured that out, that really gives me some insight into what MySQL can do that I had no idea of. From my testing, adding that 2nd LEFT JOIN (the long one) only added about .0014 seconds to my query runtime (granted it's only being tested on localhost) for 10 records. I had thought about just going the easy route and updating the "students" table after every insert/update/delete query, but I just wanted to make my results more concrete, in case I ever decide to change the way I display my data. Thanks again.

With the following example I am dealing with 2 tables: students, projects SELECT student_name, count(p.id) AS project_count FROM students AS s LEFT JOIN projects AS p ON p.student_id = s.id GROUP BY s.id So, if I have a class of 20 students, and each student has done 4 projects, each row will return the following: [student] => John Doe, [project_count] => 20 What I want to do is figure out how to get more specific data for each student (row) from my students table, such as: [student] => John Doe, [project_count] => 20, [last_project_name] => American History, [last_project_date] => 2005-05-01 So in addition to the first 2 columns, I want to grab the project from the projects table that was last added to the database, in the above results example the last project from John Doe would be titled American History and turned in on the said date. Granted I'm still a novice when it comes to MySQL, I want to know if there is a more efficient and organized way to go about this rather than what I came up with by using 2 long sub queries: SELECT student_name, count(p.id) AS project_count, (SELECT project_name FROM projects p2 LEFT JOIN students s2 ON s2.id = p2.student_id WHERE p2.student_id = s.id ORDER BY project_time DESC LIMIT 0,1) AS last_project_name, (SELECT project_time FROM projects p2 LEFT JOIN students s2 ON s2.id = p2.student_id WHERE p2.student_id = s.id ORDER BY project_time DESC LIMIT 0,1) AS last_project_date, FROM students s LEFT JOIN projects p ON p.student_id = s.id GROUP BY s.id I want to emphasize that the above query DOES work, I'm hoping to find a better and quicker way to achieve the same results. Thank you in advance.

Nevermind, I got what I wanted to work. It was a combination of defining the object in the onload function and a never ending javascript loop that I completely overlooked.

So I have an object that I defined in my main page. I'd like to be able to use that object in a page I load into a DIV with AJAX. I'm relatively new with JS OOP and Ajax so I don't even know if this is possible. Here's an example: index.php <script type="text/javascript"> function DB(element, num) { this.element = element; this.num = num; this.loadDB = function() { // load ajax library - i use Prototype new Ajax.Updater('database', 'url/to/database.php'); } this.loadDB(); } window.onload = function() { DB = new DB('test', 101); } </script> <div id="database"> </div> database.php // this page is loaded into the "database" DIV on index.php <a href="javascript:void(0);" onclick="javascript:alert(DB.element);">Show value of DB.element</a> Obviously that is just a sample, I got my database.php page loading fine, I'd like to be able to use my previously defined object/variables from the database.php page. If this isn't possible, is there a better alternative other than creating a new object with the onclick event and losing my previously defined members? Thank you.

Here is a sample of the code I'm trying to fix.  I create an object $a and want to be able to use that same object on other pages that I load (require_once) through a function. [code] <?php // index.php class A {   function load_page($page) {     require_once($page . '.php');   } } $a = new A; echo $a; // prints Object id #1 as it should $a->load_page('test'); /* If I replace this line with require_once('test.php'); the A object will carry over, but that's not what I'm trying to do */ ?> [/code] [code] <?php // test.php echo "test text"; // prints "test text", I just added this to make sure the file was being loaded echo $a; // prints nothing, this is what I'm trying to fix.. I want this to print Object id #1 like it did on index.php ?> [/code] I'm pretty new to class/object orriented programming, but basically I want my A object that was defined to index.php to carry on to other pages that I load with my $a->load(); function.