I have a list of cities that have numerous incorrect spelling for the same city. One city is misspelled 18 times! I am trying to clean this up but its taking hours. Is there some algorithm that might "guess" at the valid city name for each of these misspelled ones? Some form of weighting? The data is in MySQL and I do have a table o...
I'm planning a chat-like web application, where whenever one user posts something, all other users (that is, people with their browser pointing to that site) would get instant updates. The common choice for this is comet-style messaging using long-polling AJAX requests. Writing the client-side part with jQuery isn't much of a problem. ...
I have a table with the following format. mysql> describe unit_characteristics; +----------------------+------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------------------+------------------+------+-----+---------+----------------+ | id ...
This not a go/no go question. I've used all kinds of 4GLs (PL/SQL, T-SQL, PGSQL etc.) for database back-end coding and find very useful to establish data layer "closest" to database engine. Now MySQL stored procedures got my attention and very interested in coding in this brand new 4GL platform. I would like to start using it in one o...
I have to perform a SELECT operation in the DB. Since I want to narrow down the search results, I was wondering if I should use nested SELECTs or use a JOIN operation? I want to perform a dual search on the table. I unsuccessfully tried this: $receive_listings = mysql_query('SELECT * from show_detai...
I was considering using MySQL views to provide an abstraction when pulling data from the DB. As I was looking for material on this, I came across this article, which ends with: MySQL has long way to go getting queries with VIEWs properly optimized. The article is from 2007. Is this still applicable? Eg: Has MySQL solved these iss...
I have installed ruby and gems (both from macport) and have mysql 5.1.49 (see below for full details of version). I get an error on libssl 0.9.8 when I try to install the mysql gem. Any suggestions jcoelh000 ~# mysql --version mysql Ver 14.14 Distrib 5.1.49, for apple-darwin9.8.0 (i386) using readline 5.1 jcoelh000 ~# ruby...
I have a table with large amount of data. The data need to be updated frequently: delete old data and add new data. I have two options whenever there is an deletion event, I delete the entry immediately I marked delete the entries and use an cron job to delete at unpeak time. any efficiency difference between the two options? or an...
I want to produce an alphabetized list of names produced by adding together two columns of names, columnA, and columnB. I have tried this: <cfquery name="listAuthors" datasource="hhLibrary"> SELECT title, (a1_Fname + a2_Fname) AS ColumnA, (a1_Lname + a2_Lname) AS ColumnB FROM books WHERE ColumnB LIKE '#firstletter#%' ORDER BY ColumnB <...
I understand the size/storage constraints of MySQL TEXT and MEDIUMTEXT fields, but I just wanted to make absolutely sure (before I sign off on a change) that I'm not looking at any adverse effects from converting a field with existing data from TEXT to MEDIUMTEXT. My concerns are mainly performance, integrity, and disk storage. Thanks ...
Hi. I've got a problem with inserting two rows to table. The database is in UTF8. The problem seems to be connected to the collation. This statement works: insert into test(code,text) values('xx','aaa'); However when i try to add other row to the table: insert into test(code,text) values('xx','aąą'); it fails with duplicate entry e...
I am deploying a Ruby on Rails application that I developed with Sqlite3 to a server with either MySQL or PostgreSQL. I quickly discovered that the "group by" and "strftime" functions that I am using heavily to produce by-month rollup reports are working differently or not compatible between the various databases. I can refactor my co...
This is one of the more confusing functions I've attempted. The logic works within a select statement, but I can't seem to create a user-defined function. Here's the error: "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'delimiter$$ creat...
I'm trying to save some values from a form in CakePHP into a MySQL database and when the values go into the database they are getting html encoded. So something like (#45) gets stored in the database as (#45) and then when someone tries to go back and edit the form they see all the extra characters. I've tried setting the enc...
Dear all, I have a set of large values that need to be compared in mysql. May I know which is faster? For example: Opt 1: SELECT * FROM table WHERE v = 1 or v = 2 or v = 3 or v = 4 or... v = 100 Opt 2: SELECT * FROM table WHERE v IN (1,2,3,4,5,6,7,8,...,100) May I know which option is faster for large value? Is there any better s...
I'm thinking of adapting a current database application to have a persistent web-based component. The idea is to have a local client version that can be run whether or not the client has connectivity. The data for the application is stored in a local database. What I'd like to have is that, whenever the client does have connectivity, the...
I was wondering if it is possible in a mysql stored-function or stored-procedure compose a mysql query as a string variable you can execute later? I have a stored-function get_district_part ((district_id INT,county_id INT,city_id INT,zip_id INT)) RETURNS INT that references the table: CREATE TABLE IF NOT EXISTS `modx`.covereage_distric...
i have a simple mysql database. it has 3 tables. here is the create statement for them: CREATE DATABASE `qcvalues` /*!40100 DEFAULT CHARACTER SET latin1 */ CREATE TABLE `batchinfo` ( `rowid` int(11) NOT NULL AUTO_INCREMENT, `datapath` mediumtext, `analysistime` varchar(50) DEFAULT NULL, `reporttime` varchar(50) DEFAULT NULL, ...
I have a database of all countries and cities, I would like to use ajax to use it to give suggesstions in the country cities fields in forms, I have a lot of tables in my db related to the inner workings of the site. Is it ok if I put this in a different database (just this world map database)? I rather put it in a different db so it's m...
Can SVN replicate/copy/synchronize a database like it does code? ...