mysql

How to do nested Django SELECT?

class Friendship(models.Model): from_friend = models.ForeignKey(User, related_name='friend_set') to_friend = models.ForeignKey(User, related_name='to_friend_set') I'd like to SELECT all to_friends that have from_friend = a certain User. Then, I'd like to pass to_friends to inside filter in another .objects.filter(). Is this th...

Merging database structure changes from one server to another.

I currently have a website with three builds: development, staging and production. Each of those has it's own MySQL database instance. Each of the instances has different data in it which should not change (orders). My question is, if I made changes to the structure of the development database, is there an easy way to propogate those ch...

'in' and 'not in' counts do not add up - what's wrong?

I have some servers. Some of them have ips assigned. I want to figure out how many do not. There are clearly more servers than have ips assigned, but my db tells me there are no servers that have no ips assigned... I'm at my wit's end here. Is my DB corrupted in some strange way? SELECT COUNT(*) FROM server ...returns: +--------...

prepare statement-What is the problem here

DELIMITER $$ CREATE PROCEDURE `Insert1`(IN NAME VARCHAR(100),IN valuees VARCHAR(100)) BEGIN SET @r = CONCAT('Insert into', NAME,'(name)','VALUES',valuees); PREPARE smpt FROM @r; EXECUTE smpt; DEALLOCATE PREPARE smpt; END$$ DELIMITER ; it is successfully compiling... but when i execute gives me problem... *...

Using MySQL Workbench

I am trying to create a graphical representation of table relations using "Mysql workbench". I double clicked on "MySQL Model" tab and added Diagram. But I can not figure out how to display the tables from current DB. Any help? ...

SQL - Check which years contain data

How to select only years from mysql database (but only those years which contain data)? The PHP time() function is used to populate the date in the database. There are more then 10,000 records stored since 2006... I would like to categorize data by date (firstly, user selects year, then month, than day...). I'd also like to be able to...

Complex MySQL Timer

If we have a timer that starts from lets say CURRENT_TIMESTAMP - 1 Hour we can simply calculate the difference and return 3600 seconds have passed. But what if we want the timer to only count during certian times of the day and only lets say work during weekday or specified days. Look at the below code to see the Create statment to get...

Multiple sites in same MySQL database

I'm planning to have 20 or more sites in the same database tables. Like a structure similar to this: cms_config cms_pages cms_users cms_modules I have been thinking that each of these tables should have a customer_id column, so I easily could select and call rows that is for the given customer. But is this the best way of doing it when...

Efficient storage of and access to web pages with Python

So like many people I want a way to download, index/extract information and store web pages efficiently. My first thought is to use MySQL and simply shove the pages in which would let me use FULLTEXT searches which would let me do ad hoc queries easily (in case I want to see if something exists and extract it/etc.). But of course perform...

MySQL ordering SELECT by associated column

I'd like to select some Addresses from a MySQL DB. Addresses can have a Contact, an Organization, or both. (A Contact can have many Addresses. So can an Organization.) Let's say I want to get all of an Organization's Addresses. That's no big deal. But what if I want to ORDER them by Contact.last_name? Some of the Addresses don't have a ...

Store data in mysql (data-migration)

Hi, guys! I'm trying to make an online dictionary for my own purposes (I know there are some other dictionaries, but they don't have what I need) and I have a file with words in the following format: 戚谊 戚誼 [m1][b]qīyì[/b][/m] [m2]some translation[/m] 三州府 [m1][b]sānzhōufǔ[/b][/m] [m2]translation #2[/m] First two lines ...

Simple formatting of multidimensional array from mysql in php

Hello. I have an array of results from mysql relational tables. It looks similar to this: array(10) { [0]=> object(stdClass)#14 (35) { ["eventID"]=> string(1) "1" ["eventTitle"]=> string(7) "EVENT 1" ["artistID"]=> string(1) "1" ["artistName"]=> string(8) "ARTIST 1" ["artistDescription"]=> s...

Selecting random in rails sqlite vs mysql

Hey guys, I'm trying to select random data from the database in Ruby on Rails. Unfortunately, sqlite and mysql use different names for the "random" function. Mysql uses rand(), sqlite use random(). I've been pretty happy using sqlite in my development environments so far, and I don't want to give it up for just this. So I have a soluti...

Can MySQL use indexes over 2 different storage engines?

I have a myisam table and a innodb one. The innodb has an index on a foreign key from myisam. Can MySQL use that index when performing joins? ...

SQL with 0 counts

I have the following sql query : SELECT DATE(procedures.start) date, name, COUNT(procedure_types.id) count FROM `procedure_types` LEFT OUTER JOIN procedures on procedure_types.id = procedures.procedure_type_id WHERE (DATE(procedures.start) = '2009-10-24') GROUP BY DATE(procedures.start), procedure_types.id ORDER BY DATE(procedures.sta...

Help with another mysql query

I Would like to select the count of the newly added DISTINCT product_id in the table ID product_id 1 abc 2 abc 3 cat 4 dog 5 dog 6 man 7 man 8 bat 9 bat 10 abc 11 cat 12 dog 13 dog I want a query that gives me a similar result from the above table - dog -2 c...

Mysql text extract with Regex

Hi, i try to extract text from html text stored in a db. This is an example: <P style="FONT-SIZE: 13px; MARGIN-LEFT: 6px"><FONT color=#073b66><STRONG><A href="/generic.asp?page_id=p00497">Practice Exams</A> - </STRONG><FONT color=#000000>ours are the most realistic exam simulations, and the best way to prepare for your exams. Get de...

"Show databases" with condition

i would like to query a MySql Database to show me all the existing databases based on provided condition (condition applied on the database name). now since my condition is complex, the simple "LIKE" condition is not enough, and i need to use the regular WHERE clause. can anyone provide a sample of how to do that? ...

Problem with parsing data via php and storing it to MySQL database

Sorry for duplicating this question, but here I tried to explain it in more details. I need to parse the data from certain file and store it to database (MySQL). This is how the data is displayed in the file: 戚谊 戚誼 [m1][b]qīyì[/b][/m] [m2]translation 1[/m] [m1][b]qīyi[b][/m] [m2]translation 2[/m] 三州府 [m1][b]sā...

Insert rownums in mysql table via query or script

Hey! I have a table that has 5 fields and has a total of 400 records. I added a new field which I called 'order' which, as the name suggests, is to keep an order in the records. Since I added this after the records had been inserted I was wondering if there was a way to populate this field with the current order of the records using so...