mysql

How do I enable cross-database joins in mysql?

I am trying to port some data over from my production database to my sandbox using a query like this: INSERT `dbsandbox`.`SomeTable`(Field1, Field2, Field3) SELECT t.Field1, t.Field2, t.Field3 FROM `dbprod`.`SomeTable` t; When I attempt this cross-database join I get the following error: ERROR 1142 (42000): SELECT command denied to u...

Inverse of SQL LIKE '%value%'

I have a MySQL table containing domain names: +----+---------------+ | id | domain | +----+---------------+ | 1 | amazon.com | | 2 | google.com | | 3 | microsoft.com | | | ... | +----+---------------+ I'd like to be able to search through this table for a full hostname (i.e. 'www.google.com'). If it were t...

Mysql "magic" catch all column for select statement

Is there a way that I can do a select as such select * from attributes where product_id = 500 would return id name description 1 wheel round and black 2 horn makes loud noise 3 window solid object you can see through and the query select * from attributes where product_id = 234 would return the same results ...

MySQL (InnoDB): need to delete column, and accompanying foreign key constraint and index

Here's my table: CREATE TABLE `alums_alumphoto` ( `id` int(11) NOT NULL auto_increment, `alum_id` int(11) NOT NULL, `photo_id` int(11) default NULL, `media_id` int(11) default NULL, `updated` datetime NOT NULL, PRIMARY KEY (`id`), KEY `alums_alumphoto_alum_id` (`alum_id`), KEY `alums_alumphoto_photo_id...

MySQL/PHP - escaping characters that may slow my database down (or make it perform unexpectedly)

I run all my integers through a (int)Integer to make them safe to use in my query strings. I also run my strings through this function code if(!get_magic_quotes_gpc()) { $string = mysql_real_escape_string($string); } $pattern = array("\\'", "\\\"", "\\\\", "\\0"); $replace = array("", "", "", ""); if(pr...

PHP mySQL - Can you return an associated array with a number index?

Hi! I have this method in my db class public function query($queryString) { if (!$this->_connected) $this->_connectToDb(); //connect to database $results = mysql_query($queryString, $this->_dbLink) or trigger_error(mysql_error()); return mysql_num_rows($results) > 0 ? mysql_fetch_assoc($results) : false; ...

How do I forward from one local port to another local port on OS X?

I have a MySQL instance running locally on port 3306, but for some legacy apps I also want to make it available on port 3305 (don't ask). Is there an easy way to do this on OS X, so that if I try to connect on either 3305 or 3306 they will both go to the MySQL server on 3306? ...

Best way to represt n/ depth tree for use in PHP (MySQL / XML / ?)

I am currently in the process of rewriting an application whereby teachers can plan curriculum online. The application guides teachers through a process of creating a unit of work for their students. The tool is currently used in three states but we have plans to get much bigger than that. One of the major draw cards of the application...

MySQL - how to use index in WHERE x IN (<subquery>)

I'm using this query to get all employees of {clients with name starting with lowercase "a"}: SELECT * FROM employees WHERE client_id IN (SELECT id FROM clients WHERE name LIKE 'a%') Column employees.client_id is an int, with INDEX client_id (index_id). The subquery should IMHO return a list of id-s, which is then used in the WHERE...

mysql join question

Hi, I have two tables with the following columns: table1: id, agent_name, ticket_id, category, date_logged table2: id, agent_name, department, admin_status What I'm trying to achieve is to Select all rows from table1 where an agents department is equal to that of table2. I've tried a few different join statements but I'm either ...

MySQL subselect performance question?

I need advice regarding subselect performance in MySQL. For a reason that I can't change, I am not able to use JOIN to create quesry filter, I can only add another AND clause in WHERE. What is the peformance of: select tasks.* from tasks where some criteria and task.project_id not in (select id from project where project.is_templa...

What does character set and collation mean exactly?

I can read the mysql docs and they are pretty clear. But, how does one decide which character set to use? On what stuff does collation have an effect? I'm asking for an explanation of the two and how to choose them... Thanks! ...

MySQL config file location - redhat linux server

What is the default location for the MySQL configuration file on a redhat linux box? ...

MySQL - Changing the query to be distinct on just 1 column

I have the following query which works except that I would like it to behave differently. Now it looks for all the duplicate rows on url and returns it in the order of number of duplicate urls. I use GROUP_ CONCAT to seperate all the different screen_name's. However there can be multiple rows with the same url and same screen_name. How...

mysql update statement behaves differently based on whether a user variable exists

I'm reseting a sort column that has duplicate or missing values like so: set @last=''; set @sort=NULL; update conf_profile set sort= if( @last=(@last:=concat(org_id,',',profile_type_id,',',page,',',col)), (@sort:=@sort+1), (@sort:=0) ) order by org_id,profile_type_id,page,col,sort,id; (Go through all th...

Best way to merge two Django projects' user tables?

I've got two Django projects on the same server. The first launched several months ago, and has since collected hundreds of user accounts. The second project is launching in a few days, and we'd like the second project to allow users of the first app to authenticate using the same credentials. At first, I was going to simply dump the ...

What is the deal with the unicode character 首(U+9996) and how java/mysql deal with it and its friends?

Man, this character encoding hole just keeps on getting deeper. Sigh. Ok. Check this out: I have a java String that contains the unicode character U+9996 (that's what I get if I do codePointAt()). If I look at it in the debugger expressions panel (in eclipse) then all is well and it looks like "首". However if I print it out to the conso...

Two column primary key in MySQL

I have a very simple problem and a solution that will work, but I'm looking for a simpler one. I'd like to prevent rows from being added to a database when multiple values equal existing values. For example, if a2=a1 AND b2=b1 then the data gets rejected. If only a2=a1 or only b2=b1 it is allowed. Basically I want it to act like a pr...

Getting around MySQL "Can't reopen table" error

Hi folks, I'm currently busy implementing a filter of sorts for which I need to generate an INNER JOIN clausse for every "tag" to filter on. The problem is that after a whole bunch of SQL, I have a table that contains all the information I need to make my selection, but I need it again for every generated INNER JOIN This basically loo...

What is the correct/ fastest way to update/insert a record in sql (Firebird/MySql)

I need some SQL to update a record in a database if it exists and insert it when it does not, looking around there looks to be several solutions for this, but I don't know what are the correct/ accepted ways to do this. I would ideally like it to work on both Firebird 2 and MySQL 5 as the update will need to be ran against both database...