MySQL Alter syntax to drop a column if it exists
What is the syntax to drop a column in a MySQL table, if that column exists on version 4.0.18? ...
mysql
Selecting a maximum order number in SQL
I have a table that records a sequence of actions with a field that records the sequence order: user data sequence 1 foo 0 1 bar 1 1 baz 2 2 foo 0 3 bar 0 3 foo 1 Selecting the first item for each user is easy enough with WHERE sequence = '0' but is there a way to selec...
How can I create a MySQL JOIN query for only selecting the rows in one table where a certain number of references to that row exist in another table?
I have two tables in my database, called ratings and movies. Ratings: | id | movie_id | rating | Movies: | id | title | A typical movie record might be like this: | 4 | Cloverfield (2008) | and there may be several rating records for Cloverfield, like this: | 21 | 4 | 3 | (rating number 21, on movie number 4, givin...
How can I use the TRUNCATE sentence avoiding MySql to write it into the binlog?
It seems MySQL does not support the flag "NO_ WRITE_ TO_ BINLOG" for TRUNCATE. So I have to wait until delay is 0, then stop the replication, make the TRUNCATE of the table/s, reset the master, and then start replication again. Really painful. Any other suggestion? ...
Grabbing rows from multiple tables as single result?
I have 2 tables. Table1 has fields A, B, C, D and Table2 has fields A, B. Field A and B of both tables have same record type. I would like to grab the records from both tables of fields A and B as single result. Is there any Query or Function in PHP+MySql? Thanks... ...
SQL Absolute value across columns
I have a table that looks something like this: word big expensive smart fast dog 9 -10 -20 4 professor 2 4 40 -7 ferrari 7 50 0 48 alaska 10 0 1 0 gnat -3 0 0 0 The + and - values are associated with the...
Compiling in c++ with mysql, pthreads and gtk
Have someone ever done this before??? I am trying to use MinGW to compile a program using the MySQL libraries. I keep getting the message that the function 'rint' is redefined. Ok it's true that the function is in both files config-win.h, from MySQL and math.h from the standard library, but both of them are suppose to be libraries with ...
Can MySql 5.0 have a view of a table located on another server
Can MySql 5.0 views use tables that are located on another server? What is the syntax for creating such a view? ...
Pathing in a non-geographic environment
For a school project, I need to create a way to create personnalized queries based on end-user choices. Since the user can choose basically any fields from any combination of tables, I need to find a way to map the tables in order to make a join and not have extraneous data (This may lead to incoherent reports, but we're willing to live...
Problem with conflict between mysql and math.h
Hello there... The problem is that the compiler says that there is a redefinition of a function between a library that belongs to MySQL and math.h from the std library. I have been over this for two days and I still can't figure it out. Has this ever happened to anyone? This is the output from the compiler C:\mingw\bin\mingw32-m...
How can I copy a mySQL Database in ruby on rails?
We are making a Ruby On Rails webapp where every customer gets their own database. The database needs to be created after they fill out a form on our website. We have a template database that has all of the tables and columns that we need to copy. How can I do this in programatically from ruby on rails? ...
Add new field to multiple table at once?
I want to add same field to multiple tables. Can I do this with single query in MySql? Thanks... ...
PHP: mysql_connect() won't work via command line
Hi all, I have a script which calls mysql_connect() to connect to a MySQL DB. When I run the script in a browser, it works as expected. However, when I run it from a command line I recieve the error Call to undefined function mysql_connect() This seems completely paradoxical. Anyone have any ideas as how I can run it from the comman...
Help migrating from Visual FoxPro to MySQL
Our company is planning to migrate to VB.net and database to My SQL. (our system and database currently is VFP). We are in outsourcing business and one of our service is installing a program in client's workstation. This program is a clone of our main program that would generate reports for them. We update the data by sending them the db...
What techniques are most effective for dealing with millions of records?
I once had a MySQL database table containing 25 million records, which made even a simple count(*) query takes minute to execute. I ended up making partitions, separating them into a couple tables. What i'm asking is, is there any pattern or design techniques to handle this kind of problem (huge number of records)? Is MSSQL or Oracle bet...
MySQL - force not to use cache for testing speed of query
Hi all I'm testing the speed of some queries in MySQL. The database is caching these queries making it difficult for me to get reliable results when testing how fast these queries are. Is there a way to disable caching for a query? System: MySQL 4 on Linux webhosting, I have access to PHPMyAdmin. Thanks ...
Can PHP PDO Statements accept the table name as parameter?
Why can't I pass the table name to a prepared PDO statement? $stmt = $dbh->prepare('SELECT * FROM :table WHERE 1'); if ($stmt->execute(array(':table' => 'users'))) { var_dump($stmt->fetchAll()); } Is there another safe way to insert a table name into a SQL query? With safe I mean that I don't want to do $sql = "SELECT * FROM $tab...
MySQL driver segfaulting under mod_perl - where to look for issue
I have a webapp that segfaults when the database in restarted and it tries to use the old connections. Running it under gdb --args apache -X leads to the following output: Program received signal SIGSEGV, Segmentation fault. [Switching to Thread -1212868928 (LWP 16098)] 0xb7471c20 in mysql_send_query () from /usr/lib/libmysqlclient.so.1...
How do I display the first letter as uppercase?
I have fname and lname in my database, and a name could be stored as JOHN DOE or john DOE or JoHN dOE, but ultimately I want to display it as John Doe fname being John and lname being Doe ...
Importing data to MySQL from MS SQL
Hello, i want to import data from mssql, run it through some sort of regexp to fitler out stuff, and import it into mysql. I then, for each query, wish to display a relevant image from a third database. What would be the easiest way to do this, importing and linking wise? Thankyou Clarification: It is a php application to filter data ...