georgerobbo

I'm currently trying to get a list of all the students and when they last logged in. When a user is authenticated, a session is created and stored in the Sessions table. The primary key is a SHA1 hash of the time they logged in, their internet protocol address and username. In my query, I only want to get the latest Session in the join. I can't seem to achieve this with GROUP BY. How do I go about doing this? At the moment I'm getting duplicate results. SELECT Users.UserID, Users.Firstname, Users.Surname, Sessions.SessionID, Sessions.IPv4, Sessions.LoginStamp, Sessions.LastActivity FROM Users JOIN Students ON Students.UserID = Users.UserID LEFT JOIN SessionsUsers ON SessionsUsers.UserID = Users.UserID LEFT JOIN Sessions ON Sessions.SessionID = SessionsUsers.SessionID ORDER BY Sessions.LastActivity DESC, Users.Surname ASC Users ----------- UserID Firstname Surname Hash Students ----------- UserID House LeavingYear SessionsUsers ------------------ UserID SessionID Sessions ----------- SessionID Ipv4 LoginStamp ExpireStamp LastActivity I have highlighted the duplicate. How do I group by the last Session. (Effectively ordering by the latest LoginStamp). Thanks [attachment deleted by admin]

How should I structure XMLHTTPRequests? Should I create a controller called Ajax and post/get all ajax calls through the methods of that controller? What have other people done in the past? What worked best for you?

Can you have a method called new or do you need to use Uri mapping to map 'new' to another method. http://127.0.0.1/home/new For example class Home extends Controller function Home() { parent::Controller(); } function new() { } surely does not work, as it takes new as a sign to create a new object.

Thanks! [attachment deleted by admin]

I'm currently working on a multiple choice online examination app and I need to get the assignments given to a student. They are set an assignment and foreign keys to the AssignmentID (primary key) and UserID are stored in the table AssignmentsUsers. Questions are selected by random, (depending on the QuestionQuantity) entity in the Assignments table and foreign keys are stored in three tables, Results, ResultsSessions, ResultsAssignments. This is so during and after the examination, we can see which questions the students took, at what time they answered, what answer they selected and what assignment they were sitting. With my current query, I'm trying to get a list assignments set for the student (who is signed in) and tell whether they have completed each assignment by using a JOIN statement with the Results table. If ResulID is NULL, we can tell that the assignment hasn't be started. However, if it has been started - I'm getting multiple results as for each answer, the ResultsAssignments table contains two foreign keys. ResultsAssignments ResultID AssignmentID Name Slug DueDate 1 1 Networks networks 2011-04-15 13:35:00 2 1 Networks networks 2011-04-15 13:35:00 3 2 Policies policies 2011-04-18 13:35:00 SELECT Results.ResultID, Questions.TheQuestion, Answers.TheAnswer FROM Results JOIN ResultsAssignments ON ResultsAssignments.ResultID = Results.ResultID JOIN Assignments ON Assignments.AssignmentID = ResultsAssignments.AssignmentID JOIN AssignmentsUsers ON AssignmentsUsers.AssignmentID = Assignments.AssignmentID JOIN Users ON Users.UserID = AssignmentsUsers.UserID JOIN SessionsUsers ON SessionsUsers.UserID = Users.UserID JOIN Questions ON Questions.QuestionID = Results.QuestionID JOIN QuestionsAnswers ON QuestionsAnswers.QuestionID = Questions.QuestionID JOIN Answers ON QuestionsAnswers.AnswerID = Answers.AnswerID WHERE SessionsUsers.SessionID = '30c65fa97d8f3997761c1e01bb310475bce9a572' AND Assignments.Slug = 'computer-networks-and-the-internet' AND Assignments.AssignmentID = 1 AND Results.ResultID = 1 Assignments AssignmentID (Primary Key) Name Slug Difficulty (Integer) TimeLimit (TimeDate) QuestionQuantity (Number of Questions) DueDate (Date which the assignment must be complete by) AssignmentsUsers AssignmentID UserID Users UserID Firstname Surname Hash SessionsUsers UserID SessionID Results ResultID QuestionID AnswerID AnswerStamp ResultsAssignments ResultID AssignmentID ResultsSessions ResultID SessionID

Ah! Thanks. I usually use where statements to join my tables. Will do!

