mysql-query

How to calculate monthly average in MySQL given monthly data that spans more than one month?

I have data that spans multiple months and I want to be able to take the average per day and separate it to the appropriate months. For example, say that one data point is 2/9/2010 - 3/8/2010 and the amount is 1500. Then, the query should return 1071.4 for February 2010 and 428.6 for March. I am hoping there is a MySQL statement that ...

MySQL: Get Root Node of Parent-Child Structure

I have a table similar to this: ================= | Id | ParentId | ================= | 1 | 0 | -----+----------- | 2 | 1 | -----+----------- | 3 | 0 | -----+----------- | 4 | 3 | -----+----------- | 5 | 3 | -----+----------- | 6 | 0 | -----+----------- | 7 | 6 | -----+----------- ...

SQLite3 (or general SQL) retrieve nth row of a query result

Hi All, quicky question on SQLite3 (may as well be general SQLite) How can one retrieve the nth row of a query result? row_id (or whichever index) won't work on my case, given that the tables contain a column with a number. Based on some data, the query needs the data unsorted or sorted by asc/desc criteria. But I may need to quickly ...

Small Issue PHP Pagination Mathematics Help

I have a small problem with my PHP pagination, I have 6 records and I display 2 at a time, when I click on next and it displays from 2 to 4 records, that works fine, But to display from 4 to 6 records, that does not work. I am not sure what im doing wrong. Anyone have any ideas ? the problem is to do with the calculation for the Next rec...

How do I group repeated values to find the start and end of series?

I have a table with the following data. serial date flag 1 1 aug 1 2 2 aug 1 3 3 aug 0 4 4 aug 0 5 5 aug 1 6 6 aug 1 7 7 aug 1 What I need is this result set: from to flag 1-aug 2-aug 1 3-Aug 4-Aug 0 5-Aug 7-Aug 1 When I group...

mysql decimal query issue

First off I am running mysql Ver 14.12 Distrib 5.0.45, for redhat-linux-gnu (x86_64) using readline 5.0 Sorry for the formatting but I don't have the ability to copy it so I have to type it in. the two fields in question are the following id int(11) startj1950 decimal(38,6) mysql> select id,startj1950 from store where id = 32513; ...

MySQL: Complex data structuring and querying

I'm having a bit of trouble figuring out what would be the best approach for creating the database tables for the following (rather complex) data structure and I'm hoping that someone with more experience than me could help out. The main reason for my trouble is both normalization and trying at all costs to avoid querying inside loops. ...

Calculations in mysql statement for rating

Hello, I want order a selection of items by the rating. There are 2 fields: rateup and ratedown. I use these fields to calculate a number and order the selection on that number. Can that be done in mysql? I have a querylike this, but this doesn't work: SELECT * FROM table ORDER BY (((9/rateup+ratedown)*rateup)+1) DESC How can I make t...

php + mysql (join?)

Maybe this way it will make more sense. MySQL "table" Code: id1 | id2 1 | 2 1 | 3 2 | 4 2 | 3 3 | 4 3 | 5 WHERE id1 = 1, this id is connected to 2 and 3: 1->2, 1->3 What I want to do is output the IDs of 2 and 3 which are NOT connected to 1, which in this particular case would be 4 and 5. 2->4 (1 is NOT connecte...

search for value in mysql tables with less queries to the database?

Hi. I have a user with his unique username in a mysql table, but I have to test and do many queries to find it. I wonder if its a better way to avoid all does queries to the db. I have multiple rows in the table with columns like user1, user2, user3, user4 up to 30. for ($x=0; $x < 30; $x ++){ $user = "user"; $user .= $x; /...

sql date hour conversion

I have SQL like this: `table1`.`DateField` >= DATE_SUB(NOW(), INTERVAL {$days} DAY My table1.DateField is in the format of 2010-03-10 10:05:50. The value of {$days} could be 4 hours or 3 days. How can I make the above sql smart enough to know that? Should I use `table1`.`DateField` >= DATE_SUB(NOW(), INTERVAL {$days} DAY_HOUR ...

MySQL Query, Subquery optimization, SELECT, JOIN

I have one table with some data and I want select newest data for each type... Table: +----+------+------+---------------------+ | ID | data | type | date | +----+------+------+---------------------+ | 1 | just | 2 | 2010-08-07 14:24:48 | | 2 | some | 2 | 2010-08-07 18:07:32 | | 3 | data | 9 | 2010-08-06 02:5...

Where clause with Zend_Db not working as expected

I am getting an error: Message: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '%'Chinese'%) ORDER BYtext_idDESC LIMIT 10' at line 2 caused by this line of code $select = $this->_db->select('')...

MySQL: how can i convert a string '1,2,3,4' to a (1,2,3,4) so i'll be able to use it in a 'where X in ()' statement

I ran a query that resulted in the string '1,2,3,4'. How can I run a second query that treats that string as a list of numbers. So I'll be able to do: select * from tbl where name not in (1,2,3,4) I would like an answer in pure MySQL. ...

Java and MySQL PreparedStatement: storing 5000 numbers in a table. what's the right way ?

Hello. I'm Writing a facebook application using the Java-facebook-api and MySQL 5.1. I'm trying to find a faster way to store the friends list of a user who logs into my application. When a user logs into my application the following happens: deleting the old friends list of the logged-in user gathering the user friends list into an...

How To Save the State of Current Work?

I am working on a site that has tests users can take. I have a test area set up with 100 questions on each subject. If a user is taking a test, I want them to be able to save the work they have already done and when they return I want them to be able to continue the work from where they left. The questions being answer are multiple choic...

One-to-many Query in MySQL

What's the best way to query one-to-many in MySQL? This is a simplified version of the database I am working on (if anything doesn't look right tell me): CREATE TABLE Tenant( tenant_id int NOT NULL, first_name varchar(20), last_name varchar(20), PRIMARY KEY (tenant_id) ); CREATE TABLE Rent( tenant_id in...

Is this query possible?

Sorry in advance for the massive queries. I've been trying and cannot for the life of me get this query to work. It's adding wins and losses to both users when two logged in users are playing against each other. (It's rock paper scissors). I can make it work for a single user, but when I try to gather a 'stats-table' summing over use...

MySql NOT IN Performance problem

Hi, I am running the query below on a table with around 100k rows in it using a NOT IN and the performance is terrible. Is there another way of achieving the same result? SELECT c.Id, c.Name, c.address, c.town, c.county, c.postcode, FROM contractor AS c inner join Order w on w.Id = c.Id WHERE (c.valid = 1) and c.Id not in (select w.Id ...

find missing values in MySQL

Hi all, I am trying to write a query to find out what users are not enrolled on some courses. I have 2 tables; courses, and users. Both contain the fields 'id' and then 'coursename' and username' respectively. Using these two table the existing system adds users to a third table (called enrolled_users). Users take up one row for each. ...