SQL: how to get a weighted count using GROUP BY?
How can I make a query like: select myvalue, count() as freq from mytable group by myvalue; But where freq sums values from a weight column instead of counting each row as 1? I'm using MySQL 5.1. ...
mysql
statement.getGeneratedKeys() and MySQL
I've learned it the hard way that last_insert_id in mysql is not pool-safe. i.e. if you are pooling connections, you'll get messed up insert_ids. How does java's statement.getGeneratedKeys() get the key on inserts? Is it pool-safe? ...
Does MySQL handle a single utf-8 character key as well as an integer?
I' working on a Chinese/Japanese learning web app where many tables are indexed by the characters (the "glyphs") of those languages. I'm wondering if the integer codepoint value of the glyph would be better for performance than using a single utf8 character (for primary key and indexes)? Using a single utf8 character would be very usef...
GROUP BY and ORDER BY in same MySQL query?
I want to select a bunch of data from a table using a GROUP BY clause. This works great, but I need to order the data by the date it was created, with an ORDER BY clause. My question is, can I use both these clauses within the same query, or should I be using one in a sub-query, or something else? The original query (no modification) is...
Join two mysql tables
I have two databases - one for articles and the another for the articles' meta information (Like author, date, category and atc.). I have the following columns in meta table: ID, article id, meta type and meta value. I wonder how can I join these two tables to get both - article and meta information - with one mysql query. The article id...
Why am I getting "Unknown Column" error?
The exact error I am getting is: "Unknown column 'trans_paid' in 'where clause'" My query ($from_date and $to_date are correctly formatted): SELECT o.order_id, o.order_po_no, o.order_ship_date, acct.acct_company, SUM( ROUND( i.item_qty * i.item_price, 2 ) ) AS item_amount, ( SELECT SUM( trans_amount ) FR...
Mysql JOIN of four tables with two key tables
I hate to admit it by my knowledge of MySQL is lacking when it comes to the more complex queries. Essentially I have four tables two of them contain the data I want to return, and two are relational tables linking the data. Table A is present just to provide filler for Table D.aID. +--------+ +--------+ +--------+ +-----------+ +------...
Selecting count of zero while grouping by column
I have a jobs table, and am trying to get a count of jobs for different time frames. My current query looks like this: SELECT COUNT(*) AS 'count', WEEK(j.created_at) AS 'week', MONTH(j.created_at) AS 'month', YEAR(j.created_at) AS 'year', DATE_FORMAT(j.created_at, '%y') AS 'short_year' FROM jobs j WHERE j...
IDs in MySQL, standard IDs or alphanumerics?
I'm thinking of this, if I make a web site for a specific university's students should I make the ID as standard IDs on MySQL (integer, auto increment) or should I give the IDs as how is their email address is, like if the student's email address is [email protected] then his/her id is e12234 (on my web site). Is it ok, what about pe...
User schema for website
I need to create a database schema for storing user information (id, name, p/w, email address ...etc). I have always picked arbitrary amounts when sizing these fields. With this said, I have two questions: 1) What are good sizes for these fields? I am sure there is a maximum email address length for example...etc. 2) I now need to...
Is there a way to make a table name dynamic in a query?
I am trying to create a Class-Inheritance design for products. There is the base table that contains all the common fields. Then for each product type there is a separate table containing the fields that are for that product type only So in order to get all the data for a product I need to JOIN the base table with whatever table that c...
class array within double quotes
Hi. I have follow two classes class A { .... protected $arr = array('game_id','pl_id'); ... } class B extends A { //for example here add method private function add_to_DB() { $query = "INSERT INTO TABLE(game_id,player_id) VALUES(????,????)"; //Here is my ...
Join two mysql tables
I have two databases - one for articles and the another for the articles' meta information (Like author, date, category and atc.). I have the following columns in meta table: ID, article id, meta type and meta value. I wonder how can I join these two tables to get both - article and meta information - with one mysql query. The article id...
How to capture the executed sql query in MySQL using a trigger?
I'm doing a bit of audit work with MySQL for the first time and wanted a way to capture the executed sql during the insert (as part of the trigger). But I can't seem to find a good example/blog post/etc Any help would be much appreciated ...
Mysql Select Rows Where two columns do not have the same value
I'm trying to run a query where two columns are not the same, but it's not returning any results: SELECT * FROM `my_table` WHERE `column_a` != `column_b`; column_a AND column_b are of integer type and can contain nulls. I've tried using <> IS NOT, etc without any luck. It's easy to find if they're the same using <=>, but <> and != doe...
how can i retrieve the child table data from master table in a string value
This is the master table structure CREATE TABLE IF NOT EXISTS `gf_film` ( `film_id` bigint(20) NOT NULL AUTO_INCREMENT, `user_id` int(20) NOT NULL, `film_name` varchar(100) DEFAULT NULL, `film_cat` varchar(30) CHARACTER SET latin1 DEFAULT NULL, `film_plot` longtext, `film_release_date` date DEFAULT NULL, `film_post_...
Installing SphinxSE as a plugin in an existing MySQL install (on OS X)
Pretty simple question. I have an existing MySQL install on OS X. I want to install the Sphinx engine as a plugin. Is there any straightforward way to do this? ...
How do I do a faster MySQL Import?
We need to dump existing production data and then import it to a development DB for final rounds of testing. In pseudo-commands, currently we: mysqldump .... mysql -u __USER__ __DB__ < ./dbdump.sql mysql "sanitize script" The problem is that the middle command does the import and takes over an hour to import 600MB of data. Maybe thi...
Index Multiple Columns w/ Ruby on Rails
I have a table in a Mysql db that I want to use multicolumn indexing on. How can I do this in rails w/o using the mysql console? ...
Help writing a SELECT statement
Hi. I'm fairly new to MySQL and don't know how to write a query I need for a personal project of mine. I have three tables that need to be talked to in this query - books, tags, and books_tags (junction table). The basic structure of them is fairly basic - books and tags both have an id column and a name column (in books it's called...