mysql

Should I use ON DELETE CASCADE, :dependent => :destroy, or both?

In a Rails app, I have foreign key constraints in MySQL which I set them all up manually, separate from my migrations. I'm trying to figure out whether I should use ActiveRecord's :dependent => :destroy option. For example, in my schema I have tables... users ----- log_entries ----------- user_id # Has FK constraint to users.id with ...

Same data, two different ways to store it

Hola, The two tables below can both hold the same data - a full year, including some arbitrary info about each month table1 (one row = one month) ------ id month year info table2 (one row = one year) ------ id year jan_info feb_info mar_info apr_info may_info jun_info jul_info aug_info sep_info oct_info nov_info dec_info Table A ...

I need help writing a summation query in MySQL

I have a table that looks like this: posid sales eid 1 20 001 1 20 002 1 30 001 2 30 001 1 30 002 2 30 001 1 30 002 2 20 002 2 10 002 I want to write a query that would give me sum...

Use jquery to check MySQL database for changes and load the changes with AJAX?

I have a database of links that users can submit. Every time a user submits a new link it is added to the database. I have a separate page that lists all the submitted links. How can I have this separate page check the database for changes and load them with AJAX when it finds them? ...

Changing MySQL primary key when foreign key contraints exist

I have two already-existing tables which look (in part) roughly like this: CREATE TABLE parent ( old_pk CHAR(8) NOT NULL PRIMARY KEY ) ENGINE=InnoDB; CREATE TABLE child ( parent_key CHAR(8), FOREIGN KEY (parent_key) REFERENCES parent(old_pk) ON UPDATE CASCADE ON DELETE CASCADE ) ENGINE=InnoDB; I want to add a new ...

How to test MySQL query speed, with less inconsistencies?

I need a simple way to test SQL queries for speed. I am not to worried about hardware differences, I basically need a relative number. This is what I've been doing with PHP (its fuzzy, but works): // CONNECT TO DB HERE $sub = new YomoSubscription(95,783); $t = microtime(TRUE); // contains the SQL db call i'm testing $fp = $sub->gen...

What's the best way to optimize this MySQL query?

This is a query that totals up every players game results from a game and displays the players who match the conditions. select *, (kills / deaths) as killdeathratio, (totgames - wins) as losses from (select gp.name as name, gp.gameid as gameid, ...

Building Breadcrumbs in MySQL

I have a table that defines the possible categories in my website - fields look something like this: - id - name - parentID The information is stored something like this: +-----+------+----------+ | id | name | parentID | +-----+------+----------+ | 1 | pets | 0 | +-----+------+----------+ | 2 | cats | 1 | ...

What is the best way to ensure that primary key and foreign key never intersect?

Say I have a table with a primary key a_id and foreign key b_id. It is important that a_id and b_id never intersect, i.e. it should never be the case that there exists an a_id = b_id. What would be the best way to implement this? Could I somehow do this on the database end (mySql), or should this be ensured programmatically? If I ens...

whats the quickest way to get row count of innodb tables, in mysql 4.0?

MySQL 4.0 doesn't have information_schema and 'show table status from db' only gives approximate row count for innodb tables. So whats the quickest way to get the count of innodb tables, of course other than count(*), which could be slower with big tables. ...

What is the tried and true way of counting the number of rows returned from PHP `mysql_query` function?

I know about mysql_num_rows ( resource $result ) but I have read somewhere that is it not 100% accurate. I have seen other alternatives that actually gets each row and run a counter but that sounds pretty inefficient. I am interested in seeing what others do to get an accurate and efficient count. ...

MySQL Weekday/Weekend count - Part II

Hey everyone, I have posted about this before, which helped to give me the following SQL: SELECT fname, MONTH( eventDate ) , IF( WEEKDAY( eventDate ) <5, 'weekday', 'weekend' ) AS DAY , COUNT( * ) FROM eventcal AS e LEFT JOIN users AS u ON e.primary = u.username GROUP BY fname, MONTH( eventDate ) , IF( WEEKDAY( eventDate ) <5, 'we...

Is there a simple way to convert MySQL data into Title Case?

I have a MySQL table where all the data in one column was entered in UPPERCASE, but I need to convert in to Title Case, with recognition of "small words" akin to the Daring Fireball Title Case script. I found this excellent solution for transforming strings to lowercase, but the Title Case function seems to have been left out of my vers...

Do aggregate MySQL functions always return a single row?

I'm sorry if this is really basic, but: I feel at some point I didn't have this issue, and now I am, so either I was doing something totally different before or my syntax has skipped a step. I have, for example, a query that I need to return all rows with certain data along with another column that has the total of one of those columns...

An interesting Encryption Problem

Hi Guys, I have an interesting encryption problem at hand. I do not know if it can be solved but here goes: A database is to contain sensitive user information. As such, the user information must be encrypted (two way encryption). The user has a login / password and these may be used in the two way encryption. Now, the encryption is to...

Convert mysql data from uppercase

How can I convert and update all my data in one colum from uppercase to uppercase just for the first letter of each word? Need to update the database with the new values. Thanks ...

Why am I not able to query a database from a forked child in Perl?

I have tried with Perl fork manager and DBI . But i got the error DBD::mysql::st execute failed: Lost connection to MySQL server during query . Here the sample code: I want make query between low to high value (i have spitted int 10k records) use Parallel::ForkManager; my $pm = new Parallel::ForkManager(50); my $db = krish::DB->new or...

Save into 1 table or create multiple tables?

Hi, I am in dilemma situation, and do not know which one is better. Lets say I have 100++ (or more) games on my website. I want to store highscore for each game. Should i store all the highscore in 1 table or each game has its own table? Compare: 1 table: 1 table contains a lot of rows(data). Every user play different game will submit ...

SqlDataSource set SelectCommand dynamicly

I'm using a SqlDataSource and to avoid writing long queries directly in my code I thought I could make a Query class that returns the query I want as a string. I tried the code below but I just get "Server tags cannot contain <% ... %> constructs." Before I was using stored procedures but my webhosting doesn't allow that, so thats when...

Should I JOIN a fourth mysql table or use an IN clause?

Below is a mysql query I am working on for a page that hase User Status POst (like twitter in a way) Comments on these post User photo and name to show who posted the status post or the comment The list of user ID's in the IN clause is generated from another query which is a large table around a million rows. Should I try to JOIN that t...