I have written the following db interface class: <?php // dbinterface.php class db { private $con; private $host; private $user; private $pass; private $database; private $error; function db($host, $user, $pass, $database) { $this->con = mysql_connect($host, $user, $pass); mysql_select_db($databa...
So, I've never understood the explain of MySQL. I understand the gross concepts that you should have at least one entry in the possible_keys column for it to use an index, and that simple queries are better. But what is the difference between ref and eq_ref? What is the best way to be optimizing queries. For example, this is my latest ...
When recovering a MyISAM table the only file that is strictly needed is the data file (tablename.MYD) - the rest of the files (the index file tablename.MYI and tablename.frm) can be recreated from the data file using REPAIR TABLE. Assume I'm using InnoDB (with the "innodb_file_per_table" setting) instead - what is the minimum set of fil...
On redesigning a MySQL database I came up with an idea: creating a VIEW which can be altered when a TRIGGER (on UPDATE of other table) runs, using information selected from the INFORMATION_SCHEMA database. Hence we could have a view of whichever columns might be necessary at runtime. Since ALTER TABLE is illegal inside triggers, the set...
Hi, We have a function used within our PHP/MySQL application which returns basic configuration information, it contains a simple select query and looks like this: public function getConfigurationValue($field) { $res = mysql_query("SELECT `cfg_value` FROM `ls_config` WHERE `cfg_name` = '".mysql_real_escape_string($field)."'"); $...
I have a table in a MySql database with a datetime field. This is mapped to my domain object in an hbm.xml file with a property similar to the following: <property name="StartDate" column="datStartDate" not-null="true" type="datetime"/> This all works fine, except MySql doesn't store the millisecond portion of the DateTime field...
Is it possible to replace value field_id_36 with field_id_39 if field_id_36 is empty? SELECT wt.title, wt.url_title, wt.weblog_id, wt.entry_id, wd.field_id_36 FROM exp_weblog_titles wt LEFT JOIN exp_weblog_data wd ON wt.entry_id = wd.entry_id WHERE wt.weblog_id = {weblog_id_insert} ORDER BY field_id_36+0 So in theo...
I'm wanting to optimize a query using a union as a sub query. Im not really sure how to construct the query though. I'm using MYSQL 5 Here is the original query: SELECT Parts.id FROM Parts_Category, Parts LEFT JOIN Image ON Parts.image_id = Image.id WHERE ( ( Parts_Category.category_id = '508' OR Parts_Categ...
Following on from : http://stackoverflow.com/questions/1034360/how-to-save-portlet-positions I'd like to know how to get the new position of the portlet in the first place. ...
Many thanks once again in advance. What I am trying to do is list if an agency nuse works in hospital H2 or has a qualification Q3 or both I have two tables Agency_A & Qualification Agency_A Nurse Hospital Thomas H1 Taylor H2 Evans H3 Davies H2 Qualification Nurse Qualification Thomas Q2 ...
Looking for a scalable, flexible and fast database design for 'Build your own form' style website - e.g Wufoo. Rules: User has only 1 Form they can build User can create their own fields or choose from 'standard' fields User's 1 Form has as many fields as the user wants Values can be the sibling of another value E.g A photo value coul...
Wufoo is a: HTML form builder that helps you create contact forms, online surveys, and invitations so you can collect the data, registrations and online payments you need without writing a single line of code. How would you approach the database design if building a similar site? Higher level designs (tables and relationships) or ...
I would like to get Python's documentation for MySQLdb in Man -format such that I can read them in terminal. Where are Man -pages for MySQLdb in Python? ...
Consider the query (it runs on both Oracle and MySQL) UPDATE table1 SET something_id = CASE WHEN table1_id = 1446 THEN 423 WHEN table1_id = 2372 THEN 426 WHEN table1_id = 2402 THEN 428 WHEN table1_id = 2637 THEN 429 WHEN table1_id = 2859 THEN 430 WHEN table1_id = 3659 THEN 433 END WHERE table1_id IN (1446,2372,2402,2...
I am trying to fetch the sum of several counts in one query: SELECT( SELECT COUNT( * ) FROM comments + SELECT COUNT( * ) FROM tags + SELECT COUNT( * ) FROM search ) I am missing something here. I get syntax error. ...
Soon I'm going to build an application that needs to be scalable and flexible. Since I'm not a "MySQL" Guru I'm wondering if someone with experience could give me a couple of recommendations for achieving this application I'm going to build. Which database model structure do you think is the best for scalable and flexible sites? ...
I want to join 2 tables together but i cant get it to work. These are the tables: threads: id, title posts: thread_id, message $sql = mysql_query("SELECT threads.id, threads.title FROM threads JOIN posts ON posts.thread_id = threads.id WHERE threads.id = ".intval($_GET['id']))...
I have the following table: CREATE TABLE IF NOT EXISTS `notes` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `uid` int(10) unsigned NOT NULL DEFAULT '0', `note` text, PRIMARY KEY (`id`) ) INSERT INTO `notes` (`id`, `uid`, `note`) VALUES (1, 1, 'noteteeext'), (2, 1, 'notenotenotenote'); As you can see i have 2 rows with uid=1 but i...
We had an applicationg running using MySql. We found MySql was not suitable for our app after we found that it didnt support some of the GIS capability that PostGIS has (note: mysql only supports minimum-bounding rectangle GIS search). So we changed our DB to PostgreSQL. We then found out that Postgresql 8.2 running on Windows is so muc...
I have two arrays of attributes that describe products in the database: $sizes = array( 'Shirt Size' => array('Small', 'Medium', 'Large'), 'Sleeve Size' => array('Short', 'Long') ); // Level 1 $colors = array( 'Shirt Color' => array('Black', 'Red'), 'Slee...