mysql-query

mysql result is "special character"-insensitive

Hi, It seems that when i alter a mysql table (on a utf-8 table/columns) unique that it returns a duplicate entry error. Example: ALTER TABLE name ADD UNIQUE(name) error: Duplicate entry 'Adé' for key 'name_UNIQUE' I think it's because of the follow to rows in my database Ade, Adé Is it possible to alter a table unique with spe...

Alter MySQL column with values to auto increment

Hi all, I had a hard time with the title, so let me explain. What I'm doing is using the jQuery UI to create sortable list elements on a page. Once the order is submitted, php assigns an incrementing value to the list elements based on their order, drops the existing id column, creates a new id column and inserts each list elements valu...

mySQL: Updating multiple values in a record with a SELECT from another table

Trying to sort out the correct syntax for this UPDATE: UPDATE `foo` SET (`x`, `y`, `z`) = (SELECT `x`, `y`, `z` FROM `bar` WHERE `id` = 'baz'); In the actual query, there are 165 columns so I very much do not want to have to do x = x for each column. The columns are not a p...

mysql date query which selects multiple dates

I need a single query that is similar to SELECT datetime FROM blog WHERE UNIX_TIMESTAMP(datetime) < '".($date_string_in_seconds +1000)."' ORDER BY datetime DESC but instead of returning the latest date that is smaller than the one given in parameter, it has to return: -the record with the exact date that is given in the parameter -if ...

Change Column to Auto_Increment

Hi, I asked this question a little earlier today but am not sure as to how clear I was. I have a MySQL column filled with ordered numbers 1-56. These numbers were generated by my PHP script, not by auto_increment. What I'd like to do is make this column auto_incrementing after the PHP script sets the proper numbers. The PHP script wor...

escaping special character in mysql statements while using UPDATE...

I am trying to update a field using UPDATE table set field='some_variable' where id=1; The problem here is that the some_variable comtains data with multiple special characters in it. so I am not able to use 'some_variable' or "some_variable" as it breaks and fails when it encounters the first same character(' or "). How can I overco...

Proper sql query in MySQL

I have table like this: Name Result T1 fail T2 pass T3 pass T2 fail T4 fail T1 pass T4 fail Now, I want to get a results like this: Name Result T1 pass T2 pass T3 pass T4 fail I tried using query like this, but it does not work. select (case when Result = "pass" then "p...

MySQL left join subquery fail

Following query runs well in MySQL 5.x SELECT m_area.id, m_area.cn_areaName, m_area.de_areaName, m_area.en_areaName,m_area.jp_areaName,t_shop.count FROM m_area left join ( select t_shop.areaID, count(areaID) AS count from t_shop group by t_shop.areaID ) t_shop on m_area.id = t_shop.areaID However, when I have to run it in a 4...

Select order products in MySQL from muliple tables

I have 3 tables: orderProducts (orderId, productId, productValue), products (productId, productName) values (productId, productValue, productValuePrice). I need to select order products (product id, name, value, price) with defined orderId. How to do that with one MySQL query? ...

Query optimisation

In my application, my module uses the following tables PUBLIC_APPLICATION CATEGORY_MASTER NOTIFICATION_SITE_DETAIL DIMENSION_MASTER DM PUBLIC_REGISTRATION ALLOTMENT_NOTIFICATION. From the following tables I am retrieving the data SELECT PA.REGISTRATION_NO,PA.APP_ID,PA.NO_OF_ATTEMPTS,CM.CATEGORY_NAME, DM.SITE_DIMENSION,PR.B...

how to delete multiple entries in mysql

I have db with multiple entries. I Google out something like this SELECT COUNT(*), item_id, text, number FROM ads GROUP BY item_id, text, number HAVING COUNT(*)>1; this select (I think) all my multiple entries, I use SQLyog, ... and there is no option to press button and delete all results of this query. but even if I select all one...

Sorting by ratings in a database - Where to put this SQL? (PHP/MySQL)

Hi folks OK - I'll get straight to the point - here's the PHP code in question: <h2>Highest Rated:</h2> <?php // Our query base $query = $this->db->query("SELECT * FROM code ORDER BY rating DESC"); foreach($query->result() as $row) { ?> <h3><?php echo $row->title." ID: ";echo...

ON DUPLICATE KEY UPDATE for Specific Key in Multi-Key Table

Let's say I have a table Foo. This table has the columns ID and UniqueCode defined like so: CREATE TABLE Foo ( ID BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, UniqueCode VARCHAR(255) NOT NULL, PRIMARY KEY (ID), CONSTRAINT UNIQUE(UniqueCode) ); Now let's say I have the following query: INSERT INTO Foo (ID, UniqueCode) VAL...

mysql_query preserve the data type created in the table on return?

Hi I have a table in mysql: CREATE TABLE user (id INT, name VARCHAR(250)); I query the table: $result = mysql_query("SELECT id,name FROM user"); I gather the results: while($row = mysql_fetch_assoc($result) $rv[] = $row; And I return the data: echo json_encode($rv); The problem is that the id returns as string and not int, ev...

Simplify a MySQL query

Hi, I have below a working query that needs to be simplified. The reason is that I need to expand it a lot to cover the real application. For each condition (Pos=xxx AND Indata=yyy) the current query doubles in size, and I have a lot of conditions. Also the ON clause will contain many more conditions than in the example.... The real a...

MYSQL - how to update a field for all items resulting from group by select statement

I have a select like this: SELECT field1, field2, field3 FROM table WHERE field1= 5 AND field_flag =1 GROUP BY field1, field2, field3 limit 1000; I want to update field_flag for the resulting rows. How can I do that in MySQL? ...

Importing databases which contain cross schema views

I am dealing with a project which uses cross-schema views heavily. The problem I am looking to solve is importing these schemas in simple manner, without errors. I'll explain this further: Let's say the schemas are A and B. In schema A there is a view which points to schema B and the other way around. When importing the A mysqldump fil...

MySQL query help

Hi I cant figure out any good way to get a list of tag.names and lang which isn't already inserted in combination inside images_urls. My database looks something like this. tags name user_id +-------+---------+ |hi | 1 | |friend | 1 | |friend | 2 | |people | 2 | +-------+---------+ users id lang...

how can I use curly braces in a mysql query?

I tried to do this: INSERT INTO test123 VALUES(Some text, 'More Text (and in braces.)'); but it doesn't work. The braces in the string 'More Text (and in braces.)' cause the syntax error. How can I make it work - I need mysql to accept the braces. ...

filter mySQL database with multiple words in any order in Concatenated field

I have Concatenated in mySQL to produce a field that I can search with. This contains animal as well as owner names and addresses for a client database. I need to be able to search by animal name, owner names and postcode in any order. Thus if if my name is john smith and i own a dog called Fido and live in postcode AB1 2CD, I want the s...