mysql

Reorganizing MySQL table to multiple rows by timestamp.

OK MySQL Wizards: I have a table of position data from multiple probes defined as follows: +----------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+----------+------+-----+---------+-------+ | time | datetime | NO | | NULL | | | probe_id | char(3) | ...

Getting MySQL record count with C#

Hello, I would like to know how can I get record count of a query with C#. Here is the code that I use.. MySqlDataReader recordset = null; query = new MySqlCommand("SELECT * FROM test ORDER BY type_ID ASC", this.conn); recordset = query.ExecuteReader(); while (recordset.Read()) { result.Add(recordset["typ...

One to two relationship in Doctrine with YAML

I'm working on my first Symfony project with Doctrine, and I've run into a hitch. I'm trying to express a game with two players. The relationship I want to have is PlayerOne and PlayerTwo each being keyed to an ID in the Users table. This is part of what I've got so far: Game: actAs: { Timestampable:- } columns: id: { type: i...

Is there any way to simplify a verbose SQL INSERT INTO(..) query?

I have a table with one id (autonumber) field and 50 other fields. The table is normalized, these are 50 material properties etc. I want to copy a record from this table into the same table - only the autoincrement id will be different. The query I am using now is INSERT INTO tableName (field1,field2,....field50) SELECT field1,field2,...

mysql error using Rails-- Please help

Alright I am sry for the noob question but this has been driving me up a wall-especially because I got it to work yesterday and I can't remember what I did.... I am just trying to use mysql with rails with a mongrel server. I set up the server fine and can run rails applications that don't need mysql but when I create a project usin...

Copying a mysql database from localhost to remote server using mysqldump.exe

I want to copy a mysql database from my local computer to a remote server. I am trying to use the mysql dump command. All the examples on the internet suggest doing something like The initial mysql> is just the prompt I get after logging in. mysql> mysqldump -u user -p pass myDBName | NewDBName.out; But when I do this I get You hav...

Wordpress - Total User Count who only have posts

I want to display total number of user who only have posts at Wordpress. I can get all users by this query <?php $user_count = $wpdb->get_var("SELECT COUNT(*) FROM $wpdb->users;"); echo $user_count ?> But for the user count only with posts, i think i might need to join another table, does anyone have snippets ? Thanks. ...

MySQL GROUP BY and JOIN

Guys what's wrong with this SQL query: $sql = "SELECT res.Age, res.Gender, answer.*, $get_sum, SUM(CASE WHEN res.Gender='Male' THEN 1 else 0 END) AS males, SUM(CASE WHEN res.Gender='Female' THEN 1 else 0 END) AS females FROM Respondents AS res INNER JOIN Answers as answer ON answer.RespondentID=r...

Perl daemon script for message queue hanging for 20 seconds after each process. Why?

I have daemon script written in Perl that checks a database tables for rows, pulls them in one by one, sends the contents via HTTP post to another service, then logs the result and repeats (only a single child). When there are rows present, the first one is posted and logged immediately, but every subsequent one is delayed for around 20 ...

Efficient way to combine results of two database queries.

I have two tables on different servers, and I'd like some help finding an efficient way to combine and match the datasets. Here's an example: From server 1, which holds our stories, I perform a query like: query = """SELECT author_id, title, text FROM stories ORDER BY timestamp_created DESC LIMIT 10 ...

is the ruby mysqlplus adapter production ready

is the ruby mysqlplus adapter production ready? have a choice between: 1. mysql library by tmtm (has a pure ruby and c version) 2. mysqlplus by http://github.com/oldmoe/mysqlplus/ 3. dataobjects (used my datamapper ORM) 4. em-mysql Currently using the C version of the mysql gem, that is the oldest and most popular option. Evented...

how to do minus in mysql

i want to do 1 minus from a variable who is stored in my table in mysql i do it by this query UPDATE table SET var = var -1 but when i do it 0 - 1 = -1 but mysql do 0 - 1 = 9223372036854775807 when i set in bigint when i set it to int property then he do 0 -1 = 2147483647 what i do to get 0 - 1 = -1 ...

MySQL regexp on Indexes

HI, I have query having multiple regexp in where clause. The coloumns contained in the where clause have already been indexed. But the query is not using the indexes. Does MySql regexp cause use of indexes ? If not, what could be the workaround for this ? ...

How can I get the rank of rows relative to total number of rows based on a field?

I have a scores table that has two fields: user_id score I'm fetching specific rows that match a list of user_id's. How can I determine a rank for each row relative to the total number of rows, based on score? The rows in the result set are not necessarily sequential (the scores will vary widely from one row to the next). I'm ...

[mysql] how to find a value in the row?

Hello. I have pretty big table with lots of columns. I want to find all lines, with some word in it. Sure, i can write something like SELECT * FROM table WHERE 'blablabla' IN col1 OR 'blablabla' IN col2 OR ... But I think it's not the best solution =)) UPD Of course, it's bad database design, I know it. =) I did not created th...

help with mysql triggers (checking values before insert)

hi I'm quite new to mysql and I'm trying to figure out how to use triggers. what I'm trying to do: I have 2 tables, max and sub_max, when I insert a new row to sub_max I want to check if the SUM of the values with the same foreign_key as the new row are less than the value in the max table. I think this sounds confusing so here are my ...

mySQL: Knowing when to UPDATE and when to INSERT?

Hello, I'm still quite a mySQL newbie, so I would like to do this right to avoid future mishaps. What is the right way to detect when do I need to UPDATE an entry in a table or INSERT a new one? Thank you. ...

Creating a MySQL view with an auto-incrementing id column

I have a MySQL database from which a view is created. Is is possible to add an auto-incrementing id for each row in the view? I tried CREATE ALGORITHM=UNDEFINED DEFINER=`database_name`@`%` SQL SECURITY DEFINER VIEW `MyView` AS set @i = 0; select @i:=@i+1 as `id` ... but that doesn't work in a View. Sorry, my SQL is weak, a...

Does a transaction stop all race condition problems in MySQL?

Consider this situation: Begin transaction Insert 20 records into a table with an auto_increment key Get the first insert id (let's say it's 153) Update all records in that table where id >= 153 Commit Is step 4 safe? That is, if another request comes in almost precisely at the same time, and inserts another 20 records after step 2 ...

Currently using View, Should I use a hard table instead?

I am currently debating whether my table, mapping_uGroups_uProducts, which is a view formed by the following table: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `db`.`mapping_uGroups_uProducts` AS select distinct `X`.`upID` AS `upID`,`Z`.`ugID` AS `ugID` from ((`db`.`mapping_uProducts_Pr...