GOT error for the following query in MYSQL(version5.1) SELECT year,month,sum(fact_1),sum(fact_2),sum(fact_3),sum(fact_4) from( select year,month,fact_1,fact_2,0 as fact_3,0 as fact_4 from table_1 intersect select year,month,0 as fact_1,0 as fact_2,fact_3,fact_4 from table_2 ) as combined_table group by month,year Error Line with code#...
I'm working with a table in MySQL that contains the following columns: id, january, february, march, april, etc The data in the table looks like this: aa, 0, 0, 1, 0 ab, 1, 0, 1, 0 ac, 1, 1, 0, 0 ad, 1, 1, 1, 0 To query it, I could easily do this: select * from table where january = 1 and february = 1 The result would be: ac, 1...
For example: select * from T where T.id IN(4,78,12,45) I want the returned record set just order by the position in the 'IN' clause. How can I do this? ...
I have a web app that lets a client define a daily rate for their service. For any given day, the client can enter two rates, an hourly rate (rateTypeId=1) and a dailyRate (rateTypeId=2). These rates are often assigned in advance, and they often change. I need to track all assignments, but only pull the latest assigned rate. I have tw...
Just had a discussion at work about the merits of using PostgreSQL over MySQL and vice-versa. Does anyone have any practical experience where there is a valid reason to use one over the other? Some people were saying that Postgre is better for security purposes whereas MySQL is becoming more feature rich... I'm not sure what to make of...
Hi all. I have a problem wich is a little strange. My page contains a html link which refreshes the page and calls a PHP variable. This variable appends a date string to the url string which is fed into a MySQL query, which grabs records matching this date. I think this is causing an injection as it sometimes deletes the user from the d...
I am new to php and MySQL. I have a site where a user can choose a state and display hospitals in that state. It works well in FireFox for all states, but in IE, when I have state with a couple hundred hospitals, the screen flashes and I eventually get a message that the page cannot be displayed. For smaller states with a few hospital...
I have a page that displays a user's current personal information and a handler that cycles through the form elements, filtering them through to the relevant mysql query. There are two tables, one that contains the master data, e.g. username, email, password hash, and one that has address data. However, the script doesn't work and I can...
If the value of @param is null, which is better to use: WHERE t.column = COALESCE(@param, '') WHERE t.column = IFNULL(@param, '') WHERE (@param IS NULL OR t.column = @param) Is the OR more expensive than comparing the column to a value that will return all values for the specified column? My understanding is that options 1 & 2 will ...
I've got a rails app using the master_slave_adapter plugin (http://github.com/mauricio/master_slave_adapter/tree/master) to send all selects to a slave, and all other statements to the master. Replication is setup using Mysql master / slave. I'm trying to validate that all the SQL statements are indeed going to the right place. Select...
MySQL allows the value of "0000-00-00" for date fields, however ActiveRecords treats an assignment to a Date value with that string as being nil. I have a legacy application that relies on that exact field content for the notion of "Never", so if the customer last payment date is "0000-00-00" it acknowledges as being "Never" paid. Is t...
Hi all, If I'm getting my data from Mysql like so: $result = $dbConnector->Query("SELECT * FROM branches, businesses WHERE branches.BusinessId = businesses.Id ORDER BY businesses.Name"); $resultNum = $dbConnector->GetNumRows($result); if($resultNum > 0) { for($i=0; $i < $resultNum; $i++) { $r...
I have a MySQL database with 3 tables: The main table of records called "tracks" (as in music) A tags table called "tags" A join table for the two called "taggings" The tags table is basically a list of genres, which is pre-defined. A track can then have 1 or more tags (via the join table). The idea is, that the user checks off the ...
I'm trying to set up a series of history triggers to automatically collect history for a given table via triggers. I want to use triggers as this guarantees I capture all changes regardless if someone forgets about saving it in the app. My problem is that I have this trigger. CREATE TRIGGER `db`.`delete_history_trigger` BEFORE DELETE ON...
I am very interested in postgesql because they have a uuid data type. I have done searches around SO and many people say that Postgres is harder to maintain and manage. Why is this? Is it because postgres needs more configuration, is it because postgres does not have a GUI? ...
I have a MySQL database that I recently migrated to another server. Unfortunately, MySQL dumps its data in Latin1 with any UTF-8 characters represented by composite bytes (ex. – instead of —). Is it possible to run a simple query or script that would convert these composite bytes to UTF-8 within my tables? It's impossible to do it row...
I need to join an existing table with date entries with a list of months in a given period. For this I would need to temporary generate a list of months within this period. Is there a command in mysql for doing this? Temporary Table, stored procedure? Thx, martin ...
Hi, is there an easy way to import a large .sql file of my database on local host without using phpmyadmin. I am using the WAMP server. Thanks for any help. ...
I am selecting everything from prod_drop and joining 2 other tables for additional data. I do not want any of the rows from prod_drop to be repeated, I just want 1 row for each prod_drop row. Here is my query: SELECT prod_drop.* , prod_drop_products.product_id , cart_product.product_image_sm FROM prod_drop LEFT JOI...
Im revising my auto-complete style search script. The site has 2 categories... movies and tv shows. Currently, the auto-complete query string looks like this: SELECT * FROM movies WHERE mov_title LIKE '%" . $queryString . "%' AND mov_status = 1 AND mov_incomplete = 0 ORDER BY mov_type, mov_title LIMIT 10 The problem with this is if t...