mysql

Inserting rows into a MySQL table based on a query

I have a query SELECT A.a, A.b, A.c FROM A LEFT JOIN X ON A.a = X.x WHERE X.x IS NULL which finds entries in table A which have no corresponding entry in table X. How can I insert the results of this query into table X? If I had only a few entries I would do INSERT INTO X(xa, xb, xc) VALUES ('a1', 'b1', 'c1'), ('a2', 'b2', 'c2') ...

Error installing mysql2 gem on ruby 1.9.2

I am currently trying to install the mysql2 gem for a ruby on rails application. I am using ruby 1.9.2. Here is the error todd-andrewss-macbook:shiftly toddOld$ gem install mysql2 Building native extensions. This could take a while... ERROR: Error installing mysql2: ERROR: Failed to build gem native extension. /Users/toddOld/.rvm/r...

Database and model setup in Ruby On Rails

Hi I'm pretty new to database setup in Ruby and need help in setting it up correctly along with the models. Can anyone help? Basically I have an Organisation, which will have at least 2 types of Users - Member and Admin User. Both the Organisations and the Users have an Address. I was thinking this was basically three tables - Organ...

MySQL Foreign Key Constraint - Integer Column

I have an integer column which I would like to add a foreign key constraint to. Only problem is if that column does not have/need a value, by default MySQL puts in a '0' value. This obviously breaks the foreign key constraint as there is no record in the primary table with a PK of 0. How can I overcome this problem? ...

How to echo a formatted date in PHP from an array

I have a datetime column in MySQL let's call it $time. It's coming form a CakePHP form. If I try to echo it I get I just get "Array". If I print_r() on it I get: Array ( [month] => 10 [day] => 30 [year] => 2010 [hour] => 16 [min] => 30 ) I want to echo this out as a formatted date, nothing seems to work because it's not a string bu...

Cakephp Complex Relation Find

I have a little complex Database model on which i am not able to make a proper find user(id, name...) 1 Harsha 2 Jasmine modules (id, name ) 1 Users 2 Restaurants 3 Dishes restaurant (id, name.... ) 1 KFC 2 Pizza Hut dishes (id, name, restaurant_id .. ) 1 Cheese Pizza 2 items (id, module_id, item_id) 1 1 1 (refers to U...

mysql group_concat

I have two tables. products id title image_ids --------------------- 1 myproduct 1,2,3 images id title file_name ------------------------- 1 myimage myimage.jpg 2 myimage2 myimage2.jpg 3 myimage3 myimage3.jpg I want to query so that the names of the images are concatenated into a single field for each prod...

MySQL implementation with CUDA

Hi, I am a senior undergrad majoring in CS. At the moment I am taking a Computer Architecture class. We need to do a project. I want to do something related to CUDA, where the performance of the computation will have a moderate increase compred to a serial implementation. I am really interested in databases so I decided to do something...

SELECTING with multiple WHERE conditions on same column

Ok, I think I might be overlooking something obvious/simple here... but I need to write a query that returns only records that match multiple criteria on the same column... My table is a very simple linking setup for applying flags to a user ... ID contactid flag flag_type ----------------------------------- 118 99 ...

Scripting a MySQL query in Unix using daemon in PHP

I'm trying to make an "at" job work at a given time, for testing purposes I'm using $time but this will be a datetime that I get from a separate MySQL query. In the command line I go like this: echo "mysql -e 'UPDATE admin SET row1=row2 WHERE id=1;'" | at now And I get a "job 36 at 2010-10-28 15:05". in PHP I tried going like this: ...

Pylons: Sharing SQLAlchemy MySQL connection with external library

I am running Pylons using SQLAlchemy to connect to MySQL, so when I want to use a database connection in a controller, I can do this: from myapp.model.meta import Session class SomeController(BaseController): def index(self): conn = Session.connection() rows = conn.execute('SELECT whatever') ... Say my controller ...

Does FastCGI or Apache2 limit upload sizes?

I'm having a problem with file uploading. I'm using FastCGI on Apache2 (unix) to run a WSGI-compliant application. File uploads, in the form of images, are begin saved in a MySQL database. However, larger images are being truncated at 65535 bytes. As far as I can tell, nothing should be limiting the size of the files and I'm not sure whi...

passing mysql rows to scoring algorithm?

i have this php algorithm ranking function(hacker news), function calculate_score($votes, $item_hour_age, $gravity=1.8){ return ($votes - 1) / pow(($item_hour_age+2), $gravity); } and i have this table in mysql: post{id, post_text, votes, date}: i was wondering how i can pass these mysql data paramters to that function, to det...

[MySQL] Extremely slow UPDATE query

Hi everyone. I noticed that a script of mine became very slow, then I narrowed down to the problem: it was an Update query. The weird thing is that the SELECT query is very fast. The table has about 600,000 entries. And yes, id is UNIQUE PRIMARY KEY. Here are some examples: SELECT * FROM `tmp_pages_data` WHERE id = 19080 LIMIT 0 , 30 ...

How to do a mysqldump with a use database in the dump

So the command I normally use to get a database sent to another server is: mysqldump -u user -p --add-drop-tables database *file* But I always have to go into the file and add a line at the top: use database; So that I use the file to import to a different server. Is there a flag in mysqldump to automatically add the use database? ...

MySQL InnoDB Text Search Options

Knowing full well that my InnoDB tables don't support FULLTEXT searches, I'm wondering what my alternatives are for searching text in tables ? Is the performance that bad when using LIKE ? I see a lot of suggestions saying to make a copy of the InnoDB table in question in a MYISAM table, and then run queries against THAT table and matc...

MySQL Select And Count Date Range: Doesn't work crossing the month barrier

Hi Can you please tell me why this works: $customer_data_date14daysAgo = mysql_query("SELECT COUNT(*) AS count FROM tableName WHERE datetime BETWEEN '$date14daysAgo%' and '$dateToday%' ") or die(mysql_error()); But this doesn't? $customer_data_date30daysAgo = mysql_query("SELECT COUNT(*) AS count FROM tableName WHERE datetime BE...

querying results from mysql in the last 3 hours?

i want to query blog posts from the database created in the last 3 hours, table blogs{id,blog_text,date} date format: datetime ...

What's the difference between IN and INNER JOIN in my case?

In my MySQL database, table events has a composite index with columns closingeventid, timeStart and eventCode. There are more than 21 million rows in table events. Here is two SQLs, if I run the first one in MySQL command line, the Mem Usage of process mysqld-nt.exe increases gradually with 10 M per second, it goes up around 1.6 G then...

load balancing in php

I have a web service running written in PHP-MYSQL. The script involves fetching data from other websites like wikipedia,google etc. The average execution time for a script is 5 secs(Currently running on 1 server). Now I have been asked to scale the system to handle 60requests/second. Which of the approach should I follow. -Split function...