I used a query a few weeks ago in MySQL that described a table and suggested possible improvements to its structure. For example, if I have an int field but only the numbers 1-3 in that field, it will suggest set(1,2,3) as the type. I think I was using phpMyAdmin but I've been through all the functions I can find - Analyze, Describe, Ex...
Say I have a table called "xml" that stores XML files in a single column "data". How would I write a MySQL query that run an XPath and return only rows matching that XPath? ...
Hi, I have a user table like this user_id | community_id | registration_date -------------------------------------------- 1 | 1 | 2008-01-01 2 | 1 | 2008-05-01 3 | 2 | 2008-01-28 4 | 2 | 2008-07-22 5 | 3 | 2008-01-11 For each community, I would like ...
Hi There, I'm starting to build a community website from the site up and my web framework will be Asp.net and Mysql. I want to start planning some scalability into the infrastructure early because I'm anticipating high traffic when the site goes live. Are there any case studies which you recommend reading where asp.net or mysql has be...
I have a database with 2 tables. One of the tables holds a row containing numbers, from 0 to 10. In PHP, I do this: $query = "SELECT ".$param." FROM issues WHERE ".$param." >=0"; $result = @mysql_query($query) or showError("query failed"); if (!($record = mysql_fetch_array($result))) return null; return $record; The $param holds t...
I have an ASP.Net form and i would like to save it into a database any ideas?? ...
Hi! I have a MySQL table LOGIN_LOG with fields ID, PLAYER, TIMESTAMP and ACTION. ACTION can be either 'login' or 'logout'. Only around 20% of the logins have an accompanying logout row. For those that do, I want to calculate the average duration. I'm thinking of something like select avg(LL2.TIMESTAMP - LL1.TIMESTAMP) from LOGIN_LOG L...
I have a stored procedure that needs to convert hexadecimal numbers to their decimal equivalent. I've read the documentation for the UNHEX() function, but it is returning a binary value. What I'm wanting to do is something like this: CREATE PROCEDURE foo( hex_val VARCHAR(10) ) BEGIN DECLARE dec_val INTEGER; SET dec_val = UNHE...
Ok, I'm not great in mysql, but I know an index would help me out here, however I've done some plugging and can't find one to help... Anyone got any ideas? explain select `users_usr`.`id_usr` AS `id_usr`, `users_usr`.`firstname_usr` AS `firstname_usr`, `users_usr`.`lastname_usr` AS `lastname_usr`,`users_usr`.`social_usr` AS `socia...
Heres the issue. I have a table(mySQL) that contains dates and some of them are null. I know that I can use DateTime? or Nullable to allow nulls, but I'm not sure where to set it. What I've tried: I built the class in the dblm. The date propery has the following attributes set: Nullable : True Server Data Type : DateTime Type : NUll...
I'm trying to get Unicode working properly in rails using MySQL. Now, Rails displays the text correctly, but it shows up as ??? in MySQL. Additionally, I am not able to filter the text. My MySQL database has been configured with the utf8 character set. My client character is also UTF8. Likewise, rails is set to use UTF8. If I ent...
Hello, I have some html that was inserted into a mysql database from a csv file, which in turn was exported from an access mdb file. The mdb file was exported as Unicode, and indeed is unocode. I am however unsure as what encoding the mysql database has. When I try to echo out html stored in a field however, there is no unicode. This i...
How to implement a great search within a mysqldb - within a table if i search with '...LIke %bla%....' not all entrys would be found - if bla within a word for example. a search with soundex would be great to - but if i read the manual i must create an soundex-index to search for soundex-values? So the question whats the "best practice...
Data table structure is: id1,id2,id3,id4,... (some other fields). I want to create summary query to find out how many times some ID value is used in every column. Data 1,2,3,4,2008 2,3,5,1,2008 1,3,2,5,2007 1,2,3,6,2007 3,1,2,5,2007 For value 1, the result should be 1,0,0,1,2008 2,1,0,0,2007 How to accomplish this with one query (in M...
I have 2 tables (A and B) with the same primary keys. I want to select all row that are in A and not in B. The following works: select * from A where not exists (select * from B where A.pk=B.pk); however it seems quite bad (~2 sec on only 100k rows in A and 3-10k less in B) Is there a better way to run this? Perhaps as a left join? ...
For a web application I'm developing, I need to store a large number of records. Each record will consist of a primary key and a single (short-ish) string value. I expect to have about 100GB storage available and would like to be able to use it all. The records will be inserted, deleted and read frequently and I must use a MySQL databas...
After upgrading a few gems via terminal on my mac, I have created a new rails project backed up by a mysql database. Upon starting the app, the regular welcome aboard page appears. Here's the problem - I tried clicking the link entitled "About your application's environment", I receive the following output in my browser: MissingSource...
I have the following attributes in my DB. statistic id, devicename, value, timestamp. For a given statistic, I want to find the 2 most recent timestamps and corresopnding values for a every unique device. I am trying stuff like Trial 1) select statistic, devicename, value, timestamp from X_STATSVALUE where statistic=19 order by...
I am setting up a very small mySQL database that stores, first name, last name, email and phone number and am struggling to find the 'perfect' datatype for each field. I know there is no such thing as a perfect answer, but there must be some sort of common convention for commonly used fields such as these. For instance, I have determined...
I want to convert user-submitted date format (mm/dd/yyyy) to a MySQL date format (YYYY-mm-dd). Submission is via a simple PHP form direc tto MySQL database. ...