Adding a counter to mysql results?
|
| Author | Content |
|---|---|
| techiem2 May 11, 2010 7:07 PM EDT |
Ok, I don't even know if this is possible or not.
:) Basically I have a query that pulls records from several tables in the database for the attendance system to see what groups the selected students have records for. http://pastebin.com/TsZQ4ziW The query itself seems to be working fine within phpmyadmin. The problem comes when I pull the query results into moodle. It's api is doing something odd and not pulling the full results into it's array. It is pulling the user id and then the last set of results for that user id. I.E. Say you have a resultset from mysql: user_id/first_name/last_name/course_id/group_id 8/Bob/Jones/243/900 8/Bob/Jones/232/431 8/Bob/Jones/345/109 15/Jan/Jones/200/435 15/Jan/Jones/272/105 15/Jan/Jones/100/503 When pulling the data into moodle I would only get 8/Bob/Jones/345/109 15/Jan/Jones/100/503 instead of the full dataset. I assume this is because of some way the moodle api is working and is auto grouping the results it gets or something. So what I would like is an auto-incrementing id field added in front of user_id during the mysql query so each record will be completely unique so moodle won't break the result set. Is there a way to do such a thing? I can't use the course id or group id fields since they will be shared among many students, so putting them first would likely cause the same problem. Thanks! |
| gus3 May 11, 2010 7:25 PM EDT |
Or, a new, computed column in the query, without modifying the schema. For example: (user_id*1000000) + (course_id*1000) + group_id Guaranteed unique. |
| techiem2 May 11, 2010 7:34 PM EDT |
that gives me an empty data set... select distinct (user_id*1000000) + (course_id*1000) + group_id, etc.... edit nvm...forgot to change variable to number in code. :P tweaking query now to see what happens... |
| techiem2 May 11, 2010 7:50 PM EDT |
K, that works great. Thanks! I'm trying to redo a procedure that currently takes an extremely long time, so hopefully with a better dataset I can significantly reduce the time it takes. |
| techiem2 May 11, 2010 9:35 PM EDT |
Yay it works right now! It's amazing what happens when you replace a procedure with several loops through large arrays from a couple queries each loop containing bunches of queries with a single loop through a single array from a query having most of the data you need with many less queries inside the loop. :P i.e. the whole procedure now takes about 30s or less instead of 30m (and usually not finishing). |
| gus3 May 11, 2010 10:23 PM EDT |
Have you considered using stored statements? Some engines are smart enough to do preliminary work when the statement is stored, minimizing the query-time work to only what depends on the statement's parameters. (I don't know if MySQL is that kind of smart, though.) Also, if your many-to-one/many relations have large values for "many", and/or long lists of columns for "one" (not likely in a course schedule), you may find a separate-query approach to be better, with joins carried out on the client side. It minimizes the network traffic, by avoiding duplicated data on the wire. Trust me, it works. |
| gus3 May 11, 2010 10:41 PM EDT |
Oh, hey, one more neat trick. That computed column can be tweaked into being a multi-column sorting key, where (user_id*1000000) + (course_id*1000) + group_id would sort first by user_id, then course_id, then group_id, thanks to the way they are multiplied and added. However, I would use that only in some brain-dead spreadsheet or tabulator. |
| techiem2 May 11, 2010 10:47 PM EDT |
Well, the new query and logic structure is MUCH faster (as you can see from my message above) than it was before.
The db server is on the same server as the web server. As I have time I'm trying to go back through my code and trying to optimize functions and the related queries. That one was the main one I'd been trying to fix because it didn't finish running most of the time because of the horrendous amounts of looping it was going through on the PHP side (foreach student inside foreach group loop - or maybe it was the other way around...either way it was awful) and the related horrendous number of sql queries it was running. I haven't really looked into stored statements at all, I'm just figuring out the details of working with explicit joins. |
| techiem2 May 11, 2010 10:48 PM EDT |
lol I'm not even using the computed column for anything other than slapping moodle into actually using all the results. But still cool. |
| gus3 May 11, 2010 10:59 PM EDT |
I thought that might be the case. I put that last comment up, for the benefit of people who find this thread via Google. |
| TxtEdMacs May 12, 2010 8:23 AM EDT |
techiem2, You might want to check out O'Reilly's "MySQL Stored Procedure Programming", which I believe is still the first edition version dating from 2006. The author(s) is/are Guy Harrison and Steven Feuerstein [the latter has a large number of Oracle programming books for O'Reilly to his credit, but may play a subsidiary role here]. This book allowed me finally to see IBM's version of its programming code syntax. To me this SQL looks too procedural and not enough set operation code. In addition I am left perplexed with the apparent love affair predisposed to landing results into cursors when so many database people are irrationally performance fanatics over all other concerns. HTH, at least the first mini paragraph (unfortunately all serious tags). Txt. |
Posting in this forum is limited to members of the group: [Editors, MEMBERS, SITEADMINS.]
Becoming a member of LXer is easy and free. Join Us!