mysql

Add 1 to a field (MySQL)

How do I turn the following 2 queries into 1 query $sql = "SELECT level FROM skills WHERE id = $id LIMIT 1;"; $result = $db->sql_query($sql); $level = (int) $db->sql_fetchfield('level'); $db->sql_freeresult($result); ++$level; $sql = "UPDATE skills SET level = $level WHERE id = $id;"; $result = $db->sql_query($sql); $db->sql_freeres...

Multiple foreign keys?

I've got a table that is supposed to track days and costs for shipping product from one vendor to another. We (brilliantly :p) stored both the shipping vendors (FedEx, UPS) with the product handling vendors (Think... Dunder Mifflin) in a "VENDOR" table. So, I have three columns in my SHIPPING_DETAILS table that all reference VENDOR.no. F...

How do you manage databases in development, test, and production?

I've had a hard time trying to find good examples of how to manage database schemas and data between development, test, and production servers. Here's our setup. Each developer has a virtual machine running our app and the MySQL database. It is their personal sandbox to do whatever they want. Currently, developers will make a change to ...

What's the best way of converting a mysql database to a sqlite one?

I currently have a relatively small (4 or 5 tables, 5000 rows) MySQL database that I would like to convert to an sqlite database. As I'd potentially have to do this more than once, I'd be grateful if anyone could recommend any useful tools, or at least any easily-replicated method. (I have complete admin access to the database/machines...

Remove Quotes and Commas from a String in MySQL

I'm importing some data from a CSV file, and numbers that are larger 1000 get turned into "1,100" etc. What's a good way to remove both the quotes and the comma from this so I can put it into an int field? Edit: The data is actually already in a MySQL table, so I need to be able to this using SQL. Sorry for the mixup. ...

MySQL replication for fallback scenario

When I have two mysql servers that have different jobs (holding different databases) but want to be able to use one of them to slip in when the other one fails, what would you suggest how I keep the data on both of them equal "close to realtime"? Obviously it's not possible to make a full database dump every x minutes. I've read about...

Pylons error - 'MySQL server has gone away'

[I hope this isn't too obscure I'll ask the newsgroup if nobody knows here] I'm using Pylons (a python framework) to serve a simple web application, but it seems to die from time to time, with this in the error log: (2006, 'MySQL server has gone away') I did a bit of checking, and saw that this was because the connections to MySQL were...

MySQL replication: if i don't specify any databases, will log_bin log EVERYTHING?

I'm setting up replication for a server which runs a bunch of databases (one per client) and plan on adding more all the time, on my.cnf, Instead of having: binlog-do-db = databasename 1 binlog-do-db = databasename 2 binlog-do-db = databasename 3 ... binlog-do-db = databasename n can I rather just have binlog-ignore-db = mysql ...

MySQL Administrator Backups: "Compatibility Mode", What Exactly is this doing?

In Mysql Administrator, when doing backups, what exactly is "Compatibility Mode"? I'm trying to bridge backups generated by webmin with the upload tool available inside mysql administrator. My data already has a couple of inconsistencies (ticks, commas, etc, I think) I just wont try to kink out (they might just reappear in the future a...

any sample MySQL databases I can download?

I'm doing some inter-database operational research... e.g. synchronizing Oracle, MySQL, etc. Are there any nice MySQL databases that I can download, so that I can test some importing on real-world cases? I'm thinking of some open project that might have a weekly data dump available for download. Of course, anything similar for Oracle,...

Is there any list datatype in MySQL stored procedures, or a way to emulate them?

I would like to create a stored procedure in MySQL that took a list as argument. For example, say that I would like to be able to set multiple tags for an item in one call, then what I want to do is to define a procedure that takes the ID of the item and a list of tags to set. However, I can't seem to find any way to do this, there is no...

Select all columns except one in MySQL?

I'm trying to use a select statement to get all of the columns from a certain MySQL table except one. Is there a simple way to do this? EDIT: There are 53 columns in this table (NOT MY DESIGN) Sounds like there's no good solution, thanks anyways guys. ...

What is the best MySQL Client Application for Windows

Are there any MySQL Client applications for Windows that get even close to what the Enterprise Manager / Server Management Studio is for Microsoft SQL? I have two requirements and one "nice to have" feature: Editing table structure should not be a pain in the a** -> I don't want to click an "add another column" button, then edit the c...

Bidirectional outer join

Suppose we have a table A: itemid mark 1 5 2 3 and table B: itemid mark 1 3 3 5 I want to join A*B on A.itemid=B.itemid both right and left ways. i.e. result: itemid A.mark B.mark 1 5 3 2 3 NULL 3 NULL 5 Is there a way to do it in one query in MySQL? ...

Differences Between MySql and MS SQL

I'm an ASP.NET developer who has used Microsoft SQL Server for all my database needs (both at work and for personal projects). I considering trying out the LAMP stack for some of my personal projects. What are some of the main differences between MySQL and MS SQL? Are using Stored Procedures a common practice in MySQL? Any advice or reso...

Is there any kind of non text interface to MySQL?

I have a MySQL query that returns a result with a single column of integers. Is there any way to get the MySQL C API to transfer this as actually integers rather than as ASCII text? For that matter is there a way to get MySQL to do /any/ of the API stuff as other than ASCII text. I'm thinking this would save a bit of time in sprintf/ssca...

How do I get PHP and MySQL working on IIS 7.0 ?

Okay, I've looked all over the internet for a good solution to get PHP and MySQL working on IIS7.0. It's nearly impossible, I've tried it so many times and given up in vain. Please please help by linking some great step-by-step tutorial to adding PHP and MySQL on IIS7.0 from scratch. PHP and MySQL are essential for installing any CMS... ...

Specify a Port Number in Emacs sql-mysql

I've been using Emacs's sql interactive mode to talk to the MySQL db server and gotten to enjoy it. A developer has set up another db on a new non-default port number but I don't know how to access it using sql-mysql. How do I specify a port number when I'm trying to connect to a database? It would be even better if Emacs can prompt me...

PHP + MYSQLI: Variable parameter/result binding with prepared statements.

In a project that I'm about to wrap up, I've written and implemented an object-relational mapping solution for PHP. Before the doubters and dreamers cry out "how on earth?", relax -- I haven't found a way to make late static binding work -- I'm just working around it in the best way that I possibly can. Anyway, I'm not currently using p...

What is a good way to denormalize a mysql database?

I have a large database of normalized order data that is becoming very slow to query for reporting. Many of the queries that I use in reports join five or six tables and are having to examine tens or hundreds of thousands of lines. There are lots of queries and most have been optimized as much as possible to reduce server load and incr...