mysql

MySQL database design question

I'm trying to weigh the pros and cons of a database design, and would like to get some feedback as to the best approach. Here is the situation: I have users of my system that have only a few required items (username, password). They can then supply a lot of optional information. This optional information continues to grow as the syst...

Is there a problem with this MySQL Query?

public function create() { echo $this->equipment->getCatId() . "<br/>"; echo $this->equipment->getName() . "<br/>"; echo $this->equipment->getYear() . "<br/>"; echo $this->equipment->getManufacturer() . "<br/>"; echo $this->equipment->getModel() . "<br/>"; echo $this->equipment->getPrice(...

MySQL Query: How to select rows that don't have a certain value?

I am having trouble writing a query and I don't even know if it is possible. Take this table for example: id group active 1 A NO 2 A YES 3 A NO 4 B YES 5 B NO 6 C NO 7 C NO Table above is just an example. In real table there are much more columns the those t...

MySQL charset issue.

I'm not sure exactly why this happened, but I'm assuming it was an dump and import. The db is full of characters like — for commas and such. I've tried various solutions on the web, but nothing seems to work. I've verified that the html header specifies utf8. Any ideas on how I can get the entire db back to normal characters? ...

Convert date/time string value to .NET DateTime

Hi guys, i've this string example value: Sun, 09 May 2010 11:16:35 +0200 I've to insert it into MySql Date/Time field. How can i convert it into .NET format (or Mysql format), so i can make my INSERT INTO mydate='2010-05-09 11:16:35' ? Thank you ! ...

MySQL : delete from table that is used in the where clause

I am writing a small script to synchronize 2 MySQL tables ( t1 to be 'mirrored' to t2 ) In a step I would like to delete rows inside t2 that has been delete in t1 with the same id. I tried this query : delete from t2 where t2.id in ( select t2.id left join t1 on (t1.id=t2.id) where t1.id is null ) But Mysql forbid me to use t2 ...

How can i found out if there is transaction open in MySQL?

How can i find out if there is transaction open in mySQL? I need to start new one if there is no transaction open, but i don't want to start new one if there is one running, because that would commit that running transaction. UPDATE: i need to query database in one method of my application, but that query could be called as part of bi...

MySQL: automatic rollback on transaction failure

Is there any way to set MySQL to rollback any transaction on first error/warning automatically? Now if everything goes well, it commits, but on failure it leaves transaction open and on another start of transaction it commits incomplete changes from failed transaction. So i need to rollback automatically those failed transactions.. (I'...

Mysql Master Slave Replication on Large Database table (how to sync initial data)

We have a production server and a dev server. We have found that backups are nearly impossible on the production server because of the query volume we experience. So, we're looking at setting up replication with our dev server being the slave. This is ideal because we can afford to lock the tables on that server and additionally it will ...

Tab Delimited to Comma Separated file in UNIX - One liner?

I have a tab delimited file (MySQL Out file). I want to convert it into CSV file. I got everything working except for replacing NULLs to nothing or spaces. What I have is : sed -e 's/^/"/; s/$/"/; s/\t/","/g;' < file.csv > file1.csv How to also replace NULLs in the same line. The following doesn't work. sed -e 's/NULL//; s/^/"/; s/$...

Is it safe to use connection.createStatement().executeQuery(String query);?

Is it safe to do connection.createStatement().executeQuery(String query);, or should each java.sql.Statement object be created and closed by "hand" on every use, even if the connection is closed in a finally block? If multiple queries are executed in one method by one connection it would use much less code if instantiating and closing e...

Should I be concerned about performance with connections to multiple databases?

I have a PHP app that is running on an Apache server with MySQL databases. Based on the subdomain that users access, I am connecting them to a database (sub1.domain.com connects to database_sub1 and sub2.domain.com connects to database_sub2). Right now there are 10 subdomain-database combos, but that number could potentially grow to wel...

Is there a good way to execute MySQL statements atomically via JDBC?

Suppose I have a table that contains valid data. I would like to modify this data in some way, but I'd like to make sure that if any errors occur with the modification, the table isn't changed and the method returns something to that effect. For instance, (this is kind of a dumb example, but it illustrates the point so bear with me) sup...

SQL to get friends AND friends of friends of a user

My MySQL tables structure is like this. USER int id varchar username FRIEND_LIST int user_id int friend_id For each friend relationship I insert 2 records in FRIEND_LIST. If user 1 is friend of user 2 then the next rows are inserted into FRIEND_LIST 1,2 2,1 I want to get the friends and friends of friends of an specific user. The...

Most efficient approach for multilingual PHP website

I am working on a large multilingual website and I am considering different approaches for making it multilingual. The possible alternatives I can think of are: The Gettext functions with generation of .po files One MySQL table with the translations and a unique string ID for each text PHP-files with arrays containing the different tra...

MySQL, C++: Retrieving auto-increment ID

I have a table with an auto-incrementing ID. After inserting a new row, I would like to retrieve the new ID. I found an article that used the MySQL function LAST_INSERT_ID(). The article says to create a new query and submit it. I'm using MySQL Connector C++, Windows XP and Vista, and Visual Studio 9. Here are my questions: Is ...

MySQL replication - Should I handle load balancing from my client code (PHP) ?

In a MySQL master-slave replication enviroment if I have 4 slave servers how can I execute load balanced select queries? Should I write a PHP class to dealing with the 4 slaves or it is possible to address queries to MySQL's own load balancer solution? Is there any MySQL load balancing solution? Can I use some other tool to distribute...

changing existing duplicate entries in mysql

sorry for the (probably) noob question, but I', new at this stuff. i have a table that has column 'position', and I want to, when inserting a new row, change positions of every row that has position lower than row that is inserted. for example, if I add row with position 4, if there is a duplicate, it should become 5, 5 should shift to ...

Zend_Db: How to connect to a MySQL database over SSH tunnel?

How can I connect to a MySQL database that requires an SSH tunnel using PHP and the Zend Framework? ...

PHP & MySQL Pagination Update Help

Its been a while since I updated my pagination on my web page and I'm trying to add First and Last Links to my pagination as well as the ... when the search results are to long. For example I'm trying to achieve the following in the example below. Can some one help me fix my code so I can update my site. Thanks First Previous 1 2 3 4 5 ...