Hello I'm developing a Model-View-Controller framework for my personal learning curve. I've created an active record class and I've got stuck when using variables in a where clause. My problem is this: When, in a where statement, you want to find the Users with the first name Mike, you might write SELECT Firstname, Surname FROM Users WHERE Firstname = 'Mike' Where Mike is enclosed with quotation marks. When you're comparing entities quotation marks shouldn't be used. For example, SELECT Firstname, Surname, OrderTitle FROM Users, Orders WHERE Firstname = 'Mike' AND Users.Firstname = Orders.Firstname So when using variables in my where statements, like $Name = Mike $this->Where(Firstname',$Name); $this->Where('Users.Firstname','Orders.Firstname'); How do I differentiate, in my function Where() when to enclose the string with quotation marks, i.e when it is a (Table.Entity) rather than a constant. Thank you

Hello, I'm attempting to build a Model View Controller and I've run into some issues with inheriting objects / extending classes. The application is focused around a "SuperObject", which contain instantiated libraries, helpers, controllers, models and views. I current have a Base class, which is effectively the "SuperObject". Previously I called the function GetInstance() within my model to get an instance of the SuperObject and assign it to a variable. So, $this->Instance->(The Object)->(The Method)(); However, I want to remove the need for that variable $this->Instance and just have $this->(The Object)->(TheMethod)(); And so, If you see my Controller class, libraries are instantiated as $this->Object which works perfectly. If from, within my controller I was to load the Model class. For example, $this->Loader->Library('Model'); when that model becomes instantiated, even though an extension of the Base class, I cannot use the libraries I instantiated in my controller. And of course there is a Fatal Error: Fatal error: Call to a member function UserAgent() on a non-object <?php class Base { private static $Instance; public function Base() { self::$Instance =& $this; } public static function &GetInstance() { return self::$Instance; } } function &GetInstance() { return Base::GetInstance(); } <?php class Controller extends Base { function Controller() { parent::Base(); $this->Initialize(); } function Initialize() { $Array = array( 'Uri', 'Loader', 'Router', 'Database', ); foreach($Array as $Object) { $this->$Object = loadClass($Object); } } } [/Code] [Code] <?php class Model extends Base { function Model() { parent::Base(); echo $this->Input->UserAgent(); } } [/Code] [Code] <?php class Home extends Controller { function Home() { parent::Controller(); } function index() { $this->Loader->Library('Session'); $this->Loader->Library('Model'); $Data = array( 'Controller' => $this->Router->FetchClass(), 'Title' => ucfirst($this->Router->FetchClass()) ); $this->Loader->View('meta',$Data); $this->Loader->View('header',$Data); $this->Loader->View('home'); $this->Loader->View('footer'); } } [/Code] Any input would be appreciated!

Hi! I'm having some trouble finding related articles by their assigned tags. The database structure is as follows: Primary Key = underlined Table: Blog BlogID BlogTitle (etc) Table: Tag TagID The Tag Table: BlogTags BlogID TagID Once I have the script fully working I shall tidy everything up and create a class, so I apologise for such a bad practise for the time being. $BlogQuery = mysql_query("SELECT BlogID FROM blog WHERE BlogSlug = '$QueryString'"); $BlogResult = mysql_fetch_row($BlogQuery); $BlogID = $BlogResult[0]; $BlogTagQuery = mysql_query("SELECT blog.BlogID, blogtags.TagID FROM blog JOIN blogtags ON blogtags.BlogID = blog.BlogID WHERE blog.BlogID = $BlogID"); while($BlogTagResult = mysql_fetch_array($BlogTagQuery)): $TagID = $BlogTagResult['TagID']; $RelatedQuery = mysql_query("SELECT blogtags.BlogID FROM blogtags WHERE blogtags.BlogID != $BlogID AND blogtags.TagID = $TagID"); while($RelatedResult = mysql_fetch_array($RelatedQuery)): $RelatedID = $RelatedResult['BlogID']; $FindBlogQuery = mysql_query("SELECT BlogTitle, BlogSlug FROM blog WHERE BlogID = $RelatedID"); while($FindBlogResult = mysql_fetch_array($FindBlogQuery)): echo '<li><a href="'.$RootUrl.$FindBlogResult['BlogSlug'].'">'.$FindBlogResult['BlogTitle'].'</a></li>'; endwhile; endwhile; endwhile; To explain all the junk above. I retrieve the ID of the currently displayed article which I then query to find all TagID linked to the article in the BlogTags table. I then take each tag and try to find another blog with the same TagID. There are a few problems with this which I hope to address. 1. Efficiency - the above is a pretty pathetic script - way too many MySQL queries. 2. If a related post has more than one identical tag then it will display the link to the post for each related tag found. Of course we don't want this so I want to create some method to rank the related blogs by the number of identical tag and then display the link once in ranked order. Any help would be very very appreciated!