mysql

join where the specifying column depends on the results

I want to set up a join so that the following pseudocode would apply SELECT * FROM client_rates cl, languages l IF cl.Language is numeric // i.e. is not a specific language join condition = WHERE cl.Language = l.category ELSE join condition = WHERE cl.Language = l.Language Is it possible to achieve this just in (My)SQL? ...

How can I create a random, circular partner assignment?

I'm allowing users to register on my site for a 'game'; as is normal, they all receive unique IDs. At a given point in time, registration will cease. At that time, I would like to run code to assign partners, but not pairs. So, if I had the following: +--------+-------+ | user_id | partner | +--------+-------+ | 1 | | ...

Very slow TRUNCATE accompanied with "Server seems busy" on httpd log

I have this very simple table with only 35 rows on 5 simple columns (primary key on id auto increment, title (varchar), a bool, and 2 datetime). As we work in team, we import data from Google Docs, rephrase it as a SQL to do a TRUNCATE->LOCK TABLE WRITE->INSERT INTO (multiple lines)->UNLOCK TABLE at once. On other bigger, more complicate...

Rewrite a SQL query to work on heroku

Hi, How would i rewrite this rails query to run on Heroku (Portegres) @students = Student.find(:all, :conditions =>["(concat(first_name, ' ', last_name) LIKE ?) OR (concat(first_name, ' ', middle_names, ' ', last_name) LIKE ?)", "%#{params[:search]}%"...

MSSQL Try Catch and Set Identity Insert in MySQL ?

Do you know how to rewrite these this query in MySQL ? I can't find Identity insert, I can't find any try catch, I don't understand it. CREATE TRIGGER T1 ON DB1.dbo.A AFTER INSERT AS BEGIN TRY SET IDENTITY_INSERT DB2.dbo.B ON INSERT INTO dbo.B(id, text) SELECT A.id,A.text FROM dbo.A INNER JOIN inserted I ON I.id = A.id SET ID...

How could I create two levels of authentication for registration on my site?

I already have a simple registration system in place using php and mysql. It operates well enough. However, when people visit my site and register, I would like for them to register as part of a particular group. So, I was thinking that registration would happen like this: Visitor lands on index.php, clicks on "Group Registration" li...

PDO Prepared Statement does not UPDATE table

I am developing an ORM based on PDO, for tables that don't have unique ID fields, so when I update or delete, I need to compare to the previous values of the record, and LIMIT 1. When my query finally gets to the database and is executed with the parameters, everything is correct, as confirmed by the general query log, however, nothing ...

Implementing database search through query using like

Hi I am planning to implement database search through a website - I know there is full-text search offered by mysql, but turns out that it is not supported for innodb engine (which I need for transaction support). Other options are using sphinx or similar indexing applications. However they require some re factoring of the database str...

SELECT last id, without INSERT

Hi! I'm trying to retrieve the id of one table A to insert into another table B. I cannot use last_insert_id() as i have not inserted anything into A. Any ideas on how to do this nicely? $n = mysql_query("SELECT max(id) FROM tablename"); doesn't seem to work, nor does $n = mysql_query("SELECT max(id) FROM tablename GROUP BY id"); ...

select * math operations

is it possible to select colums and do complex operations on them for example select factorial(column1) from table1 or select integral_of(something) from table2 perhaps there are libraries that support such operations? ...

SELECT clause: Optional Columns?

I want to run a single query on my MYSQL database. The table I am searching has a file_type field that will be one of three values (1,2,3). Depending what these values are I want to look for different information from the database. So for example if the 'file_type' field is 1 or 2 then I want to SELECT fields a,b,c,d However if I notice...

how to fetch all the words which can be formed with the given letters?

hi consider I've a tables which has all the dictionary words, I want to fetch the list of words from the db which can be formed with the given set of alphabets.. for this what could be query???.. I thought of fetching every character using substr and comparing whether its present in a list and word length less than or equal to given ...

Jquery Plugins - date selector and crud(php mysql)

hi, I am trying to find a date picker that can show dates to select starting current date so that the users don't select the past date and I need not worry about validating. I am not sure if this will show the correct date for any one from any place. I found some plugins but none of it has this option. I found this http://jqueryui.com...

How to iterate through a MySQL table with Python?

Hi, I have a Python script which uses the MySQLdb interface to load various CSV files into MySQL tables. In my code, I use Python's standard CSV library to read the CSV, then I insert each field into the table one at a time, using an INSERT query. I do this rather than using LOAD DATA so that I can convert null values and other minor c...

MySQL Transaction handling with Insert Ignore (or similar) and Unique IDs

I'm using MySQL + PHP. I have some code that generates payments from an automatic payment table based on when they are due (so you can plan future payments... etc). The automatic script is run after activity on the site and sometimes gets run twice at the same time. To avoid this, we generate a uuid for a payment where there only can be ...

Django models : Inverted index on timestamp with query for 'n' latest rows

I have the datetime filled with auto_now=True Database is mysql. Range queries won't work for me directly. Is there any one liner for this? For simplicity, how can I get 10 rows with highest values for a field(let it be Integerfield) ? ...

create an index without locking the DB

I have a table with 10+ million rows. I need to create an index on a single column, however, the index takes so long to create that I get locks against the table. It may be important to note that the index is being created as part of a 'rake db:migrate' step... I'm not adverse to creating the index manually if that will work. UPDATE: ...

How to throttle or prioritize a query in MySql

Is there anyway to prioritize or throttle a query at all in MySQL? I'm a DBA on a server that sees a lot of unoptimized queries come into the server and they just destroy the CPU. I'm looking at throttling certain users that hit on the database in poor fashion. Clarification: I realize that MySQL has facilities built in to limit numbe...

Programming a front-end for a MySQL database

I need to make a front-end desktop application for a MySQL database to be used on a Windows machine. What language(s) should I use to make a front end that will connect to the MySQL backend? I will need to be able to search through the database and pull data. ...

MySql Connector prepared statement only transfers 64 bytes

I am using the MySql Connector C++ to store a JPEG image from a file into the database. I am using the prepared statement. After execution of the prepared statement, only the first 64 bytes of the file are copied into the database. My research of examples show that no iteration is necessary and the examples assume that the prepared ...