mysql

MySQL: hourly average value

Hello, I have a table with a 'timestamp' column and a 'value' column where the values are roughly 3 seconds apart. I'm trying to return a table that has daily average values. So, something like this is what i'm looking for. | timestamp | average | | 2010-06-02 | 456.6 | | 2010-06-03 | 589.4 | | 2010-06-04 | 268.5 | etc... ...

MySQL JOIN with 3 tables and COUNT() not working

Hi, I'm having a problem with my MySQL statement. I need a query that counts the number of comments and the number of topics a user has created. My table structure is something like this: Table 'users' ------------- user_id user_name ... Table 'topics' -------------- topic_id topic_user_id ... Table 'topiccomments' -----------------...

MySQL regexp for matching list of numbers in a string

I have several rows with strings similar to this: 1,19|11|14,2 The info I want to look at is: 19|11|14 (which is a list of the numbers, 19 11 and 14) This info should be matched to see if any of the numbers are in the range between 8 and 13 What's the most effective way to accomplish this? I tried using a regexp like: [^0-9]*(8|9|10|11...

bash: output \n \t characters from mysql query

Hi there, I currently have $ echo "`echo "use joomla; describe jos_content" | mysql --batch -u root -ppassword`" and I would expect output that starts like this Field\tType\tNull\tKey\tDefault\tExtra\n but instead I get Field Type Null Key Default Extra I have tried all sorts of items at the moment. I could use mysql --h...

MySql, Entity Framework, Inner Joins with null checks generated

Hi, I have had a play with the Entity Framework and the MySQL driver. Excuse me if this is a silly question. Table adverts has a FK to the PK of vacancies. Both fields are marked as NOT NULL. I am performing a simple join: var qry = (from vacancy in context.vacancies join advert in context.adverts on vacancy...

mySQL: how to update table and fields dependent on values in other tables?

I have a database with hinky data in it. Fortunately it is consistently hinky so I should be able to run some nice SQL on it and clean it up. Here's the story: Table 'uc_products': nid model 1 asdf 2 qwer 3 ghjk Table 'uc_product_stock': nid sku 1 asdf 22 qwer 34 ghjk So you can see that model=sku, and ni...

mysql search top match should come first

in the match - against am getting the correct results, there is no problem but the thing i want is the result combination. Like for "computer graphics" i am getting results for "+computer +graphics" as "computer" alone results and "computer graphics" results and "graphics" results and etc. Here i want "computer graphics" results first...

Python MySQLdb cursor truncating values

I ran into an interesting problem. I have a MySQL database that contains some doubles with very precise decimal values (for example, 0.00895406607247756, 17 decimal places). This is scientific data so this high level of precision is very important. I'm using MySQLdb in Python to select data from the database: cursor.execute("SELECT * F...

how to handle online status. guest, users etc

Whats the best way to keep track of how many users and guests are online? Im making a forum for fun and learning Right Now I have a 2 fields in the users table called is_online and last_access_time. If current time is 5 minutes or more than last_access_time i set is_online it to zero. And if the signed in user refreshes browser i set i...

Numeric order when returning results from MySQL

I have the following types of titles in a table in my db: Topic 1 blah blah Topic 2 blah blah Topic 3 blah blah ... Topic 10 blah blah Topic 11 blah blah etc... The select query will always return the results like this: Topic 1 blah Topic 10 blah blah Topic 11 blah ...leaving out Topic 2, Topic 3 etc... until after all the teens...

Does this case call for InnoDB or MyISAM?

I'm doing a search on a table (few inner joins) which takes max 5 seconds to run (7.5 million rows). It's a MyISAM table and I'm not using full-text indexing on it as I found there to be no difference in speed when using MATCH AGAINST and a normal "like" statement in this case from what I can see. I'm now "suffering" from locked tables ...

MySQL: Multiple Inserts for a single column

I'm looking for a way to do multiple row inserts when I'm only inserting data for a single column. Here is the example table: +-------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+----------------+ | id | tinyint(4...

Get simplified version of strings with special characters in Mysql

I need to create a web page listing glossary terms. The listing is paginated alphabetically (first page is for terms starting with A, second for B, etc.). Say I have a Mysql table with just one column: 'name' which is a utf8 encoded varchar field. This table contains two records: 'oto' and 'óto' which should appear on the same page. I ...

Partitioning in mysql for select and update query

I am using mysql where I am having a table with 3 integer columns c1, c2, c3. Value of column c2 is different on every row Value of column c1 will change around every 3 million rows. c3 is having only two values (1 or 2). primary key of the table is (c1, c2). Table can have millions of records and we are require to perform followin...

Raw Binary Tree Database or MongoDb/MySQL/Etc ?

I will be storing terabytes of information, before indexes, and after compression methods. Should I code up a Binary Tree Database by hand using sort files etc, or use something like MongoDB or even something like MySQL? I am worried about (space) cost per record with things like MySQL and other DB's that are around. I also know that ...

MySQL: reference subquery variable in another subquery

I'm working on a messaging application and am trying to find the number of new messages and the date of the most recent messages for all users with whom the current user has active conversations. The following query works, but expanding on it would mean I'd have to run the aggregate subquery twice to get both the count and last sent tim...

mysql create procedure syntax issue line 1

Hello All. I get a syntax error on line 1 of the following procedure: DELIMITER | CREATE PROCEDURE sp_autocallFillCallQueue BEGIN DECLARE maxCalls TINYINT(1); SELECT autocall_maxCalls INTO maxCalls FROM `options` LIMIT 0,1; REPEAT INSERT INTO `callQueue` (`phoneNumber`, 'waiting') SELECT `phoneNumber` FR...

Devel Show Queries

I may be missing something obvious here, but in the devel module settings I have checked "Collect query info" and "Display query log", and saved. Where does this information show up? I don't see anything different when I load pages. Is there a variable that I need to inject somewhere in my templates that I missed? I would like to view wh...

Modeling products with vastly different sets of needed-to-know information and linking them to lineitems?

I'm currently working on a site that sells products of varying types that are custom manufactured. I've got your general, standard cart schema: Order has many LineItems, LineItems have one Product, but I've run into a bit of a sticking point: Lets say one of our products is a ball, and one of our products is a box of crayons. While peop...

Selecting Users signed up in past 30 days and in past 7 days in one efficient query

I have this query that selects the number of user signups in the past 30 days: SELECT COUNT(*) AS UserCount30 FROM User WHERE User.UserDateCreated > (CURDATE() - INTERVAL 30 DAY) Then I have this query that selects the number of users that signed up in the past 7 days SELECT COUNT(*) AS UserCount7 FROM User WHERE User.Use...