Get average time between times in SQL
I'm really clueless as how to do this. Say my table looks like so: MESSAGES - id - date_posted - content How do I get the average time between postings? (date_posted) ...
mysql
Hibernate one-to-one mapping problem.
Hello I've the following class: public class Movimenti implements java.io.Serializable { private Integer id = null; private Integer idCommessa = null; private String nomemovimento = null; private Movimento preventivato = null; private Movimento effettivo = null; public Movimento getEffettivo() { return effettivo; } public void se...
MySQL query to evaluate aggregate duration of time something is "on"
I have a series of timestamped on/off data in a table, representing on/off states, or the point at which a state "starts" 00:00:00 0 04:00:00 1 08:00:00 0 09:00:00 1 15:00:00 0 20:00:00 1 23:59:59 0 I need to calculate the total duration of (say) the ON state over a 24h period. In this simplified example total du...
How do I convert a mySQL timestamp into RFC-822 using Perl?
I have data stored in a mySQL table under the column type "timestamp" that I would like to output as an RFC-822 compliant date, for inclusion in a valid RSS feed. I know how I could simply output the current time as an RFC-822 object using perl, but I haven't figured out how to convert an existing string. The string is formatted like...
PHP+MySQL join statement arrange multiple values as row in mysql_fetch array
hi, I am using a MySQL INNER JOIN statement which returns various results for one distinct record in the main table from the joins, similar to http://stackoverflow.com/questions/1038653/how-to-join-multiple-joins-yet-return-distinct-values My query is SELECT cl.*, dep.dept_name FROM epapers.clientelle cl INNER JOIN epapers.dept_s...
inserting record mysql
Hello all! I have a method which insert a record into a table as shown below: public static function AddCategory($categoryName) { $sql = 'INSERT INTO category(name) VALUES(' . $category_name .')'; $parameters = array('category_name' => $categoryName); return DB::Execute($sql,$parameters); ...
Mysql query to find a user with all of the given permissions
Hi All, I want to find the users(userid) from a permissions table who have all of the given permissions. Something like : select userid, permission from permissions where all_of permissions in ('view', 'delete', 'add', 'edit'); Note: this query is not to do with mysql permissions. It is a generic question, assuming that I have a user...
How to change string date to MySQL date format at time of import of CSV using MySQL's LOAD DATA LOCAL INFILE
I'm using MySQL's LOAD DATA LOCAL INFILE SQL statement to load data from an CSV file into an existing database table. Here is an example SQL statement: LOAD DATA LOCAL INFILE 'file.csv' INTO TABLE my_table FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n' (name, address, dateOfBirth) The third column in the CSV that m...
nested foreach loops with a many to many relationship
I have two tables (groups and parties) in a many to many relationship using a lookup table called groupparty. I'd like to be able to assign prices to each party, such that a party that belongs to two groups might have one price in one group and a different price in another group. In the table groupparty, I have three columns: groupid, p...
Apache/PHP processes hanging while interacting with MySQL
Hi all, We're seeing some strange behaviour and we're not sure if it's a problem with apache, php, mysql or the OS, so over to the big brains of stackoverflow! We have Apache and mod_php talking to a mysql5 server. Sometimes, a process will choose to hang, trying to read from a file descriptor. Firing up strace one on of them (all han...
mysql performance degrades after increasing data stored. Need hints on systematic diagnostic procedure
We have an established web application, which is supported by a mysql database. We add data incrementally to various tables on a monthly basis. After recent updates, the database performance has degraded significantly and we had to back out the updates. So far I have identified the queries involved in the web service logic - there are no...
create personal MySQL Database
What's a good way to approaching this? I would like to create a local database, just on my laptop (for now), so that I can teach myself some PHP and how to interact with databases. ... preferably a free approach... Thanks, Hristo ...
Imposing a Partial Ordering on Git Commit IDs
I'm converting the infrastructure at my workplace to use git instead of svn. The overall migration is going well, but we have a tool that I developed to do our SQL schema migrations. In order to deal with individual schema change dependencies, the migrations script used subversion keyword replacement to put the last-changed revision nu...
MySQL - Select statement, order by number of comments per article.
Hi I'd like to do something like the following: SELECT * FROM tbl_article JOIN tbl_comments ON tbl_article.id = tbl_comments.article_id ORDER BY COUNT(tbl_comments.article_id) Can anyone suggest how I might get this to work? ...
Singular/Recurring Schedules in my CMS
I'm building a CMS that has a requirement to allow users to store schedules. The interface is similar to MS Outlook, so the recurring schedules pretty much needs to mimic what that UI can support. When the schedule is saved, we need to somehow be alerted when an event is supposed to happen. Right now, the plan is to have a cron job run ...
MySQL - Grouping dayname() for unique columns
I have a stored procedure that takes data from multiple tables. The stored procedure is called from a PHP script and the resulting data is placed in a grid. The issue I'm having is I'm not sure how to get the dayname to be unique so that at maximum there are seven columns, one for each day of the week, as opposed to getting results like...
multiple database and transaction problem
hello! If i use two database, i have to start to transaction? Is this correct, or this code is wrong? If i make a mistake in the second query, then call rollback(), but unfortunatelly won't rolli'n back the frist query... $conn_site=mysql_connect("localhost", "us", "ps"); mysql_select_db("site",$conn_site); $conn_forum=mysql_connec...
Do multiple foreign keys make sense?
can it make sense for one table to have multiple foreign keys? Suppose I have three tables, Table A, Table B and Table C. If I think of the tables as objects (and they are mapped to objects in my code), then both Tables A and B have a many to one relationship with Table C. I.e. Table/object A and B can each have many instances of C. ...
Changing MySQL Record value for Rails App Subdomain
I'm not sure if I'm able to describe this clearly but I'm going to give it a shot...I've painted myself into a bit of a corner. Obviously there's a bug in my code, so pls pardon the newbie question... My Rails app uses subdomains like this... UserAccount1.theapp.com - Primary user UserAccount1.theapp.com - Secondary user UserAccount2...
Mixed Lucene / MySQL Query or Concept
Our project requires near-real time searches and constant updating. The data is currently stored in a MySQL database and the Lucene index is updated as the database is modified. We have the search capability currently where we want it. However, we are attempting to add the ability to "tag" documents in the index/database. Since the d...