|
Doing it in PHP would probably be the easiest, otherwise you'd have to do something retarded likecode:
|
# ? Oct 19, 2010 22:55 |
|
|
# ? May 28, 2024 06:27 |
|
I created a website for my fraternity. I was wondering if there is a way to order database output in the greek alphabet, for example alpha, beta, gamma ect. Currently it is being output in alphabetic order.
|
# ? Oct 20, 2010 05:32 |
|
Golbez posted:Let's say I have a status history table... Totally doable. I think. This will use MySQL's don't-have-to-GROUP BY-all-the-columns logic, which may not work in not-MySQL databases. You did not specify which SQL server you're using, but you're using PHP, and PHP and MySQL go together like Dumb and Dumber. code:
|
# ? Oct 20, 2010 05:40 |
|
McGlockenshire posted:Totally doable. I think. This will use MySQL's don't-have-to-GROUP BY-all-the-columns logic, which may not work in not-MySQL databases. You did not specify which SQL server you're using, but you're using PHP, and PHP and MySQL go together like Dumb and Dumber. Thanks, though I'm not really getting it to work, but... immediate last status? What if there's three in a row? Yes, PHP/MySQL, I keep forgetting to mention my DB here.
|
# ? Oct 20, 2010 14:49 |
|
Golbez posted:Thanks, though I'm not really getting it to work, but... immediate last status? What if there's three in a row? Uh, yeah, dunno about that one. Time to break out the PHP.
|
# ? Oct 20, 2010 17:50 |
|
Golbez posted:Thanks, though I'm not really getting it to work, but... immediate last status? What if there's three in a row? Yes, PHP/MySQL, I keep forgetting to mention my DB here. I haven't given it lots of thought but in SQL Server I'd start looking at breaking out the cursors or do it clientside. Or look at the application that inputs the data and make it just update the date if the status is the same.
|
# ? Oct 20, 2010 18:14 |
|
Golbez posted:Thanks, though I'm not really getting it to work, but... immediate last status? What if there's three in a row? Yes, PHP/MySQL, I keep forgetting to mention my DB here. I think if you do something like my last query it would work. Innermost subquery: SELECT Status FROM Status_History ORDER BY Date DESC LIMIT 1 this gives you the most current Status Middle subquery: SELECT Date FROM Status_History WHERE Status <> [Innermost subquery] ORDER BY Date DESC LIMIT 1 This gives you the date of the next most current Status Outer query: SELECT Date FROM Status_History WHERE Date > [Middle subquery] ORDER BY Date ASC LIMIT 1 This should give you the next most current date after the second Status, which I think is what you're looking for. code:
|
# ? Oct 20, 2010 18:33 |
|
DholmbladRU posted:I created a website for my fraternity. I was wondering if there is a way to order database output in the greek alphabet, for example alpha, beta, gamma ect. Currently it is being output in alphabetic order. You might be able to do this if you mess around with location/language encoding set but that would be a Bad Idea in my opinion. The greek letters are basically extended characters, and probably go up incrementally anyway. You might be able to do something like convert the first letter(s) to their ascii value and sort that way. Otherwise it sounds like something you'd have to do on the client.
|
# ? Oct 20, 2010 18:44 |
|
You could make a table to hold greek letters, and have foreign keys to that table. Then ordering by would be a case of ordering by the foreign key columns. If MySQL, code:
PS I can't actually use Greek letters in the above code because the forums are dumb. You might add extra columns for things like HTML entities and so on.
|
# ? Oct 20, 2010 19:57 |
|
I will take a look at that when I have time, thanks. I wont be using greek letters, but the words spelled out. so alpha, beta.. ect/.
|
# ? Oct 21, 2010 03:46 |
|
A couple of database design questions: 1) Phone numbers (American only, 10 digits) and Social Security Numbers. Should these be stored with or without dashes? Right now I'm doing it with dashes, validating input along the way. 2) Totals. Right now, we monitor transactions of adding or subtracting funds to a person's account. And right now, to get the total funds in an account, I sum up all of their transactions. And I was wondering if perhaps there was a better way of doing it. Maintaining it in a field in the user's record. But then I worry about when a transaction is made and, for whatever reason, the user's record isn't updated. MyISAM tables in a MySQL database. Any thoughts, or should I just keep it how it is? Edit: In a similar vein, the status updates I wrote about earlier. Right now, to find someone's status, I simply grab the most recent status message from the status history table. Should that be stored as well, or keep it 'dynamic' like this? Golbez fucked around with this message at 22:12 on Oct 21, 2010 |
# ? Oct 21, 2010 22:04 |
|
Golbez posted:A couple of database design questions: I don't store any formatting in the database, and phone numbers are a pain in the rear end especially because there are so many goddamn ways that people format them. If you just store digits, you can preg_replace('/[^\d]/','',$phone) and validate it that way. As for the transactions, if the calculation is trivial, you might as well do it every time. You could make a view that has the calculated total as one of the columns.
|
# ? Oct 21, 2010 22:14 |
|
Doctor rear end in a top hat posted:I don't store any formatting in the database, and phone numbers are a pain in the rear end especially because there are so many goddamn ways that people format them. If you just store digits, you can preg_replace('/[^\d]/','',$phone) and validate it that way. As for the transactions, if the calculation is trivial, you might as well do it every time. You could make a view that has the calculated total as one of the columns. Internal app, maybe 2 people ever using this system, and I validate numbers on a strict regex, so that's actually not too big an issue. Though, one might say the fact that I have to validate them that strictly is indicative of an issue.
|
# ? Oct 21, 2010 22:16 |
|
Golbez posted:A couple of database design questions: In general, I tend to advocate "unless you use actual numeric properties of the data, or real profiling indicates that doing otherwise is a speed concern, you should store opaque data as text rather than numbers". Why? Consider postal codes: In some countries, postal codes contain letters. In some countries, they contain leading zeroes. You never need to do numeric operations like adding two postal codes or checking if one postal code is less than another; such operations are meaningless. So if you store them as text, you won't have to go change everything later when you decide to expand to international codes. If you store your phone numbers as strings, your life will be easier when people want to start giving you "sip:example.com/John_Doe" as a phone number. This day may be many years in the future; it may never come at all. But the cost for planning ahead in this case is probably so low that it may as well be zero. Golbez posted:2) Totals. Right now, we monitor transactions of adding or subtracting funds to a person's account. And right now, to get the total funds in an account, I sum up all of their transactions. And I was wondering if perhaps there was a better way of doing it. Maintaining it in a field in the user's record. But then I worry about when a transaction is made and, for whatever reason, the user's record isn't updated. MyISAM tables in a MySQL database. Any thoughts, or should I just keep it how it is? You probably shouldn't be tracking money in a MySQL database, especially one that isn't transactional. In a real database, I'd suggest doing something like writing some stored procedures which simultaneously insert into the transaction table and update a cache of the balance in the user table. Then you only have one place you need to get the logic correct, and you don't have to worry about your application crashing midway through the process or something and leaving garbage data around.
|
# ? Oct 21, 2010 22:17 |
|
ShoulderDaemon posted:You probably shouldn't be tracking money in a MySQL database, especially one that isn't transactional. Does it make things better or worse that this is replacing an Access application?
|
# ? Oct 21, 2010 22:21 |
|
Golbez posted:Does it make things better or worse that this is replacing an Access application? Better. If you're writing a new application, is there any reason you have to be using MyISAM? Could you switch to postgres, or at least InnoDB?
|
# ? Oct 21, 2010 22:24 |
|
ShoulderDaemon posted:Better. If you're writing a new application, is there any reason you have to be using MyISAM? Could you switch to postgres, or at least InnoDB? MySQL is non-negotiable. InnoDB possibly, but house rules say use MyISAM. Fortunately, no money is moving through the system, so it doesn't have to be transactionally perfect; it's not like I'm debiting from one account and crediting another. I'm importing data from a weekly spreadsheet. It's record keeping and auditing.
|
# ? Oct 21, 2010 22:27 |
|
Another question, probably simple but I'm having a mindfart. Let's say I have... pre:User ID | Status | Date -------------------------------- 1 | AVAIL | 1/1/2009 2 | AVAIL | 1/5/2009 2 | NOT | 2/2/2009 1 | NOT | 3/3/2009 1 | AVAIL | 4/4/2009 3 | NOT | 5/5/2009 3 | AVAIL | 6/6/2009
|
# ? Oct 21, 2010 22:39 |
|
Golbez posted:MySQL is non-negotiable. InnoDB possibly, but house rules say use MyISAM. You will likely want a "transaction" table that links to the user table that lists all of the balances etc thats and innodb and just set it up to show the newest kinda thing in your sql.
|
# ? Oct 21, 2010 22:42 |
|
Golbez posted:MySQL is non-negotiable. InnoDB possibly, but house rules say use MyISAM. What's the reasoning behind that? AFAIK there are only a few special usage scenarios for which MyISAM is more appropriate than InnoDB. For something like what you are describing I really don't know why you would use MyISAM.
|
# ? Oct 21, 2010 22:55 |
|
Hammerite posted:What's the reasoning behind that? AFAIK there are only a few special usage scenarios for which MyISAM is more appropriate than InnoDB. For something like what you are describing I really don't know why you would use MyISAM. Because that's how it's been done and I just got here a few months ago and am still technically a temp? vv Though, since this is a project that is 99% independent from the rest of the site, I could probably get away with it without anyone knowing.
|
# ? Oct 21, 2010 22:56 |
|
Golbez posted:Another question, probably simple but I'm having a mindfart. This is the easiest thing i could come up with. code:
|
# ? Oct 21, 2010 23:12 |
|
Golbez posted:MySQL is non-negotiable. InnoDB possibly, but house rules say use MyISAM. If you do switch to InnoDB, make sure you look at the settings. It makes some kind of rollback that eats up a retarded amount of space with the default settings.
|
# ? Oct 21, 2010 23:20 |
|
Sprawl, I have a question based on something from what you did, which looks like it might work but this bit is confusing me. Here's a query:code:
Edit: And your query isn't working because of said issue. Could it be MySQL's fault? Edit 2: OK, I swapped the inner query for a simple "select ... order by desc limit 1", and added a "where status = 12" to the outer query, and it's working now. Thanks Golbez fucked around with this message at 15:47 on Oct 22, 2010 |
# ? Oct 22, 2010 14:53 |
|
Golbez posted:Sprawl, I have a question based on something from what you did, which looks like it might work but this bit is confusing me. Here's a query: If it wasn't working you were doing it wrong and without a full explanation of what you were doing with exact query and table layout i can't do anything to fix your broken work.
|
# ? Oct 22, 2010 15:48 |
|
Sprawl posted:If it wasn't working you were doing it wrong and without a full explanation of what you were doing with exact query and table layout i can't do anything to fix your broken work. Literally copying your query and changing the field names. That was it. Oho, wait - I was misreading your final 'group by id'; I was taking that to mean employee_id, you meant row id. OK, running that... oh hey it works Sorry. So yes, your work was actually perfect and I thank you very much for it.
|
# ? Oct 22, 2010 15:58 |
|
Golbez posted:A couple of database design questions:
|
# ? Oct 22, 2010 16:32 |
|
MoNsTeR posted:The current status should be stored on the entity to which it pertains. The history of that status should be stored in a separate table, typically trigger-populated (though you could use a procedure if you are allergic to triggers). Why, though? I've generally thought that you shouldn't really store calculated values, only the stuff they were calculated from, and the 'latest' entry in a history could qualify as that. (Now, if I were storing every single change, yeah, that wouldn't make sense, but that's more of a "history of the record" thing, rather than a record of history. Does that make sense?)
|
# ? Oct 22, 2010 16:46 |
|
Golbez posted:Why, though? I've generally thought that you shouldn't really store calculated values, only the stuff they were calculated from, and the 'latest' entry in a history could qualify as that. (Now, if I were storing every single change, yeah, that wouldn't make sense, but that's more of a "history of the record" thing, rather than a record of history. Does that make sense?) Storing calculated values if its a complex calculation, ie involving taxes, burdens, discounts etc. should likely be stored so you don't run into rounding issues.
|
# ? Oct 22, 2010 16:50 |
|
Golbez posted:Why, though? I've generally thought that you shouldn't really store calculated values, only the stuff they were calculated from, and the 'latest' entry in a history could qualify as that. (Now, if I were storing every single change, yeah, that wouldn't make sense, but that's more of a "history of the record" thing, rather than a record of history. Does that make sense?) "Current state" is by no means a calculated value, it only seems that way because you're starting from a model of storing state changes as events rather than just... storing state. Now, sometimes an event model is appropriate. But if a thing has a state, just store it! It is "history of the record" because state is part of the record and that's what you're keeping history of. 9 times out of 10, in the actual transactional system (viz. reporting), you care about the current state. And when you do want the history, you just need to display it, not analyze or interact with it. Optimize for those use cases.
|
# ? Oct 23, 2010 00:22 |
|
Ok, I'm beating my head against a wall here. I'm trying to make an sql update that will check the recipients table to see how many times a given message has been sent, and update the num_recipients value in the messages table with that number.pre:Table 1: messages msgID num_recipients 1 ? 2 ? 3 ? Table 2: recipients msgID recipient 1 frank 1 ben 2 jesse code:
|
# ? Oct 23, 2010 01:52 |
|
Like this?code:
|
# ? Oct 23, 2010 02:14 |
|
I didn't know you could alias the table you were working on in the update and then reference it within the statement. Thanks so much! (clearly I was over thinking the issue)
|
# ? Oct 23, 2010 02:40 |
|
MoNsTeR posted:You're not storing every change? That doesn't make any sense. By every change, I meant along the lines of, "On timestamp X, they changed the address from this to this. On timestamp Y, they changed this minor field in this minor table to this", etc. It would make no sense to generate a record from that. But generate a sum from a list of sums, or generate "current status" from the most recent status update, where past statuses do matter? However, because of other new requirements, I have to end up generating dates from the most recent status, so the whole 'not storing a calculated value' thing is out the window.
|
# ? Oct 23, 2010 08:30 |
|
Roughly speaking I have data that looks like this: userID,username,hits,date 1,bob,100,01/01/2010 2,bob,200,01/01/2010 3,jim,300,01/01/2010 4,bill,150,01/01/2010 But it's over 40 userid, 20 usernames and the last 4 years. I want to see a progression of the average hits per month that's based on the average hits per day of usernames that have a total of over 200 hits in a day. Since I have so much raw data, I figured using a database would be the best way to crunch it. However, I'm pretty stuck. Am I using the wrong tool, or could someone explain how I would use a database to get what I want? I basically want to examine if people's stats are declining.
|
# ? Oct 25, 2010 01:49 |
|
Gimpalimpa posted:Roughly speaking I have data that looks like this: Thats mysql? and that date field is a date type? if so that's easy. code:
|
# ? Oct 25, 2010 07:16 |
|
edit: nevermind, didn't see your table structure correctly
|
# ? Oct 25, 2010 07:19 |
|
Gimpalimpa posted:Roughly speaking I have data that looks like this: When I read what you're saying, I think this: code:
Avarkx fucked around with this message at 14:43 on Oct 28, 2010 |
# ? Oct 28, 2010 13:37 |
|
Best practices: Storing names. Right now, we store last name and first name. But I'm seeing some problems and inconsistencies. For example, sometimes there's a middle initial tacked to first name, and sometimes it even has a period after it. Sometimes "Jr." is after the last name, sometimes the first name. This obviously is not good. What's the best way of storing this stuff? I don't need too much, and only ever search on first and last name; middle, etc. is irrelevant for everything but reports. I was wondering if this seemed reasonable: LastName FirstName FullName It seems a little off to demand people enter or change a name twice, but on the other hand, a name is not something that changes often. People may still enter middle initials without a period but that's less a worry. And there may be inconsistency with how they handle Jr. (with or without a comma? with or without a period?) But, at least the searchable data itself is clean. Another option is: LastName FirstName MiddleName Suffix The middle name could enforce a period if there's only one letter in it, and the suffix could be populated from a dropdown which would ensure the proper formatting. Then I wonder if I'm making a big deal out of nothing. As stated, the name outside of the basics is for display only, but I'd like our display to be nice.
|
# ? Oct 28, 2010 15:54 |
|
|
# ? May 28, 2024 06:27 |
|
In the camp of "things I believe in," I would say that's application layer stuff. If you're as utterly misanthropic as I am, you do something like this:code:
That particular query will fail hard for last names like "Van J%" and is culturally insensitive to hillbilly names like "Billy Bob," so I would say setting up the application layer to take prefix, firstname, middlename, lastname and/or suffix is the best way to allow users to tell you all about their marital statuses, Ph.Ds, knighthoods, military ranks and esquire-isms that make them more different and special than the next processing number. Avarkx fucked around with this message at 17:41 on Oct 28, 2010 |
# ? Oct 28, 2010 17:01 |