I'm creating a threaded message board and I'm trying to keep it simple. There's a message table, and then a replies table that has an 'reply_id' field that can be null to indicate a top level response, or a value that indicates a threaded response. I'm a bit confused on how to do a SELECT call on this type of table though? Reply -id...
Ugh ok I'm terrible at explaining things, so I'll just give you the quotes and links first: Problem 4b (near bottom): 4b. List the film title and the leading actor for all of 'Julie Andrews' films. movie(id, title, yr, score, votes, director) actor(id, name) casting(movieid, actorid, ord) (Note: movie.id = cast...
Alright, this one (3a; sample problem with provided answer) has got me scratching my head: bbc(name, region, area, population, gdp) 3a. Find the largest country in each region: SELECT region, name, population FROM bbc x WHERE population >= ALL (SELECT population FROM bbc y WHERE y.region = x.region AND...
I have a query : select score, count(1) as 'NumStudents' from testresults where testid = 'mytestid' group by score order by score where testresults table contains the performances of students in a test. A sample result looks like the following, assuming maximum marks of the test is 10. score, NumStudents 0 10 1 20 2 12 3...
I created a field in my table and set it as the index but I can't get it to increase on it s own when a new item is added. How do I do make it do this through PHPMyAdmin? ...
I have a very slow query that I need to run on a MySQL database from time to time. I've discovered that attempts to update the table that is being queried are blocked until the query has finished. I guess this makes sense, as otherwise the results of the query might be inconsistent, but it's not ideal for me, as the query is of much lo...
I am storing the response to various rpc calls in a mysql table with the following fields: Table: rpc_responses timestamp (date) method (varchar) id (varchar) response (mediumtext) PRIMARY KEY(timestamp,method,id) What is the best method of selecting the most recent responses for all existing combinations of metho...
What do you recommend for setting the MAMP development stack? I've seen the quickstart thing at http://www.mamp.info, but I don't really like that approach. I'd rather have a full-fledged Apache that I can install any number of modules into. I remember seeing in the Leopard Server demo a really slick GUI that allows you to setup all this...
I have the following code: $sql = "INSERT INTO table VALUES ('', ...)"; $result = mysql_query($sql, $link) or die(mysql_error()); $id = mysql_insert_id($result) or die('oops'); //mysql_error() instead of oops produces the same result echo $id . "\nDone"; The table that this insert occurs on has an auto-incroment field however all that...
[Edited to use much simpler code which shows the problem] The following code gives a segmentation fault on the last line require 'rubygems' gem 'mysql' gem 'dbi' require 'dbi' require 'mysql' dsn = "DBI:Mysql:DATABASE:www.HOST.net" # redacted dbh = DBI.connect(dsn, "USERNAME", "PASSWORD") # redacted sth = dbh.execute("select * from ...
Is there any advantage in using one over the other? Should I use the vendor provided PKG file or use the Darwin Ports version? Is there a general rule of thumb for deciding how to install a package? ...
I have a rather simple query SELECT col1 FROM table1 This command outputs about 300K+ entries per second, which sounds reasonable, yet other methods for mass decoding of saved data (e.g. array deserialization) works at the limit of what the hard drive enables, i.e. 40-50 MB/s, compared with 2-3 MB/s with MySQL. I see that my MySQL ma...
When migrating a project from MySQL 4 to MySQL 5, what are the primary things I need to address in order to ensure queries remain compatible? In general things should be fine, but I know that some things that worked implicitly in MySQL 4 queries have to be defined explicitly in MySQL 5 (but I can't for the life of me remember what exaxt...
I'm trying to run rake test:units and I keep getting this: Mysql::Error: Duplicate entry '2147483647' for key 1: INSERT INTO `ts_schema_migrations` (version) VALUES ('20081008010000') The "ts_" is there because I have ActiveRecord::Base.table_name_prefix set. I'm confused because there is no value '20081008010000' already in the tabl...
I have encountered this problem a couple of times, in the last few days. So, it happens occasionally. I have setup mysql on a remote machine, and there is a java program on another machine querying the database to read and write records every few seconds. I am using phpMyAdmin to administer my database. And, at times, after running som...
I'm starting to get a bit desperate in my attempts to get a ruby script to connect to MySQL. I've given up on DBI, as I just don't seem to be able to get it to work no matter what I do. I figured I might have more luck with just using mysql.rb from ruby-mysql. However, in using that I get the following error: ./mysql.rb:453:in `read': ...
How can you return a string minus the first character in a string in MySQL? In other words, get 'ello' from 'hello'. The only way I can think to do it is to use mid() with the second offset being larger than the string could possibly be: select mid('hello', 2, 99) But I'm convinced there must be a more elegant way of doing it. Is th...
How would you reference table1 columns to 2 columns in table 2 I created a table 'State' with 50 exact rows trying to relate (weddingState,contactState) in 'Wedding' table This is the statement that I created but it only joins the top WeddingState correctly - seems not to care about the INNER Join below it... SELECT * FROM weddings...
I'm just trying to get MySQL to store time in GMT... I've read the documentation here: http://dev.mysql.com/doc/refman/5.1/en/time-zone-support.html It says to set: default-time-zone='timezone' in an option file. However, I've googled for several different terms and cannot find possible values "timezone" is supposed to be. I also do...
Is creating an index for a column that is being summed is faster than no index? ...