Is it possible to build a single mysql query (without variables) to remove all records from the table, except latest N (sorted by id desc)? Something like this, only it doesn't work :) delete from table order by id ASC limit ((select count(*) from table ) - N) Thanks. ...
I'm in over my head with a big mysql query (mysql 5.0), and i'm hoping somebody here can help. Earlier I asked how to get distinct values from a joined query http://stackoverflow.com/questions/508707/mysql-count-only-for-distinct-values-in-joined-query The response I got worked (using a subquery with join as) select * from media m i...
What are the best practices for optimizing a MySQL installation for best performance when handling somewhat larger tables (> 50k records with a total of around 100MB per table)? We are currently looking into rewriting DelphiFeeds.com (a news site for the Delphi programming community) and noticed that simple Update statements can take up ...
A client has just asked me to fix an issue with their webside. I did not build it but here is what's happening. The images are stored in the db as longblobs. Before moving to a new server, everything was working a ok. On the new server, some images only display partially. Even though the image is displayed at desired width and heigh...
Basically I have articles in my database and I want to alter the way the first record displays. I want the lastest (Posted) article to be the focus and the older article just to list, (see F1.com). I need to know how to get the first of my values in the array and get it to display differently but I am not sure how to do this, I can do it...
I have a php / mysql / jquery web app that makes 13 ajax queries to various APIs and stores the result in a database table. To each result I assign a score and when all the results have completed I want to display a 'total score'. My problem is knowing when all the ajax queries have completed and all the results are in and ready to be t...
Is there a command to determine which configuration file MySQL 5.0 is currently using? ...
I've recently been given a MS Access .mdb database file and asked to make it usable in a Linux system. What I'm looking for is a way to convert the Access database to an open-source database such as MySQL or PostGres. I don't have MS Office, and it's a one-time project for a volunteer organization so I don't want to spend money if it...
Hi, I am in dilemma, do not know which one is better. (I am using php and mysql btw) Lets say I am developing a social community website. A user can add as many friends as he wants. In my database, I have a users table which store user's details include user's friends. Should I store the user's friends in: Strings => john;bryan;sam;pau...
Hi, I have used an application to import some data from a text file to mysql. I have used the following code. try { stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE); stmt1= (Statement) conn.createStatement(); int deleteRows = stmt1.executeUpdate("delete fro...
I have 2 tables: 'Users' Table id username ---- -------- 0001 user1 0002 user2 0003 user3 0004 user4 'Friends' Table user_id friend_id friend ------- --------- ------ 0001 0004 1 0002 0004 1 0005 0004 0 How do I display all user4 friends'...
If I have a table CREATE TABLE users ( id int(10) unsigned NOT NULL auto_increment, name varchar(255) NOT NULL, profession varchar(255) NOT NULL, employer varchar(255) NOT NULL, PRIMARY KEY (id) ) and I want to get all unique values of profession field, what would be faster (or recommended): SELECT DISTINCT u.profession FR...
My web application parses data from an uploaded file and inserts it into a database table. Due to the nature of the input data (bank transaction data), duplicate data can exist from one upload to another. At the moment I'm using hideously inefficient code to check for the existence of duplicates by loading all rows within the date rang...
I am currently developing a sports website where one of the pages with be forthcoming fixtures in which the user will be able to what team and where the team are playing their next match. I have a database with the following fields... ID TEAM NUMBER OPPOSITION VENUE DATE MEET TIME MATCH TYPE So a row of data pulled from the DB an...
I am designing a threaded message display for a PHP/MySQL application - like comments on Slashdot or Youtube - and am wondering how I should go about ordering the comments and separating it into pages so that you can have, say, 20 comments to a page but still have them nested. Comments in my app can be nested unlimited levels, and this ...
Hello, I am trying to learn how Hibernate works, and I am running into an almost unacceptable learning curve. I can't see how to get Hibernate to respect the auto_increment policy for my objects. Instead, it is overwriting entries in the database with existing IDs, beginning with 1. I have a simple Foo object, backed by a MySQL table d...
Apologies if this has been asked already. I've seen answers regarding static SQLs, but in this case I'd like to use PDO->prepare() for a query string that is built dynamically at runtime. Breaking down into a simple example: $TempSQL = "SELECT field1, field2, field3 FROM table WHERE "; if ($numberParams == 1) { $TempSQL = $TempSQ...
With the economy the way it is my team and I have been looking into using MYSQL to reduce our licensing costs. I am wondering if there are any gotchas or issues that may crop up. Is there anything we need ot do special to get .NET to talk to mysql as opposed to MsSQL? When developing for it will LINQ to SQL have issues? Any caveats we...
I had the following code <?php $getFriendQuery = "SELECT DISTINCT U.username FROM users as U INNER JOIN test_friends as F on U.Id = F.user_id WHERE F.friend_id = '{$userID}' && F.active=1"; $getFriendResult = mysql_query($getFriendQuery, $conn) or die (mysql_error()); $friendName = ""; while($getFriendFetch = mysql_fetch_assoc($ge...
Is there a command I can run within MySQL to determine where/which configuration file MySQL is using? ...