How to compare MySQL database schemas
I am looking for a tool that will allow me to compare schemas of MySQL databases. Wich is the best tool to do that? ...
mysql
How to find the average time difference between rows in a table?
I have a mysql database that stores some timestamps. Let's assume that all there is in the table is the ID and the timestamp. The timestamps might be duplicated. I want to find the average time difference between consecutive rows that are not duplicates (timewise). Is there a way to do it in SQL? ...
php form issues
I have a form with which I am trying to display a form with checkboxes, and then insert into a database whether or not those checkboxes are checked. Then, each time the page is called, check the value and append information to the html form to appropriate display the checkbox as checked or not. I have modified the below code example to ...
MySQL: Initialize a summary table with placeholders for non-existent data
I'll try to give this as generically as I can so it's reusable. I am running a site with a fairly large MySQL database which has grown to need some summary/rollup tables initialized. For the example's sake, let's say it's soccer statistics. Since I handle multiple soccer leagues in the same database, many of them play games of differe...
How do I calculate a moving average using MySQL?
I need to do something like: SELECT value_column1 FROM table1 WHERE datetime_column1 >= '2009-01-01 00:00:00' ORDER BY datetime_column1; Except in addition to value_column1, I also need to retrieve a moving average of the previous 20 values of value_column1. Standard SQL is preferred, but I will use MySQL extensions if necessary. ...
Faster to query in MYSQL or to use PHP logic
I have a page that will pull many headlines from multiple categories based off a category id. I'm wondering if it makes more sense to pull all the headlines and then sort them out via PHP if/ifelse statements or it is better to run multiple queries that each contain the headlines from each category. ...
How to recover mysql db from .myd, .myi, .frm files
How to restore one of my mysql db from .myd, .myi, .frm files? ...
Quickest way to delete enormous MySQL table
I have an enormous MySQL (InnoDB) database with millions of rows in the sessions table that were created by an unrelated, malfunctioning crawler running on the same server as ours. Unfortunately, I have to fix the mess now. If I try to truncate table sessions; it seems to take an inordinately long time (upwards of 30 minutes). I don't c...
Mysql Daily Performance Reporting
I wish to create a daily report on how my mysql server perform on the day. But it seems like mysql only stores aggregate data since uptime. One way is to generate a report and then do a mysql restart (or do a FLUSH STATUS). Any other suggestions to do the same? ...
MySQL + Drupal: Index ignored in INNER JOIN
Our environment: Drupal+MySQL Examining the query log indicates that the following query, originating from Drupal core's node_load function is taking considerable amount of time. EXPLAIN on the node_load query reveals that the index is not used on the USER table. mysql> explain SELECT n.nid, n.vid, n.type, n.status, n.created, n.chang...
Mysql ORDER BY using date data row
I have a query something like this: SELECT title, desc, date FROM tablename ORDER BY date ASC, title ASC; Works fine when the data actually has a date. Issue is, date submission is optional, so I sometimes get 0000-00-00 as a date, which has the unfortunate effect of placing all nondated rows on top. So, I then tried this: S...
Problems formating a date
I am trying to write a function to format a date and time for me. I have an almost identical function which formats just a date. That function works fine. I just added some code to try and have it format the date with a time. It should return something like "May 18, 2009 9:50 PM" but I am getting this warning: Warning: mktime() expects ...
close mysql connection important?
is it crucial to close mysql connections efficiency wise? or does it automatically close after php file is run? ...
how to escape special character in mysql
eg: select * from tablename where fields like "%string "hi" %"; Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'hi" "' at line 1 please help me how to build this query ...
How do I join to the latest record in the table?
What I need done is simple... but its 3am and Im probably overlooking the obvious. Im coding a simple forum. One table stores the forum titles, descriptions, etc, while the other stores the posts. In the forum listing, that shows the list of all forums, I want to grab the latest post in each forum, and display the post subject, poster a...
I want to back up stored procedure in mysql
I use mysqldump with mysql 5.0 and I back it up every day, but do not understand the method that only stored procedure backs up. How can I back it up? ...
Troubleshooting consistent "SQLException: Lock wait timeout exceeded"
I have an application running Quartz 1.6.1 w/persistent job store, with MySQL 5.1 as the DB. This application used to boot up okay in Tomcat6. At some point, it began throwing the following exception upon EVERY boot: - MisfireHandler: Error handling misfires: Failure obtaining db row lock: Lock wait timeout exceeded; try restarting tran...
How do I mark duplicates in a given group in MySQL?
I have the following table structure (in MySQL): DocID, Code, IsDup, DopOf , where DocID is Unique. Values are like : 1,AAAA,nul,nul 2,AAAA,nul,nul 3,AAAA,nul,nul 4,BBBB,nul,nul 5,CCCC,nul,nul 6,CCCC,nul,nul What I want is to write a procedure which can update the table and give the desired result as: 1,AAAA,0,0 2,AAAA,1,1 3,AA...
How to implement related posts feature without tagging each post?
We have 2 text fields ('post_text' & 'post_slug') in our database. Let's say, post_text = "Hello World!", so its post_slug = "hello-world". How to implement related posts feature without tagging each posts operating only existing fields? (PHP, MySQL) p.s. the database contains a lot of posts. ...
Installing PDO extension for MySQL on Mac OS X Server
I would like to keep the PHP / MySQL combo as 'stock vanilla' as possible but would like to install the PDO driver for MySQL. How can I do this on 10.5? ...