When I make a query, I often wonder about this: Is it better to add extra field(s) to the table, or just get the values and calculate in your server side language? For example, table Student contains Student.score field. I want to get the grades. Do I get the score and make a bunch of if/else/switch for the grades? ...
PL/Ruby is a loadable procedural language for Postgres that lets you use Ruby to write user defined functions for the database. Anyone know of an equivalent way of writing user defined functions for MySQL in Ruby? ...
I'm building an English web dictionary where users can type in words and get definitions. I thought about this for a while and since the data is 100% static and I was only to retrieve one word at a time I was better off using the filesystem (ext3) as the database system instead of opting to use MySQL to store definitions. I figured there...
I have a PHP web application which uses a MySQL database for object tagging, in which I've used the tag structure accepted as the answer to this SO question. I'd like to implement a tag hierarchy, where each tag can have a unique parent tag. Searches for a parent tag T would then match all descendants of T (i.e. T, tags whos parent is T...
Here is a simple scenario with table characters: CharacterName GameTime Gold Live Foo 10 100 3 Foo 20 100 2 Foo 30 95 2 How do I get this output for SELECT Gold, Live FROM characters WHERE name = 'Foo' ORDER BY GameTime: Gold Live 100 3 0 -1 -5 0 using MySQL stored procedure (or query if it's even ...
Does anyone know of a simple way for a C++ program to communicate directly with a MySQL database? I have looked at MySQL++ and found it to be very confusing. If anyone knows of a very simple way of doing this, please let me know. Thanks P.S. I am developing on a Windows machine. PHP and MySQL web web application setup. C++ setup to...
I want to search a table to find all rows where one particular field is one of two values. I know exactly what the values would be, but I'm wondering which is the most efficient way to search for them: for the sake of example, the two values are "xpoints" and "ypoints". I know for certain that there will be no other values in that field...
Hi I was hoping to get some advice from the panel. I have a requirement to develop a .NET-based application whose data requirements are, in the future, likely to exceed the 4 gig limit of SQL 2005 Express Edition. There may be other customers of the same application in the future witwh a requirement to use a specific DB platform (such...
If I have a table like this: CREATE TABLE sizes ( name ENUM('small', 'medium', 'large') ); Is MySQL going to store those strings in every row, or internally will it use something smaller like integers to know which enum value to refer to? I want to use an enum in a table but I'm worried if it's as wasteful as storing a string in ...
I know that php has md5(), sha1(), and the hash() functions, but I want to create a hash using the MySQL PASSWORD() function. So far, the only way I can think of is to just query the server, but I want a function (preferably in php or Perl) that will do the same thing without querying MySQL at all. For example: MySQL hash -> 464bb2cb3...
In class, we are all 'studying' databases, and everyone is using Access. Bored with this, i am trying to do what the rest of the class is doing, but with raw SQL commands with mySQL instead of using Access. I have managed to create databases and tables, but now how do i make a relationship between two tables? If i have my two tables li...
Although I grasp the concept of Bitwise Operators, I can't say that I have come across many use cases during the webdevelopment process at which I had to resort to using Bitwise Operators. Do you use Bitwise Operators? Why do you use them? What are some example use cases? Please remember that this question is specifically intended fo...
I work on a app which has only one way to get a working DB connection: a function that returns a MySQL connection resource. Is there any way for me to convert that MySQL connection to a PDO MySQL connection? I don't have access to the MySQL server in any other way, no username, password, nothing. I can't see/get the file with the funct...
No doubt I'm missing something really simple here but I just can't see the problem with this query which is producing the following error: SQL query: INSERT INTO ads( ad_id, author, ad_date, category, title, description, condition, price, fullname, telephone, email, status, photo, photothumb ) VALUES ( NULL , 'justal', ...
After reading a couple of answers and comments on some SQL questions here, and also hearing that a friend of mine works at a place which has a policy which bans them, I'm wondering if there's anything wrong with using backticks around field names in MySQL. That is: SELECT `id`, `name`, `anotherfield` ... -- vs -- SELECT id, name, anoth...
I have a query on my database as such: SELECT * FROM expenses WHERE user_id = ? AND dated_on = ? I have added an index to the table on both the user_id and dated_on columns. When I inspect the indexes using SHOW INDEXES FROM expenses, there are two lines -- one with a seq_in_index value of 1, the other with a seq_in_index value of 2....
So I', debugging some code and there is the following SQL query (simplified from what it really is). SELECT ads.*, location.county FROM ads LEFT JOIN location ON location.county = ads.county_id WHERE ads.published = 1 AND ads.type = 13 AND ads.county_id = 2 OR ads.county_id = 5 OR ads.county_id = 7 OR ads.county_id = 9 I'm getting v...
In our office, we regularly enjoy some rounds of foosball / table football after work. I have put together a small java program that generates random 2vs2 lineups from the available players and stores the match results in a database afterwards. The current prediction of the outcome uses a simple average of all previous match results fro...
I have a contacts table which contains fields such as postcode, first name, last name, town, country, phone number etc, all of which are defined as VARCHAR(255) even though none of these fields will ever come close to having 255 characters. (If you're wondering, it's this way because Ruby on Rails migrations map String fields to VARCHAR...
Hi all, Does anyone know how much memory MyISAM and innoDB use? How does their memory usages compare when dealing with small tables vs. when dealing with bigger tables (up to 32 GB)? I know innoDB is heavier than MyISAM, but just how much more? Any help would be appreciated. Thanks, jb ...