Consider writing an application that requires registration for an entity, and the schema has been defined to require the country, state/prov/county data to be normalized. This is fairly typical stuff here. Naming also is important to reflect. Each country has a different name for this entity: USA = states Australia = states + territori...
MySQL noob with a table-joining question: I have two tables, "splits" and "dividends" that describe events in the stock market. The splits table has "day", "ratio", and "ticker". The dividends table has "day", "amount", and "ticker". I would like to get a resulting joined table that has both tables' information, AND sorted by date and...
Hello to all, I want to know about the how can we prevent the data lost (or) data theft in mysql database? regards babu ...
In short: Is there any way to sort the values in a GROUP_CONCAT statement? Query: GROUP_CONCAT((SELECT GROUP_CONCAT(parent.name SEPARATOR " » ") FROM test_competence AS node, test_competence AS parent WHERE node.lft BETWEEN parent.lft AND parent.rgt AND node.id = l.competence AND parent.id != 1 ORDER BY parent.lft) SEPAR...
Hi All I need your help in identifying why this error is coming Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT) for operation '=' The procedure was working fine till yesterday and today it started giving error of illegal mix of collations. I checked each and every table of my database and all the tab...
I have the following problem (using mysql 5.0.70): In one table I have a varchar field containing some kind of a number - "0303A342", "21534463", "35663CE3", etc. Collation is set to utf8_general_ci. The problem shows up when a user of the system is trying to search for a record containing part of this number. SQL query looks like "...W...
I want to do a query containing 'like' and 'not like'. Current example: i want everything starting with '1|%' but not with '1|6|199|%' or '1|6|200|%'. Current query: 'SELECT * FROM `links` WHERE `category` LIKE '1|%' NOT LIKE '1|6|199|%','1|6|200|%' ORDER BY `score` DESC LIMIT 9'. But that doesn't work. Any tips? thx ...
I'm building a PHP/MySQL app where users can log in and search for media assets. They can also save searches and create lightboxes (collections of assets). Finally, they can create user groups. Does it make more sense to query all at once, at log in, for the id's for their saved searches, lightboxes, and groups and save those in sessi...
A few minutes ago, I asked whether it was better to perform many queries at once at log in and save the data in sessions, or to query as needed. I was surprised by the answer, (to query as needed). Are there other good rules of thumb to follow when building PHP/MySQL multi-user apps that speed up performance? I'm looking for specific w...
I want to run the following sql command: ALTER TABLE `my_table` ADD UNIQUE ( `ref_id` , `type` ); The problem is that some of the data in the table would make this invalid, therefore altering the table fails. Is there a clever way in MySQL to delete the duplicate rows? ...
The query below is truncated,with 'created' showing only 'cre'(scroll to the right most first:): mysql> SELECT GROUP_CONCAT(CONCAT('<comment><body><![CDATA[',body,']]></body>','<replier>',if(uid is not null,uid,''),'</replier>','<created>',created,'</created></comment>') SEPARATOR '') -> ...
Is it best to use a pre created class / api for database interaction (e.g Pear MDB2 or php's PDO) or create your own? I will be only be using mysql with 1 database and perform relatively simple SELECT, INSERT, UPDATE, DELETE queries. ...
I've had the same problem with sql for a while and as I sit down to write the same 'brute-force' hack I always use, I figure there MUST be a more efficient way to do what I want to do. I have tables similar to this: grades(gradeID, taskID, grade, username, date) tasks(taskID, task...) assignment(assignmentID, title...) assignment_tasks...
(no responses from my identical post on the MySQL forums, so I'm hoping to have better luck here) I'm trying to migrate a MS SQL database for a friend. I do not have physical access to the machine, nor do I have admin access -- just a read/write user. Using "SQL Server Management Express" in XP, I can easily login using IP/user/passwor...
I am coding in php and the code takes data from an array to fetch additional data from a mysql db. Because I need data from two different tables, I use nested while loops. But the code below always only prints out (echo "a: " . $data3[2]; or echo "b: " . $data3[2];) one time: foreach($stuff as $key) { $query3 = "SELECT * FROM foobar...
Hi, I have the following code $result = $handle->select()->from('store_products_id', array('count'=>'COUNT(store_products_id.product_id)')) ->where('store_products_id.store_id=?', $this->store_id) ->columns($selectColumns) ...
Here is a simplified query of something I am trying to do on a larger join query. It is still breaking on this small scale. I am trying to generate a random number for each row pulled back in the range of 1-60. I then want to order the returned rows by this random number. SELECT downloads . * , (FLOOR( 1 + ( RAND( ) *60 ) )) AS random...
I want my product, like every other web product out there, to pull a data information from my database and convert it to the user's timezone before displaying it on a page. I am using PHP and MySQL. Is the use of TIMESTAMP fields more common than the use of DATETIME fields? From my research, it seems that using DATETIME fields, I would...
Hi all i'm writing a simple Mysql query to pull out some CSV but the where clause don't seem to work any ideas would be a great assist select SUBSTRING_INDEX(email,'@',1)AS email , clear , SUBSTRING(email,LOCATE('@',email)+1) as domain where domain like 'somestring' from sometable; ...
I've been working in Postgis for point in poly's and i have this function: select * from table_name where st_contains(column, st_setsrid(st_makepoint(-92.095109, 46.804100),4326)); the column is a multipoly(((points))) I'm trying to find a way to do a similar exercise with MySQL, and wondered if there were similar functions to do so. ...