These are the tables: threads: id, date, title, text comments: id, thread_id, date, comment How would I do to list the last commented thread on top? This is currently how it looks: $threads = mysql_query("SELECT id, title FROM threads ORDER BY date ASC"); while ($thread = mysql_fetch_assoc($threads)) { echo $thread['title']; }...
My table has only a single column calld id. This column is an autoincrementing key (I am using it for sequencing). I want to do something like: insert into sequencer; but this gives me SQL errors because I guess I need to have a values portion. But there are no other columns in the table and I want the id column to be autoincremented....
Hello All. I currently am creating a book inventory system with CodeIgniter (am new to CodeIgniter) and I would like each of the books to have to have tags. Currently, I have 4 tables: Books Tags BooksTags (matches bookid to tagid) Collections (series collection) In the controller for the main view which will show all the books, I ...
If one column is of type int and say has a value 10. The other column is of type varchar and has a value of '10'. Is it safe to join on these values (mySql), and would I get the same result as if both were of type int and value 10? I need to have them of different types because in one table the column is an autoincrementing key (so it...
I will soon be switching from sqlite3 to either postgres or mysql. What should I consider when making this decision? Is mysql more suited for Rails than postgres in some areas and/or vice versa? Or, as I somewhat suspect, does it not really matter either way? Another factor that might play into my decision is the availability of tools t...
I have started a new project in .NET which uses some old system's datababase in MySql. The data stored in mysql is periodicaly transfered to MS Sql on which our system works. I need to authenticate users with their login and password. User's passwords are stored as hash generated by OLD_PASSWORD function from mysql. Is there any way to ...
We get a MYSQL 5.0 dataset each month (1.7gig) and I need to create a process to migrate this to a SQL Server 2008. This seems a little harder than I first thought... I've tried a few ways: Using the Import wizard Setting up a linked server I've also tried different ways: Using the .net Framework Dataprovider for MYSQL Using MYSQ...
I find that when trying to construct complex MySQL joins and groups between many tables I usually run into strife and have to spend a lot of 'trial and error' time to get the result I want. I was wondering how other people approach the problems. Do you isolate the smaller blocks of data at the end of the branches and get these working f...
This code selects cell values in MySQL and manually adds them to PHP variables: $sql = "SELECT * FROM table LIMIT 0,1"; $result = mysql_query($sql); while($rows = mysql_fetch_array($result)) { $col1 = $rows['col1']; $col2 = $rows['col2']; $col3 = $rows['col3']; ..... } This is obviously unmanageable for multiple and la...
I'm getting performance problems when LIMITing a mysql SELECT with a large offset: select * from table limit m, n; If the offset m is, say, larger than 1,000,000, the operation is very slow. I do have to use "limit m, n" ; I can't use something like "id > 1,000,000 limit n". How can I optimize this statement for better performance? ...
I wish to do a select on a table and order the results by a certain keyword or list of keywords. For example I have a table like so: ID Code 1 Health 2 Freeze 3 Phone 4 Phone 5 Health 6 Hot so rather than just do a simple Order By asc/desc I'd like to order by Health, Phone, Freeze, Hot. Is this possible? Thanks :) ...
Hi wondering if perhaps someone could shed some light on the below error. The sql works fine locally but i get the the below error remotely. SQL query: SELECT COUNT(node.nid), node.nid AS nid, node_data_field_update_date.field_update_date_value AS node_data_field_update_date_field_update_date_value FROM ...
Hello, I am wondering at which point would my MySQL table be considered too big. The table is this: id customer_id (int) value (float) timestamp_1 (datetime) tmestampt_2 (datetime) so the row size is not too great, but would be constantly being added. In my estimation I am looking at around 17000 new rows a day, so about 500,000 a ...
Hi all! I'd like to optimize my queries so i look into mysql-slow.log. Most of my slow queries contains ORDER BY RAND(). I cannot find a real solution to resolve this problem. Theres is a possible solution at MySQLPerformanceBlog but i don't think this is enough. On poorly optimized (or frequently updated, user managed) tables it doesn...
For an internal Tomcat/Java/Struts application, we're converting custom-written authentication code to use JDBCRealm. The database is MySQL 5.0, and the passwords are stored as PASSWORD()-encrypted strings. In our version of MySQL, the PASSWORD() function is a non-standard (proprietary?) 41-byte hash. (I know now that we shouldn't be ...
I'm working on a small banner-rotation script that loads a random banner from the database. I am tracking impressions in the database and would like to know if I can select a random record and update its impression-value in a single query, or would I need to select a random record, and then update based upon the record pk. Using MySQL. ...
I am trying to better understand why this query optimization is so significant (over 100 times faster) so I can reuse similar logic for other queries. Using MySQL 4.1 - RESET QUERY CACHE and FLUSH TABLES was done before all queries and result time can be reproduced consistently. Only thing that is obvious to me on the EXPLAIN is that on...
This seems like such a simple task, but I'm having a hard time finding a solution that I like for this. I can't find anything I would consider anything other than clunky. Here's what I'm working with: There is a search form that posts variables to the processing script. These variables are the filters for the data being queried. Dep...
Can anyone tell the difference between mysqli->commit and mysqli::commit? The header in this page is mysqli::commit but in examples they use mysqli->commit I'm confused. ...
Hi, this is a follow up from http://stackoverflow.com/questions/1242223/mysql-find-rows-matching-all-rows-from-joined-table Thanks to this site the query runs perfectly. But now i had to extend the query for a search for artist and track. This has lead me to the following query: SELECT DISTINCT`t`.`id` FROM `trackwords` AS `tw` IN...