Suppose we have two tables. Post and Comment. Post has many Comments. Pretend they are somewhat filled so that the number of comments per post is varied. I want a query which will grab all posts but only the newest comment per post. I have been directed to joins and sub queries but I can't figure it out. Example Output: Post1: Comment...
Hi. The environment of my application: web-based, Spring MVC+Security, Hibernate, MySQL(InnoDB) I am working on a small database application operated from a web interface. There are specific and known users that handle the stored data. Now I need to keep track of every create/update/delete action a user executes on the database and pro...
I am trying to import a CSV file into MySQL 5.0 with the following line: LOAD DATA LOCAL INFILE 'file' INTO TABLE MYTABLE FIELDS TERMINATED BY ';' ENCLOSED BY '"' ESCAPED BY '\\' My table schema is as follows CREATE TABLE AUCTIONS ( ARTICLE_NO VARCHAR(20), ARTICLE_NAME VARCHAR(100), SUBTITLE VARCHAR(20), CURREN...
I'm rather new to working with multiple threads in a database (most of my career has been spent on the frontend). Today I tried testing out a simple php app I wrote to store values in a mysql db using ISAM tables emulating transactions using Table Locking. I just wrote a blog post on the procedure Here: Testing With JMeter From my...
I have MySQL installed on a Windows 2003 server on our domain. I cannot connect to it from other machines on the domain. I open up the MySQL Administrator panel and try to change the server name from localhost to the machine name it does not recognize it. I have tried fully qualifying the name also. Any ideas what I need to change? ...
I decided to use favs (id's of users which marked that post as a favorite) as a comma separated list in a favs column which is also in messages table with sender,url,content etc.. But when i try to count those rows with a quey like: select count(id) from messages where favs like '%userid%' of course it gives wrong results because al...
Can it support a small business? Do people recommend MySql + Php set up or a Java server to support a business with say 2000 editors accessing the data at the same time? ...
SELECT a.* b.* c.* FROM TOPIC a, BOARD b, MSGS c WHERE c.MessageID = 1 AND a.TopicID = c.MessageID AND b.BoardID = c.BoardID How to memcache this data? - set_cache("MY_WACKY_QUERY_1", data) note: 1 is the message id Now there are lots of places in the code which update these 3 tables (independantly of each other), so we need to del_cac...
Is there a way in mysql to calculate the offset for any timezone. For example to get the local time in the time zone 'Asia/calcutta' what i want to do is calculate the offset for this time zone and add that offset to GMT to get the local time. ...
We're using MySQL with InnoDB storage engine and transactions a lot, and we've run into a problem: we need a nice way to emulate Oracle's SEQUENCEs in MySQL. The requirements are: - concurrency support - transaction safety - max performance (meaning minimizing locks and deadlocks) We don't care if some of the values won't be used, i....
How do you insert data into a MySQL date or time column using PHP mysqli and bind_param? ...
In my site i m fetching my mysql data by using PHP. I want open that data in pdf file when i click pdf print button is it possible? ...
I have a MySQL table with a column of well-formed URLs. I'd like to get a table of the unique domain names in the column, and the number of times each domain appears. Is there a MySQL query that can do this? I thought of doing something like... SELECT COUNT(*) FROM log GROUP BY url REGEXP "/* regexp here */" ...but this doesn't work ...
I have two tables, and want to update fields in T1 for all rows in a LEFT JOIN. For an easy example, update all rows of the following result-set: SELECT T1.* FROM T1 LEFT JOIN T2 ON T1.id = T2.id WHERE T2.id IS NULL The MySQL manual states that: Multiple-table UPDATE statements can use any type of join allowed in SELECT state...
I have a table whose primary key is referenced in several other tables as a foreign key. For example: CREATE TABLE `X` ( `X_id` int NOT NULL auto_increment, `name` varchar(255) NOT NULL, PRIMARY KEY (`X_id`) ) CREATE TABLE `Y` ( `Y_id` int(11) NOT NULL auto_increment, `name` varchar(255) NOT NULL, `X_id` ...
I am not professional programmer so i can not be sure about this.How many mysql queries your scripts send at one page and what is your optimal query number .For example at stackoverflow's homepage it lists questions shows authors of these questions . is stackoverflow sends mysql query foreach question to get information of author. or...
I am writing a distributed DB Application with an Access Front end. Essentially an mde with some forms and reports. Should I use an access mdb to hold the backend tables or use MySQL or some other database? ...
Looking to move data from a table A to history table B every X days for data that is Y days old and then remove the data from history table B that is older than Z days. Just exploring different ways to accomplish this. So any suggestions would be appreciated. Example for variables X - 7days Y - 60days z - 365days Thank you ...
I have two tables, a vehicle table with columns: id stock year make model and an images table with columns: id vehicle_id name caption default tinyint(1) I am trying to list the vehicle's information, its default image, and a total count of images the vehicle has. Currently I am using the following SELECT statement: SELECT vehi...
How can I optimize this query? It seems there should be a much easier way to do this. The goal is that it will still be able to be readily turned into a delete statement. SELECT * FROM team WHERE team_id IN ( SELECT team_id FROM ( SELECT team.team_id, ( ...