mysql

MySQL query for geographic midpoint

I need a MySQL query (or function) to calculate the geographic midpoint of an arbitrary number of latitude/longitude coordinates. I want to use Method C (Average latitude/longitude) as described on this page http://www.geomidpoint.com/calculation.html but can't figure out how to convert this into a SQL query. I'm looking for something of...

How do I do a GROUP BY and ORDER BY in PostgreSQL?

PostgreSQL is about to make me punch small animals. I'm doing the following SQL statement for MySQL to get a listing of city/state/countries that are unique. SELECT DISTINCT city , state , country FROM events WHERE (city > '') AND (number_id = 123) ORDER BY occured...

Inserting a post in Wordpress using MySql

Does anyone know how to insert a new post into Wordpress using sql? ...

Symfony and Lucene

SOLVED See my answer below. Question left unchanged for anyone else who has trouble with this. I'd like to use lucene (or anything else that could be used withs symfony for searching really) however I can't get the sfLucene plugin to work (says there are no tasks in the namespace "lucene" when i do ./symfony lucene:initialize). What ...

Do variables transfer over from one seperate script to another via PHP?

I have a script that has PHP on it, and a link that goes to a new script that alters that PHP. When writing the query and containing variables on the second script, do I need to use the same variables or are the new ones I create completely seperate? or can will the variables from the first script carry over to the second script if used?...

Can MySQL replace multiple characters?

I'm trying to replace a bunch of characters in a MySQL field. I know the REPLACE function but that only replaces one string at a time. I can't see any appropriate functions in the manual. Can I replace or delete multiple strings at once? For example I need to replace spaces with dashes and remove other punctuation. ...

Why won't MySQL use a reference index on the JOIN?

In the following example, MySQL fails to use to find a ref for the JOIN clause (or so it appears). Can anyone explain why? mysql> explain SELECT 1 FROM `businesses` INNER JOIN `categories` ON (`businesses`.`id` = `categories`.`business_id`) WHERE (`categories`.`category_id` IN (1321, 7304, 9189, 4736, 4737, 1322, 8554, 1323, 1324, 9...

Unable to output MySQL tables which involve dates in Sequel

I'm trying to use Sequel to access a MySQL database in Ruby. When I try accessing a table which involves a date column, I am presented with an error. When I access a table without, it functions fine. What is wrong? Example Code: require 'rubygems' require 'sequel' DB = Sequel.connect(:adapter=>'mysql', :host=>'localhost', :database=>'...

How to store SQL query results for fast access ?

Hello, SQL beginner here. I have a query which takes around 10 seconds to run, so we have a slow down in the application. Would it be possible in MySQL or more generally in SQL for the server to periodically (every 1 to 5 minutes) run the query and store it somewhere, so that I can query this "cache" table for easy access ? Many tha...

MySQL: Getting highest score for a user

I have the following table (highscores), id gameid userid name score date 1 38 2345 A 100 2009-07-23 16:45:01 2 39 2345 A 500 2009-07-20 16:45:01 3 31 2345 A 100 2009-07-20 16:45:01 4 38 ...

New to MySQL scaling-how to have 2 master-write servers?

From what I learned, a MySQL MASTER server is one that can both read and write. Correct? My question is: Suppose you have 2 MySQL master-write servers (and many slave-read servers). How can you solve the problem of auto-increment? How do you sync it? Suppose you are writing to both the Master1 and Master2...how will you sync it so tha...

I have one database (mysql). What is the best "failover" strategy?

Does MySQL come with a built-in automatic failover? Or, are there load-balancers out there that can "detect" whether the database is alive...and if not...shoot it to the failover database? I know basic MySQL, but do not know the best way to design failover for one master DB. ...

SQL returning extra data.

Hey there, was wondering if anyone could help a newbie on SQL and Python. I thought I had a pretty decent grasp of it, however something odd happened recently. Here is the the following code snipped from a larger portion: try: self.db.query("SELECT * FROM account WHERE email = '{0}' AND pass = '{1}'".format(self.mail.strip(self...

in which database software / language is it possible to create a database accessible by multiple users except its two collumns to be accessed only by admin user .

in which database software / language is it possible to create a database accessible by multiple users except its two collumns to be accessed only by admin user. please give details how this database can be created. ...

What is the best driver for mysql to connect?

I think quesiton is clear. I'm programming with C# and using MySQLDriverCS to connect MySQL. But I'm having issues about speed. Which drivers are you using to connect MySQL? MySQLDriverCS is the best solution? Please make some suggestions for me. Thanks in advance. ...

How to split a single row in to multiple columns in mysql

Hi All, Simply Asking, Is there any function available in mysql to split single row elements in to multiple columns ? I have a table row with the fields, user_id, user_name, user_location. In this a user can add multiple locations. I am imploding the locations and storing it in a table as a single row using php. When i am showing the u...

query on MySQL 5 replication

I have a table which contains a large volume of data...thousands of million of rows. I am planning to have a replica of this table. Can I have an index on replica only not on the original table, so that insertion in the orignal table will be fast. ...

ERROR 2003 (HY000): Can't connect to MySQL server on '127.0.0.1' (111)

I use the following command: mysql -u root -h 127.0.0.1 -p and the error message is : ERROR 2003 (HY000): Can't connect to MySQL server on '127.0.0.1' (111) Who can help me to fix it, thanks in advance. ...

MySQL Query is far too complicated

Hi there, I've got a query that I've made but I've got a problem with - Is this the 'neatest' way of writing this? I'm this could be written a lot better, I've heard you can 'loop through recordset' using more queries - would this be a good thing to do? And if so can someone point me in the right direction? This query must use up a lot ...

PHP and MySql ID's

Hi , Basically I have setup up SESSIONS on my site. Once a user registers an Auto Incremented ID is made for this user (I hope that is the right word) What I want to do is once the POST method is sent and the user is signed up on the next page say if the action is Welcome.php I would like on the Welcome.php page to echo the users ID...