I have two tables Organisation and Employee having one to many relation i.e one organisation can have multiple employees. Now I want to select all information of a particular organisation plus first name of all employees for this organisation. What’s the best way to do it? Can I get all of this in single record set or I will have to get ...
At the beginning of each PHP page I open up the connection to MySQL, use it throughout the page and close it at the end of the page. However, I often redirect in the middle of the page to another page and so in those cases the connection does not be closed. I understand that this is not bad for performance of the web server since PHP aut...
I want to copy a live production database into my local development database. Is there a way to do this without locking the production database? I'm currently using: mysqldump -u root --password=xxx -h xxx my_db1 | mysql -u root --password=xxx -h localhost my_db1 But it's locking each table as it runs. ...
Hi, I have a members table in MySQL CREATE TABLE `members` ( `id` int(10) unsigned NOT NULL auto_increment, `name` varchar(65) collate utf8_unicode_ci NOT NULL, `order` tinyint(3) unsigned NOT NULL default '0', PRIMARY KEY (`id`) ) ENGINE=InnoDB; And I would like to let users order the members how they like. I'm storing the...
We have got loads of options for php + MySQL + Apache combo... Which is the best pack among these ? Lets confine our ideas to WAMP vs XAMPP, is there a better option to go for ? I created an online programming contest web app called CodeFire on XAMPP, later I had to switch to WAMP, where none of the php scripts worked properly... what ...
The original query looks like this (MySQL): select * from books where title like "%text%" or description like "%text%" order by date Would it be possible to rewrite it (without unions or procedures), so that result will look like this: - list of books where title matches query ordered by date, followed by: - list of books where descript...
Is there a SQL or PHP script that I can run that will change the default collation in all tables and fields in a database? I can write one myself, but I think that this should be something that readily available at a site like this. If I can come up with one myself before somebody posts one, I will post it myself. ...
I've just installed MediaWiki on a web server. Obviously it needs lots of privileges during installation to set up its database correctly. Now that it's installed can I safely revoke some privileges (e.g. create table, drop table?) Or might it need to create more tables later (when they are first needed?) If not then I would prefer t...
Hi all, Tackling a strange scenario here. We use a proprietary workstation management application which uses mySQL to store its data. Within the application they provide number of reports, such as which user logged into which machine at what time, all the software products installed on the monitored machines, so on and so forth. We ar...
I was given a .dump MySQL database file that I need to restore as a database on my Windows Server 2008 machine. I tried using MySQL Administrator, but I got the following error: The selected file was generated by mysqldump and cannot be restored by this application. How do I get this working? ...
I'm mocking about with plt-scheme's ffi and I have a C-function that returns a char ** (array of strings). If I declare my function as (_fun _pointer -> _pointer), how do I convert the result to a list of strings in scheme? Here are the relevant C-declarations: typedef char **MYSQL_ROW; /* return data as array of strings */ // ... MY...
I have a list of ranked users, and would like to select the top 50. I also want to make sure one particular user is in this result set, even if they aren't in the top 50. Is there a sensible way to do this in a single mysql query? Or should I just check the results for the particular user and fetch him separately, if necessary? Thanks! ...
I use PHPMyAdmin for convenience in updating a remote database. But it doesn't show warnings, by default, which recently got me into some embarrassing trouble where I was updating a SET field with string not on its list and not noticing the problem. I'm using 2.11.9.1 (Dreamhost's default install). On the PHPMyAdmin wiki it lists "Di...
I have a table y Which has two columns a and b Entries are a b 1 2 1 3 1 4 0 5 0 2 0 4 I want to get 2,3,4 if I search column a for 1, and 5,2,4 if I search column a. So, if I search A for something that is in A, (1) I get those rows, and if there are no entries A for given value, give me the 'Defaults' (a = '0') ...
Consider an indexed MySQL table with 7 columns, being constantly queried and written to. What is the advisable number of rows that this table should be allowed to contain before the performance would be improved by splitting the data off into other tables? ...
I've seen questions for doing the reverse, but I have an 800MB PostgreSQL database that needs to be converted to MySQL. I'm assuming this is possible (all things are possible!), and I'd like to know the most efficient way of going about this and any common mistakes there are to look out for. I have next to no experience with Postgre. Any...
I've got C# code that accesses MySQL through ODBC. It creates a transaction, does a few thousand insert commands, and then commits. Now my question is how many "round trips", so to speak, happen against the DB server? I mean, does it simply transmit every insert command to the DB server, or does it cache/buffer them and send them in bat...
We are currently working with JavaEE and MySQL 5 in our company, but we have some queries, especially delete queries that take > 10 min to execute. We consider to switch to PostgreSQL. What are the advantages of PostgreSQL over MySQL if there are any? Do you have experiences with both DBs and may give me a roundup whether this is a good...
I have a table which is referenced by foreign keys on many other tables. In my program if I want to delete one of these rows I need to first search for dependencies and present them to the user - "This object depends on x from table y, z from table q, etc". I also expect the number of tables which have foreign keys to this table to grow ...
How to enable inno-db support on installed instance of MySql? I have installed mysql-5.0.67-win32. 'InnoDB' is 'DISABLED' when executing 'show engines'. According to documentation MySql is compiled with support of inno-db (From doc: A value of DISABLED occurs either because the server was started with an option that disables the engine,...