mysql

MySQL - Show All Permutations?

Table1: Id MyFK f_name l_name =========================== 1 100 John Doe 2 100 Little Timmy Table2: Id MyFK item price =========================== 1 100 Car 200 2 100 Bike 100 In MySQL, how do I produce a table like this: Id MyFK f_name l_name item price ================...

Find average of last X records for every person

I have a table which stores every score from every player. I want to take out the last 3 (or less) and display the average. Example: TABLE log time p_id score ---- ---- ----- 12:00 0 10 14:23 0 25 14:25 1 30 16:27 0 70 17:00 0 80 Now I want to find the average of all players' last 3 entries... Result: p_id ...

PHP counting user clicks

Wondering what would be the most efficient way to do the following. Game play. Player can train and every 8 mins a captcha verify kicks in to stop them from using bots/scripts to auto train. However thats still a lengthy amount of time. So thinking about making it per 100 clicks the verify enables. Now I could do this simply in the da...

MySQL table recovery from crashed server

I managed to wipe a server by mistake but PhotoRec was kind enough to recover the .frm and .myi files from the hard drive. I now have a desktop set up with the same version of MySQL to recover the data but my question is: what do I do? I have about 160 of these files. I haven't yet reinstalled the server in case I need anything else. Al...

MySql.Data.MySqlClient.MySqlException: Timeout expired

In recent times, a particular page in my web app throws the Exception Details: MySql.Data.MySqlClient.MySqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. Though I use Ibtais as persistence layer, this error occurs. I have restarted the MySql service instanc...

Documenting relational databases (tables, views, functions, triggers)

I'm trying to improve the knowledge management of MySQL structures available on a project. By structure, I mean tables, views, functions, procedures, triggers. All these structures are extracted in .sql files. I'm looking for a way to document these structures, presenting results à la doxygen in HTML files. For example, I want this too...

PHP Variable in Select Statement

Hey, first sorry that i had written it in german. Ok in english: i've written this PHP-Script which is working, and now i want to change the rowname into a variable to (not sure if row is correct), i mean the "name" from the select name... I've tried near everything, but nothing gave me the right result. I know that the normal thing ho...

Databases allow bad foreign keys from Rails Fixtures

Hi, I am using Rails Fixtures to load some test data to my database and accidentally I introduced a foreign key out of range. To my surprise, the database accepted it despite having referential integrity constraints (that work). I tried with PostgreSQL and with MySQL InnoDB and both allowed. Example: Having in the database "Flavour...

In MySQL, is a BIT column a suitable way to store 5, 6, or 7-byte integers?

I have a table whose size I'd like to keep down, and one of the columns can be treated as a 5-byte unsigned integer. This is a column that I won't need to search by. MySQL offers integer datatypes TINYINT, for 1-byte integersSMALLINT, for 2-byte integersMEDIUMINT, for 3-byte integersINT, for 4-byte integersBIGINT, for 8-byte integers. ...

Assigning a user to multiple projects in my PHP application, best approach?

I am currently building a web application for my work and you can add tasks, projects and clients. The dilemma I've encountered is that a client can be assigned to multiple projects at once. The application is being built upon the latest version of Codeigniter 2.0 (if that helps). I am usually pretty good at working out problems like th...

How to do this query?

Hello I have this mysql table: id - auto_increment id_stock - int price - double date - date sample data is: 1 1 10.5 2010-08-10 2 1 16.5 2010-08-11 3 2 12.5 2010-08-12 now, i have to group by id_stock and search for the MAX(date) of the stock, then i have to compare the MAX(date) to a date i have to...

mysql replication incompatible statements problem

hello, i wanted to setup a new mysql database slave running a newer version of mysql => 5.1.41 than the master => 5.0.75, which -- as far as i know -- should normally be no problem. however, as it turns out setting up replication fails, because i used a SQL statement in 5.0.75 which apparently does not work in 5.1.41 anylonger: the sta...

MySQL group by consecutive appearances

Hi all, I would like to group query results by consecutive appearances of a column values. Let's say I have a table which lists the winners of a competition for each year as follows: year team_name 2000 AAA 2001 CCC 2002 CCC 2003 BBB 2004 AAA 2005 AAA 2006 AAA I would like a query which outputs: start_end ...

MySQL DROP all tables, ignoring foreign keys

Is there a nice easy way to drop all tables from a MySQL database, ignoring any foreign key constraints that may be in there? ...

MySQL cluster: I could not create any new table.

I have just mysql-cluster-gpl-7.1.4b-win32 installed on my XP computer. There are three command: START ndb_mgmd -f d:\work\my_cluster\conf\config.ini --initial START ndbd --defaults-file=d:\work\my_cluster\conf\my.cnf --initial START mysqld --defaults-file=d:\work\my_cluster\conf\my.cnf --user=root --console I notice in step 3., I...

Mysql replace regular expression

Hi is there any way to 'replace' all occurences of 10 or more occurances of _ (underscore) within a mysql table with something else. background I've inherited a database of content written for the web. Unfortunately the origional author has used ________________ instead of <hr /> for the horizontal rules. It's a wordpress installation...

Date Comparision in PHP and my sql

Hi all i want to compare database field of date that has YYYY-MM-DD format and i want to compare from MM-DD actually i want to fetch records those have birthdays between month /selected week durations and birthdate is stores as YYYY-MM-DD format in db table how can i achieve this kindly help. ...

django 1.1 creating high temporary tables with mysql 5.1

Hi, we are using django 1.1/mysql5.1 for one of our projects. Seems like as the load on the webserver(apache/mod-wsgi) increases, the number of temporary tables created on mysql also increases, causing heavy alerts on our monitoring infrastructure. To give you an example, when the number of connected clients increases from 100 - 300, the...

ERD help needed Bug Tracking System image attached

Can you see anything that might have been left out for a simple bug tracking system? Here's an updated version with new changes ...

How to approach a youtube's embed code liberation

I have a site based on php/mySQL where publishers had inserted a lot of youtube videos in a specific database column copiyng/pasting youtube embed codes. There is a wide variance of the codes reflecting the variations google made during time. Different sizes of the player window is the main variation. Now I need to uniform all the window...