mysql

Pull data from production to dev for debugging (Django)

This is what I want: [Production MySQL Data] -> [Development MySQL Data] For debugging or improvement, I occasionally need to have a copy of the data running on the production site to be present in my development environment. Obviously I don't want to actually use the production database and manually entering the data is out of the qu...

Why is this MySQL UPDATE taking forever?

I'm trying to figure out why one of our migration scripts is taking forever we are trying to do an update that joins from another table to get a relevant piece of data. Each table (A, B) has about 100,000 rows. # now populate the ACHIEVEMENT_INSTANCE.OBJECTIVE_INSTANCE_ID update A a, B b set a.INSTANCE_ID = b.INSTANCE_ID where a.ID = b...

Mysql group by substring

I have a table with the following structure: id bigNumber text 1 1200321030011010233 "an item with some text" 2 1200321030011014563 "another item with some more text" 3 3120323434432211133 "more...." ... ... The table contains approximately 50.000 records. I want to do the following query but it is slow: SELECT COUNT(*), ...

Rails migration file not adding all my defined columns to the db

Hi, I'm trying to create a rails app, but somethings going wrong, I'm getting a nasty error undefined method title' for #` I've used this ling: ruby script/generate scaffold Blog title:String body:text to generate the following migration file: class CreateBlogs < ActiveRecord::Migration def self.up create_table :blogs do |t| ...

Delphi 7 Personal, MySQL using libmysql.dll + UTF8

Hi, I'm using Delphi 7 Personal. To access MySQL database I'm using libmysql.dll + very simple wrapper, which is good enough for me. Except one thing ... it doesn't seem to handle Utf8... is that possible somehow to pass Utf8 strings from libmysql to Delphi? Please keep in mind I'm not using commercial delphi, this means no ADO / dbExpr...

MySQL - Search and replace table1.field's contents with contents of table2.otherfield

I need a MySQL query that will do the following: Instead of the alpha team names in the 3rd column of the players table, I want it to reference teams.id table: players id player_name team ------------------------------------------ 1 Sue Smith Silly Chimps 2 Mike Olson Black Cats 3 ...

little complex sql

i have album table and album permission table. album album_id name private (values yes or no) album permission id album_id user_id **albums** album_id name private 1 family yes 2 friend no 3 outdoor pic yes **album permission** id album_id user_id 1 3 5 now i want to get all ...

Select * from table1 that does not exist in table2 with conditional

I have 2 tables. One is a table with things that can be learned. There is a JID that desribes each kind of row, and is unique to each row. The second table is a log of things that have been learned (the JID) and also the userid for the person that learned it. I am currently using this to select all of the data for the JID, but only t...

PHP/MYSQL: SELECT statement, multiple WHERE's, populated from database

I'm trying to figure out how to get a select statement to be populated by an ever-changing number of where's. This is for an order-status tracking application. Basically, the idea is a user (customer of our company) logs in, and can see his/her orders, check status, etc. No problem. The problem arises when that user needs to be associat...

MYSQL SUM GROUP BY

I'm working on a high school grading system. At my school, grades can be changed by reworking problems and I store these changes with dates. I have a function that properly returns averages because the most recent grade is flagged with a "current" field with a value of '1'. I'd like to make the function capable of returning the most re...

File download time in PHP/MySQL

Hi friends, I want to find download time in PHP by sending a request to my MySQL server. I have one table with an href link to download the file. If I click download the download time should show beside the file name in a table. Can anyone please help me out? Today is my deadline. Please check out my code: <?php $con = mysql_connect("lo...

Does this query guarantee me a 'race free' PK value?

I was just reading How to avoid a database race condition when manually incrementing PK of new row. There was a lot of good suggestions like having a separate table to get the PK values. So I wonder if a query like this: INSERT INTO Party VALUES( (SELECT MAX(id)+1 FROM (SELECT id FROM Party) as x), 'A-XXXXXXXX-X','Joseph'...

MySQL C API custom values

I have been working with the tutorial on MySQL C API from http://zetcode.com/tutorials/mysqlcapitutorial/ the following example is working fine: #include <my_global.h> #include <mysql.h> int main(int argc, char **argv) { MYSQL *conn; conn = mysql_init(NULL); mysql_real_connect(conn, "localhost", "zetcode", "passwd", "testdb", 0, NULL...

Mysql SELECT nested query, very complicated?

Okay, first following are my tables: Table house: id | items_id | 1 | 1,5,10,20 | Table items: id | room_name | refer 1 | kitchen | 3 5 | room1 | 10 Table kitchen: id | detail_name | refer 3 | spoon | 4 5 | fork | 10 Table spoon: id | name | color | price | quantity_available | 4 | spoon_a | white | 50 | 100...

Filter mysql results help

Hi... Im have a code that querys a mysql database against a certain criteria, and returns a result. The result then, is displayed in table rows inside a while loop, using the 'mysql_fetch_array' function: while($row = mysql_fetch_array($qry_result)){ By the way, this is a classifieds website... Here is my problem: After the resu...

Caching with PHP to take stress away from MySQL

I'm writing a web service for my application and want to know the best way to handle the possibly tons of requests I might get. A lot of the data probably won't change throughout the day but the particular script I'm writing makes 3 MySQL queries which seem a little excessive considering the data will probably be the same as the last re...

Problem with foreign key constraint

I am getting error #1005 - Can't create table (errno: 150). I have been through the checklist of: both tables are InnoDB the columns are the same type (INT) attributes are the same (UNSIGNED NOT NULL) the collation is the same I have tried with indexes on the foreign keys, it still doesn't work (and they shouldn't be needed for MySQL ...

SQL statement queries parameters become case sensitive how so???

Hi guys, I'm running a SQL query on my mysql database but for some reason its case sensitive like if an entry exists as 'NAME = Andrew' running a sql query like: SELECT * WHERE NAME Like 'and%' Doesn't work but SELECT * WHERE NAME Like 'And%' does work? WHat gives I'm running a php mysql set up here... ...

Converting a database from one character encoding to another

I have a MYSQL database. Text is currently stored in charset latin1, collation latin1_swedish_ci. These are the defaults and it wasn't a problem back in the day when the database was originally created. I want to switch over to UTF8 so the text encoding in the database matches out text encoding used elsewhere on the web site that uses t...

Database Design - 2d tile grid

I played a web game a while back called racewarkingdoms. It used a 2d text based system. What interest me is the map. The map is made up of 255 tiles in X direction, and 255 in Y direction. There are 5 of these maps total. But what is amazing is that each one contains so much data. Each one contains its own 'kingdom' which would be...