mysql

Save R plot to web server

I'm trying to create a procedure which extracts data from a MySQL server (using the RODBC package), performs some statistical routines on that data in R, then saves generated plots back to the server such that they can be retrieved in a Web Browser via a little bit of php and web magic. My plan is to save the plot in a MySQL BLOB field ...

Installing MySQLdb on Snow Leopard

I'm having all sorts of trouble trying to instal MySQLdb (1.2.2) on snow leopard. I am running python 2.5.1 and MySQL 5.1 32bit. Python and MySQL are running just fine. I've also installed django 1.2.1, although I don't think thats all that important, but wanted to give an idea of the stack i'm trying to install. I am using python 2.5....

MySQL Math -- what's faster, INTEGER or FLOAT ?

I'm doing some queries that require a lot of joined rows have some of their fields added and multiplied together. I know it is sort of vague, but my question is which numeric data type is fastest for these types of operations? Will FLOAT be faster than DOUBLE since DOUBLE is 8 bytes, and FLOAT is 4? Will INT be faster than FLOAT (ev...

Incredibly hard join (for me)

I'm trying to make a web interface for a c++ game a friend have done. Unfortuantly Im not a php/sql guru. Its especially hard when I need to join tables. Im not good with those. To show you what im looking for: (i want to get all the members of a guild) Guilds > The Badasses ------------------------------- Rank Player name ------...

Need your help with creating an invoicing application using php

Hello, I am working on a php and mysql based client and invoice management web application. In my apps Invoice section, I want to allow users to add any number of invoice items (qty, description, price) dynamically (by default only one will be displayed), and autogenerate total, grandtotal amount. I want to do this using jquery. And wh...

What design pattern works for you? ASP.NET with MySQL.

Hi this is a question to all experienced developers who create their web applications using ASP.NET(C#) with MySQL. I am currently using the Microsoft Enterprise Library to implement a database factory design pattern. I have a DAL which returns a DataTable. I have a BLL that executes that DAL which returns a List<> of my DataObjects. T...

Wordpress post count per month

I need to run a query in Wordpress to get how many posts there are per month including zeros. My query right now returns the first part of what I am trying to return: select distinct date_format(post_date, '%y') "year", date_format(post_date, '%b') "month", from wp_posts where post_type = 'post' and post_status = 'publish' group by dat...

How do I calculate a cumulative value without using python or php?

I have a table with: location_id (location) - ranges from 0-90,000 time (time_period) - ranges from 1-15 for each location temperature (temp) - A unique value for each location x time_period. Example data: location time_period temp 91 1 -4 91 2 3 91 3 12 ....................... .......

mysql: how to find current size of memory table?

i have an in-memory table specified using engine=MEMORY. what command can i run to find out how much space it is currently taking? what about the maximum size? ...

Parentheses become encoded in codeigniter db query

I have a legacy database with a field which contains some parentheses, like "red(b)". But if I try to query for that value, the parentheses come out encoded. This query: select * from table where field1 = "red(b)" Becomes this query: select * from table where field1 = "red & #40; b & #41; " (I put a space between the & and # so...

Unable to add an index on a field with InnoDB?

hey all, I have an innodb table that I'm trying to run an index on and I'm getting the following error: Deadlock found when trying to get lock; try restarting transaction The syntax is: ALTER TABLE mytable ADD INDEX (fieldtoindex); any ideas as to why that would throw a deadlock error? thanks! ...

Speeding up a MySQL stored routine

Hi, I was wondering if it was possible to speed up this MySQL query. Currently, I am calling it 20,000+ times, and it takes a while to run (a while being about 10 to 20 minutes). Here is the basic table layout: db1: INT(11) id VARCHAR(45) col1 VARCHAR(100) col2 VARCHAR(100) col3 db2: VARCHAR(45) id db3: VARCHAR(45) fk_db2 INT(11) fk...

How to order by a part of a column ?

I want to make an order by stuff using a part of some column.For example,some records of the column user look as below vip1,vip2,vip21,vip10,vip100 If I do a order by user,the order would be vip1,vip10,vip100,vip2,vip21 Then how can I make the result as follows vip1,vip2,vip10,vip21,vip100 Thanks. ...

Select from where field not equal to Mysql Php

Hi, I'm just wondering what kind of mysql command i could execute in php that would select all items from a certain table where columna is not equal to x and columnb is not equal to x so like select something from table where columna does not equal x and columnb does not equal x If anyone could help that would be great! Thanks ...

How to choose optimized datatypes for columns [innodb specific]?

I'm learning about the usage of datatypes for databases. For example: Which is better for email? varchar[100], char[100], or tinyint (joking) Which is better for username? should I use int, bigint, or varchar? Explain. Some of my friends say that if we use int, bigint, or another numeric datatype it will be better (facebook does it). ...

changing a column based on it's highest value

I have a column, num, and I'm trying to update the column such that num = num / MAX(num). Now the thing is that MAX(num) should remain static throughout the update.. it should be the MAX prior to the update. I was wondering whether this was possible to do in one SQL statement. The obvious solution otherwise that I'm currently using it v...

MySQL WHERE and Like Question.

Is there a way I can combine the multiple WHERE's into one Here is what I want to combine. SELECT * FROM question WHERE content LIKE '%$search_each%' WHERE title LIKE '%$search_each%' WHERE summary LIKE '%$search_each%' ...

Querying multiple tables

Hi there. Following on from my query yesterday, I've restructured my recipe database as follows: categories cid | category_name 1 | desserts 2 | cakes 3 | biscuits recipes id | recipe_name 1 | black forest cake 2 | angel cake 3 | melting moments 4 | croquembouche 5 | crepes suzette ingredients iid | ingredient_name 1 ...

how can I store a set of values in SQL table

Hi All, I need to store a set of values e.g. A, B, C under say country column of a SQL table. Coould you let me know if there is any data type that is able to store a set of values in a SQL table column? Thanks in advance. ...

long-time Oracle user switching to MySQL, any gotchas to be aware of?

It happened that all projects during the last 10 years were with Oracle as a database server. Now I'm starting on a side project on my own with MySQL (the latest stable). Are there any gotchas (things that do not work as expected by an Oracle user)? Anything to do with transaction management, locking, isolation levels, indexes, that ki...