What are your methods of linking data spread over multiple databases architectures (think MySQL vs PostgreSQL etc), into a single application? Would you create giant hashtables/arrays to match content against one another? Are there other, more effective and less memory-consuming options for doing this? If you were to use data both fro...
Hello, I'm wondering if MySQL takes collation into account when generating an index, or if the index is generated the same regardless of collation, the collation only being taken into account when later traversing that index. For my purposes, I'd like to use the collation utf8_unicode_ci on a field. I know this particular collation ha...
I'm trying to optimize a query which uses a view in MySQL 5.1. It seems that even if I select 1 column from the view it always does a full table scan. Is that the expected behaviour? The view is just a SELECT "All Columns From These Tables - NOT *" for the tables I have specified in the first query below. This is my explain output from...
I write the code for my own website as an educational/fun exercise. Right now part of the website is a blog (like every other site out there :-/) which supports the usual basic blog features, including commenting on posts. But I only have comments enabled for logged-in users; I want to alter the code to allow anonymous comments - that is...
In my rails app, I am running a sql query using find_by_sql() since I need subqueries. This works if I do either the first or second query but when I add them together with the AND, it starts complaining about more than 1 row in subquery. I want all rows (records) returned that match the criteria. What needs to be fixed/changes here? W...
function getLink($sa){ if($sa=='1'){ $sa = 'qp_bbl'; } else { $sa = 'qp_sbl'; } return $sa; } if(!$_POST['action']){ header ("Location: index.php"); }else{ $sa = $_POST['select']; $sa = getLink($sa); $link = connect(HOST, USER, PASSWORD, $sa); (....) } This is driving me crazy. Select can be...
I want to do a query to retrieve the record immediately after a record for any given record, in a result set ordered by list. I do not understand how to make use of the limit keyword in sql syntax to do this. I can use WHERE primarykey = number, but how will limiting the result help when I will only have one result? How would I obtain ...
I am creating a web game for learning new words aimed at children. I have a set of four links each displaying a specific word retrieved from my database and a clue, I need to check that the word which has been selected matches the correct word for that clue. I know that I need to use javascript because of the onClick function and I can...
I am currently working on a simple revision system that enables me to store multiple versions of a single file, which works fine so far. Table structure is as follows (obsolete columns removed for the sake of brevity): file_id file_revision file_parent file_name -------------------------------------------------------- 1 ...
For a website implemented in Django/Python we have the following requirement: On a view page there are 15 messages per web paging shown. When there are more two or more messages from the same source, that follow each other on the view, they should be grouped together. Maybe not clear, but with the following exemple it might be: An ex...
I need to retrieve rows from a table (i.e: 'orders') ordered by a column (lets say 'user') randomly. That is, I need all orders from the same user to remain together (one after the other) and users to be ordered randomly. ...
hi all, i have a hard time locating an error when trying to create a stored procedure in mysql. if i run every single line of the procedure independently, everything works just fine. CREATE PROCEDURE cms_proc_add_child (param_parent_id INT, param_name CHAR(255), param_content_type CHAR(255)) BEGIN SELECT @child_left := rgt FROM cm...
I know that probably it's not possible to do this, but just want to check it out with you, guys... Is it possible to do something like this, using MySQL? I want to search in all fields from all tables a given string: SELECT * FROM * WHERE * LIKE '%stuff%' ...
The MySQL "show warnings" output identifies problematic rows by number. What's the best way to quickly see all the data for such a row? For example, after running an update statement the result indicates "1 warning" and running show warnings gives a message like this: "Data truncated for column 'person' at row 65278". How can I select...
Hi, I have 3 mysql 4.0 tables: all have fields ID(int), type(int) and another field, value which is either varchar(255), tinyint or int. I need to write them all out and I end up getting three DataTables, looping over them, and creating rows into a temporary table (in .NET 1.1). Do you see any faster/cleaner way than this to join or ju...
If I have a table with the hypothetical columns foo and bar. bar might have 50-60 distinct values in it. My goal here is to pick say, up to 5 rows for say 6 unique bars. So if the 6 unique bars that get selected out of the 50-60 each happen to has at least 5 rows of data, we'll have 30 rows in total. ...
I've got a table in a mysql database that contains monetary transactions. Let's say the tabled is called transactions: id int, to_user int, from_user int, created datetime, amount double I'm trying to create a query that returns the average amount of a transaction grouped by the number of transactions that appear per day. The applic...
Possible? To crazy to contemplate? if yes and no (respectively) any idea how to go about doing this? ...
Ok this is a new one for me. Basically I have a table articles: id: int auto increment title: varchar(200) description: varchar(1000) ctext: longtext chtml: longtext Now I do an insert into this table with mysql_query: INSERT INTO articles (title, description, ctext, chtml) VALUES ('$title', '$description', '$text', '$html') All v...
I currently have a javascript file 'score.js' which makes use of jQuery.js, which is being called correctly via a link. The code in score.js is: function originalUpdateScore(answer,correct){ if (answer == correct) { $.post('updateScore.php'); } window.location.reload(true); } This function calls 'updateScore.php': <?php inclu...