I've been experimenting with fulltext search lately and am curious about the meaning of the Score value. For example I have the following query: SELECT table. * , MATCH ( col1, col2, col3 ) AGAINST ( '+(Term1) +(Term1)' ) AS Score FROM table WHERE MATCH ( col1, col2, col3 ) AGAINST ( '+(Term1) +(Term1)' ) In the results for Score I'v...
What is the fastest way to load data from flatfiles into a MySQL database, and then create the relations between the tables via foreign keys? For example... I have a flat file in the format: [INDIVIDUAL] [POP] [MARKER] [GENOTYPE] "INDIVIDUAL1", "CEU", "rs55555","AA" "INDIVIDUAL1", "CEU", "rs535454","GA" "INDIVIDUAL1", "CEU",...
Hi, I'm terribly new to SQL, and cannot seem to get the desired information out, after trying a few different google searches, and reading through some SQL tutorials. I think it involves some sort of joins, but cannot get them straight. Given the following sample tables: Table 1(Activity This is updated every time a change is made to ...
we are dealing with a legacy application built on MySQL 5.0 (MyISAM). I need to know in real-time when an update, delete, or insert happens to a particular table. The 'gotcha' is that I can't change the db (i.e. write triggers) in any way. I have to use the bin log, replication or something else which is somewhat non-invasive. We've ...
Why don't databases automatically index tables based on query frequency? Do any tools exist to analyze a database and the queries it is receiving, and automatically create, or at least suggest which indexes to create? I'm specifically interested in MySQL, but I'd be curious for other databases as well. ...
I am trying to implement an application by using MySQL/JAVA and I found this tool in netbeans to easily create a new form for an existing table. (edit, insert, delete etc.) Is there an easy way to modify the code to join another table to display fields from that table. Example: I have an employee table that has a foreign key TitleID and...
Alright, so I have a query that looks like this: SELECT `orders`.*, GROUP_CONCAT( CONCAT( `menu_items`.`name`, ' ($', FORMAT(`menu_items`.`price`,2), ')' ) SEPARATOR '<br>' ) as `items`, SUM(`menu_items`.`price`) as `additional`, `children`.`first_name...
I'm working with a MySQL query that writes into an outfile. I run this query once every day or two and so I want to be able to remove the outfile without having to resort to su or sudo. The only way I can think of making that happen is to have the outfile written as owned by someone other than the mysql user. Is this possible? Edit: ...
I have a volunteers_2009 table that lists all the volunteers and a venues table that lists the venues that a volunteer can be assigned to, they are only assigned to one. What I want to do, is print out the number of volunteers assigned to each venue. I want it to print out like this: Name of Venue: # of volunteers table: volunteers_2...
I plan on using MySQL and it's built-in encryption functionality to encrypt / decrypt certain columns in certain tables. The concern I have is that I need to store the key somewhere. I could certainly store the key in a file and control the permissions of that file and the permissions of the application that accesses it, but is that en...
I want to find an SQL query to find rows where field1 does not contain $x. How can I do this? ...
Hi All, I was wondering if anyone had come across this one before. I have a customer who uses special characters in their product description field. Updating to a MySQL database works fine if we use their HTML equivalents but it fails if the character itself is used (copied from either character map or Word I would assume). Has anyone ...
So my group is trying to set up a shared-server environment for various and sundry web services. I think we've settled on setting disable_functions and disable_classes site wide in php.ini and php_admin_value to force open_basedir in each app's httpd.conf for php scripts, and passenger's user switching for ruby scripts. We still nee...
We put common prefixes on related tables to assure they display next to each other in our DB management software (Toad, Enterprise Manager, etc). So for example, all user tables start with the word User: User UserEvent UserPurchase Ideally, in honor of the three great virtues of a programmer these tables should be named User, Eve...
I am trying to connect to 2 databases on the same instance of MySQL from 1 PHP script. At the moment the only way I've figured out is to connect to both databases with a different user for each. I am using this in a migration script where I am grabbing data from the original database and inserting it into the new one, so I am looping t...
Hi, Im trying to use to define a one-to-many relationship in a single table. For example lets say I have a Groups table with these entries: Group: Group_1: name: Atlantic Records Group_2: name: Capital Records Group_3: name: Gnarls Barkley Group_4: name: Death Cab For Cutie Group_5: name: Coldplay Group...
I have been slowly learning SQL the last few weeks. I've picked up all of the relational algebra and the basics of how relational databases work. What I'm trying to do now is learn how it's implemented. A stumbling block I've come across in this, is foreign keys in MySQL. I can't seem to find much about the other than that they exist in...
I have a table named Info of this schema: int objectId; int time; int x, y; There is a lot of redundant data in the system - that is, objectId is not UNIQUE. For each objectId there can be multiple entries of time, x, y. I want to retrieve a list of the latest position of each object. I started out with this query: SELECT * FROM Inf...
When I connect to a MySQL database using PDO, the way I need to connect is: $pdoConnection = new PDO("mysql:host=hostname;dbname=databasename",user,password); But, for PostgreSQL, the DSN is more standard (IMO): $pdoConnection = new PDO("pgsql:host=hostname;dbname=databasename;user=username;password=thepassword"); Is there any reason w...
Say each row in a table has data pertaining to one particular user. The user has a password to access the system. How do I encrypt a column of data using InnoDB so that no one other than the user who's data it is can read the data ? I was thinking of something like using one of the MySQL encryption functions (say AES) with a key based o...