mysql

MySQL String Function equivalent to PHP ucwords() function

Is there a MySQL String function equivalent to PHP ucwords() function. The end goal is to use MySQL to uppercase the first letter of each word in a string. Example. -- The name field in the table holds "JOHN DOE" SELECT LOWER(name) FROM table This will give me the result 'john doe' I want the result 'John Doe' ...

How To determine the order data is read from a MYSQL Database?

is there a way to change the order data from a DB are read ? you know , the default order is that they are read from the first data inserted to the latest so is there a way to change it to latest->first? ...

MySQL look for missing ratings

Hopefully I can ask this without being confusing. I am a photographer and I am having some of our clients rate pictures that we have taken. We have hundreds of pictures in our portfolio that they may be rating. What I want to do is ask our clients to rate pictures again, but only show them the pictures they haven't yet rated. I currently...

How to set up a database and API web page to accept POST updates from iPhone?

Hi there, I am developing an iPhone app, which now can update Twitter account with GPS coordinates in real-time, by the Twitter API link: http://username:[email protected]/statuses/update.xml , and I am looking at how to make my own database to accept updates from iPhone, via a similar API page. It seems a .php page can serve as the...

Index part of the mysql/innodb table?

I am sorry if this is a dumb question (cause it sounds unlikely). I have a table that is 20 Million rows. However, only about 300K of these rows get accessed regularly, and they can be identified in a column condition called "app_user=1" Is there anyway i can just index those rows, and when I call a select, i will be sure to pass in t...

JTA Transaction: What happens if an exception happens but rollback is not called on the transaction?

We have some third party code wherein they do the following 1) Create a User Transaction e.g. txn = (UserTransaction)ctx.lookup( "UserTransaction" ); txn.begin( ); 2) Do some work persisting to the database (via JPA) to a MySQL database 3) txn.commit() They have Exception blocks but NONE of them call txn.rollback. Good c...

What data actually gets cached in InnoDB/MySQL?

Hi folks, i am trying to optimize performance for my database. My question is - what get cached in the db memory? For example: (table with 2 columns: key (indexed), data (not indexed) updated (not indexed) Select * where updated=20100202 (the db will do a scan - will the scanned rows be kept in memory?) Select * where key = 20 (th...

How do you catch a MySQLIntegrityConstraintViolationException correctly?

I am working on a web application, and at a point where I submit forms that are unique to the user, a MySQLIntegrityConstraintViolationException Duplicate entry -'username' for key 'PRIMARY' I am well aware of why this is being thrown, because I am attempting to enter a duplicate entry into the database with the same primary key. I wo...

How would Sphinx Search index / handle JSON files?

I have a mysql database that stores mainly JSON objects. I am thinking of using Sphinx to search through it - but i was wondering how would i index and retrieve these json objects? ...

How to remove duplicate entries from a mysql db?

I have a table with some ids + titles. I want to make the title column unique, but it has over 600k records already, some of which are duplicates (sometimes several dozen times over). How do I remove all duplicates, except one, so I can add a UNIQUE key to the title column after? ...

MySQL - SELECT * INTO OUTFILE LOCAL problem.

MySQL is awesome! I am currently involved in a major server migration and previously, our small database used to be hosted on the same server as the client. So we used to do this : SELECT * INTO OUTFILE .... LOAD DATA INFILE .... Now, we moved the database to a different server and SELECT * INTO OUTFILE .... no longer works, understanda...

Duplicating table in MYSQL without copying one row at a time

Hi folks, I want to duplicate a very large table, but I do not want to copy it row by row. Is there a way to duplicate it? For example, you can TRUNCATE w/o deleting row/row, so i was wondering if there is something similar for copying entire tables UPDATE: row by row insert is very painful (because of 120M rows). Anyway to avoid that...

Mysql query in drupal database - groupwise maximum with duplicate data

I'm working on a mysql query in a Drupal database that pulls together users and two different cck content types. I know people ask for help with groupwise maximum queries all the time... I've done my best but I need help. This is what I have so far: # the artists SELECT users.uid, users.name AS username, n1.title AS artist_...

When to use a foreign key in MySQL

Is there official guidance or a threshold to indicate when it is best practice to use a foreign key in a MySQL database? Suppose you created a table for movies. One way to do it is to integrate the producer and director data into the same table. (movieID, movieName, directorName, producerName). However, suppose most directors and produ...

Why does this PHP script interfere with my CSS layout?

This page uses $_GET to grab an asset id and query a mysql database and return some information. If 'id' does not match anything, no results are displayed but the page looks fine. If 'id' is null an error would occur at $id = $_GET["id"] or die(mysql_error()); When this occurs, they page layout is not displayed correctly. How do I fi...

Any good examples/references implementing ACLs via SQL?

I currently have a web-application that implements a rudimentary form of ACL using UNIONs in mysql, but it's somewhat limited, and doesn't necessarily scale that well I'm interested in any alternative ACL algorithms that could be implemented efficiently in SQL. Thanks! ...

Optimizing a MySQL query that finds duplicate data

I have a query that I use for finding duplicate data. I have found that lately the query is very slow, and only getting slower. This is the query I am using (columns renamed): SELECT col1, COUNT(col1) AS Counter FROM people GROUP BY col1 HAVING (Counter > 1) I have indexed col1 (which is a varchar(500)), but the q...

SQL select from last inserted

Hello, in MySQL, I have table. One column has auto-increment. When I insert something mysql_query('INSERT INTO `table` SET `column` = "data";'); Now, what I want is, without further queries, to get the value of the auto-incrementing column, maybe like this: mysql_query('insertion = (INSERT INTO `table` SET `column` = "data"); SELEC...

How can I detect the domain that is parsing my RSS feed?

I've got a php rss feed. There's a lot of domains that are using my RSS feed for news. I'd like to be able to track which domains are using my rss feed. I tried using $_SERVER['http_referrer'] to no avail. Any ideas? ...

SQL Query taking too long

I am trying to optimize the SQL query listed below. It is basically a search engine code that retrieves products based on the products name. It also checks products model number and whether or not it is enabled. This executes in about 1.6 seconds when I run it directly through the phpMyAdmin tool but takes about 3 seconds in total to lo...